Nesting Dimensions from SAP BW in Report Studio

Nesting dimensions from SAP BW InfoCubes in Cognos Report Studio reports can sometimes lead to failure when the report is run.  This is primarily because the amount of data returned by the query can very quickly become enormous.  This discussion gives some pointers as to how you might overcome the problem.

To understand why nesting dimensions can very quickly lead to massive data returned by a query, we first need some background.  In SAP BW, an InfoCube is made up of a series of dimensions (called Characteristics in BW speak).  Each of these dimensions is made up of a series of categories or members (Characteristic Values in BW).  The dimensions hold every possible value, regardless of whether there is data for the member or not.  Thus if you have an InfoCube with three dimensions – product, customer and time, and measures (Key Figures in BW), and the customer and product dimensions have 1,000 members each and time has, let’s say 20 members, then the InfoCube will have 1,000 x 1,000 x 20 = 20 million possible intersections.

When we create a Cognos Framework Manager package against that InfoCube (note you should avoid multicubes) and then a report against the FM package and nest Products into Customers, we end up with 1,000,000 rows.  Add 13 columns for time and we have 13,000,000 cells.

Normally in Cognos if we were addressing a PowerPlay cube, the unused members of a dimension would not be included in a cube, unless specifically added, and thus the report would be nothing like this large.  In BW it doesn’t work that way, 13 million cells are returned and then if we want, we can zero suppress them, and this is where the report is likely to fail just simply because of the volume of data.

To avoid this problem, we need to apply a filter command to the level of the dimension being nested.  For example, if we want ProductID nested in CustomerID, we would need to specificallly create a Data Item in the Report Studio report and apply the Filter command to that in the syntax filter(customerid,currentmeasure is not null).  Then only those customers with a value in the current measure will be included.

Thanks to Wayne McCallum of Goodman Fielder in New Zealand for explaining this to me.  Any errors are mine in describing a rather complex issue and if there are any, please let us know so we can correct them.  Any additional suggestions are also very welcome!