Create and Manage Dimensions with Level-Based Hierarchies

Each business model can have one or more dimensions, each dimension can have one or more logical levels, and each logical level has one or more attributes (columns) associated with it.

The following sections explain how to create dimensions:

About Level-Based Hierarchies

A dimension contains two or more logical levels. When creating logical levels, you should create a Grand Total level and then create child levels, working down to the lowest level.

The following are the parts of a dimension:

Grand Total level

The Grand Total level represents the sum of all totals for a dimension. Each dimension can have just one Grand Total level. The Grand Total level doesn't contain dimensional attributes and doesn't have a level key. You can associate measures with a Grand Total level. The aggregation level for those measures is the grand total for the dimension. The Grand Total level can exist without any columns.
Level

Levels must have at least one column. You don't need to explicitly associate all of the columns from a table with logical levels. Any column that you don't associate with a logical level is automatically associated with the lowest level in the dimension that corresponds to that dimension table. You must associate all logical columns in the same dimension table with the same dimension.

You can have an unlimited number of levels in a dimension. When using extremely complex SQL queries, a few levels in a dimension can impact query performance.
Hierarchy

Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level. For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with a common leaf level of Day. In this example, Day has two named parent levels, Fiscal Year and Calendar Year that are both children of the All root level.

In the Business Model and Mapping layer, logical hierarchies aren't defined as independent metadata objects, unlike hierarchies in the Presentation layer. Logical hierarchies exist implicitly through the relationships between levels.

You can define intermediate levels in your hierarchies to avoid having very large numbers of members at one level. For example, if you're creating a Product dimension for an automotive company that tracks data on 500 different car models, you might want to create some finer-grained hierarchical levels such as Minivans, Subcompacts, and Midsize Sedans. You could improve query performance and make reports and diagrams easier to read and navigate.
Level keys

Each logical level, except the Grand Total level, must have one or more attributes that compose a level key. The level key defines the unique elements in each logical level. You must associate the dimension table logical key with the lowest level of a dimension.

A logical level can have multiple level keys. When a logical level has multiple level keys, specify a key as the primary key for the level. All dimension sources that have aggregate content at a specified level need to contain the column that's the primary key of that level. Each logical level should have one level key that's displayed when an Oracle BI Server user selects the object to drill down. You can use any level key to provide user access to the level.

You must create an unique level key. Month isn't an unique level key. To create an unique level key with month include the year attribute as part of the key.

If you don't ensure that your level key is unique by including higher-level attributes, then queries might return unexpected results. For example, when the Oracle BI Server needs to combine result sets from multiple physical queries, some expected rows might be dropped because they aren't considered unique according to the level key definition.

Create meaningful level keys using common business keys such as Month_name='2010 July', rather than generated surrogate keys such as time_key='1023793'. The generated surrogate keys are physical artifacts that only apply to a single instance of a source table. A business key can map to any physical instance for that logical column, for example, month_name might map to a detailed table, an aggregate table from an aggregate star, or a column in a federated spreadsheet. The Physical layer uses the surrogate keys in the joins. Using a business key doesn't impose a performance or flexibility penalty in the business model.
Time dimensions and chronological keys

You can identify a dimension as a time dimension. At least one level of a time dimension must have a chronological key. Use the following guidelines when setting up and using time dimensions:
- At least one level of a time dimension must have a chronological key, see Select and Sort Chronological Keys in a Time Dimension.
- All time series measures using the AGO, TODATE, and PERIODROLLING functions must be on time levels. AGO, TODATE, and PERIODROLLING aggregates are created as derived logical columns.
- AGO, TODATE, and PERIODROLLING functionality isn't supported either on fragmented dimensional logical table sources, or on fact sources fragmented on the same time dimension. Fact sources may be fragmented on other dimensions.
  
  See About Time Series Functions.
Unbalanced or ragged hierarchy
An unbalanced or ragged hierarchy is a hierarchy where the leaves (members with no children) might not have the same depth. For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.

User applications can use the ISLEAF function to determine whether to allow moving down from any particular member.

A missing member is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all missing nodes together.

Unbalanced hierarchies aren't necessarily the same as parent-child hierarchies. Parent-child hierarchies are unbalanced by nature. Unbalanced level-based hierarchies are possible.
Skip-level hierarchy
A skip-level hierarchy is a hierarchy where there are members that don't have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city Washington, D.C. doesn't belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington, D.C.) and below.

