We need to create a series of P&L reports in Cognos Analytics over a TM1 cube. We need Accounts to display with their ID and their current Description. Then when the description changes in TM1, be updated automatically in Cognos. It’s dead easy to do and here are the steps.
How to Create a Cognos P&L over a TM1 Cube
TM1 Cube Structure
We have the following dimensions in TM1:
- Month-Year
- Cost Centre
- Account
- Measures-GL
In the Account dimension, we have an attribute called Description.
In TM1, the cube is defined with Month-Year as the Time dimension and Measures-GL as the Measures dimension.
“All Cost Centres” is defined in the }HierarchyProperties cube, as the default element.
We are using CAM security, so have the TM1 model is set to Integrated Security Mode = 5.
We have a simple view as shown below.
Cognos Setup
In Cognos Analytics, we simply created a Planning Analytics data source in Cognos Admin, and added a package with our cube as the only cube in it. We identified English as the language and published it.
Create the Report in Cognos Analytics
These are the detailed steps to then create the report and use an Attribute to display on rows, rather than the account ID.
- Create Report, 1 column
- Select the data source as our new package
- Click the plus in the middle of the page and select crosstab
- Name it whatever you want
- Drag:
- All Cost Centres into columns and drill down
- All Accounts to rows and drill down
- A period (Jan-19) to the context filter
- The Value measure into the crosstab
Run the report and you should get the following report that shows Account IDs.
Add the Description TM1 Attribute
Now that is all very useful if you’re an accountant who knows all their accounts by ID. But what if you want to display the Description, stored in the Description attribute in TM1?
That’s pretty easy too. Just do this:
- Drag the Description from the Insertable Objects pane onto your report, say, to the right of the Account IDs.
- Click on the new field in your report and go to Properties and change the Source Type from “Member Type” to to “Data Item Value” and the Data Item Value you want to choose is Description.
- You will then have a report that shows the ID in one column and the Description right beside it, like this:
Remove the IDs so only the Attribute is Displayed
Finally, if you then want to remove the ID’s all together, just:
- Select the ID column, and
- Hit Delete in the toolbar
And viola, you will get this as your output:
Test Changing TM1 Attribute
Now save and close your report, then go back to TM1 and change one of the Description attributes. Then go back to Cognos and run your saved report. Bingo, the new Description show.
