Creating Level-Based Measures in OBIEE 11G

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?

OBIEE11g: Create Prompts or Presentation Variables using "DIRECT DATABASE REQUEST"

Scenario:
We need to create a report using the Direct Database Request and also have to create a prompt to dynamically enter the values by the users. We all know that Direct Database Request is nothing but writing the query which hits the database directly through the RPD Connection Pool. This post explains you how to create the prompts in the database query itself. Though it is almost similar to creating a prompt on the database query minor tweaking has to be done to append this to Obiee.

1)    Create one Dashborad prompt with presentation variable as shown in the below screenshot and assign a default value in this presentation variable.

then Click on Ok

Save the Prompt with the desired name.

   2)  Click on New--->Select Analysis---> click on Create Direct data base request as shown in the below screenshot

Give the Connection pool name (It should match with the connection pool name in the RPD and no two connection pools should have the same name) and SQL statement as per requirement.

In the SQL query statement  pass the presentation variable name information as follows.

Query:

 select products.prod_id  ,products.prod_category , products.prod_name ,   sum(amount_sold) from products, sales  where products.prod_id=sales.prod_id and products.PROD_ID IN( @{VAR}{'13'}) group by products.prod_id  ,products.prod_category , products.prod_name

 3)    Now Create one Dashboard and place the report and Dashboard prompt on the  dashboard

       4)   Now select the values from the prompt,click on apply and see the values on the report are changing as per the selected prompt values.