IBM Cognos 8 BI – Query Across Dimensional Data Sources

In some cases, a comparison between two dimensional data sources may be required where it is not feasible to contain all the data in one data source.
For example, a company may capture a rolling twelve month period at the month level in an IBM Cognos 8 PowerCube, which is built at the end of each month. Due to the volume of data captured, the size of the PowerCube can be well over one gigabyte in size and require a longer build time. The same company may also capture the current month's data at the day level and build this cube once a day. The company may want to compare their current month's performance each day with that of the previous month's performance to see if they are on track.
Using the following technique, the company can compare the current month's data, found in the Current Month PowerCube, with the previous month's data found in the Rolling Twelve Month PowerCube.

---

Create a Package with Multiple Cubes
To compare the data between the two PowerCubes in a report, both PowerCubes must be contained in the same IBM Framework Manager package and published to IBM Cognos 8.

1. To create this package, ensure data sources are created for each PowerCube and then import them into Framework Manager as shown below.
   
   
   
   
2. Create a package.
   
   
   
   
3. Ensure the package contains both PowerCubes.
   
   
   
   
4. Publish the package to IBM Cognos 8.

---

Create a Union Query
The following steps will illustrate how to relate the data from the two PowerCubes using a Union object.

1. Open IBM Cognos Report Studio and select the package just published.
2. Select Blank for the new report.
3. Using the Query Explorer, click on Queries.
   
   
   
   
4. From the Insertable Objects pane, add three queries to the middle pane.
   
   
   
   Two of these queries will be used as base queries, one for each PowerCube. Each query will contain the same number of items with the same names and in the same order so that they can be unioned in the third query.
5. Rename the queries as shown below.
   
   
   
   
6. Double-click the Current Month query and then add the following items from the Current Month cube to the query.
   • Month level from the Time dimension
   • Product line from the Products dimension
   • Order method type from the Order method dimension
   • Quantity from Measures
   • The results appear as shown below.
   
   
   
   
   A data item will be added to this query to identify items from this query as representing the current month's data.
7. Under Insertable Objects, click the Toolbox tab and then drag a Data Item to the query.
8. In the Expression Definition pane, type 'Current Month'.
   
   
   
   
9. Click OK, and then rename the data item to Month Flag.
10. Using the Query Explorer, navigate to the Previous Month query.
    
    In this query, the Month data item will be a calculation that uses a dimensional function to retrieve the last month in the cube. The last month in the rolling 12 month cube contains the previous month's data.
11. From the Toolbox, drag a Data Item to the query, and then in the Expression Definition pane, type closingPeriod(.
12. Under Available Components, expand the Rolling 12 Month cube, add the Month level from the Time dimension to the expression, and then type ).
    The expression appears as shown below.
    closingPeriod([Sales and Marketing - Rolling 12 Months].[Time].[Time].[Month])
13. Click OK, and then rename the data item to Month.
14. Add the following items from the Rolling 12 Month cube to the query.
    • Product line from the Products dimension
    • Order method type from the Order method dimension
    • Quantity from Measures
15. Under Insertable Objects, click the Toolbox tab and then drag a Data Item to the query.
16. In the Expression Definition pane, type 'Previous Month'.
    
    
    
    
17. Click OK, and then rename the data item to Month Flag.
18. From the Query Explorer, click on Queries, and then add a Union item to the right of the Combined Cubes query.
    
    
    
    
19. Drag the Current Month query to the top dotted box and the Previous Month query to the bottom dotted box of the Union item.
    
    
    
    
20. Double-click the Combined Cubes query, and then add all the items from Union1 to the query.
    
    
    
    

Now if the tabular data for this query is viewed, there will be rows for the current month and the previous month as indicated by the Month Flag item.


Data is retrieved from both cubes, the current month, in this case July 2007, and the previous month, June 2007.

---

