Date Hierarchy comparison between transformer cubes.

[vincydza@gmail.com]

Hi,

I have two cubes created in Cognos Transformer

1. Sales Analysis Cube
2. Sales BI cube.

The cubes are created and published in Content store. However when reports are created based on these cubes the levels for date hierarchy are not identical.

In the Sales Analysis cube Year/Quarter/Month/Week and Day show as Levels however in the Sales BI cube Year/Quarter/Month & Week show as parent child.

How can the date dimension in the Sales BI cube be configured to be identical to the Date Dimension in Sales Analysis Cube.

See attached document for comparison of date dimension between two cubes

Please advise

Vincent

[MFGF]

Hi,

I have two cubes created in Cognos Transformer

1. Sales Analysis Cube
2. Sales BI cube.

The cubes are created and published in Content store. However when reports are created based on these cubes the levels for date hierarchy are not identical.

In the Sales Analysis cube Year/Quarter/Month/Week and Day show as Levels however in the Sales BI cube Year/Quarter/Month & Week show as parent child.

How can the date dimension in the Sales BI cube be configured to be identical to the Date Dimension in Sales Analysis Cube.

See attached document for comparison of date dimension between two cubes

Please advise

Vincent

It looks to me like there is something funky going on in the Transformer model for your Sales BI cube. I'm seeing both the root category and the drill category in the image you posted - "All Dates" and "By All Dates", and normally you'd only see one. I suspect you have security which is suppressing a primary drill path, and this is an alternate? If so, you need to suppress the root category in this security view in Transformer and rebuild the cube.

Cheers!

MF.

[vincydza@gmail.com]

MF,

you are the best. your recommendation resolved the issue.  However I have two  qn

1. Why should the  root category be suppressed?
2. There are few dimensions where the levels are repeating. see attached image. Why are the levels repeating in the hierarchy.

Vincent

[MFGF]

MF,

you are the best. your recommendation resolved the issue.  However I have two  qn

1. Why should the  root category be suppressed?
2. There are few dimensions where the levels are repeating. see attached image. Why are the levels repeating in the hierarchy.

Vincent

Hi,

Glad that worked for you!

1. The root category is there as an anchor at the top of the category tree. However, your drill path also has a top-level category, which normally appears in reporting tools as the topmost category. Having both visible is an issue as the reporting tools don't expect two apexes to the hierarchy, and they get very confused :)

2. Can you compare your view in Report Studio with the level definitions in the dimension map in Transformer? Are there actually levels in there with the same names?

MF.

[vincydza@gmail.com]

MF,

Thanks for educating me on Question 1.  Now I understand.

With the 2nd Question, i realized that both the Dimension and the Level had the same name therefore it looked like level was getting duplicated. See attached image for details.  I have given them different names and this issue has been resolved.

Thanks a ton MF.  Appreciate your response.

Vincent

[MFGF]

MF,

Thanks for educating me on Question 1.  Now I understand.

With the 2nd Question, i realized that both the Dimension and the Level had the same name therefore it looked like level was getting duplicated. See attached image for details.  I have given them different names and this issue has been resolved.

Thanks a ton MF.  Appreciate your response.

Vincent

Ah. That makes sense! Thanks for the update - it might prove useful for others in the same situation.

Cheers!

MF.