By Peter Beck, CBIP

In Part 1 of this series we examined how Category Codes are generated. For many users the “uniqueness” of these codes is never a problem, but if they change errors can result in reports that use them.

For example, if the categories are deleted and re-generated, Transformer may not assign the same value to the Category Code. Here is the result after deleting the category California from our example in Part1, and regenerating it:

Instead of CA~8, we now have CA~9. This causes the category to disappear from our Reporter-mode report from Part 1:

You can imagine the impact on users depending on reports with large numbers of dimensions that suddenly disappear on them. As a developer, if you don’t understand the impact of category code generation you can spend a lot of time scratching your head about what is going on.

So what can we do? The surest method is to always ensure that the Category Code is unique in a dimension by assigning a completely predictable value to it that will never change, and that you can count on as being unique at least in the hierarchy, if not in the entire model. By taking control of this value we are ensuring that it is not assigned by Transformer. This is probably best done in the database, during the generation of the dimension at the database level (not the Transformer “dimension”.)

As a quick, alternative method you can ensure uniqueness by creating a calculated column in the dimension that you know will be unique for the level. In this case I have created a calculated column for All_States called State_Cat, and one for All_Countries called Country_Cat.

These are calculations based on concatenating Country_CD and Country (and State_CD and State). Since these calculations will be unique in our dimension we can use them as sources for the Catergory Code in each respective level (only All_States is shown, but the idea is the same for All_Countries):

Now when we generate categories, we see that the Category Code for California is CACalifornia:

Because we are controlling this value, and not Transformer, we can be sure that this value will not change, even if the category is removed and regenerated in the Transformer model. This will ensure that reports that use this Category Code will continue to work correctly.

By Peter Beck, CBIP

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.