Home > About Dimensional Database ... > About Cubes > Data Sources for Cubes
![]() Previous |
![]() Next |
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.
You can map the measures to columns with these SQL data types:
NUMBER
INTEGER
DECIMAL
BINARY_FLOAT
BINARY_DOUBLE
VARCHAR2
NVARCHAR2
CHAR
NCHAR
DATE
TIMESTAMP
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
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. |
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:
Perform calculations on the relational data using any combination of functions and operators available in the OLAP expression syntax.
Create measures that are more aggregate than their relational sources. For example, suppose the Time dimension has columns for Day, Month, Quarter, and Year, and the fact table for Sales is related to Time by the Day foreign key column. In a basic mapping, you would store data in the cube at the Day level. However, you could aggregate it to the Month level during the data refresh. Using a technique called one-up mapping, you would map the cube to the Month column for Time, and specify a join between the dimension table and the fact table on the Day columns.
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
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