Creating Logical Dimensions with Ragged and Skipped- Level Hierarchies

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

2

3 . Right-click Connection Pool and select Import Metadata to open the Import Wizard.

2

4 . In the Select Metadata Types screen, accept the defaults and click Next.

2

5 . In the Select Metadata Objects screen, in the data source view, expand BISAMPLE.

6 . In the data source view, select the SAMP_PRODUCTS_DR table for import:

2

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.

2

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.

2

11 . Create the following alias for the table: D20 Product

2

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"

2

13 . Right-click D20 Product and select View Data.

2

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

Old NameNew Name
BRANDBrand
LOBLOB
PROD_DSCProduct
PROD_KEYProduct Number
TypeProduct Type

2

3 . Rename the D20 Product logical table source to LTS1 Product (Ragged)

2

Desired to gain proficiency on OBIEE 11g? 

Explore the blog post on OBIEE 11g training online to become a pro in OBIEE 11g

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

2

2 . Rename D20 ProductDim to H20 Product.

2

3 . Double-click the H20 Product logical dimension to open the Logical Dimension dialog box.

2

4 . On the General tab, select both Ragged and Skipped Levels.

2

5 . Click OK to close the Logical Dimension dialog box.

6 . Expand H20 Product.

2

7 . Create the following hierarchy:

LevelColumnKeyUse for Display
Product Total<none><none><none>
Product BrandBrandBrandYes
Product LOBLOBLOBYes
Product TypeProduct TypeProduct TypeYes
ProductProductProductYes
Product DetailProduct NumberProduct NumberYes

2

For indepth understanding of OBIEE click on