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.
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 . On the General tab, enter Product Total Revenue in the Name field.
3 . Click the Column Source tab.
4 . Select Derived from existing columns using an expression.
5 . Open the Expression Builder.
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.
7 . Click OK to close Expression Builder.
8 . Click the Levels tab.
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.
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.
11 . Repeat the steps to create a second level-based measure:
|Name||Logical Dimension||Logical Level|
|Product Type Revenue||H2 Product||Product Type|
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.
For an in-depth understanding of OBIEE click on
- OBIEE 11g Tutorials
- New Features in OBIEE11g
- Checking Out a Project in OBIEE 11g
- Making Changes to the Repository in OBIEE
- Setting Up a Multiuser Development Environment in OBIEE