In a query, skipped levels aren't displayed, and don't affect computations. When sorted hierarchically, members appear under their nearest ancestors.

A missing member at a particular level is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all skip-level nodes together.

The image shows a hierarchy with both unbalanced and skip-level characteristics. For example, A-Brand 4, B-LOB 3, and Type 5 are unbalanced branches, while skips are present between A-Brand 2 and Type 3, B-LOB 2 and Product 6, and others.

Description of the illustration rgd_hier.png

About Using Dimension Hierarchy Levels in Level-Based Hierarchies

Learn how to use dimension hierarchical levels.

Dimension hierarchical levels can be used to perform the following actions:

Set up aggregate navigation.
Configure level‐based measure calculations, see Level-Based Measure Calculations.
Determine what attributes appear when Oracle BI Presentation Services users drill down in their data requests.

Manually Create Dimensions, Levels, and Keys with Level-Based Hierarchies

Learn how to create and manage hierarchy level in level-based hierarchies.

Perform the tasks described in the following sections:

Create Dimensions in Level-Based Hierarchies

After creating a dimension, each dimension can be associated with attributes (columns) from one or more logical dimension tables and level-based measures from logical fact tables.

After you associate logical columns with a dimension level, the tables in which these columns exist appear in the Tables tab of the Dimension dialog. See Work with Physical Hierarchy Objects.

It's a best practice to ensure that the physical hierarchy type set in the Physical layer matches the dimension properties you select in the Business Model and Mapping layer. In addition, you must ensure that the Ragged and Skipped Levels dimension properties are set correctly for queries to work.

In the Business Model and Mapping layer of the Model Administration Tool, right-click a business model, select New Object, select Logical Dimension, and then select Dimension with Level-Based Hierarchy.

This option is only available when there is at least one dimension table that has no dimension associated with it.
In the Logical Dimension dialog, in the General tab, type a name for the dimension.

The Default root level field is automatically populated after you associate logical columns with a dimension level.
If the dimension is a time dimension, select Time.
If the dimension is an unbalanced dimension, select Ragged.
If the dimension is a skip-level dimension, select Skipped Levels.
Optional: Type a description of the dimension.
Click OK.

Create Logical Levels in a Dimension

When creating logical levels in a dimension, you also create the hierarchy by identifying the type of level and defining child levels.

See Automatically Create Business Model Objects.

If you're defining the level as a Grand Total level, leave this field blank. The default value is 1.

The number doesn't have to be exact, but ratios of numbers from one logical level to another should be accurate. For relational sources, you can retrieve the row count for the level key and use that number as the number of elements. For multidimensional sources, you can use the number of members at that level.

The Oracle BI Server uses this number when selecting which aggregate source to use. For example, when aggregate navigation is used, multiple fact sources exist at different grains. The Oracle BI Server multiplies the number of elements at each level for each qualified source as a way to estimate the total number of rows for that source. Then, the Oracle BI Server compares the result for each source and selects the source with the lowest number of total elements to answer the query. The source with the lowest number of total elements is assumed to be the fastest.

In the Business Model and Mapping layer of the Model Administration Tool, right-click a dimension and select New Object, then select Logical Level.
In the Logical Level dialog, in the General tab, specify a name for the logical level.
For Number of elements at this level, specify the number of elements that exist at this logical level.
Choose one of the following options, if appropriate:
- If the logical level is the Grand Total level, select Grand total level. There should be only one Grand Total level for a dimension.
- If measure values at a particular level fully constitute aggregated measures at its parent level, select Supports rollup to higher level of aggregation
Click Add to define child logical levels.
In the Browse dialog, select the child logical levels and click OK.
In the Child Level pane, remove a previously defined child level, select the level in the Child Levels pane and click Remove.
Optional: Type a description of the logical level.
Click OK.

Associate a Logical Column and Its Table with a Dimension Level

After you create all logical levels within a dimension, drag and drop one or more columns from the dimension table to each logical level, except the Grand Total level.

The first time you drag a column to a dimension it associates the logical table to the dimension. The drag and drop action associates the logical column with that level of the dimension. To associate the logical level with that logical column, drag a column from one logical level to another.

You must associate the logical column or columns that comprise the logical key of a dimension table with the lowest level of the dimension.

After you associate a logical column with a dimension level, the tables in which these columns exist appear in the Tables tab of the Dimensions dialog.

