Creating a Repository Using the Oracle Business Intelligence Administration Tool ( part 5 )

LAB 3:Creating Calculation Measures

In this set of steps you use existing measures to created a derived calculation measure. To create a derived calculation measure you perform the following steps:

• Open the Repository in Offline Mode

• Create a Calculation Measure Derived from Existing Columns

• Create a Calculation Measure Using a Function

• Load the Repository

• Create and Run an Analysis

• Check the Query Log

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_BI0025.rpd. Recall that these are the repositories that have been loaded into Oracle BI Server memory.
4 .	Enter BISAMPLE as the repository password and click OK to open the repository.

Create a Calculation Measure Derived from Existing Columns

1 .	In the BMM layer, expand Sample Sales > F1 Revenue.
2 .	Right-click F1 Revenue and select New Object > Logical Column to open the Logical Column dialog box.
3 .	On the General tab, enter Actual Unit Price in the Name field.
4 .	Click the Column Source tab.
5 .	Select Derived from existing columns using an expression.
6 .	Click the Edit Expression button to open Expression Builder.
7 .	In the left pane select Logical Tables > F1 Revenue > Revenue.
8 .	Click the Insert selected item button to move the Revenue column to the right pane.
9 .	Click the division operator to add it to the expression.
10 .	In the left pane select Logical Tables > F1 Revenue and then double-click Units to add it to the expression.
11 .	Click OK to close Expression Builder. Notice that the formula is added to the Logical Column dialog box.
12 .	Click OK to close the Logical Column dialog box. The Actual Unit Price calculated measure is added to the business model.
13 .	Drag Actual Unit Price from the BMM layer to the Base Facts presentation table in the Presentation layer.
14 .	Save the repository and check consistency. Fix any errors or warnings before proceeding.

Create a Calculation Measure Using a Function

1 .	In the BMM layer, right-click F1 Revenue and select New Object > Logical Column to open the Logical Column dialog box.
2 .	On the General tab, enter Revenue Rank in the Name field.
3 .	Click the Column Source tab.
4 .	Select Derived from existing columns using an expression.
5 .	Click the Edit Expression button to open Expression Builder.
6 .	In the left pane select Functions > Display functions > Rank .
7 .	Click the Insert selected item button to move the Rank function to the right pane.
8 .	Click <<numExpr>> in the expression.
9 .	In the left pane select Logical Tables > F1 Revenue and then double-click Revenue to add it to the expression.
10 .	Click OK to close Expression Builder. Notice that the formula is added to the Logical Column dialog box.
11 .	Click OK to close the Logical Column dialog box. The Revenue Rank calculated measure is added to the business model.
12 .	Drag Revenue Rank from the BMM layer to the Base Facts presentation table in the Presentation layer.
13 .	Save the repository and check consistency. Fix any errors or warnings before proceeding.
14 .	Close the repository. Leave the Admin Tool open.

Load the Repository

1.	Return to Fusion Middleware Control Enterprise Manager, which should still be open. If not, open a browser and enter the following URL to navigate to Fusion Middleware Control Enterprise Manager: http://<machine name>/:7001/em In this tutorial the URL is http://localhost:7001/em
2.	If necessary, log in as an administrative user. Typically you use the administrative user name and password provided during the Oracle BI installation. In this example the user name is weblogic.
3 .	In the left navigation pane, expand Business Intelligence and click on coreapplication.
4 .	Click the Deployment tab.
5 .	Click the Repository sub tab.
6 .	Click Lock and Edit Configuration.
7 .	Click Close when you receive the confirmation message Lock and Edit Configuration - Completed Successfully.
8 .	Click Browse and navigate to the directory with the BISAMPLE repository.
9 .	Select the BISAMPLE.rpd file and click Open.
10 .	Enter BISAMPLE as the repository password and confirm the password.
11 .	Click Apply.
12 .	Confirm that the default RPD is now BISAMPLE with an extension. In this example the file name is BISAMPLE_BI0025.
13 .	Click Activate Changes.
14 .	Click Close when you receive the confirmation message Activate Changes - Completed Successfully.
15 .	Click Restart to apply recent changes to navigate to the Overview page (this may take a moment).
16 .	On the Overview page, click Restart.
17 .	Click Yes when you receive the message Are you sure you want to restart all BI components?
18 .	Allow the processing to complete.
19 .	Click Close when you receive the message Restart All - Completed Successfully.

Create and Run an Analysis

1.	Return to Oracle BI, which should still be open. If not, open a browser or browser tab and enter the following URL to navigate to Oracle Business Intelligence: http://<machine name>/:9704/analytics In this tutorial the URL is http://localhost:9704/analytics.
2.	If necessary, log in as an administrative user. Typically you use the administrative user name and password provided during the Oracle BI installation. In this example the user name is weblogic.
3 .	In the left navigation pane, under Create... Analysis and Interactive Reporting, select Analysis. Hint: If your session has not timed out, you can create a new analysis by selecting New > Analysis.
4 .	Select the Sample Sales subject area.
5 .	In the left navigation pane, expand the Base Facts folder and confirm that the Actual Unit Price and Revenue Rank columns are visible.
6 .	Create the following analysis by double-clicking column names in the Subject Areas pane: Products.Product Base Facts.Revenue Base Facts.Revenue Rank Base Facts.Units Base Facts.Actual Unit Price
7 .	Sort Revenue Rank in ascending order.
8 .	Click Results to view the analysis results. Please note that the Actual Unit Price calculation is correct, although it does not make sense from a business perspective. For example, the unit price for an LCD HD Television would not be 9 dollars. This is a result of the underlying sample data.

Check the Query Log

1 .	Return to Fusion Middleware Control Enterprise Manager, which should still be open, and check the query log. If you need help, click here to review steps from earlier in this tutorial.
2 .	Your log entry should look similar to the screenshot. Note that the division of Revenue by Units is calculated in the outer query block (D1.c2 / nullif ( D1.c1, 0) as c3 in this example). Because you defined the Actual Unit Price calculation using logical columns, the SUM aggregation rule is applied to the Revenue and Units columns first and then the division is calculated.
3 .	Leave Enterprise Manager open.