OBIEE 11G Creating Level-Based Measures

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 share measure.
To create level-based measures and a share 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.
Screenshot for Step
2 . On the General tab, enter Product Total Revenue in the Name field.
Screenshot for Step
3 . Click the Column Source tab.
Screenshot for Step
4 . Select Derived from existing columns using an expression.
Screenshot for Step
5 . Open the Expression Builder.
Screenshot for Step
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.
Screenshot for Step
7 . Click OK to close Expression Builder.
8 . Click the Levels tab.
Screenshot for Step
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.
Screenshot for Step
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.
Screenshot for Step
11 . Repeat the steps to create a second level-based measure:
Name
Logical Dimension
Logical Level
Product Type Revenue H2 Product Product Type
Screenshot for Step
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.
Screenshot for Step

Create a Share Measure

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, name the logical column Product Share.
Screenshot for Step
3 . On the Column Source tab, select "Derived from existing columns using an expression."
Screenshot for Step
4 . Open the Expression Builder.
Screenshot for Step
5 . In the Expression Builder, Select Functions > Mathematic Functions > Round.
Screenshot for Step
6 . Click Insert selected item. The function appears in the edit box.
Screenshot for Step
7 . Click Source Number in the formula.
Screenshot for Step
8 . Enter 100* followed by a space.
Screenshot for Step
9 . Insert Logical Tables > F1 Revenue > Revenue.
Screenshot for Step
10 . Using the toolbar, click the Division button. Another set of angle brackets appears, <>.
Screenshot for Step
11 . Click <>.
Screenshot for Step
12 . Insert Logical Tables > F1 Revenue > Product Total Revenue. Recall that this is the total measure for the hierarchy.
Screenshot for Step
13 . Click between the last set of angle brackets, <>, and enter 1. This represents the number of digits of precision with which to round the integer.
Screenshot for Step
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.
Screenshot for Step
16 . Click OK to close the Logical Column dialog box. The Product Share logical column is added to the business model.
Screenshot for Step
17 . Add the Product Share measure to the Base Facts presentation table.
Screenshot for Step
18 . Save the repository. Check consistency. You should receive the following message.
Screenshot for Step
If there are consistency errors or warnings, correct them before you proceed.
19 . Close the repository.

Test Your Work

1 . Return to Fusion Middleware Control and load the BISAMPLE repository. If you need help, click here to review steps from earlier in this tutorial.
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

Screenshot for Step
4 . For the Product Share column, select Column Properties.
Screenshot for Step
5 . On the Data Format tab, select Override Default Data Format.
Screenshot for Step
6 . Change Treat Numbers As to Percentage and set Decimal Places to 2. Deselect Use 1000's separator.
Screenshot for Step
7 . Click OK to close the Column Properties dialog box.
8 . Sort Product Share in descending order.
Screenshot for Step
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.
Screenshot for Step
10 . Sign out of Oracle BI.
11 . Click OK when you receive the message: Are you sure you want to navigate away from this page?

No comments:

Post a Comment

Popular Posts