Modeler Guide - Part 3 – Kepion Support Center

Dimension Hierarchy

Hierarchies can be created on dimensions. There are three methods when creating a hierarchy:

Create a member list (hierarchy mode or list mode)
Create a shared or private level based hierarchy based on dimension properties
Create a time hierarchy on the Time dimension – also known as calendar generation

Member List

Member List (ML) hierarchies can be created on a dimension based on either a list or parent child relationship of the dimension members. This means that each member of this hierarchy will have the full properties available to it from the dimension. Below is an example of an ML hierarchy created for the Entity dimension.

You can create an ML hierarchy by selecting the MEMBER LIST button on the dimension as shown in the image below.

Note: By default, the List Mode is set to List.

Once a new hierarchy is created, you will automatically be focused to the hierarchy module.

The hierarchy editor supports the following actions. Note that the hierarchy must be in hierarchy mode for the Move Left and Move Right actions:

Move Up: Move the selected member up in the hierarchy. All children members will automatically be moved up along with the member.

Move Down: Move the selected member down in the hierarchy. All children members will automatically be moved down along with the member.

Move Left: Move the selected member to the left, promoting it up one level.

Move Right: Move the selected member to the right, demoting it down one level.

Add: Create a new sibling member below the selected member.

Add From Dimension: Add to the hierarchy with members that already exist on the dimension.

Remove Member: Removes the member from the hierarchy. All descendants of the member will be removed from the hierarchy as well.

Refresh Hierarchy: Refresh the hierarchy definition with the most up to date version on the server.

Expand/Collapse All: Expand All will expand all member nodes of the hierarchy showing the full structure. Collapse All will collapse all the members of the hierarchy.

Import: The import feature allows for bulk imports by business users to define the hierarchy. The import requires a CSV file that will contain all the properties and relationships for the hierarchy. For more information, please refer to the Import/Export section.

Export: The export feature allows for bulk export of the hierarchy definition to a CSV file. You can also choose to export an empty hierarchy to generate the starting CSV file for use in imports.

Configure: Change the mode for the Member List, either from List to Hierarchy or vice versa.

Show MemberId: Show MemberId (read-only) in the Member List

MemberwithData: Display data members for non-leaf members in the parent attribute

Adding Members from Dimension

You can add members from the dimension’s flat list or from an existing ML hierarchy by clicking on the small PLUS button.

Use the hierarchy actions to manipulate the hierarchy into the structure you desire. The image below is an example of the entity members organized into its final hierarchy arrangement. Note that any updates made to the members will be immediately applied and saved to the application. To have the changes reflected on the OLAP database, click on the Process action for the dimension from the All Dimensions node.

Level Hierarchy

Level hierarchies are created by specifying the different properties in a given order. Level 0 indicates the top most level of the hierarchy, with the other levels corresponding to their depth within the hierarchy. Use level hierarchies when you want to create different rollups based on properties of the dimension.

You can create a leveled hierarchy by selecting the DEFINE button on the dimension module.

There are two types of Level Hierarchy. An Input Hierarchy is a hierarchy that includes MemberId at its lowest level. A Reporting Hierarchy is a hierarchy that contains no MemberId. An Input Hierarchy can be used in Forms to enable data input at the lowest (MemberId) level of the hierarchy.

Level hierarchies can be created within a Member List scope. Thus, private Level Hierarchies are possible when it is created with the scope set to a particular Member List while a Level Hierarchy created with Scope set to Shared will be available to all Member Lists. This option is useful to define hierarchies for only a particular set of members. Take a look at the private hierarchies in Time dimension and see how there are different Level Hierarchy definition based on different Member Lists (Months, Years, etc).

Time Calendar

Creating the Time Calendar correctly in the application is essential for planning and reporting scenarios. To create a calendar, follow the steps below:

Define the application Calendar by clicking on the Configure Calendar button and enable the Fiscal calendar. If the Fiscal Calendar is the same as the Gregorian Calendar (regular calendar), leave the Start and Fiscal Year values by default, otherwise specify the appropriate values and click Save.

Next, click on the Add Calendar The Generate Calendar window will appear and allow you to specify the range of time members with which to create. It is recommended that a single Granularity is selected such as Month as shown below. This will create an ML hierarchy with all the members representing Months listed out.

The image below is an example of a generated calendar with the levels FiscalYear, FiscalQuarter, and MemberId selected.

