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.
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.

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.
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, <>.
11 .	Click <>.
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, <>, 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.

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
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.
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?