(For more resources on IBM, see here.)
Before proceeding with the actual steps of the recipe, we will take a note of the following integration considerations:
- The measured Dimension in the TM1 Cube needs to be explicitly identified.
- The Data Source needs to be created in IBM Cognos Connection which points to the TM1 Cube. New Data Source can also be created from IBM Cognos Framework Manager, but for the sake of simplicity we will be creating that from IBM Cognos Connection itself.
- The created Data Source is used in IBM Cognos Framework Manager Model to create a Metadata Package and publish to IBM Cognos Connection.
- Metadata Package can be used to create reports, generate queries, slice and dice, or event management using one of the designer studios available in IBM Cognos BI.
We will focus on each of the above steps in this recipe, where we will be using one of the Cubes created as part of demodata TM1 Server application and we will be using the Cube as a Data Source in the IBM Cognos BI layer.
Ensure that the TM1 Admin Server service is started and demodata TM1 Server is running. We should have IBM Cognos 8 BI Server running and IBM Cognos 8 Framework Manager installed.
How to do it…
- Open the TM1 Architect and right-click on the Sales_Plan Cube.
- Click on Properties. In the Measures Dimension box, click on Sales_Plan_Measures and then for Time Dimension click on Months.
- Note that the preceding step is compulsory if we want to use the Cube as a Data Source for the BI layer. We need to explicitly define a measures dimension and a time dimension.
- Click on OK and minimize the TM1 Architect, keep the server running.
- Now from the Start menu, open IBM Cognos Framework Manager, which is desktop-based tool used to create metadata models.
- Create a new project from IBM Cognos 8 Framework Manager.
- Enter the Project name as Demodata and provide the Location where the model file will be located. Note that each project generates a .cpf file which can be opened in the IBM Cognos Framework Manager.
- Provide valid user credentials so that IBM Cognos Framework Manager can link to a running IBM Cognos BI Server setup. Users and roles are defined by IBM Cognos BI admin user. Choose English as the authoring language when the Select Language list comes up. This will open the Metadata Wizard – Select Metadata Source. We use the Metadata Wizard to create a new Data Source or point to an existing Data Source.
- In the Metadata Wizard make sure that Data Sources is selected and click on the Next button.
- In the next screen, click on the New button to create a new Data Source by the name of TM1_Demodata_Sales_Plan.
- This will open a New data source wizard, where we need to specify the name of the Data Source.
- On next screen, it will ask for the Data Source Type for which we will specify TM1 from the drop-down, as we want to create a new Data Source based on the TM1 Cube Sales_Data.
- On the next screen specify the connection parameters. For Administration Host we can specify a name or localhost, depending on the name of the server. In our case, we have specified name of the server as ankitgar, hence we are using an actual name instead of a localhost. In the case of TM1 sitting on another server within the network, we will provide the IP address or name of the host in UNC format.
- Test the connection to test whether the connection to the TM1 Cube is successful.
- Click on Close and proceed. Click on the Finish button to complete the creation of the Data Source.
- The new Data Source is created on the Cognos 8 Server and now can be used by anyone with valid privileges given by the admin user. It’s just a connection to the Sales_Plan TM1 Cube which now can be used to create metadata models and, hence, reports and queries perform the various functions suggested in the preceding sections.
- Now it will return to Metadata Wizard as shown, with the new Data Source appearing with the list of already created Data Sources.
- Click on the newly created Data Source and on the Next button.
- It will display all available Cubes on the DemoData TM1 Server, the machine name being the server name (localhost/ankitgar).
- Click on the Sales_Plan cube and then on Next.