Solved, Level-Based Dimensions

The script generated by OLAP_PC_TRANSFORM.CREATE_SCRIPT creates a table that stores the values from the parent-child table in levels.

The resulting level-based dimension table includes the full lineage of every level value in every row. This type of dimension table is solved, because the fact table related to this dimension includes embedded totals for all level combinations.

If you want to enable parent-child dimension tables for access by the OLAP API, you must convert them to solved, level-based dimension tables. The OLAP API requires that dimensions have levels and that they include a GID (Grouping ID) column and an Embedded Total (ET) key column. GIDs and ET key columns are described in Example: Creating a Solved, Level-Based Dimension Table.

Figure 25-2, "Sample Solved, Level-Based Dimension Table Columns" illustrates how the parent-child relationships in Figure 25-1 would be represented as solved levels.

Figure 25-2 Sample Solved, Level-Based Dimension Table Columns

TOT_GEOG COUNTRY REGION    STATE CITY           DESCRIPTION     
-------- ------- --------- ----- -------        ----------------------
World    USA     Northeast  MA   Boston         Boston, MA
World    USA     Northeast  MA   Burlington     Burlington, MA
World    USA     Northeast  NY   New York City  New York, NY
World    USA     Southeast  GA   Atlanta        Atlanta, GA
World    USA     Northeast  MA                  Massachusetts
World    USA     Northeast  NY                  New York State
World    USA     Southeast  GA                  Georgia
World    USA     Northeast                      North East Region
World    USA     Southeast                      South East Region
World    USA                                    United States of America
World    Canada                                 Canada
World                                           World

When creating OLAP Catalog metadata to represent a solved, level-based dimension hierarchy, specify a solved_code of SOLVED,LEVEL_BASED, as described in Chapter 20, "CWM2_OLAP_HIERARCHY".