Create Calculations to Separate Current and Previous Month Values
Since the result set of the Combined Cubes query is a union from which current month and previous month values are to be compared, the quantity values need to be separated based on the Month Flag values. To do this, two calculations will be created, one for the current month values and one for the previous month values.

1. In the Combined Cubes query, from the Toolbox, add a Data Item to the query.
2. In the Expression Definition pane, create the following expression.
   
   

   case when [Union1].[Month Flag] = 'Current Month' then [Union1].[Quantity] end

   
   This expression checks the value of the Month Flag. If it is 'Current Month' then retrieve the Quantity value.
3. Click OK, name the data item Current Month, and then set the Regular Aggregate property to Total.
   
   
   
   
4. Create a second Data Item called Previous Month with the following expression and set the Regular Aggregate property to Total.
   
   

   case when [Union1].[Month Flag] = 'Previous Month' then [Union1].[Quantity] else 0 end

   
   This expression checks the value of the Month Flag. If it is 'Previous Month', then retrieve the Quantity value, otherwise make the value zero. The zeros are added so that null values are not returned. This is required for a future Boolean variable calculation which will compare the Current Month and Previous Month values. The Combined Cubes query now contains the following items.
   
   
   
   

---

Create the Report and Add Conditional Formatting
Now that the two cubes have been unioned together and values have been created to compare current month data to previous month data, the report layout can be created. The report in this example will be a crosstab.

1. Using the Page Explorer, navigate to Page1, and then from the Toolbox, add a Crosstab object to the report layout.
2. Select the crosstab and then in the Properties pane, change the Query property to use the Combined Cubes query.
3. Under Insertable Objects, click the Data Items tab, and then drag items from the Combined Cubes query to the crosstab as shown below.
   
   
   
   
4. Using the Condition Explorer, click Variables, and then add a new Boolean Variable object to the Variables pane.
5. In the Expression Definition of the new Boolean Variable, create the following expression.
   
   

   [Combined Cubes].[Current Month] > [Combined Cubes].[Previous Month]

   
   
6. Click OK, and then rename the new variable to var_Month_Comparison.
   
   
   
   
7. Using the Page Explorer, navigate back to Page1.
8. Select the fact cells for the Current Month column.
9. In the Properties pane, beside the Style Variable property, click the ellipsis.
10. Under Variable, select var_Month_Comparison from the drop down list, and then click OK.
11. Using the Condition Explorer, under var_Month_Comparison, click Yes.
12. In the Properties pane, change the Foreground Color property for the Current Month fact cells to Green.
    
    
    
    
13. Using the Condition Explorer, under var_Month_Comparison, click No.
14. In the Properties pane, change the Foreground Color property for the Current Month fact cells to Red.
    
    
    
    
15. Run the report. The results appear as shown below.
    
    
    
    Current month values that are less than those of the previous month are highlighted in red, and those that are greater than or equal are highlighted in green.
    For a consistent display of null values, a conditional style can be used to hide the zeros displayed in the Previous Month column. Remember, the zeros were used to replace null values, which allows the Boolean Variable calculation [Combined Cubes].[Current Month] > [Combined Cubes].[Previous Month] to behave as expected.
16. In Report Studio, right-click the fact cells of the Previous Month column, point to Styles, and then click Conditional Styles.
17. Click the New Conditional Style icon, and then click Advanced Conditional Style.
    
    
    
18. Click the New Advanced Condition icon in the lower left corner, and then in the Expression Definition pane, create the following expression.
    

    [Combined Cubes].[Previous Month] = 0

    
    
19. Click OK, and then click the Edit icon (pencil) beside the new condition.
20. Click on the Advanced tab, and then from the Visible drop down list, select No.
21. Click OK to all three open dialogs, and then run the report again.
    
    
    
    The zeros are now hidden from the Previous Month column.

---

Conclusion
Using the techniques in this document, authors can compare and conditionally format values from different dimensional sources.

---

Download

Description	Name	Size	Download method
Zip file	Query_Across_Dimensional_Data_Sources.zip	3,430KB	HTTP