Substitute dim in a cube when new dim name needed
Posted: 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:
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):
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).
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
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 (65.32 KiB) Viewed 6038 times
- 2. New Dim And Temp Cube.JPG (62.33 KiB) Viewed 6038 times
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 (152.75 KiB) Viewed 6038 times
- 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
