Year and Month

[cognos05]

Hi All,

I have a time dimension with alternate hierarchy which has a Yearmonth that rolls up to a calnedar year and fiscal year .

I have some reports where I try to some how show the years on y axis and months on x axis with parallel period olap functions .

User is requesting for a Year and Month as a seperate dimensions so that they can build own reports or dashboards on both year and month seperately .

I know in relational we have them as seperate columns so this will work ,but for olap is it a good design to have them seperate dimensions.

[MFGF]

Hi All,

I have a time dimension with alternate hierarchy which has a Yearmonth that rolls up to a calnedar year and fiscal year .

I have some reports where I try to some how show the years on y axis and months on x axis with parallel period olap functions .

User is requesting for a Year and Month as a seperate dimensions so that they can build own reports or dashboards on both year and month seperately .

I know in relational we have them as seperate columns so this will work ,but for olap is it a good design to have them seperate dimensions.

Hi,

I have seen this approach before - especially in TM1 (Planning Analytics) cubes. In a regular date hierarchy each month member is a descendant of a specific year member, so even if the fourth month in 2020 and the fourth month in 2021 both have captions of 'April', they have different Member Unique Names (MUNs) so they are always distinct from each other in any report you write.

If you need to be able to show aggregations for 'April' regardless of which year, then having a separate Month dimension is really the only way to go. You just need to make sure that your report authors understand that the months won't aggregate up into years and that there will be no drill down/up capabilities.

Cheers!

MF.

[cognos05]

Yeah , but they wanted the one dimension with drill down and two more dimensions seperatley with year and month seperately .

Is it a ood design to have three dimensions for time period .

[MFGF]

Yeah , but they wanted the one dimension with drill down and two more dimensions seperatley with year and month seperately .

Is it a ood design to have three dimensions for time period .

The way I look at it is that the cube is there to provide the ability for users to analyse their data in the way they need to. It makes sense to try to design it to be as intuitive and efficient as possible, but the overriding priority has to be making it deliver the capabilities the users need. I often used to find that users would get confused if there were multiple date dimensions, but it sounds like your users are stating this as a requirement. As long as they understand how it works and what issues they might encounter, then go ahead - it's what they need. If you try to force them into your elegant design and it doesn't deliver what they need, they will just end up dumping the data out and using Excel :)

Cheers!

MF.

[cognostechie]

User is requesting for a Year and Month as a seperate dimensions so that they can build own reports or dashboards on both year and month seperately .

I know in relational we have them as seperate columns so this will work ,but for olap is it a good design to have them seperate dimensions.

It's been some time but I remember when you make a report using a cube, on the left side where you have the cube dimensions, there was an icon which will let you see the cube in a different way, one way was to see in a hierarchy manner and another was to see things at the level manner. Not 100% sure but I remember that's how I was able to get all members of a level regardless of the position of that level within the hierarchy.

[MFGF]

It's been some time but I remember when you make a report using a cube, on the left side where you have the cube dimensions, there was an icon which will let you see the cube in a different way, one way was to see in a hierarchy manner and another was to see things at the level manner. Not 100% sure but I remember that's how I was able to get all members of a level regardless of the position of that level within the hierarchy.

Yep, there is the structural view (Dimensions/Hierarchies/Levels) and the Member Tree view (where you see the members) but the limiting factor in this case is that the months are different members with different MUNs, so you can't create a visualization with years on one axis and months on the other, which I think is what cognos05's users need to do.

Cheers!

MF.