Skip Headers
Previous
Previous
 
Next
Next

Data Sources for Cubes

A cube typically corresponds to a single fact table or view. However, you can create more complex mappings using the OLAP expression syntax, which supports expressions, join conditions, and filters.

Although the dimension columns in a fact table typically contain only key values at the detail level, you can also map cubes to summary tables that contain the values from multiple levels. For example, a Time column might contain days, months, quarters, and years; a Geography column might contain cities, states, and countries. When a build rolls up the data in the cube from the detail level, the calculated values overwrite the loaded summary values, thereby correcting any inconsistencies.

Data Types

You can map the measures to columns with these SQL data types:


Note:

In compressed cubes, all measures inherit the data type of the cube. In uncompressed cubes, the measures inherit the data type of the cube, but you can override this default by specifying the data type for a particular measure.

Using Expressions

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

Using Join Conditions

In the tabular view, the mapping for each dimension includes a join condition. In the basic case where you are mapping the foreign keys in a fact table to the primary keys in the related dimension tables, you can leave the join condition blank. OLAP derives this information from the relational source tables when you save the mapping.

For example, OLAP provides this join condition for the TIME dimension in the UNITS_CUBE mapping:

GLOBAL.TIME_DIM.MONTH_ID = GLOBAL.UNITS_FACT.MONTH_ID

Using Filters

A filter applies a WHERE clause to the query that loads data from the relational source into the cube. You can use a filter to limit the rows to those matching a certain condition. This filter restricts the data to the year 2011:

GLOBAL.UNITS_FACT.MONTH_ID LIKE '2011%'

You can also use a filter to join two or more tables containing the measures. This filter joins the UNITS_FACT and PRICE_FACT tables in the Global schema on the Time (MONTH_ID) and Product (ITEM_ID) dimensions:

GLOBAL.PRICE_FACT.MONTH_ID=GLOBAL.UNITS_FACT.MONTH_ID AND GLOBAL.PRICE_FACT.ITEM_ID=GLOBAL.UNITS_FACT.ITEM_ID