Skip to main content

Shared Dimension

A multidimensional analysis model is based on dimensions, which are composed of different fields from their dimension tables. We can organize the fields of the dimension table in different ways and set different attributes. One way of organizing is a hierarchy, where fields are its levels. A dimension can have multiple hierarchies to represent different analytical perspectives.

This article uses the Demo - FoodMart Model as an example to explain how to create a common shared dimension in a multidimensional analysis modeling scenario.

Creating a Shared Dimensionโ€‹

We will create a shared dimension using Warehouse as the analysis dimension.

  1. Open the semantic model Demo - FoodMart Model, click the add button in the left sidebar, or drag the dimension table warehouse from the lower area of the left sidebar to the model area to create an entity;
  2. Select the type as Dimension, enter the name Warehouse and description Warehouse, choose the physical table as warehouse, and click apply;
  3. A new dimension will create a default hierarchy with the same label as the dimension;

Creating Dimension Levelsโ€‹

The levels of a hierarchy are generally set from coarse to fine granularity. For example, the field levels of warehouse can be set as:

  1. warehouse_country
  2. warehouse_state_province
  3. warehouse_city
  4. warehouse_id

Next, let's create the level fields for the dimension:

  1. In the Hierarchy area below, you can see the warehouse table has been added (if multiple tables need to be associated, you can drag tables from the Data Tables area on the left to this area, refer to Star and Snowflake Models for details);
  2. Expand the warehouse table to see the list of table fields. Drag the fields listed above to the Hierarchy area in order; whenever the hierarchy changes, you can preview the changes in actual dimension data in the right data area (Count column shows the actual number of entries in the database).
  3. You can adjust the order of level fields by dragging them, or drag them to the delete button to remove them;

Level Detailsโ€‹

Select a level field, open the right-side property editing interface, and set detailed properties for the level:

Refer to Dimension Designer#Creating Levels

The uniqueMembers attribute is used to optimize SQL generation. If you know that the values of a given level column in the dimension table are unique across all the other values in that column across the parent levels, then set uniqueMembers="true", otherwise, set to "false". For example, a time dimension like [Year].[Month] will have uniqueMembers="false" at the Month level, as the same month appears in different years. On the other hand, if you had a [Product Class].[Product Name] hierarchy, and you were sure that [Product Name] was unique, then you can set uniqueMembers="true". If you are not sure, then always set uniqueMembers="false". At the top level, this will always be uniqueMembers="true", as there is no parent level.

Level Propertiesโ€‹

Set the property field list for the level, then you can use the MDX statement member.Properties("propertyName") function to access member properties, and use DIMENSION PROPERTIES [Hierarchy].[Level].[Property Name] to output properties in the result.