For examples, see:

For time dimensions, ensure that all time-related logical columns in the source table are defined in the time dimension. For example, if a time-related logical table contains the columns Month Name and Month Code, you must ensure that both columns are dragged to the appropriate level within the dimension. The image shows how to associate logical columns with a logical level.

Description of the illustration dim_col_map.gif

In the Business Model and Mapping layer of the Model Administration Tool, double-click a dimension to verify tables that are associated with a dimension.
In the Dimensions dialog, click the Tables tab.

The Tables tab list contains tables that you associated with that dimension. If you created level-based measures, the list only includes one logical dimension table and one or more logical fact tables.
Click OK or Cancel to close the Dimensions dialog.

Level-Based Measure Calculations

A level-based measure is a column whose values are always calculated to a specific level of aggregation.

You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue. For example, a company might want to measure its revenue based on the country, region, and city.

When a query containing a presentation hierarchy includes a level-based measure column, and the query grain is higher than the level of aggregation specific to the column, the query results return null. If the request only contains ordinary columns and no hierarchical columns, the level-based measure isn't replaced with null.

The AllProductRevenue measure is an example of a level-based measure at the Grand Total level. Level-based measures allow a single query to return data at multiple levels of aggregation. Level-based measures are also useful in creating share measures, calculated by taking some measure and dividing it by a level-based measure to calculate a percentage. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

For example, to set up these calculations, you need to build a dimensional hierarchy in your repository that contains the Grandtotal, Country, Region, and City levels. This hierarchy contains the metadata that defines a one-to-many relationship between Country and Region and a one-to-many relationship between Region and City. For each country, there are many regions, but each region is in only one country. Similarly, for each region, there are many cities, but each city is in only one region.

After building a dimensional hierarchy, you need to create three logical columns one each for CountryRevenue, RegionRevenue, and CityRevenue. The columns use the Revenue logical column as its source. The Revenue column has a default aggregation rule of SUM and has sources in the underlying databases.

Drag the CountryRevenue, RegionRevenue, and CityRevenue columns into the Country, Region, and City levels, respectively. Each query that requests one of these columns returns the revenue aggregated to its associated level.

The image shows the business model in the Business Model and Mapping layer for this example.

Description of the illustration level.gif

Grand Total Dimensional Hierarchy

Learn how to use a grand total dimensional hierarchy with revenue.

If you've a product dimensional hierarchy with TotalProducts (Grand Total level), Brands, and Products levels, and a Revenue column defined with a default aggregation rule of Sum, you can then create an AllProductRevenue logical column. The AllProductRevenue column uses Revenue as its source. Drag the AllProductRevenue column to the Grand Total level. Each query that includes the AllProductRevenue column returns the total revenue for all products. The value is returned regardless of any constraints on Brands or Products.

If you've constraints on columns in other tables, the grand total is limited to the scope of the query. For example, if the scope of the query asks for data from 1999 and 2000, the grand total product revenue is for all products sold in 1999 and 2000.

If you've three products, A, B, and C with total revenues of 100, 200, and 300 respectively, then the grand total product revenue is 600, the sum of each product's revenue. If you've set up a repository as described in this example, the following query produces the results listed:

SELECT product, productrevenue, allproductrevenue
FROM sales_subject_area
WHERE product IN 'A' or 'B'

The results are as follows:

PRODUCT;;PRODUCTREVENUE;;ALLPRODUCTREVENUE
A;;;;;;;;100;;;;;;;;;;;;;600
B;;;;;;;;200;;;;;;;;;;;;;600

The AllProductRevenue column always returns a value of 600, regardless of the products on which the query constrains.

Identify the Primary Key for a Dimension Level

Use the Keys tab in the Logical Level dialog to identify the primary key for a level.

In the Business Model and Mapping layer of the Model Administration Tool, expand a dimension and then expand the highest level (Grand Total level) of the dimension.
Double-click a logical level below the Grand Total level.
In the Logical Level dialog, click the Keys tab.
In the Keys tab, from the Primary key list, select a level key.

If only one level key exists, it's the primary key by default.
To add a column to the list, perform the following steps:
1. In the Logical Level dialog, click New.
2. In the Logical Level Key dialog, type a name for the key.
3. In the Logical Level Key dialog, select a column or click Add.
4. If you click Add, in the Browse dialog, select the column, and then click OK.
  
  The column you selected appears in the Columns list of the Logical Level Key dialog and is automatically selected.
