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

Lab 7: Creating Logical Dimensions with Ragged and Skipped-Level Hierarchies

To create logical dimensions with ragged and skipped-level hierarchies, you perform the following steps:

• Import Metadata and Define Physical Layer Objects

• Create Logical Table and Logical Columns

• Create a Ragged/Skipped Levels Logical Dimension

• Create Presentation Layer Objects

• Test Your Work

Import Metadata and Define Physical Layer Objects

1 .	Open the BISAMPLE repository in offline mode.
2 .	In the Physical layer, expand orcl.
3 .	Right-click Connection Pool and select Import Metadata to open the Import Wizard.
4 .	In the Select Metadata Types screen, accept the defaults and click Next.
5 .	In the Select Metadata Objects screen, in the data source view, expand BISAMPLE.
6 .	In the data source view, select the following tables for import: SAMP_PRODUCTS_DR
7 .	Click the Import Selected button to move the table to the Repository View.
8 .	Expand BISAMPLE in the Repository View and confirm that the SAMP_PRODUCT_DR table is visible.
9 .	Click Finish to close the Import Wizard.
10 .	Confirm that the SAMP_PRODUCT_DR table is visible in the Physical layer of the repository.
11 .	Create the following alias for the table: D20 Product
12 .	Use the Physical Diagram to create the following physical join for the alias table: "orcl".""."BISAMPLE"."D20 Product"."PROD_KEY" = "orcl".""."BISAMPLE"."F1 Revenue"."PROD_KEY"
13 .	Right-click D20 Product and select View Data. Notice there are skipped levels in the hierarchy. For example, brand A - Brand2 has a NULL value for LOB for the product D - Product 8.
14 .	Close View Data.

Create Logical Table and Logical Columns

1 .	Drag D20 Product from the Physical layer to the Sample Sales business model in the BMM layer to create a D20 Product logical table. The logical join to F1 Revenue is created automatically based on the join in the Physical layer.
2 .	Rename the D20 Product logical columns: Old Name New Name BRAND Brand LOB LOB PROD_DSC Product PROD_KEY Product Number Type Product Type
3 .	Rename the D20 Product logical table source to LTS1 Product (Ragged)

Create a Ragged/Skipped Level Logical Dimension

1 .	Right-click the D20 Product logical table and select Create Logical Dimension > Dimension with Level-Based Hierarchy to automatically create a logical dimension named D20 ProductDim.
2 .	Rename D20 ProductDim to H20 Product.
3 .	Double-click the H20 Product logical dimension to open the Logical Dimension dialog box.
4 .	On the General tab, select both Ragged and Skipped Levels.
5 .	Click OK to close the Logical Dimension dialog box.
6 .	Expand H20 Product.
7 .	Create the following hierarchy: Level Column Key Use for Display Product Total <none> <none> <none> Product Brand Brand Brand Yes Product LOB LOB LOB Yes Product Type Product Type Product Type Yes Product Product Product Yes Product Detail Product Number Product Number No

Create Presentation Layer Objects

1 .	Drag the D20 Product logical table to the Sample Sales subject area in the Presentation layer.
2 .	In the Presentation layer, rename D20 Product to Products (Ragged) and move Products (Ragged) to appear after Products.
3 .	Expand Products (Ragged) and notice that the H20 Product logical dimension is automatically added to the Presentation layer.
4 .	Save the repository and check consistency. Fix and errors or warnings before proceeding.
5 .	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.
3 .	Create the following analysis to test the ragged / skipped level hierarchy: Products (Ragged).Brand Products (Ragged).LOB Products (Ragged).Product Type Products (Ragged).Product Base Facts.Revenue
4 .	Click Results. The results display correctly even though there are skipped levels (levels with NULL values) and ragged levels (leaves with varying depth).
5 .	Sign out of Oracle BI.