Skip to main content

Multidimensional Cubes

This article guides you in creating multidimensional cubes based on the FoodMart data source.

The MDX multidimensional cube functionality is based on the general Cube Designer.

Creating a Sales Multidimensional Cubeโ€‹

Let's start by creating a Sales multidimensional cube.

  1. Open the model Demo - FoodMart Model created in the previous article "Creating an MDX Semantic Model";
  2. The left sidebar is divided into two parts: Existing Data Models and Data Source Entity List. Click the add button at the top or drag the entity (Model or Fact Table) you want to create a model from the bottom to the top language model area to create a semantic model. Here we choose the sales_fact table.

    MDX models support two methods: 1. Creating an MDX model from a database physical table 2. Creating an MDX model from an XMLA data source

  3. Select the type as Cube, enter the name: Sales, label: Sales;
  4. Click apply, and after successful creation, the page will switch to the corresponding cube designer page;

In the cube designer, you can see that the dimensions (Dimensions) and measures (Measures) areas in the middle of the page are still empty. Next, we will add dimensions, measures, and calculated members to the Sales cube.

Creating a Promotions Dimensionโ€‹

Let's create a Promotions dimension for sales:

  1. Click the add icon on the title row of the Dimensions area, and you will see a blank dimension row added below;
  2. Select this blank dimension row, open the property settings bar on the right, and you will see the Dimension property settings interface;
  3. Enter the following information:
    1. Name: Promotions
    2. Caption: (optional) Promotions
    3. Foreign Key: promotion_id
    4. Dimension Type: Regular (optional)

At this point, you cannot see selectable hierarchies in the Calculation page's Dimension selection because we have not yet created the related dimension table hierarchy for the Promotions dimension.

For more detailed dimension properties, refer to Dimension Designer - Dimension Properties

Creating a Promotions Hierarchyโ€‹

The hierarchy of a dimension represents which dimension table it is associated with and the hierarchical organization of the fields to be used from the dimension table. A dimension can have multiple hierarchies.

  1. Click the add icon on the Promotions dimension row to add a blank hierarchy row;
  2. Select the newly added hierarchy row, and the right property settings bar will switch to the hierarchy property settings interface;
  3. Enter the following information:
    1. Name: Leave blank to represent the default hierarchy, which will have the same name as the dimension itself;
    2. Has All: true (see explanation below);
    3. Add a table named promotion in the Dimension Table;
    4. Select Primary Key as: promotion_id
  4. Click the add icon on the hierarchy row, and a blank level row will appear in the hierarchy's child nodes;
  5. Select this level row, and the property settings bar will switch to the level property settings interface. Enter the following information:
    1. Name: Promotion Name
    2. Column: promotion_id
    3. Unique Members: true
    4. Name Column: promotion_name
  6. Save, and if successful, you will be prompted with a success message without any error messages.

Verify the success of the dimension creation by opening the Calculation page, adding a row in the Preview area, and opening the selection list. If you can see [Promotions] in the Dimensions area, it means the dimension was successfully created. You will see a hierarchy property button in the Promotions field settings, where you can set properties related to the hierarchy, such as selecting a hierarchy, choosing a specific level field, selecting a specific member, and adding dimension property lists.

By default, every hierarchy contains a top level called '(All)', which contains a single member called '(All {hierarchyName})'. This member is the parent of all other members of the hierarchy and thus represents a grand total. It is also the default member of the hierarchy; that is, the member used for calculating cell values when the hierarchy is not included on an axis or in the slicer. The allMemberName and allLevelName attributes override the default names of the all level and all member.

If the <Hierarchy> element has hasAll="false", the 'all' level is suppressed. The default member of that dimension will now be the first member of the first level; for example, in a Time hierarchy, it will be the first year in the hierarchy. Changing the default member can be confusing, so you should generally use hasAll="true".

Creating a Measureโ€‹

We have already created a Promotion dimension, and next, creating a measure will complete a basic cube.

  1. Click the add icon on the Measures row, and a blank measure row will appear below;
  2. Select this blank measure row, and the right settings bar will switch to the measure settings interface. Enter the following information:
    1. Name: Sales
    2. Caption: Sales (optional)
    3. Column: store_sales
    4. Aggregator: sum
  3. Similarly, create a measure Cost
    1. Name: Cost
    2. Caption: Cost
    3. Column: store_cost
    4. Aggregator: sum
  4. Click save, and if successful, you will be prompted with a success message without any error messages.

You can now verify the model's usage again by opening the Calculation interface, adding the Promotions field in the Rows of the Preview area, and adding the Sales measure field in the Columns. You will see the data area on the right refresh with the corresponding data. If you can see the data, then everything up to this step is correct.

Note: For cube modeling, data queries for this model information will only take effect after the model is saved to the server.

Creating a Multi-level Customers Dimensionโ€‹

In the previous steps, we created a basic cube where the Promotions dimension only had one field, Promotion Name, along with the Sales measure field and store cost.

Next, we will create a more complex dimension with multiple level fields: Customers.

  1. As introduced above, create a Customers dimension and enter the following information:
    1. Name: Customers
    2. Foreign Key: customer_id
  2. Create a hierarchy and enter the following information:
    1. Has All: true
    2. Dimension Table Name: customer
    3. Primary Key: customer_id
  3. Create level fields, creating multiple level fields in the following order:
    1. Country:
      1. Name: Country
      2. Caption: Country
      3. Column: country
      4. Unique Members: true
    2. State Province:
      1. Name: State Province
      2. Caption: State Province
      3. Column: state_province
      4. Unique Members: true
    3. City:
      1. Name: City
      2. Caption: City
      3. Column: city
      4. Unique Members: false
    4. Customer Name:
      1. Name: Customer Name
      2. Caption: Customer Name
      3. Column: customer_id
      4. Unique Members: true
      5. Name Column: fullname
  4. Save, and if successful, you will be prompted with a success message without any error messages.

Creating Member Propertiesโ€‹

For fields that do not need to be summary dimensions, if they are needed as conditions for filtering members or displaying information, they can be set as attribute fields of level fields. Attribute fields are configured under level fields because fields that are not summary dimensions only make sense when they belong to level fields and will not affect the summary results when aggregated with level fields.

Here we take the Customer Name level as an example. Attributes such as gender, marital status, education, and yearly income are only used as auxiliary attributes at the customer level for displaying information and are not used as summary dimensions.

The creation steps are as follows:

  1. Select the Customer Name level field in the level field property settings interface;
  2. In the Property area, click add property and enter the following information:
    1. Name: Gender
    2. Column: gender
  3. In this way, Gender will be used as an attribute field of the Customer Name field in subsequent data analysis;
  4. Similarly, add the following attributes:
    1. Name: Marital Status, Column: marital_status
    2. Name: Education, Column: education
    3. Name: Yearly Income, Column: yearly_income
  5. Save, and if successful, you will be prompted with a success message without any error messages.

In future data dashboards, you can choose attribute fields for display and filtering.