The Jet Data Manager allows one to create a hierarchy table. Hierarchy tables are often used to describe parent-child relationships within a single table. These tables can then be modeled as dimensions in an OLAP database. In this article we will be building a hierarchy table that describes the relationship between salespersons and custom segments which we will define.
1. Right click the Tables node and click Add Hierarchy Table .
2. Select the appropriate values for each field. In this example we choose the Salesperson Purchaser table. Once the parameters are entered click Load Values .
3. In the right-hand pane right click and Add root heading .
4. Add additional sub-headings as desired by right click on an existing heading → Add sub heading .
In this example we have a root heading of world and each segment. North, East, South, and West.
5. By either dragging and dropping or right clicking the headings add the desired members to appropriate heading.In this example we have added salespersons to each segment.
6. Let's add two more headings that will act as totaling segments. In this example we added North & South and East & West. We will want the totals of specific segments to be added together. To do this we need to define a rollup formula. In this example we take the North member plus the south member.
8. Add the hierarchy table as a new database dimension. One easy way to do this would be to add the dimension table in its own window
and then drag the table onto the dimensions node. The Jet Data Manager will automatically recognize the table as a parent-child dimension.
9. Add the database dimension to the desired cube and set the dimension relations.
In this example we have related our dimension to both fact tables, posted sales transactions and sales order transactions.
10. Deploy and execute any modified objects within the OLAP database.
The end result is a newly created dimension that displays each custom segment by salesperson and the totals for each segment combination as defined in the hierarchy table.