You can't use a derived logical column that's the result of a LOOKUP function as part of a primary logical level key. This limitation exists because the LOOKUP operation is applied after aggregates are computed, but level key columns must be available before the aggregates are computed because they define the granularity at which the aggregates are calculated.

You can use a derived logical column that's the result of a LOOKUP function as a secondary logical level key.
If the level is in a time dimension, you can select chronological keys and sort the keys by name.
(Optional) Type a description for the key and then click OK.
Repeat Step 2 through Step 7 to add primary keys to other logical levels.
In the Logical Level dialog, click OK.

Select and Sort Chronological Keys in a Time Dimension

At least one level of a time dimension must have a chronological key. Although you can select one or more chronological keys for any level and then sort keys in the level, only the first chronological key is used.

Pay attention when the column order in a chronological key has many columns. You set the column order using a SQL ORDER BY clause on the columns reflecting the real-world chronological order in the Chronological Key dialog of the Model Administration Tool. Since the range for quarters is 1 to 4 with 4 quarters in a year, using an ORDER BY clause with the Quarter before the Year (Quarter, Year) is incorrect. The incorrect order shows all first quarters across all years, before displaying any second quarters, and so on. To correct the results, use (Year, Quarter) in the ORDER BY clause.

To recognize a dimension as a time dimension, you must select Time on the General tab of the Dimension dialog.

In the Business Model and Mapping layer of the Model Administration Tool, expand a time dimension and then expand the highest level (Grand Total level) of the dimension.
Double-click a logical level below the Grand Total level.
In the Logical Level dialog, click the Keys tab.
To select a chronological key, in the Keys tab, select the Chronological Key option.
To sort chronological keys, in the Keys tab, select a chronological key and then click Edit.
In the Chronological Key dialog, select a chronological key column, click Up or Down to reorder the column, and then click OK.

Add a Dimension Level to the Preferred Drill Path

You can use the Preferred Drill Path tab to identify the drill path to use when Oracle BI Presentation Services users drill down in their data requests.

You should use this only to specify a drill path that's outside the normal drill path defined by the dimensional level hierarchy. It's most commonly used to drill from one dimension to another. You can delete a logical level from a drill path or reorder a logical level in the drill path.

To add a dimension level to the preferred drill path, click Add to open the Browse dialog, then select the logical levels to include in the drill path. You can select logical levels from the current dimension, or from other dimensions.
Click OK to return to the Level dialog.

Add Sequence Numbers to a Time Dimension's Logical Level

Adding absolute or relative sequence numbers to time dimensions optimizes time series functions and in some cases improves query time.

By default Oracle BI Server uses a complex RANK Physical SQL expression to generate sequence numbers for time dimensions. Adding an absolute or relative sequence number to the time dimension's logical level provides direct column references in the Time dimension table that contain the precomputed results of the rank expressions. This mapping, while optional, generates a simpler query that's easier for Oracle BI Server to run against the data source.

Sequence numbers are enumerations of time dimensional members at a certain level. Use an enumeration without gaps (dense). The enumeration must correspond to a real time order, for example, you can enumerate the months in a year from 1 to 12.

The sequence number type options are:

Absolute - Choose this option to configure an absolute sequence number when the column enumerates the members of the time dimension without any reference, for example, calendar year.
Relative - Chose this option to configure relative sequence numbers when you've a column that enumerates members of the time dimension relative to some parent level, for example, months in year from 1 to 12.

In the Business Model and Mapping layer of the Model Administration Tool, locate a time dimension and then double click a corresponding logical level.
In the Logical Level dialog, click the Sequence Numbers tab, specify the type of sequence numbers to add to the logical level.
Click OK.

Rules for Automatically Created Dimensions with Level-Based Hierarchies

The Create Dimensions option is only available if the selected logical table is a dimension table as defined by 1:N logical joins, and a dimension hasn't been associated with the table.

The following rules are applied:

