Using Excel for creating Dimensions & Hierarchies

Creating Hierachies

Please note that it is easier to create dimension using the dimension management interface. Here you find the description for how to do it.

To create a new Dimension with (or without) a hierarchy, a special upload identifier is used. You must provide the system with a complete hierarchy (e.g. every Level Value of a Level with depth 2 or more must map to a parent value.).

To signal the system, that a column should be treated as the data for the level values of a specific level, you have to use the

column=DIM[Dim-Name, Level-Name, Level-Depth]

syntax. Its easiest to follow an example, so let's look at a 2-Level Product hierarchy (Dimension: Product), where we want to put fresh produce (Level: Article) into categories (Level: ProductCategory).

We start by defining the Product Category, which should have Vegetables and Fruits (the Level Values).

The corresponding Excel sheet looks like:

This example is already a valid upload file, which will create a single-level dimension with these two values.

We continue to add Apples, Oranges, Cherries, Potatoes, and Broccoli at Level 2, the article level.

As you can see, each Article is assigned exactly one Product-Category. Both "=DIM" - identifiers refer to the same Product dimension, but differ in the depth value. That also means, that when adding Tomatoes you have to decide whether it is actually a fruit or a vegetable.

Best practices - dimensions & data

Our recommendation for structuring the upload file is as follows:

Create a DimensionName_DIM sheet at the very beginning of your upload file for each dimension you want to create. Let it contain only the above mentioned data structure. Create additional sheets for your upload data, where you may refer to one or multiple of these dimensions.

Create separate data sources for dimension excel files, and data files.

The column name must match the level name (here Article) to be automatically connected with the level.

If you upload the example, make sure to do so in a new project, to not interfere with an existing product dimension. Alternatively, you may rename the dimension or the levels accordingly.