By default, an application will have the following pre-defined Member List and Level Hierarchies defined:

Importing and Exporting Hierarchies

Use the Import and Export feature for hierarchies to bulk transfer dimension member definitions to and from CSV files. Once exported to CSV files, you can use Excel to update the values and then reimport back into the application.

The table below is an example of the Geographies hierarchy in the exported CSV format

MemberLabel	ParentMemberLabel	MemberName	Input	Annotate	Country	State	Region Code
USA		United States	TRUE	FALSE	United States	N/A	10000
WA	USA	Washington	TRUE	FALSE	United States	WA	12000
CA	USA	California	TRUE	FALSE	United States	CA	13000
NY	USA	New York	TRUE	FALSE	United States	NY	14000
TX	USA	Texas	TRUE	FALSE	United States	TX	15000
DAL	TX	Dallas	TRUE	FALSE	United States	TX	15100
NYC	NY	New York	TRUE	FALSE	United States	NY	14100
LA	CA	Los Angeles	TRUE	FALSE	United States	CA	13100
SFC	CA	San Francisco	TRUE	FALSE	United States	CA	13200
SEA	WA	Seattle	TRUE	FALSE	United States	WA	12100

By clicking on Import button, you can choose the file to import from as shown in the figure below.

Dimension Calculations

You can define member calculations on the any dimensions except for Time dimension. To enable the member calculation, check the Member Formula checkbox.

Once the Member Formula is enabled, please save and Deploy to OLAP. You will now be able to define member calculations directly within the Hierarchy control by clicking on the settings icon .

The Member Formula window allows users to define calculations using members from the same dimension. To add members to the calculation, simply double-click on the members in the Hierarchy to add. The formula supports all basic arithmetic operations and also supports some advanced formula structure such as case statement. After you have constructed the formula, you can give it a format by selecting from the Format drop-down.

On save, the formula will be translated to MDX and applied as Custom Roll-up Formula in the OLAP database. However, if you would like to enter your own custom MDX, you can do so with the NATIVE MDX tab. Also, please refer to the EXAMPLE tab for further formula examples.

To see all member formulas on a dimension, use the CALCULATION tab.

Dimension Advanced Settings

Select All Dimensions to configure dimension settings within the application.

When Visual Totals is checked, the aggregated value users see in the form will be filtered by their permission. Advanced settings can be configured from the All Dimensions module. Click on Show Advanced checkbox to display the options.

Dimension Size

Scenario and all user-defined dimension size can be changed after creation. On All dimension list, click the Size column and you can modify the dimension size.

Security

For optimal performance of the application, it is advised to identify the particular dimensions that will play a role in the security setup of users. For instance, if security is required along the Geography and Account dimensions for dimension member access, then only include these two dimensions for security by checking the checkbox for Security.

Hierarchy API

To enable hierarchy functions fnLEAVES, fnDESCENDANTS and fnASCENDANTS, you can enable Hierarchy API for the dimension. These functions will then be available for you to use as SQL Table-valued Function as show in the figure below. Each time the application is deployed, the hierarchy information will be updated for use by the Hierarchy API functions.

SELECT * FROM [dbo].[fnANCESTORS]
(N'Account',N'Account',N'Rental Income',1)
GO
SELECT * FROM [dbo].[fnLEAVES]
(N'Account',N'Account',N'Rental Income',1)
GO
SELECT * FROM [dbo].[fnDESCENDANTS]
(N'Account',N'Account',N'Rental Income',1)
GO

All Hierarchy

This setting will control whether to deploy the dimension flat list as part of the SSAS deployment. As best practice, this setting should be set to false for all dimensions.

Unique

This setting will control whether to enforce uniqueness for the Member Label for the dimension. As best practice, this setting should be set to true for all dimensions. The Member Label should be unique given that it can be referenced in form and rule development.

Read-only

Sometimes the dimension and hierarchy is maintained via ETL and at other times via a manual process. By making the dimension read-only, the UI will be disabled for edits to the hierarchy members.

Dimension Process

Use the Process action to refresh the definition of the dimension hierarchies on the OLAP database. Updates made to the hierarchy from the Modeler can be available for use on forms by using this option instead of full re-deploy of the application to OLAP. Note that this option only affects data changes made to the hierarchy from such actions as adding, removing or reordering of its members. New dimension properties will require a full deploy of the application to OLAP as that will be a structural change.