Add Hierarchy Tables in the Jet Data Manager

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 as Source Table. Once the parameters are entered click Load Values.

   

3. In the right-hand pane right-clickAdd root heading.

   

4. Add additional sub-headings as desired by right-click 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 roll-up formula.  In this example we take the North member plus the south member.

   

   When creating custom roll-up formulas it is important that the member names be unique.  If member names are not unique it is advisable to reference members by key.

7. Once finished deploy and execute the hierarchy table. To do that right-click the hierarchy table (here, Salesperson Segments) and select Deploy and Execute.

   

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. To open in new window, right-click the hierarchy table and select Open in New 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.