Level-Based Measures in OBIEE

In this set of steps you create level-based measures that calculate total dollars at various levels in the Product hierarchy, and then use a level-based measure to create a shared measure. To create level-based measures and a shared measure, you perform the following steps: • Open the Repository in Offline Mode • Create Level-Based Measures • Create a Share Measure • Test Your Work

Open the Repository in Offline Mode

1 . Return to the Administration Tool, which should still be open. If not, select Start > Programs --> Oracle Business Intelligence --> BI Administration. 2 . Select File --> Open --> Offline. 3 . Select BISAMPLE.rpd and click Open. Do not select any BISAMPLE repository with an extension, for example, BISAMPLE_BI0001.rpd. Recall that these are the repositories that have been loaded into Oracle BI Server memory. 4 . Enter BISAMPLE1 as the repository password and click OK to open the repository.

 

 

Desired to gain proficiency on OBIEE Training? Explore the blog post on OBIEE  training to become a pro in "OBIEE Training Online"

Create Level-Based Measures

1 . In the Business Model and Mapping layer, right-click the F1 Revenue table and select New Object --> Logical Column to open the Logical Column dialog box.

2

2 . On the General tab, enter Product Total Revenue in the Name field.

2

3 . Click the Column Source tab.

2

4 . Select Derived from existing columns using an expression.

2

5 . Open the Expression Builder.

2

6 . In the Expression Builder, add Logical Tables > F1 Revenue > Revenue to the expression. Recall that the Revenue column already has a default aggregation rule of Sum.

2

7 . Click OK to close Expression Builder.

8 . Click the Levels tab.

2

9 . For the H2 Product logical dimension, select Product Total from the Logical Level drop-down list to specify that this measure should be calculated at the grand total level in the product hierarchy.

2

10 . Click OK to close the Logical Column dialog box. The Product Total Revenue measure appears in the Product Total level of the H2 Product logical dimension and the F1 Revenue logical fact table.

2

11 . Repeat the steps to create a second level-based measure:

NameLogical DimensionLogical Level
Product Type RevenueH2 ProductProduct Type

2

12 . Expose the new columns to users by dragging Product Total Revenue and Product Type Revenue to the Base Facts presentation table in the Sample Sales subject area in the Presentation layer. You can drag the columns from either the H2 Product logical dimension or the F1 Revenue logical table.

2

For an in-depth understanding of OBIEE click on