If you are going to utilize the Cognos TM1 Architect drill through functionality from one cube to another, there are some considerations to take into account. You really want to keep dimensions that are common in both cubes the same if possible. This doesn’t mean that the cubes need the same exact dimensions. What I mean is that the month of “April” will not be recognized between a dimension called “Months” and “Mths”, even though “April” is in both dimensions.

There is a way to map the parameter being passed from one dimension to the other. In a nutshell, you will write scripting on the Prolog of your drill process that takes the parameters from the Parameter tab, and uses them as elements in subsets that you create in your process and assign to a new view. You only need to create the subsets for dimensions that are not the same, excluding your detail dimensions that are in your drill target cube. If the drill from source to target cube is used infrequently, you should not have much of an issue. However, if there will be many users needing to use this drill process at the same time, this could present a problem. The problem is that when you change the target view to a view created “on the fly” in the process to map your similar-but-not-the-same dimensions, there is a lock placed on the target view while that user has it open. If you have the situation whereby you have to have this drill available for many users, you can write it so that you concatenate the user name with the created view and subset names. That will inevitably create multiple views and subsets within Server Explorer. To get around the clutter in Server Explorer, you can fake them as Control Objects with the } bracket in the concatenation of the name. I really don’t suggest faking Control Objects, but it’s your choice to weigh the pros and cons of doing so.

Below is a TM1 drill through example of how to do this. In this scenario, the year dimensions were different and the element of the month dimension needed to get to a dimension that had days that consolidated into months. If you do not want to have the subsets and view to become Control Objects, remove the } bracket from the syntax.

For more information or if you have more specific questions, please don't hesitate to comment below or contact us at Sales@LodestarSolutions.com.

Originally written/published on 9/30/15 – SD