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:

Product-Categories=DIM[Product,ProductCategory,1]
Vegetables
Fruits


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.

Product-Category=DIM[Product,ProductCategory,1] Article=DIM[Product,Article,2]
Vegetables Potatoes
Vegetables Broccoli
Fruits Apples
Fruits Oranges
Fruits Cherries


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.


Year Article Revenue COGS
2018 Potatoes 100 25
2018 Broccoli 200 50
2018 Apples 300 75
2018 Oranges 400 100
2018 Cherries 500 125


Download the Example as Excel File - (warning) 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.


Was this helpful?

Yes | Somewhat | No