Substitute dim in a cube when new dim name needed

Ideas and tips for enhancing your TM1 application
Post Reply
jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Substitute dim in a cube when new dim name needed

Post by jstrygner » Wed Jun 29, 2011 10:28 pm

Hi,

Till now I thought that it is not possible to substitute one dimension in a cube with another that has the same elements, but a different dimension name (without recreating this cube).
Today I found a way to do it and I would like to share (this is not going to be a nice drag and drop :)).

Here is some step by step (it includes also substituting dimension in views such a cube already has, because usually that is the case).

So this is our start point, we have a Current_Cube cube with a Popular View view and data in it (1), this view uses a One Hierarchy subset for dimension DimOne (2) - large pictures but I was limited to 3 attachments:
1. Existing Cube.JPG
1. Existing Cube.JPG (65.32 KiB) Viewed 2370 times
We now want to substitute DimOne name with Dim_With_New_Name name. In order to do it we need to create such a dimension. We can do it e.g. manually from the scratch (like in my exapmle). I created it with a new alias and new Leaves above subset (3). Using this dimension I created a new empty cube that has the dimensions I want my Current_Cube to have. I called it Cube_With_No_Values (4):
2. New Dim And Temp Cube.JPG
2. New Dim And Temp Cube.JPG (62.33 KiB) Viewed 2370 times
Now it's time to lay down the server and dig in binaries. I opened .cub files for both cubes in Notepad++ with (Encoding->Encode in ANSI) and looked at differences in both files in the "dimension names" area at the beginning of the file (5 - red frames with round edges). Old cube file (with data) is larger, but we are interested in first section that we want to make the same as in new cube.
In this particular case it was enough to delete the [ACK] and [DimOne] and copy-paste the [DC1] and [Dim_With_New_Name] respectively. Some of the "black" boxes are not that simple to copy, but there is workaround to this (e.g. to "create" [NUL] box you type in 00 and then chose TextFX->TextFX Convert->Convert Hex to text from the menu).
Next step I went to Popular View.vue file and changed old dimension name to new one, also changed the subset name(6).
After picking the server up we get a nice result with new dimension name, view that is ready to open and data! (7).
3. Corrections and Effect.JPG
3. Corrections and Effect.JPG (152.75 KiB) Viewed 2370 times
Some aspects to be aware of:
- we need to update all rules in this cube e.g. if we use !dimension_name notations,
- If our cube has some control object cubes: drill defined, picklists, cellsecuritycube - all those use our old dimension,
- I do not know how Notepad++ deals with big files (could be problem to open/change/save a large file),
- other I did not think of :)

I did my test on four different cubes for now (with 8-9 dimensions in a cube, once I substituted first, once one of the middle dimensions). Till now I did not spot any additional side effects to what I mentioned in "some aspects to be aware of".

I don't know it it is going to be more comfortable that just recreating cubes, but anyway I think it is a nice point to know.

Cheers

User avatar
qml
MVP
Posts: 1059
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.4 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Substitute dim in a cube when new dim name needed

Post by qml » Thu Jun 30, 2011 4:18 pm

Good find! :geek:
Kamil Arendt

iansdigby
Community Contributor
Posts: 107
Joined: Thu Feb 26, 2009 8:44 am
OLAP Product: TM1
Version: 9 + 10
Excel Version: 2007 Professional
Location: Isle of Wight, UK

Re: Substitute dim in a cube when new dim name needed

Post by iansdigby » Fri Jan 18, 2013 11:15 am

A very useful and impressive technique!
"the earth is but one country, and mankind its citizens" - Baha'u'llah

Post Reply