Manage Hierarchies

You can create a custom dimension with a hierarchy, join the custom dimension hierarchy to prebuilt or custom facts, and add the hierarchy to any subject area to meet your business requirements.

Assemble the product hierarchy using the attributes from a dimension table. Hierarchies enable you to define aggregations and drill downs. This makes it easier to report at a summary level and then drill into details easily within the same visualization.

Within the logical star, you can filter and focus on custom dimensions by selecting Show Customizations Only. Additionally, you can search and filter on the objects within the logical star. You can create dimension or add an existing dimension with hierarchy in the logical star. See the recommendations for creating a hierarchy in Recommendations and Tips to Extend the Semantic Model.

1. Navigate to the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
2. On the selected sandbox Details page, perform these steps:
   1. Click Perform Action and then select Manage Logical Star.
   2. Click Edit Logical Star and select the applicable fact.
   3. Click Next.
3. On the Logical Star – Fact page, click Add Dimension, and then select either Create New Dimension or Add Existing Dimension.
4. On the Add a Dimension page, in step 1 of the wizard, perform these steps: .
   1. Select the schema and object, and then enter a name for your dimension in Dimension Name.
   2. Select the Use for Key check boxes for all the keys required to join to the fact.
   3. Select the Add Attributes check boxes for all the columns that you need to add to the logical star.
   4. If required, rename columns by changing the Display Name.
   5. Click Next.
5. If any of the selected attributes have been removed or modified in the source table since the last refresh, then you see those columns highlighted and you get a message asking whether you want to update the table.
   • Select OK in the message to reload the source columns.
   • If you want to review the changes to the source columns, then click Cancel in the message, and later click Refresh to reload the source columns.
6. Click Create Column to add another column to your dimension table in the target subject area using these instructions:
   1. In Create Column, enter a display name.
   2. Under Data Elements, search for a data element from the physical table of the selected dimension table.
   3. From the search results, double-click the data element to place it in the text pane.
   4. Under Functions, search for a function to construct a column using expressions. For example, search for functions like "substring" or "concatenate" to construct new expression-based columns. From the search results, double-click the applicable result to add it to the central text pane.
   5. Click Validate, and then click Save.
7. In step 2 of the wizard, assemble the product hierarchy using the attributes from this dimension and click Next.
   1. Select Add hierarchy to Subject Area.
   2. Type the name for your hierarchy in Hierarchy Name.
   3. Select, drag, and drop the available data elements into the Selected Data elements pane to design a hierarchy for the dimension.
   4. In the Selected Data Elements pane, click a level to update its primary key and set its display attribute in the Properties pane.
      You can add multiple levels in your hierarchy by right-clicking at a level and selecting Add Child or Add ‘n’ Child Levels. For example, your Region Hierarchy can have Region Total at Level 1, Region at Level 2, Country at Level 3, State at Level 4, and City at Level 5.
   5. Optional: Select a Type.
      If you select Time as Type, you can't select other Type options. You can use Ragged and Skipped Levels together.
      • Select Time to define a hierarchy that is used specifically for a time-based hierarchy. A time hierarchy is required if you want to use time series calculations such as AGO and TODATE.
      • Select Ragged to define an unbalanced level-based hierarchy where not all data is present at all levels of the hierarchy.
      • Select Skipped Levels to define a hierarchy where not all members of the hierarchy have ancestors at all levels.
   6. Click Next.
8. In step 3 of the wizard, perform these steps:
   1. In Sort Order Column, select applicable values for the columns that you selected to expose or use as a key.
   2. Ensure that the sort order column is within the same hierarchical level as the display column. For columns not requiring a custom sort, leave the sort order column in the default Select state.
   3. Click Finish.