One tricky and poorly understood problem with the deployment of Cognos Transformer cubes relates to the concept of uniqueness within a dimension. Developers and users can proceed for a long time without having any problems with the uniqueness of categories, and then suddenly – usually as a result of deployment of an enhanced version of an existing cube – user reports may start to behave strangely. Selected categories may disappear from reports authored in PowerPlay, or drill-throughs in reports created in C8 may no longer function correctly. Managing these category codes (“Member Unique Name(s)”, or MUN(s)) requires a bit of forethought that may be skipped over in the development phase
These problems may be rooted in the fact that Transformer insists on a unique identfier across all categories in a dimension, regardless of the level of the dimension the categories exists at. This can cause some subtle and hard-to-diagnose problems.
Consider a trivial “sales” data set:
We have a denormalized “flat file” of data with a hierarchy of Country … State … City. If we create a set of dimensions in Transformer using this data set, we might create something like this:
At the level All_Countries, the level is defined as shown:
At the level All_States, the level is defined as shown:
Note that in both levels the Category Code is left undefined. This will be determined by Transformer, and Transformer will ensure that it will be unique for the dimension. It will base the code on the value in the Source for the level.
This uniqueness is key, because if we look back at our data, we note that we have the value CA for both a Country_CD (Canada) for level All_Countries and a State_CD (California) for level All_States. We have used Country_CD as the Source for each respective leve. By default, since we have not defined how to calculate the Category Code, Cognos will calculate it for us. It will do so based on the Source column for each level, using value CA in the case of the Canada category in All_Countries and the California category in All_States.
The potential problem arises because we must have unique values in the entire dimension for Category Code. When Transformer finds that it has a value CA as the Source in All_Countries, and the same value as a Source for All_States, it must calculate a unique value for Category Code for All_States for California. It does so by putting a tilde (~) sign and a number after the Source value. We can see this in the Categories in Transformer, after the categories for the cube have been generated. Note the value CA~8 for Category “California”:
To reiterate: the Category Code value must be unique in the dimension.
The problem arises if we use this Category in a report in PowerPlay, for example. Consider a PowerPlay report authored in Reporter mode, that includes California:
The key point here is that the report is using the Category Code CA~8 “under the covers”, not the value California, to identify the value to be returned.
For many users this is not a problem. As long as the calculated Category Code “CA~8” never changes the user’s report will be fine.
But what happens, if as part of a development process, for example, the Category Code gets changed? This can cause some real headaches. If the Category Code is changed for any reason, the users PowerPlay Reporter-mode report will break. One result could be the disappearance of the category that is in the report:
In Part 2 well take a closer look at what can happen when Category Codes change, and propose some solutions.
