Source:http://www.ironsidegroup.com/
April 3, 2012 by
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.
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.
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.
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.
