The biggest advantage of using a cube as data source is its capability to pre-aggregate values. This recipe will show you how to exploit this feature of cube in your Cognos reports.
Create a simple crosstab report using GO Sales SSAS Cube package. Pull Organization Code2 and Code3 on rows, and Current Year on columns. Drag Stmt Year in the intersection.
- First, we will create aggregation using standard method. For that, select the Organization Code3 row.
- From the toolbar, click on the Aggregate button and choose Aggregate.
- Run the report to test it.
- Now go back to the Report Studio. Delete the new row created for Aggregation.
- Go to second tab (Data Item) of Insertable objects. Drag Organization Code2 again on report and drop it under Organization Code3 and format this row to be bold; shown as follows:
- Now run the report to test it.
- Notice that the aggregated values are the same.
The first method that we used to create aggregation rows, utilizes the Cognos's aggregation feature. That is a standard way in case of relational data sources. However, Cognos won't use the pre-aggregated values from the cube in that case. Instead, it will calculate the aggregations at run time either locally or at the database.
In order to utilize the pre-aggregation feature of cube, we then dragged the item Organization Code3 under Organization Code2. That way, we make Report Studio ask for the pre-aggregated values from the cube.
We can test that the values coming in the report from both approaches are same and correct. The latter one is a good practice when writing report against cubes. It dramatically improves the performance of the report.