(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.
(For more resources on IBM, see here.)
- The next screen allows users to Select Locales. We don't have any such considerations so go ahead by clicking on the Next button
- Uncheck Create the default package checkbox and click on the Finish button.
- This will now open the Cube in the IBM Cognos Framework Manager Tool. We will now create a new metadata package which will include the Cube. We will then publish it onto the IBM Cognos Connection.
- Right-click on the Packages icon and in the Create Package wizard provide a Name to create a new package, by name of TM1_Demodata_Sale_Plan. The Create Package wizard pops up when we right-click on Packages, click on Create | Package.
- Now we have created the TM1_Demodata_Sales_Plan package and want to publish it on the IBM Cognos BI Server so that it is available to users to work with reports and the package.
- In the Publish Wizard, uncheck the checkbox Enable model versioning, which is used to maintain different versions of the same model. Click on the Next button.
- In the next screen, define the security on the metadata package if applicable. In our case we will not specify any additional package level security and so click on the Next button.
- Publish the package by accepting the default parameters and click on the Publish button.
- So, now we have successfully published the package to the IBM Cognos Connection on the IBM Cognos BI server. Click on Finish and save the model.
- Now open the IBM Cognos Connection page by clicking on the URL to access the IBM Cognos BI Server. In general, installations have URLs in the following format http://cognos8.
- This is provided by IBM Cognos admin, once installation of IBM Cognos BI Server is done and security has been set up. The IBM Cognos Framework Manager tool is then configured to talk to the IBM Cognos BI Server through IBM Cognos Configuration, which is desktop-based and contains connection parameters to the IBM Cognos BI Server.
- We will be using a similar IBM Cognos 8 setup; however, configuration details have been skipped and we will directly focus on using the newly published package and making use of it.
- Log on to the IBM Cognos Connection by clicking on the URL. Provide login credentials and we are now able to view all the published packages, along with our TM1_Demodata_Sales_Plan package based on a TM1 Cube.
- Now click on the TM1_Demodata_Sales_Plan package and then click on the Launch menu.
- The menu displays a list of available designer studios available. Here Query Studio and Report Studio are available to us. Depending on security privileges, we may be given access to one or more studios. Here, for our users, Analysis Studio and Event Studio are not visible and hence not available to us. We will try opening the package in Report Studio and create a report from it.
- Select the Report Studio option and a pop up will open the studio, wherein the report can be authored based on the package and on our case based on the TM1 Sales_ Plan cube
- In the Report Studio, the left-hand side pane, Insertable Objects, shows up the package we have just published, which has objects from the Sales_Plan TM1 Cube. Items can be dragged and dropped onto the right-hand side authoring pane to create the report. Report can be then executed and saved on the server.
- Reports can be saved to the server, from which they can be accessed by business users on an ad hoc fashion or can be scheduled to be executed at specific time intervals and distributed to users through e-mail in different formats.
- The preceding screenshot shows up the newly created and saved report on the IBM Cognos BI Server. As and when we make changes to the data in the Sales_Plan Cube, these will be reflected in the report in real time.
(Move the mouse over the image to enlarge.)
In the IBM Cognos Framework Manager Tool we can only access the Cubes, we cannot edit them.
We are accessing this through a supported web browser as suggested in the IBM Cognos 8 BI documentation. In this recipe we are using IE, but other supported browsers can also be used in a similar fashion.
IBM Cognos BI does not have a write-back facility to the database and hence data cannot be updated to the Cube through the BI layer. It can just be read based on what is written through TM1.
How it works...
n this recipe, we have seen how we can use TM1 Cubes as Data Sources to create reports in IBM Cognos BI. Report authoring in IBM Cognos BI, and using various features according to different business use cases, is altogether a different subject of discussion. Here, the intention is to show how IBM Cognos TM1 and the IBM Cognos BI layer talk to each other.
TM1 Cubes can also be used in IBM Cognos Enterprise Planning applications in a similar fashion. Detail discussions on the IBM Cognos Enterprise Planning tool can be found in the IBM Cognos Enterprise Planning Guide by Packt Publishing.
In this recipe, we looked at how to use one of the Cubes created as part of demodata TM1 Server application and use that Cube as a Data Source in the IBM Cognos BI layer
- IBM WebSphere Application Server: Administration Tools [Article]
- Reporting Planning Data in IBM Cognos 8: Publish and BI Integration [Article]
- Q Subscription Maintenance in IBM Infosphere [Article]
- IBM WebSphere MQ commands [Article]