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

LAB 4 : Creating Logical Dimensions with Level-Based Hierarchies

In this set of steps you add logical dimension hierarchies to the business model. A logical dimension represents a hierarchical organization of logical columns belonging to a single logical dimension table. Logical dimensions can exist in the Business Model and Mapping layer and in the Presentation Layer. Adding logical dimensions to the Presentation layer exposes them to users, which enables users to create hierarchy-based queries. You implement four logical dimensions for ABC: Time, Product, Office, and Customer. Creating logical dimensions with hierarchies allows you to build level-based measures, define aggregation rules that vary by dimension, provide drill down on charts and tables in analyses and dashboards, and define the content of aggregate sources.To create logical dimensions with level-based hierarchies, you perform the following steps:

• Open the Repository in Offline Mode

• Create a Logical Dimension for Product

• Create Logical Levels

• Associate Logical Columns with Logical Levels

• Set Logical Level Keys

• Create a Logical Dimension for Time

• Associate Time Logical Columns with Logical Levels

• Create a Logical Dimension for Customer

• Set Aggregation Content for Logical Table Sources

• 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 BISAMPLE as the repository password and click OK to open the repository.

Create a Logical Dimension for Product

1 .	In the BMM layer, right-click the Sample Sales business model and select New Object > Logical Dimension > Dimension with Level-Based Hierarchy to open the Logical Dimension dialog box.
2 .	Name the logical dimension H2 Product .
3 .	Click OK. The logical dimension is added to the Sample Sales business model.

Create Logical Levels

1 .	Right-click H2 Product and select New Object > Logical Level.
2 .	Name the logical level Product Total .
3 .	Because this level represents the grand total for products, select the Grand total level check box. Note that when you do this, the Supports rollup to higher level of aggregation field is grayed out and protected.
4 .	Click OK to close the Logical Level dialog box. The Product Total level is added to the H2 Product logical dimension.
5 .	Right-click Product Total and select New Object > Child Level to open the Logical Level dialog box.
6 .	Name the logical level Product Brand.
7 .	Click OK to close the Logical Level dialog box. The Product Brand level is added to the logical dimension.
8 .	Repeat the steps to add the following child levels: Product LOB as a child of Product Brand Product Type as a child of Product LOB Product Detail as a child of Product Type Use the screenshot as a guide:

Associate Logical Columns with Logical Levels

1 .	Expand the D2 Product logical table.
2 .	Drag the Brand column from D2 Product to the Product Brand level in H2 Product.
3 .	Continue dragging logical columns from the D2 Product logical table to their corresponding levels in the H2 Product logical dimension: Logical Column Logical Level Lob Product LOB Type Product Type Product Product Detail Prod Key Product Detail Your results should look similar to the screenshot:

Set Logical Level Keys

1 .	Double-click the Product Brand logical level to open the Logical Level dialog box. On the General tab, notice that the Product LOB child level is displayed.
2 .	Click the Keys tab.
3 .	Enter Brand for Key Name.
4 .	In the Columns field, use the drop down list to select D2 Product.Brand.
5 .	Check Use for Display. When this is selected, users can drill down to this column from a higher level.
6 .	Set Brand as the Primary key.
7 .	Click OK to close the Logical Level dialog box. The icon changes for Brand to show that it is the key for the Product Brand level.
8 .	Use a different technique to create a logical level key: Right-click Lob for the Product LOB level and select New Logical Level Key to open the Logical Level Key dialog box.
9 .	In the Logical Level Key dialog box, accept the defaults and click OK.
10 .	The icon changes for Lob to show that it is the key for the Product LOB level.
11 .	Use either method to set the remaining keys for the H2 Product logical dimension: Logical Level Logical Level Key Use for Display Product Type Type Yes Product Detail Product Yes Product Detail Prod Key No Your results should look similar to the screenshot: Please note that the Detail level (lowest level of the hierarchy) must have the column that is the logical key of the dimension table associated with it and it must be the key for that level: Prod Key in this example.
12 .	Set Prod Key as the primary key for the Product Detail level. Hint: Double-click the level and select the Keys tab.

Create a Logical Dimension for Time

