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?
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.
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.