Level Based Measures in OBIEE

A level-based measure is a column whose values are always calculated to a specific level of aggregation. It is useful to calculate share measures. A company wants to calculate Revenue share of each product, First you need to calculate Product Total (Revenue is aggregated to Product Total).

Creating Level-Based Measures

1. In the BMM 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. 
    
13. 
14. 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.

 Creating a Share Measure

1. In the BMM 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, name the logical column Product Share.

3. On the Column Source tab, select "Derived from existing columns using an expression".

4. Open the Expression Builder.

5. In the Expression Builder, Select Functions > Mathematic Functions > Round.

6. Click Insert selected item. The function appears in the edit box.

7. Click Source Number in the formula.

8. Enter 100* followed by a space.

9. Insert Logical Tables > F1 Revenue > Revenue.

10. Using the toolbar, click the Division button. Another set of angle brackets appears, <<expr>>.

11. Click <<expression>>.

12. Insert Logical Tables > F1 Revenue > Product Total Revenue. Recall that this is the total measure for the hierarchy.

13. Click between the last set of angle brackets, <<Digits>>, and enter 1. This represents the number of digits of precision with which to round the integer.

14. Check your work:

Round(100* "Sample Sales"."F1 Revenue"."Revenue" / "Sample Sales"."F1 Revenue"."Product Total Revenue" , 1) 

This share measure will allow you to run an analysis that shows how revenue of a specific product compares to total revenue for all products.

15. Click OK to close the Expression Builder. The formula is visible in the Logical Column dialog box.

16. Click OK to close the Logical Column dialog box. The Product Share logical column is added to the business model.

17. Add the Product Share measure to the Base Facts presentation table.

18. Save the repository. Check consistency. You should receive the following message.

If there are consistency errors or warnings, correct them before you proceed.

19. Close the repository.

 Testing Your Work

1. Return to Oracle Enterprise Manager and load the BISAMPLE repository.
2. Return to Oracle BI, which should still be open, and sign in.
3. Create the following analysis to test the level-based and share measures.

Products.Product
Base Facts.Revenue
Base Facts.Product Type Revenue
Base Facts.Product Share

4. For the Product Share column, select Column Properties.

5. On the Data Format tab, select Override Default Data Format.

6. Change Treat Numbers As to Percentage and set Decimal Places to 2. Deselect Use 1000's separator.

7. Click OK to close the Column Properties dialog box.
8. Sort Product Share in descending order.

9. Click Results. Notice that Product Type Revenue returns dollars grouped by Type even though the query is at a different level than Type; Product in this example. Product Share shows the percent of total revenue for each product sorted in descending order.