1 .	Use a different technique to create a logical dimension for Time. Right-click the D1 Time logical table and select Create Logical Dimension > Dimension with Level-Based Hierarchy.
2 .	A new logical dimension, D1 TimeDim in this example, is automatically added to the business model.
3 .	Rename D1 TimeDim to H1 Time .
4 .	Expand H1 Time . Notice that two level were created automatically: D1 Time Total and D1 Time Detail. D1 Time Detail is populated with all of the columns from the D1 Time logical table.
5 .	Rename D1 Time Total to Time Total, and rename D1 Time Detail to Time Detail.
6 .	Right-click Time Detail and select New Object > Parent Level to open the Logical Level dialog box.
7 .	On the General tab, name the logical level Week, also check Support rollup to higher level of aggregation.
8 .	Click OK to close the Logical Level dialog box. The Week level is added to the H1 Time logical dimension.
9 .	Repeat the steps to add the remaining logical levels: Month as a parent of Week Quarter as a parent of Month Half as a parent of Quarter Year as a parent of Half Your final results should look similar to the screenshot:

Associate Time Logical Columns with Logical Levels

1 .	Use a different technique to associate logical columns with logical levels. Drag the logical columns from the Time Detail logical level (not from the D1 Time logical table) to their corresponding levels in the H1 Time logical dimension. This is a convenient technique when logical columns are buried deep in the business model. Logical Column Logical Level Per Name Year Year Per Name Half Half Per Name Qtr Quarter Per Name Month Month Per Name Week Week Your results should look similar to the screenshot:
2 .	Delete all remaining columns from the Time Detail level except for Calendar Date so that only Calendar Date is associated with the Time Detail level. Please note that deleting objects from the hierarchy does not delete them from the logical table in the business model.
3 .	Set the logical keys for the H1 Time logical dimension according to the following table: Logical Level Level Key Use for Display Year Per Name Year Yes Half Per Name Half Yes Quarter Per Name Qtr Yes Month Per Name Month Yes Week Per Name Week Yes Day Calendar Date Yes

Create a Logical Dimension for Customer

1 .

Use either technique to create a level-based logical dimension named H3 Customer for the D3 Customer logical table with the following levels, columns, and keys: Level Column Key Use for Display Customer Total <none> <none> <none> Customer Region Region Region Yes Customer Area Area Area Yes Customer Country Country Name Country Name Yes Customer State State Province State Province Yes Customer City City City Yes Customer Postal Code Postal Code Postal Code Yes Customer Detail Customer Name Customer Number Customer Name Customer Number Yes No Set Customer Total as the grand total level. Set Customer Number as the primary key for the Customer Detail level. Your results should look similar to the screenshot:

Set Aggregation Content for Logical Table Sources

1 .	Expand D1 Time > Sources.
2 .	Double-click the LTS1 Time logical table source to open the Logical Table Source dialog box.
3 .	Click the Content tab.
4 .	Confirm that Aggregation content, group by is set to Logical Level and the logical level is set to Time Detail for the H1 Time logical dimension.
5 .	Click OK to close the Logical Table Source dialog box.
6 .	Repeat to verify or set content settings for the remaining logical table sources using the table and screenshots as a guide: Logical Table Source Logical Dimension Logical Level LTS1 Product H2 Product Product Detail LTS1 Customer H3 Customer Customer Detail LTS2 Customer Address H3 Customer Customer Detail LTS1 Revenue H1 Time H2 Product H3 Customer Time Detail Product Detail Customer Detail
7 .	Save the repository and check global consistency. Fix any errors or warnings before proceeding. Notice that you did not have to make any changes to the Presentation layer.
8 .	Close the repository. Leave the Administration Tool open.

Test Your Work

1 .	Return to Fusion Middleware Control Enterprise Manager 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 if necessary.
3 .	Create the following analysis to test the Product hierarchy. Products.Brand Base Facts.Revenue
4 .	Click Results.
5 .	Click on the BizTech brand and verify that you can drill down through the hierarchy to see revenue data at each level.
6 .	Select New > Analysis > Sample Sales.
7 .	Click OK to confirm that you want to navigate away from this page.
8 .	Create the following analysis: Time.Per Name Year Base Facts.Revenue
9 .	Click Results and verify that you can drill down through the Time hierarchy.
10 .	Repeat the steps and create the following analysis to test the Customers hierarchy: Customer Regions.Region Base Facts.Revenue
11 .	Click Results and verify that you can drill down through the Customers hierarchy.
12 .	Sign out of Oracle BI. Click OK when prompted about navigating away from this page. Leave the Oracle BI browser page open.