Cognos Insight, Rapid Prototyping on Your Desktop
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:
Define Mapping: This is perhaps the most important step since we’re essentially setting up the foundation of our multi-dimensional structure which is a TM1 OLAP cube. At a minimum, we will map and define dimensions, levels and measures. For this example, our first three columns will become our ‘Dates’ dimension (Year, Quarter, Month as our levels). The next two columns, ‘Order Method Type’ and ‘City’, will become two single-level dimensions and our Revenue column will be the only measure. Notice that Insight assumes that Year, Quarter and Month will be their own dimensions. We need to map these as their own levels within a single dimension. We do this by selecting each column and mapping them accordingly:
In this case, we’ll rename ‘Year’ to ‘Dates’ as our dimension name and will set year as level 1, quarter as level 2 and Month as level 3. The other two dimensions look OK and Revenue has been correctly identified as a measure with a data type of currency. Next, we confirm the import. Now the TM1 cube will be built based on our mappings.
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:
Next, we can create a comparison calculation in Cognos Insight.
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.
Final Words
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.
