Defining hierarchies and levels

Applies to: Kyvos Enterprise Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

You can define hierarchies and levels within your data and add multiple hierarchies to dimensions. Hierarchies are a useful option for reducing the complexity between attributes and help you with drill-down behavior.

Examples of hierarchies include time-date data such as year-month-day, or location such as continent-country-state-city.

Note

Kyvos supports naming semantic model entities in the ENTITY_ format, such as DIMENSION_, DIM_, and MEASURE_, and so on.
Click the information icon next to many of the property names to learn more about that option.
You can use the Display Folder to organize hierarchies and view them while browsing a semantic model in Excel.

Here are some tips on how to use hierarchies in data

You can use the field and display field together to create more complex visualizations . For example, you can sort by states, then view data by regions.
When the data is part of a dimension level, you can view the properties and the qualified names, which include all levels above the parent level. When you choose to include qualified names when you save the semantic model, you can provide that extra data for use in visualizations. The parent data will be available in tooltips.
Set the Date Data type to Time to be able to sort based on days of the week Sunday, Monday, etc. rather than alphabetically. Time also allows you to use other predefined options such as Year > Month > Day and split data into fields for year, month, and day. You can also create custom time hierarchies such as business quarters. Choose the field that holds data for the complete hierarchy.
Consider using multiple hierarchies if you need two types of time data such as year-month-day and year-quarter-month-day for different purposes. Or if you need two types of location data, such as division-region-district-location and state-county-city-location.

To define a hierarchy, perform the following steps.

From the Toolbox, click Semantic Models.
Select a semantic model from the list. Locate the dimension to which you want to add hierarchy.
Make sure the Design tab is selected.
Right-click the dimension and choose Add Hierarchy. A hierarchy named H_<dimension name> is created.
Optionally, you can also Add Calculated Member to the hierarchy.
Optionally, from the Hierarchy Properties on the right-side pane, add an All member caption for the category.
For example, if you are working with the city, state, and country levels - you could name the category Location.

Note

From Kyvos 2023.3 onwards, when you specify a caption for a dimension hierarchy using the All Member Caption in the semantic model designer, the caption you provided for the hierarchy will be displayed when you connect the semantic model to third-party BI tools, such as Excel, XLCubed, and Microstrategy using the MDX interface.

To specify a parent-child relationship for regular data types, click the Has parent-child relation checkbox. See Creating parent child hierarchies .
To specify an alternate path to indicate that there are shared members, select the Has alternate path checkbox.
For date-related data, set the dimension type as Time from the dimension properties.
This allows you to use pre-defined time settings such as days of the week for the hierarchy.
1. You can set up a custom calendar by selecting the Custom Calendar checkbox in Hierarchy properties for the Time type hierarchy. It is disabled when a pre-defined hierarchy is defined.
Enter the name of the hierarchy folder in the Display folder field if you want to display it while browsing a semantic model in Excel. You can separate multiple folders with a semicolon (;) and indicate nested folders with a backslash (\).
If you have enabled raw data querying or drilling through, you can specify whether to Materialize the hierarchy using the corresponding checkbox. Yes means the field will be part of the built cube.
Optionally, add a Description of up to 200 characters. This description is used when you hover over the member name when creating worksheets and dashboards.
You can also filter data in hierarchies, using the Filters which are typically below the properties.

You can show parent members (the display name) from the dimension hierarchy for each member value in a visualization. For example, if the dimension level used is days of the month, the values shown are 1, 2, 3, etc. You wouldn’t know those are the days of which month and year without knowing that the parent was a date and include that information as a display name. You can specify which parent name you want to display.

To add an additional hierarchy, perform the following steps:

From the Toolbox, click Semantic Models.
Select a semantic model from the list. Locate the dimension to which you want to add hierarchy.
Right-click the dimension, and then click Add Hierarchy.
Right-click the new hierarchy entry and choose Rename. Enter a new name.
You can also select the new entry and edit its properties to rename it.
If needed, repeat this process to add another hierarchy to the same dimension.

To define a level, perform the following steps:

From the Toolbox, click Semantic Models.
Select a semantic model from the list. Locate the dimension and hierarchy to which you want to add the level.
Right-click the hierarchy and choose Add Level. A new level named Level is added to the hierarchy.
Click the newly added level and view the properties on the right. You can also rename the level.
1. Select a Field containing the value of the data from the list. You can type in the search box to find the field quicker.
2. Select a Display field from the list. You can type in the search box to find the field quicker.
Select a Sort Field for sorting the data.
Select the fields to set Member Properties and click Settings to customize their settings. You can see how many member properties you select.
Optionally, for date-related data, select a date data type.
1. Set this to use pre-defined time settings such as Day of month. You cannot add levels to predefined time type hierarchies.
2. You can set up a custom calendar by selecting the Custom Calendar checkbox in Hierarchy properties for the Time type hierarchy.
For second-level onwards, if there are parent levels available, use the Qualified Name Includes field to select which parent field data you want to store with the cube.
Click the X in the field to clear it and make a different selection.
To define custom rollup settings, click Custom Rollup Settings in the Level Properties.
1. Select the weight. Navigate to the field you want to use or type into the Search box.
2. Click Apply.
If you have enabled raw data querying or drilling through, you can specify whether to Materialize the hierarchy or the level using the corresponding checkbox. If you choose Yes, this field will be part of the built cube.
Optionally, you can choose to hide members of lower levels of the hierarchy. See Configuring ragged and unbalanced hierarchies.
Optionally, add a description of up to 200 characters. This description is used when you hover over the member name when creating worksheets and dashboards.

To add more levels, perform the following steps:

From the Toolbox, click Semantic Models.
Select a semantic model from the list. Locate the dimension and hierarchy to which you want to add the level.
Right-click the hierarchy item and choose Add Level.
Provide a name for the level in the level properties on the right.
Optionally, specify the rest of the level properties and settings.

Methods to move or add hierarchies, attributes, or levels, perform the following steps:

When using the design tab, you can drag an item to a different dimension or measure a group. You will see a placeholder indicator, for example, Drop as dimension.
When you select an item, you can use the Properties pane to move it to a different hierarchy or measure a group by selecting a different name in the field, such as the hierarchy field.
You can search among the dimensions, measure groups, or hierarchies in the Property pane menu when there are more than 10 items listed.

Custom hierarchies

You can create a custom hierarchy to do analysis appropriate for your business context. For example, the United States government fiscal year for 2019 is:

1st quarter: 1 October 2018 – 31 December 2018
2nd quarter: 1 January 2019 – 31 March 2019
3rd quarter: 1 April 2019 – 30 June 2019
4th quarter: 1 July 2019 – 30 September 2019

Once you've created a custom hierarchy to use as a business calendar, you can use time-dependent functions and measures with your custom calendar.

Enable a custom calendar by selecting the Custom Calendar checkbox in the Hierarchy properties for the Time type hierarchy.

MDX functions: Period To Date (YTD, MTD, etc.) and ParallelPeriod (YoY, etc.)

Filters: In last year OR in the last 2 months OR in the last 2 weeks, etc.

Related topics