Though the preferred method for creating dimensions in TM1 is via a Turbo Integrator Process, sometimes you may want to create TM1 Dimensions with a dimension worksheet. With a dimension worksheet, you can incorporate Excel functionality to do concatenations that will populate your attributes. This works nicely for Aliases where you would like to use the element name with a description. The example below shows you how to do this for an Accounts dimension.

Elements:
3000
4000
Gross Profit

The first Alias will be called “Name” and will have the alpha name for the accounts:
Revenue
COS
Gross Profit

The second Alias will be called “Long Name” and will have the alpha-numeric name for the accounts:
3000-Revenue
4000-COS
Gross Profit

TM1 saves the dimension worksheet files to the first directory listed in the Local Server Data Directory field in the Options dialog box. Therefore, TM1 might save your dimension worksheet (.xdi) to a different directory than your dimension file (.dim). To choose where you would like to save the file, click on “Options” and then browse to the directory where you would like to save the file:

Through the TM1 Toolbar, you will use the Developer ribbon and choose “Dimension New”:

After you do that, you would set up your worksheet as such:

• Column A has an “N” for leaf-level numeric elements; “S” for string elements and “C” for consolidated elements.
• Column B will be the element.
• Column C will be the weighting that is associated to an element in a consolidation. To the right of Column C is where you will be able to use columns for your attributes.
• In our example, Columns D and E are used for input of our alias information.

Columns F and G will contain the TM1 formulas to send that information to update the dimension.

In Cell E5, we are using the Excel concatenation function to create the Long Name using the cell data in Columns B and D. In Columns F and G, we use the DBSA function with the following syntax:
DBSA (att_value, “server_name:dimension_name”, element, att_name)
Cell F5 formula: =DBSA(D5,"Demo:Accounts",B5,$F$4)
Cell G5 formula: =DBSA(E5,"Demo:Accounts",B5,$F$4)

Through the TM1 Toolbar, you will use the Developer ribbon and choose “Dimension Save”:

After you save the worksheet, you must create the “Name” and “Long Name” aliases through Server Explorer, since dimension worksheets will not create the actual alias, just read and update the info to send to the specific attribute. Once those attributes are created, you can simply copy your formulas down for new element additions and hit recalculate.

Again, dimension worksheets are not recommended for use with dimensions that require continual maintenance, as there can be issues with the saved file becoming out-of-synch if one makes changes through the Dimension Editor and this may cause data loss.

If you liked this blog on creating TM1 Dimensions in Excel Worksheet, then check out this blog on Using Stored Procedures in TM1 Turbo Integrator by clicking here.