Cognos 8, Transformer and Uniqueness

[Sergeson]

Hello,

I've got a problem that's really making me scratch my head.

When the cube is built inside transformer, it creates MUNs which I can use to build reports in Report Studio.  From what I understand, the MUNs should be create such that the end with a tilde. 

Example:

[A_LVL_03_ACCT]->:[PC].[@MEMBER].[140/~]

Some of our lower levels are created that way.  Others, for some reason, are created with a tilde and a number. 

Example:

[A_LVL_03_ACCT]->:[PC].[@MEMBER].[142/~~35114]

Almost every time the cube is built, the reports break and these guys are the culprit.  The end number changes. 

What could be causing this?

[rockytopmark]

My first thought is that this is probably based on how the PowerCube's levels are defined, meaning the Category Codes (Member's business key) are based on a surrogate key that can change.  If this is the case, then you could see this phenomenon, where the category code representing the same business key keeps changing. 

[MFGF]

This sounds like a classic case of having non-unique IDs (Category Codes) within the dimension.  Whenever Transformer encounters a non-unique category code (ie a category code on one level of a dimension which is the same as a category code at another level of the same dimension), it makes it unique by appending a tilde and a randomly generated number.  This in turn gives you problems with broken MUNS in your reports if you re-create the dimension in Transformer, as the numbers generated each time for uniqueness are random.

Regards,

MF.