Add new dimension to existing cube

[Patrick Gr]

Hello,

I'm very new to TM1 10.2.2. I need help for adding a new dimension to existing cube. The cube is dependant to many other cubes through rules and maybe processes. I tried in performance modeler to affect existing data of the cube to a specific element of my dimension. But performance modeler had crashed. is it OK to add dimension even if there is dependencies? is it why it craches? Is there any other way to proceed?

[tomok]

You can't add a new dimension to an existing cube. Performance Modeler can do it but it is an afterthought type thing and I would never trust that it can do it right. If the cube is linked to other cubes via rules and processes then I can almost guarantee that PM can not do it completely. You are going to have to delete the cube and rebuild with the new dimension. This means you are going to have to export out all the data and put together a plan for loading it back in to the new cube. Depending on how much data there is it may be easier to use a processing worksheet. If it is a lot of data you'll need to create a TI process to do it. Then you'll need to consult your documentation to figure out where you'll need to look for rules and processes that may need modifying to account for the new dimension. This is not a trivial task by any means, especially if the cube has been in production for a while. Good luck.

[Wim Gielis]

Is there any other way to proceed?

Yes, the manual way. Consult documentation on the model, if any.
Do this on a development server first.
Backup cube data, rules, views (if needed), cube security and cell security settings (if any), cube properties, and so on, since you need to delete the cube (or rely on Performance Modeler but that's risky business if you ask me).

[tomok]

I forgot to mention reports. None of them are going to work without modifying to account for the new dimension.

[paulsimon]

Hi

This is potentially quite a lot of work as you will see below. The amount of work will depend on how much you have built on top of the existing cube.

Firstly consider whether you can accommodate the new split by an alternate hierarchy in an existing dimension, or whether there are unused dimensions in the cube that could be re-purposed.

Another alternative is to create a different cube with the extra dimension, populate that, and use a process to transfer the top level total of the extra dimension to the old cube without this dimension. In that way nothing on the old cube needs to change, and any references to that cube don't need to change. Of course none of the cubes referencing that cube can take advantage of the new split, but then if they were to do that then you would have to change those other cubes too. Rather than populate the new cube and then transfer summarised data to the old cube, it will probably more efficient and more reliable to populate both the old and new cubes from the same datasource, You just need to use CellIncrementN to populate the old cube as there will obviously have to be multiple values from the data source to populate the split given by the new dimension that will not exist on the old cube. This is quite a common technique to allow for detail and summary cubes.

I can't advise you on doing this with Performance Modeler since I don't use it. Most experienced developers use Architect/Perspectives.

If you do decide that re-creating the cube with another dimension is the only way then, it is important to look at what you will lose when you re-create the cube with an extra dimension - you will effectively be deleting the cube and creating a new one

You will lose

Data in the cube
Rules
Views
}CellSecurity Cube and in particular any rules defined on that
DrillThruRules
DrillThruProcesses

You will also

Invalidate }CubeSecurity rules
Invalidate rules that reference the cube
Break any processes that load into the cube or read from the cube
Temporarily invalidate references to the cube in Application Folders, but these should be OK once the cube is re-created.

Do the changes in the Dev environment. Even in the Dev Environment, take a backup before making any changes. Stop any other development work while making the changes. Disable any Chores or other processes that may be updating the cube or reading from the cube.

Before making changes :

Export all data from the cube.

I have a utility that generates the TI Statements needed to create all views on a cube, and the cube itself. This pushes things out to Excel where you can do a Find Replace or use Excel Formula to update the TI statements to reference a default value in the new dimension, and then paste them into a TI process to create the cube and views.

You will need to save the rules before re-creating the cube. Eg paste into Notepad. You can then do a Find and Replace on reference the new dimension. However, depending on the nature of the new dimension, you may need to make further changes to the rules. If it is a hierarchy type dimension then you can probably just reference !NewDim.

The same applies to rules on related }CubeSecurity and }CubeDrill rules.

For rules on other cubes that reference this cube, I have a utility that will do a Find and Replace on all Rux files. You will need to do these changes with the Service shutdown. if a Find and Replace is not practical you can just scan to identify rules that reference this cube and update the rules manually.

Similarly I have a utility that can do a Find and Replace on spreadsheets. There are further utilities that let you parse DBRW formula to determine the cube from the cube cell reference so that you can work out whether the DBRW formula references this cube and needs to be updated to reference an extra dimension. Alternative you can just scan to get a list of sheets that reference this cube.

If you have TI processes loading data into the cube then you will need to amend those. You will need to amend the logic to populate the new dimension. That may also mean changes to the data sources to provide the additional split.

If you have TI processes that read from views on the cube, then you will need to amend those to reference the new element. A possible approach is to base the DataSource on a wide cube with a lot of dimensions and then change to the desired cube in the Prolog. That makes it easier to increase the number of dimensions, as otherwise there is no other way to update the Variables tab. Obviously if this cube now has another dimension then something will need to be done to handle that when loading another cube from this one. That might mean changing a CellPut to a CellIncrement. It is probably best to use a utility to scan .pro files for references to this cube.

DrillThruProcesses may need to be re-created to allow for the additional dimension.

When re-importing the data you will need to take a decision on how to handle the historic data since it obviously won't have any splits by the new dimension. You may want to add an unknown element and import the historic data into that. Alternatively, if you can get the historic data by the additional split from a data source then you may want to reconcile it to the old cube to make sure that the top level numbers are the same with the new split. A convenient way to do this is to just do a file copy of the old cube, while the server is shutdown so you have eg MyCube.cub and MyCube - Copy.cub. Then when you restart the service you have the old version of the cube with the old set of dimensions and data, which you can compare to the new cube, once you have added the new dimension.

Hope this helps, sorry it is probably a bigger job than you first thought.

Perhaps there is a genius out there who can export the lot to JSON using the TM1 Rest API and parse it to do all the replacements.

Regards

Paul Simon

[Wim Gielis]

Very good answer Paul !

Perhaps there is a genius out there who can export the lot to JSON using the TM1 Rest API and parse it to do all the replacements

I have seen this in TI but it’s not my work so can’t give it out.

Wim