Merging multiple dimensions
- Feb. 21, 2011
Introduction
Last week, I was asked to merge 2 dimensions into a single dimension. The dimensions were thought to have a n-to-n relationship, but in the end the customer wanted to have dimension 1 elements as parents of the dimension 2 elements. So in fact, a merged dimension, which would lead to a more dense cube. There was a 1-to-n relationship instead of n-to-n.
This article will not contain the actual codes to get it all merged, since creating and updating a dimension is relatively straightforward for the audience on my TM1 website ;-) Rather, I would like to write about an interesting approach.
A different approach
In the cube containing the data on the dimensions, create a view by stacking dimension 1 and dimension 2 in the rows. Consequently, suppress the zero rows. Then, snapshot the view to Excel, and remove all but the first 2 columns. Also, remove the rows on top the sheet. Save the file as a text file like CSV, TXT, CMA, …
The result is now an input file that you can use in a simple TI process, in order to create the merged dimension (or update an existing dimension). The new dimension does not contain too many combinations (the product of the DIMSIZ's of both dimensions), yet you will be able to load the data again.
