There is often the need to reorganize a large dimension’s order of presentation to a more meaningful view. Elements are typically loaded in order of that presented from a source file and based on the principal element name. The resulting order is not always in the order desired by business users.
Having been presented the requirement to sort a ragged dimension consisting of over 2 million elements and composed of 10+ element levels, we have designed a solution that not only sorts the required dimension but allows any given dimension to be sorted by its alias name (i.e. Caption).
Our solution applies 3 Turbo Integrator processes to sort an existing dimension. The following TI’s were defined:
1. META Sort Subset – This TI creates an MDX subset based on the dimension entered. The MDX coding includes sorting by the dimension’s caption. The subset is then reviewed within the META tab and inserted to a ASCII Output file in the appropriate order. The TI calls a second TI detailed below.
2. META Sort Subset – Call – This TI reads the ASCII Output file from #2 above and creates a static subset in the source dimension originally used. The static subset retains the order of the MDX subset results.
3. META Sort Dimension – This TI specifies a source dimension to be sorted based on the static subset created in #2 above. The Prolog section specifies the dimension to be sorted by ‘Input’. The ASCII Output file created in #2 is then reviewed within the META tab and each element re-inserted based on the order of the file. The Epilogue tab re-establishes the order of the hierarchy by name.
Download the “TM1 Dimension Sort.zip” file above that contains the 3 Turbo Integrator files. And using this solution will allow a dimension to be sorted alpha-numerically within multiple levels.
