In this post we look at creating a level based hierarchy and generate a drill down report off that. We will use the FoodMart database (described in previous posts).
Here are the steps to create the hierarchy.
1. Right Click on the FoodMart Business Model in the Business Modeling Layer. Select New Object-> Dimension.
2) Enter ‘storeDim’ as the name and click OK.
3) Right click on storeDim -> New Logical Level. Enter ‘storeToal’ as the name. select ‘Grand Total Level’ check box. this level is the top of the hierarchy.
4) Right click on storeTotal and select New Object -> Child Level. Enter ‘Store_Country’ as the name. select ‘Supports roll up…’. Enter Number of elements at this level as 2. Click on Ok.
5) Right click on Store_Country level. Select New Object-> Logical Key. Enter ‘countryKey’ as the name. To add a column, click on Add. select the store_country column from the store table. select the checkbox that says ‘Use For DrillDown’. Make sure the the store_country column is selected. Click on ok.
6) Open the Store table in the business modeling view. double click on the store_country column. select the levels tab and select store_country as the level for the storeDim dimension.
7) This creates the store country level. similarly create the store city level as the child of store country and store id as the child of store city.
8) Next we will prepare the units_ordered column to be used for aggregation. Double click on the units_ordered column in the inventory_fact_1997 table and select the Aggregation tab. select ‘sum’ as the default aggregation rule.
9) Next create a new presentation catalog called FoodMart. Create a new presentation table called store. Add the store_country, store_city, store_id and units_ordered column to the presentation table.
This completes our configuration in the administration window.
Open the Answers tool and add store_country and units_ordered columns to the criteria
Click on results. a table is created. Links are present to allow user to drill down. The units_ordered column is summed according the the level on which the user has drilled down