Edit Hierarchies and Levels

When fact tables and dimension tables are joined, a default hierarchy is created but you can also add hierarchies and levels to those tables. For example, a Geography hierarchy might include levels for Country, State, and City.

1. In Data Modeler, lock the model for editing.
2. In the Dimension Tables area, click the dimension table for which you want to add a hierarchy. The dimension table must have at least one join to a fact table.
3. In the Dimension editor, click the Hierarchies tab.
4. In the Hierarchies area, click Add Level, and select the dimension columns or shared levels that you want to use.
   
   Description of the illustration data_mod30.gif
5. Drag and drop levels to a different location in the order, as appropriate. You can also right-click a level and select Move left or Move right.
   
   Description of the illustration data_mod31.gif
6. Click a level to display a dialog in which you can specify the level name, the key column, and the display column for the level.
7. Deselect Available if you don’t want the hierarchy visible in analyses.
8. Click Done when you’re finished.

Set Dimension Table Properties for Hierarchies

From the Overview tab for a particular dimension table, you can set properties that apply to all hierarchies for that table.

1. In Data Modeler, lock the model for editing.
2. Click the dimension table that you want to edit.
3. On the Overview tab, set properties as required.
   • Time dimension — Specifies that hierarchies for this dimension table support a time dimension. Hierarchies for time dimensions can't include skip levels or be unbalanced.
   • Enable skipped levels — Specifies that this dimension table supports hierarchies with skipped levels. A skip-level hierarchy is a hierarchy where there are members that do not have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city "Washington, D.C." does not belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington, D.C.) and below.

     In a query, skipped levels aren’t displayed, and don’t affect computations. When sorted hierarchically, members appear under their nearest ancestors.

   • Enable unbalanced hierarchies — Specifies that this dimension table supports unbalanced hierarchies. An unbalanced (or ragged) hierarchy is a hierarchy where the leaves (members with no children) don’t necessarily have the same depth. For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.