Business Model and Mapping Layer in OBIEE 11g

· Create two new logical tables and name them as Dim stores and Dim staff.

· From physical layer, drag and drop all the columns from Dim_staff_parent_child to Dim staff logical table.

· Rename the columns using Rename wizard.

· Next from physical layer drag and drop following columns: concession_name, Region_name, Store_name and store_pk columns from Dim_stores_ragged to Dim stores logical table.

· Rename these logical columns using Rename wizard.

· Join Dim staff and Dim stores logical tables with fact sales table in BMM layer diagram.

· From the Business model, create Dimension with level-based hierarchy.

· Name it as stores(ragged, skip level)

· Create the following logical levels.

· Concession =(1.The privilege of maintaining a subsidiary business with in certain premises. 2. the space allotted for business)

· Next drag and drop the columns from logical table to the corresponding level in hierarchy.

· Dim stores.RegionName-->Region

Dim stores.storeName--> Name

Dim stores.concessionname-->concession

Dim stores.store_pk-->store pk.

· Now define the new logical level key for each of the logical columns above.

· Next specify the number of elements.

· Drag the Dim stores logical table from BMM layer to presentation layer (Hierarchy will be automatically dropped in to the presentation layer)

· Specify the number of elements.

· Login to SQLPLUS gcbc_sales/password.

· Then type in the following sql to obtain the member counts for each level.

· Select count(distinct concession_name||store_name) from stores_ragged;

· Select count(distinct store_name|| region_name) from stores_ragged;

· select count(distinct region_name) from stores_ragged;

· These requests should return the numbers 19,16 and 7 respectively.

· Specify the number of elements.