Introduction

This chapter explains how to work with logical dimension objects using level-based hierarchies in the Business Model and Mapping (BMM) layer of the Oracle BI Repository.

This chapter contains following topics:

Working with logical dimensions

Create logical dimensions with level-based hierarchies

Creating level-based measures

• Logical Dimension

1. Objectives of Logical Dimension

Introduce formal hierarchies into a business model, enabling Oracle BI Server to calculate useful measures and enabling users to drill down to more detail.

• Establish level of data groupings and calculations.

• Provide paths for drill down.

The graphic illustrates a Time dimension hierarchy, where Grand Total is the level representing the grand total for the dimension, Years is the first parent level, Quarters is a child level of Years, Month is a child level of Quarters, and Days is a child level of Months.

• A logical dimension represents a hierarchical organization of logical columns belonging to a single logical dimensional table.

• Common logical dimensions used in a business model are time periods, products, customers, suppliers and so on.

• Logical dimensions are created in the Business Model and Mapping Layer (BMM) and can be used in the presentation layer for use in analysis and dashboards.

• In each dimension, you organize dimension attributes into hierarchical levels which represent the organizational rules and reporting needs required for your business.

• They provide the structure that Oracle BI Server used to drill into and across dimensions to get more detailed views of the data.

• Dimension hierarchy levels are used to perform aggregate navigation, configure level-based measure calculations, and determine which attributes appear when Oracle BI users drill down in their data requests.

In 11g, you can add that hierarchy to the presentation level. Every hierarchy have certain levels and every attribute is associated with the certain hierarchy.

2. Types of logical dimension

There are two types of logical dimensions:

1. Dimensions with level-based-hierarchies (structured hierarchies)

Members of the same type occur only at a single level.

Oracle BI Server supports unbalanced (ragged), skip-level, and time hierarchies.

E.g. Time dimension Year is the highest level and quarter is the second level. Every level has number as the common type and they occur only at the year level. Each level is distinct. With 11g, the BI server actually supports unbalanced hierarchy.

An unbalanced hierarchy is a hierarchy in which the leaves (members without children) do not necessarily have the same depth. For example, a site can choose to have data for the current month at the day level, previous month’s data at the month level, and the previous five years of data at the quarter level.

A skip-level hierarchy is a hierarchy in which there are members that do not have a value for a particular ancestor level. For e.g., in a Country-State-City-District hierarchy, the city “Washington, D.C.” does not 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.

Time hierarchies provide special functionality for modelling time series data.

Dimensions with parent-child hierarchies (value hierarchies)

All members have the same type (for e.g. an organizational reporting hierarchy).

Manage employee hierarchy.

2. Pictorial representation of Ragged & Skipped Hierarchies

Ragged: All leaf nodes are not at the same level.

Skipped: A level is skipped between the two levels.

3. Database and OBIEE Representation

4. Creating Logical Dimension

Right-click the on business model and Select Logical Dimension->Dimension with Level-based hierarchy.

Alternately, right-click a logical dimension table and select Create Dimension.

1. Add a parent Level Object

• The hierarchy should be created after adding the logical dimension to the business model.

• The highest level of the hierarchy should be created first. Right-click the logical dimension and select New Object->Logical Level.

• Typically the first level you create is Grand Total level. Give the name for the level and select the Grand total level checkbox to indicate that this is the grand total level.

Add child level objects

Add subsequent levels in the hierarchy by right-clicking the level and select New Object->Child level.

3. Specify level columns

After creating the parent object and child objects, columns must be created for each level. Then the logical columns are associated with logical levels in the hierarchy.

The quickest way is to drag one or more columns from the logical dimension table to each level (except grand total level). The first time you drag a column to the dimension, it associates the logical table with logical dimension.

• All the columns in the dimension table must not be associated explicitly with a level. If it is not associated, the BI server considers it as detail level.

• No column can be associated with more than one level, although it may be the part of the level key of a lower level.

• If column pertains to more than one level, associate with highest level to which it belongs.

• No column except the Grand Total level can exist without one column associated with it.

• The Detail level (lowest level) must have the logical key of the dimension table that is associated with it, and the logical key of the dimension table must be the level key for the Detail level.

• The aggregate table holds time data at month level. The content level setting is very important in case of hierarchy and helps BI Server to process the query efficiently.

4. Create Level Keys

• Level keys define the unique elements in each level and provide the context for drill down.

• Each level except the Grand Total level must have one or more attributes that compose a level key.

• If a level has more than one key, you must specify which key is the primary key of that level.

• All dimension sources that have aggregate content at a specified level must contain the column that is the primary key of that level.

To create a level key follow the steps below:

1. Double-click a level to open the level properties window.

2. Click the keys tab.

3. Enter a name for the key and select the appropriate column or columns.

4. Each level should have one level key that is displayed when a user clicks to drill down. This may or may not be the primary key of the level.

5. To set the level key to be displayed, select the “Use for Display” check box in the Logical Level Key dialog box.

Alternate method is by right-clicking a level column and selecting New Logical Level Key.

In this example, there are two separate keys namely Month and Month Code. It is possible to create composite keys consisting of multiple columns.

Month code is the primary key and “Use for Display” is selected for the Month key. Therefore, when users drill down in a report from the next highest level, the default is to drill down to the month column rather than the Month code column.

5. Set the preferred drill path

• Different dimensions have different hierarchies. You can drill down from one dimension to another dimension by setting preferred drill path.

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

• You should use this to specify only a drill path that is outside the normal drill path defined by the dimension level hierarchy.

• It is used to drill down from one dimension to another. For example, from the Customer Detail level you set the preferred drill path to Product Type so that users can drill down to see products ordered by customers.

5. Level-Based Measure

A level-based Measure is a column whose values are calculated to a specific level of aggregation. For e.g., a company want to measure its revenue based on the year, quarter, month, or day.

To achieve this, you could set up logical columns to measure Total Revenue, Year Revenue, Quarter Revenue, Month Revenue and Day Revenue.

The total revenue measure is an example of a level-based measure at the Grand Total level. This would calculate all revenues across all years.

Level-based measures allow a single query to return data at multiple levels of aggregation. For e.g. single query can return total revenue by year, quarter, month, and day for a single customer.

1. To Create Level-Based Measure

A level based measure is created for example Grand Total level which refers to an existing logical fact column.

6. Share Measure

Level-based measures are used for creating share measures, which are calculated by taking a measure and dividing it by a level-based measure to calculate a percentage.

For example, you can divide “salesperson revenue for a specific month” divided by “total revenue for a particular month” to calculate the share of the monthly revenue that is generated by each salesperson.

Example:

In this example, the logical dimension is named Product and is identified by the icon with multiple arrows. The hierarchy contains five levels: Product Total, Type, Subtype, Generic, and Product Detail.

Level-based measures are associated with two levels of the hierarchy. Product Type Dollars is associated with the Type level. There are also other columns and keys associated with each level.

Selvi

Add comment