Need to copy TM1 cube data from one server to another ... read below.
In IBM Cognos TM1 Turbo Integrator (TI) Process, we can use ODBO as data source type. Using ODBO we can connect to other OLAP cubes (ex: MS SSAS, TM1 cubes in other environment). ODBO is an abbreviation of abbreviation (OLE DB for OLAP - Object Linking and Embedding DataBase for OnLine Analytical Processing)
In this post, we will see how to use this ODBO in a TI process and copy a cube content from one TM1 server to another. To use the ODBO, we need to provide following information in the TI:
Note: If you have TM1 installed on AIX then please read this IBM KB article (Ref # 7041662) to work with ODBO
Before
we jump ahead, let me show the cube list from the 2 environment. In
the top TM1 server there are no cubes and dimensions. We will therefore
be importing plan_Report cube from Planning Sample. We will build the TI process to not only copy the cube data but also build the dimensions in the 1st server.
After you have successfully established the ODBO connection, click on the tab Load ODBO Cube. Select the cube from where you want to pick the data, and enter the name of the target cube, as shown below. Since we do NOT have the target cube, we will pick the choose option of Create cube. Since we are copying data from one cube to another, there is possibility of copying a very large number of records. In such scenarios, we wouldn't want to enable the logging (as this will create additional traffic, increase tm1s.log file size and slow down the process). Therefore the option of Enable cube Logging is unchecked.
Now, click on the next tab Cube Dimensions and then on MDX
Query tab, they should look like this below. Since none of the
dimensions exist in the target environment, I have chosen the option of
Create and no filter is selected. Observe the MDX of the highlighted
dimension.
Let's go back to the Cube Dimensions tab and alter the filter on Departments dimension. We could either select individual elements as shown
With level 2 selected the MDX will now appear as shown below. Notice the change in MDX between this and the one previously shown. In fact, you can compare the MDX for rest of the dimensions vs this one. You will notice that, in case of Department MDX is explicitly selecting Level2 members, while in the others, it is using ISLeaf function to evaluate.
Now Save the process as LoadCube_ODBO and close the TI Editor. Let's take a look at the processes list in the target environment.
Not only has it created the main process, but it also has created child processes for each of the dimension involved in the cube build. If you were to look in the prolog of the main process, in our case "LoadCube_ODBO", you will see that it executes the child processes first, before beginning the main process!
Well, I guess the engine is smart enough to create the child processes and in turn use them in the main process. Onelimitation
feature is that, if you were to open the main process, don't do
anything else, just do a save as, in our case let's save it as
"LoadCube_New_ODBO" and refresh the architect screen. You will notice
there is only one new process added to the existing list, as seen below:
The next logical question is, whether in the prolog tab of the new process
It looks like we have a problem, don't we? The processes are using new TI names for dimension build and yet in the architect they are not there! We are still not done yet :) ... We were in the prolog screen looking at the code; now, click on save button and check out architect. The processes have now been magically renamed to use the new names !!! This is the feature you got to be aware of.
In IBM Cognos TM1 Turbo Integrator (TI) Process, we can use ODBO as data source type. Using ODBO we can connect to other OLAP cubes (ex: MS SSAS, TM1 cubes in other environment). ODBO is an abbreviation of abbreviation (OLE DB for OLAP - Object Linking and Embedding DataBase for OnLine Analytical Processing)
In this post, we will see how to use this ODBO in a TI process and copy a cube content from one TM1 server to another. To use the ODBO, we need to provide following information in the TI:
- ODBO Provider Name - From the drop down choose IBM Cognos TM1 OLEDB MD Providee
- ODBO Data Source - Enter your Admin Host (IP address or the DNS Alias). If there are multiple NIC adapters present on your server, then enter the IP address on which the traffic of TM1 is routed
- ODBO Catalog - This is the TM1 server name. In below example we are connecting to Planning Sample instance
- ODBO User ID, ODBO Password - Enter the credentials required to connect to the TM1 server
- Connection Parameters - Leave it blank if you are working with native authentication (i.e. mode is 1), else you will need to enter the namespace name
Note: If you have TM1 installed on AIX then please read this IBM KB article (Ref # 7041662) to work with ODBO
After you have successfully established the ODBO connection, click on the tab Load ODBO Cube. Select the cube from where you want to pick the data, and enter the name of the target cube, as shown below. Since we do NOT have the target cube, we will pick the choose option of Create cube. Since we are copying data from one cube to another, there is possibility of copying a very large number of records. In such scenarios, we wouldn't want to enable the logging (as this will create additional traffic, increase tm1s.log file size and slow down the process). Therefore the option of Enable cube Logging is unchecked.
Let's go back to the Cube Dimensions tab and alter the filter on Departments dimension. We could either select individual elements as shown
With level 2 selected the MDX will now appear as shown below. Notice the change in MDX between this and the one previously shown. In fact, you can compare the MDX for rest of the dimensions vs this one. You will notice that, in case of Department MDX is explicitly selecting Level2 members, while in the others, it is using ISLeaf function to evaluate.
Now Save the process as LoadCube_ODBO and close the TI Editor. Let's take a look at the processes list in the target environment.
Not only has it created the main process, but it also has created child processes for each of the dimension involved in the cube build. If you were to look in the prolog of the main process, in our case "LoadCube_ODBO", you will see that it executes the child processes first, before beginning the main process!
Well, I guess the engine is smart enough to create the child processes and in turn use them in the main process. One
The next logical question is, whether in the prolog tab of the new process
- Is it still calling the old processes of dimension build? OR
- Is it calling the processes with new name in dimension build?
It looks like we have a problem, don't we? The processes are using new TI names for dimension build and yet in the architect they are not there! We are still not done yet :) ... We were in the prolog screen looking at the code; now, click on save button and check out architect. The processes have now been magically renamed to use the new names !!! This is the feature you got to be aware of.