An automatically created dimension uses the same name as the logical table, adding Dim as a suffix. For example, if a table is named Periods, the dimension is named Periods Dim.
A Grand Total level is automatically named logical_table_name Total. For example, the Grand Total level of the Periods Dim table is Periods Total.
When there are multiple tables in a source, the join relationships between tables in the source determine the physical table containing the lowest-level attributes. The lowest level in the hierarchy is named logical_table_name Detail. For example, the lowest level of the periods table is Periods Detail.
The logical key of the dimension table is mapped to the lowest level of the hierarchy and specified as the level key. This logical column should map to the key column of the lowest level table in the dimension source.
- If there are two or more physical tables in a source, the columns that map to the keys of those tables become additional logical levels. These additional level names use the logical column names of these key columns.
- The order of joins determines the hierarchical arrangement of the logical levels. The level keys of these new logical levels are set to the logical columns that map to the keys of the tables in the source.
If there are multiple logical table sources, the tool uses attribute mappings and physical joins to determine the hierarchical order of the tables in the physical sources. For example, you might have three sources (A, B, C) each containing a single physical table and attribute mappings for 10, 15, and 3 attributes, respectively, not counting columns that are constructed from other logical columns. The following is a list of the results of creating a dimension for this table automatically:
- The Model Administration Tool creates a dimension containing four logical levels, counting the Grand Total and detail levels.
- The key of the table in source B, which has the greatest number of columns mapped and contains the column mapping for the logical table key, is the level key for the detail level.
- The parent of the detail level is the logical level named for the logical column that maps to the key of the physical table in source A.
- Any attributes that are mapped to both A and B should be associated with level A.
- The parent of level A should be the logical level named for the logical column that maps to the key of the physical table in source C.
- Any columns that are mapped to both A and C should be associated with level C.
Table joins in a physical source might represent a pattern that results in a split hierarchy. For example, the Product table can join to the Flavor table and a Subtype table. This would result in two parents of the product detail level, one flavor level and one subtype level.
You can't create a dimension automatically in the following situations:
- If a dimension with joins and levels has already been created, Create Dimension doesn't appear on the right-click menu.
- If the table isn't yet joined to any other table, the option isn't available because it's considered a fact table.
In a snowflake schema, if you use a table with only one source and create the dimension automatically, the child tables are automatically incorporated into a hierarchy. The child tables form intermediate levels between the Grand Total level and detail level. If more than one child table exists for a dimension table, the hierarchy is a split hierarchy.

Automatically Create Dimensions with Level-Based Hierarchies

You can set up a dimension automatically from a logical dimension table if a dimension for that table doesn't exist.

To create a dimension automatically, the Model Administration Tool examines the logical table sources and the column mappings in those sources and uses the joins between physical tables in the logical table sources to determine logical levels and level keys. As a best practice, create a dimension table after all the logical table sources have been defined for a dimension table.

In the Model Administration Tool, open a repository.
In the Business Model and Mapping layer, right-click a logical dimension table that isn't associated with any dimension.
From the right-click menu, select Create Logical Dimension, then select the Dimension with Level-Based Hierarchy or Dimension with Parent-Child Hierarchy.

The new dimension is displayed in the Business Model and Mapping layer.

Populate Logical Level Counts Automatically

You can use Estimate Levels to automatically populate level counts for one or more dimension hierarchies.

Level counts are used by the query engine to determine the optimal query plan and to improve overall system performance.

You must open the repository in online mode and ensure that the business model is available for queries. In the Business Model and Mapping layer, you can select any of the following logical layer elements, and then run the Estimate Levels command:

Business model. If you select the business model object, the Model Administration Tool attempts to check out all objects in the business model.
Dimension. Run a consistency check on dimensions to ensure that the dimension is logically sound.
A combination of business models and dimensions. You can select multiple dimensions and multiple business models individually.

When run, the Estimate Levels command also launches a consistency check on the level counts as described in the following list:

Checks that a level key is valid. Columns in levels have referential integrity.
Checks the parent-child relationship. If the parent level count is greater that the child level count, an error is returned.
Generates a run report that lists all the counts that were estimated and any errors or consistency warnings.
The queries and errors are logged in the obis1_query.log located in the DOMAIN_Home/servers/obis1/logs.

Set the log level at 4 or higher to write this information to the log file. See Diagnose and Resolve Issues in Administering Oracle Analytics Server.

In the Model Administration Tool, open a repository in online mode.
Right-click one or more business models and dimension objects, and select Estimate Levels.
In the Check Out Objects dialog, click Yes to check out the objects that appear in the list.

If you click No, the action terminates because you must check out items to run Estimate Levels.

In the Model Administration Tool dialog, a list of the dimension level counts and any errors or warning messages appear.

When you check in the objects, you can check the global consistency of your repository.