Using Expressions in Cube Mappings

You can use expressions when mapping cubes to their relational sources. This capability enables you to perform tasks like these as part of data maintenance, without any intermediate staging of the data:

• Consolidate data in the cube at a higher detail level than the relational dimension keys.
• Perform calculations on the relational data using any combination of functions and operators available in the OLAP Expression Language.

Note: You must use the tabular view of the mapping canvas, not the graphical view.

Consolidating the Incoming Data

You can create dimensions that have a more aggregate base level than their relational sources. For example, you might have a Time dimension table with levels for Day, Month, Quarter, and Year. You may not want to store data in a cube at the Day level. However, Day is the dimension key and is related to the Day foreign key column in the fact table. You must specify this relationship in a join statement when omitting the base level data from the cube.

To map a cube above the detail level:

1. Create the cube dimensions with the desired levels and map them to the source dimension table.

   For example, create a Time dimension with levels for Month, Quarter, and Year, omitting the Day level.

2. Create the cube.

3. Using the tabular view, map the detail level of all the cube dimensions, including those with a base level higher than the source dimension tables.

4. Where the foreign key column in the fact table does not have a corresponding level in the cube dimension, enter a join condition between the fact table and the dimension table.

   For example, time_dim.day_key = fact_tbl.day_key.

Performing Calculations on the Incoming Data

You can perform calculations on the facts as they are loaded into a cube.

To calculate the facts of a measure:

1. Create the cube.

2. Using the tabular view, map all dimensions and measures to the source tables.

3. Edit the mapping of the measure.

For example, you might change UNITS_FACT.SALES to UNITS_FACT.SALES*1.06.

You can use row expressions, column expressions, and conditions, but not nested SQL queries.

For dimensions, you can create a top level without adding a top-level column to the source table. Time dimensions also require an expression for the TIME_SPAN and END_DATE attributes. For example, the mapping expressions used for a Total level (that is, all years) in the Time Dimension might look like this:

• Member: 'TOTAL'
• LONG_DESCRIPTION: 'Total'
• SHORT_DESCRIPTION: 'Total'
• END_DATE: TO_DATE('31-Dec-2007', 'dd-mon-yyyy')
• TIME_SPAN: 3646

Member, LONG_DESCRIPTION, and SHORT_DESCRIPTION are set to literal strings, END_DATE uses the TO_DATE function and TIME_SPAN is set to a number.

Related Topics

Creating Mappings to Data Sources
OLAP Expression Syntax: Syntax Elements
OLAP Expression Syntax: Analytic Functions
OLAP Expression Syntax: Row Functions

Copyright © 2003, 2007 Oracle. All rights reserved.