Cannot Drill Through Between PowerCubes Because MUNs Do Not Match

We recommend that business keys be unique throughout the dimension for PowerCubes. These keys are used as the source value for levels in a hierarchy of a dimension. If the values are not unique throughout the dimension, the corresponding Category Code values may be generated with tildes.

For example, if a category for the Product Line level has a source value of 101 and a category in the Product Type level has a source value of 101, the Category Code value for the Product Type level is automatically generated with a unique value such as 101~245. The Category Code values are used in the Member Unique Name (MUN) for each member, for example, [Sales and Marketing].[Products].[Products].[Product type]->:[PC].[@MEMBER].[101~245].

Because these values are generated automatically, they cannot be guaranteed from one cube build to the next or in a build for another cube with the same dimension structure using the same source values. Therefore, drilling from one PowerCube to another on what appears to be the same member might not work since the MUNs might not match.

If the MUNs do not match, consult the cube modellers to see if the business keys can be made unique throughout the dimension. If this is not likely, or might take some time to resolve, you can use calculations to pass the source value from one PowerCube to another for drill-through.

For more information about drill-through access, see the Drill-Through Access chapter in the IBM® Cognos Business Intelligence Administration and Security Guide.

Procedure

In the target report, create a filter with the following syntax: filter([Hierarchy or Level], roleValue('_businessKey', [Hierarchy or Level]) = ?Parameter?)
For example:
filter([Sales Cube].[Products].[Products].[Product type], roleValue('_businessKey',[Sales Cube].[Products].[Products].[Product type]) = ?Prod Type?)
In the source report, create a Query Calculation which is used to pass the business key (source value) to the target report by mapping it to the target parameter in the drill-through definition. Use the following syntax:
roleValue('_businessKey', [Hierarchy or Level])

For example:

roleValue('_businessKey', [sales_and_marketing].[Products].[Products].[Product type])