Cognos Insight has many potential business uses. One of these is the ability to rapidly prototype a report or planning scenario by comparing a report from Cognos Connection with an external Excel or CSV file. This particular example will walk us through the steps to import and compare an Excel budget scenario with Sales data from a Report Studio list report.
The first step is to begin with a new ‘workspace’ in Insight.
Import Cognos Report
Let’s get started with importing data from Cognos Connection.
Note: A simple Report Studio list report works best for the import data feature.
For source type, we’ll choose “IBM Cognos Report Data” and navigate to our report location in IBM Cognos Connection.
Preview the data:
Next, we’ll re-order our workspace to put Order Method types in the rows and 2007 as our columns of our crosstab. We’ll also filter on just Revenue as our measure.
Import Excel Budget Scenario
Let’s import our Excel budget file and merge with the current Sales Data cube. An important point here is that we do not want to simply go to the ‘Get Data’ menu to import the file. If we do this, a 2nd cube will be created. In this case we want to import the Excel budget data into the same cube so we can compare budget to revenue in the same crosstabs and charts. In this very simple example, we were careful to make sure that our budget file contains the same dimension and level structure as our Sales source report does.
Right click on the cube in the Data explorer and select ‘Import into cube’ (shown below):
The import exercise is similar to before, except this time we will choose a file as our source (Excel in this case).
On the next screen, we will apply an advanced mapping to map our columns to existing dimensions and levels, and bring in a new measure called Budget:
Now we can see our new measure, ‘Budget’ alongside ‘Revenue’ in our crosstab:
The comparison calculation uses an expression understood by a TM1 cube which uses a scorecard-style red/yellow/green indicator based on a <90%, 90%-110% and >110% expression. If we chose to, we could edit this calculation to change the thresholds and see different results. The expression looks like the one below.
As you can see, IBM Cognos Insight can be a valuable tool for rapidly prototyping reports and planning scenarios without the need for heavy technical skills required. Clearly, the data analyst would need to know and understand their data and also have some basic data mapping skills, but would not need to have advanced report authoring skills. The data analyst, in this case, could create this Insight workspace in short order to then share or publish back to an IBM Cognos Connection Application portal.