How to create hierarchies and dimensions

Dimensions and hierarchies define the structure users navigate when querying the cube. A hierarchy organizes members into one or more levels, and a dimension groups related hierarchies and specifies how their members relate to the fact table. Understanding dimension types helps choose the right approach for the data model and optimize query performance.

Prerequisites

Before creating dimensions and hierarchies, make sure:

A cube is created (see configure cubes)
The relationships between tables are understood

What are the dimension types?

There are two distinct dimension types:

Type	Description	Use case
Dimensions from a table	Hierarchies are defined using the columns in a table.	Standard: Filtering, simple bucketing
Dimensions without a table	Custom hierarchies not loaded from a table	Specific: Dynamic bucketing, additional hierarchies for measures

How to create dimensions from a table

When creating a dimension from a table, specify the relationship between the base or fact table and the dimension table.

When to use many-to-one relationships

A many-to-one relationship means multiple rows in the fact table map to a single row in the dimension table (e.g., multiple trades reference the same product, or multiple orders belong to the same customer). Many-to-one relationships are the most common case. Use ManyToOneRelationship with the join path from the fact table:

ICubeConfigurator cubeConfigurator = managerConfigurator.cube("cubeName");
IDimensionConfigurator dimensionConfigurator =
    cubeConfigurator
        .dimensions()
        .addDimension(
            "Geography", "Country", new ManyToOneRelationship(JoinPath.of("toCountry")));
// Configure the metadata of the dimension
dimensionConfigurator.type(DimensionType.GEOGRAPHY).description("My description");

Then add hierarchies to the dimension:

// Add a hierarchy
IHierarchyConfigurator hierarchyConfigurator =
    dimensionConfigurator.addHierarchy(
        ITableHierarchyDefinition.builder("Country")
            .withAllMember()
            .withFieldLevel("Country", FieldPath.of("country"))
            .withFieldLevel("City", FieldPath.of("city"))
            .build());
// Configure the metadata of the hierarchy
hierarchyConfigurator.folder("MyHierarchyFolder");
// Configure the metadata of a level
hierarchyConfigurator
    .level("Country")
    .type(LevelType.GEO_COUNTY)
    .description("My level description");

If no relationship is specified, the configurator searches for a unique many-to-one relationship automatically:

cubeConfigurator
    .dimensions()
    .addDimension("Geography", "Country")
    .addHierarchy(ITableHierarchyDefinition.singleLevel("Country", FieldPath.of("country")));

When to use many-to-many relationships

A many-to-many relationship means a single row in the fact table can map to multiple rows in the dimension table (e.g., a trade with multiple counterparties, or a product belonging to multiple categories). For many-to-many relationships, use ManyToManyRelationship. Map fields from the dimension table to existing levels:

cubeConfigurator
    .dimensions()
    .addDimension(
        "Tags",
        "ProductTag",
        ManyToManyRelationship.builder()
            .withMapping("productId", new LevelIdentifier("Product", "Product", "ProductId"))
            .build())
    .addHierarchy(
        ITableHierarchyDefinition.builder("Tag")
            .withFieldLevel("Tag", FieldPath.of("tag"))
            .build());

Many-to-many dimensions are computed at query time and cannot be used in PreAggregators.

When to use no-relationships

Tables that are not related to the fact table can be used as dimensions. This is useful for reference data. For dimensions not related to the fact table, use NoRelationship:

cubeConfigurator
    .dimensions()
    .addDimension("Independent", "IndependentStore", new NoRelationship())
    .addHierarchy(
        ITableHierarchyDefinition.builder("Hierarchy")
            .withFieldLevel("Level", FieldPath.of("field"))
            .build());

When to use virtual hierarchies

Virtual hierarchies do not store their members. They consume less memory but have limited features. For more information, see virtual hierarchies.

ITableHierarchyDefinition.builder("myHierarchy")
    .withFieldLevel("level1", FieldPath.of("field1"))
    .virtual()
    .build();

Use virtual hierarchies for:

Very large dimensions where member enumeration is not needed
Dimensions that do not require member-based filtering

How to create custom hierarchies

Custom hierarchies are not directly loaded from the dimension table. They can be created manually or derived from other dimensions.

When to create derived hierarchies

Derived hierarchies can be used as bucketing hierarchies. Compute members by applying a transformation to other cube elements:

SerializableFunction<IArrayReader, Object> calculator =
    reader -> {
      Object value = reader.read(0); // First underlying item
      // Apply bucketing logic
      if (value instanceof Integer intValue) {
        return intValue < 100 ? "Small" : "Large";
      }
      return "Unknown";
    };
cubeConfigurator
    .dimensions()
    .dimension(new DimensionIdentifier("myDimension"))
    .addHierarchy(
        ICustomHierarchyDefinition.builder("Bucketed")
            .derived()
            .withLevel("Bucket", IDerivedHierarchyInput.create(sourceLevel), calculator)
            .withMembers("Small", "Large", "Unknown")
            .build());

Key points:

Provide a calculator function that receives an IArrayReader and returns the bucket value
Specify the underlying cube items (levels or measures)
Optionally specify valid members with withMembers()

How to create manual hierarchies

Manual hierarchies have an explicitly defined set of members:

List<Object[]> memberPaths = List.of(new Object[] {"A1", "B1"}, new Object[] {"A1", "B2"});
cubeConfigurator
    .dimensions()
    .dimension(new DimensionIdentifier("myDimension"))
    .addHierarchy(
        ICustomHierarchyDefinition.builder("myHierarchy")
            .manual()
            .withAllMember(allMember)
            .withLevels("Level A", "Level B")
            .withMembers(memberPaths)
            .build());

Use manual hierarchies for:

Fixed member sets
Analysis dimensions not related to data
Custom categorizations

How to update dimension, hierarchy or level metadata

Once a dimension, hierarchy, or level is defined, only metadata can be changed. Metadata includes visibility, folder location, and formatting:

IDimensionsConfigurator dimensionsConfigurator = cubeConfigurator.dimensions();
// Update a dimension
dimensionsConfigurator.dimension(new DimensionIdentifier("Geography")).hidden();
// Update a hierarchy
dimensionsConfigurator.hierarchy(new HierarchyIdentifier("Geography", "Country")).virtual(true);
// Update a level
dimensionsConfigurator
    .level(new LevelIdentifier("Geography", "Country", "City"))
    .formatter("MY_PLUGIN_KEY");

How to rename cube elements

Renaming applies when working with a predefined configuration.

How to rename elements with YAML

Add name overrides in the application configuration file:

semantic-model:
  name-overrides:
    cubes:
      MyCube:
        dimensions:
          Dimension1:
            newName: DimensionA
            hierarchies:
              Hierarchy1:
                levels:
                  Level1:
                    newName: LevelA
              Hierarchy2:
                newName: HierarchyB
                levels:
                  Level2:
                    newName: LevelB
          Dimension2:
            hierarchies:
              Hierarchy1:
                newName: HierarchyC
                levels:
                  Level1:
                    newName: LevelC

This configuration renames Dimension1 to DimensionA, Level1 to LevelA, and so on.

Create measures: measure definitions and aggregations

​Prerequisites

​What are the dimension types?

​How to create dimensions from a table

​When to use many-to-one relationships

​When to use many-to-many relationships

​When to use no-relationships

​When to use virtual hierarchies

​How to create custom hierarchies

​When to create derived hierarchies

​How to create manual hierarchies

​How to update dimension, hierarchy or level metadata

​How to rename cube elements

​How to rename elements with YAML

​Related pages