How to Import a Dimension

Overview

This video teaches you how to manage dimension members in bulk using the export/import feature on the Dimensions page, instead of adding them one at a time in the UI. It covers exporting a dimension to a spreadsheet, editing member and hierarchy columns directly in Excel, and importing the file back in.

Facts to Remember

• The exported file has one tab per dimension. You can delete tabs for dimensions you're not editing.
• A dimension with alternate hierarchies will have a seperate table for each hierarchy.
• Sort order must be unique among children of the same parent — a duplicate sort order under the same parent throws an error on upload.
• You must fill in the key column yourself.
• After importing, you must click Process (top right) to push the changes to the cube — until then, the new/changed members aren't usable in canvases or for loading data.

Transcript

In this video, we're going to go over how to manage your dimensions using the import and export feature.

Let's start within the dimensions page.

On the top right-hand side, you'll have your import and export button.

Let's click on the export button to generate our dimension file.

Let's open this up.

On the bottom, you'll notice that there are tabs for each one of your dimensions.

If you have multiple tabs for a dimension, it means that your dimension has alternate hierarchies.

So in this case, account has two tabs, one for the default hierarchy under account and an alternate hierarchy called account alternate hierarchy.

You don't have to keep all of the tabs within this file.

If you're only working with one or two dimensions, You can delete the rest of the tabs and only work within the dimensions that you need to change.

Let's delete all of the tabs except for our account and our test dimension.

Within the tab itself, there are multiple columns.

Let's start with the columns to the left-hand side.

The name, caption, and key are member columns for the member itself.

So name, caption, and key.

The next two are parent, the direct parent.

So this is the parent key that is the direct parent of the member.

Parent name is not used, so you can keep this column blank.

Tag is the column that keeps track of any tags applied to the member.

Column G is storage.

We won't go into details about this column.

For now, most of your members should be storage type zero.

The other storage types are special storage types used by our RevIQ module and other Parts of the software, most of your members will be type zero.

The next two are base member key and base member name.

This is for shared members.

So if your member has a value in base member key, it means it's a shared member and the base member key will refer back to the original member that it was shared from.

The next column is sort order.

Sort order is the order of the direct children underneath a parent.

One thing to keep in mind, the sort order must be unique within the children.

You cannot have two children underneath the same parent with the same sort order number.

If you do, you will get an error when you upload the file.

On the right hand side are dimension properties.

In this case, we have our count specific properties.

Let's go through an example of adding a parent and its children.

So let's go to our test dimension.

Right here, we just have one member, none.

Let's add a new member.

Let's call it all tests.

The caption, we'll call it all tests.

The key, you have to increment yourself.

You cannot leave it blank.

The system will not generate keys for you through the import export method.

So in this case, we'll put two.

For the parent key, we want all tests to be the top level member.

So if your member is the top level member, you can leave the parent key blank.

Put zero for storage.

In this case, sort order one.

So none and all tests will be at the same level.

So we need to keep the sort order unique.

So we'll put all tests underneath none.

Let's add three more members.

We'll call it test one.

Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, and importing data from flat files.

Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, and importing data from flat files.

Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, and importing data from flat files.

For sort order, it does need to be unique.

So you can manually put it in.

So we could put zero, we'll start back at zero, one and two.

So this will give us our sort order.

If in a larger hierarchy, if you don't know what the next sort order is, you can use the max ifs function.

So max ifs.

And you do the max if on the sort order.

And then the criteria is the parent.

And that way it will always check the parent key.

So in this case now the max is two, so we'll add one to it and give us three.

So this formula will always give you the next highest sort order key.

And that way you'll always maintain uniqueness.

Let's get rid of that.

So we have all of our member set.

So what I like do at the end is just to do a copy paste values just to clean it up with the formulas.

We can save the file.

Go back to our window and click import.

And we can choose our dimension file.

So once we import, you'll get a message up top.

The bell will hold the message.

You can see that it has processed completely.

And then once we're ready and our changes are final.

You want to make sure to click the process button at the very top right hand side.

This will push your changes to the cube.

So that is now visible and usable within your canvases and when you load data into your data model.

So you can see here our process metadata is running.

And once it's successful, you'll know it's completed.

This one will take a little bit longer because we're in a demo queue.

We have multiple data models in here, but this will complete and let us know once it's completed successfully.

This concludes our video on how to manage your dimensions using the import and export function.

Related Articles

• Staging Tables — dimensions page
• Hierarchy Manager — reviewing the hierarchy after import