Different Reporting Periods

[MarenC]

Hi,

We have a requirement to report off 2 different set of periods (Calendar and Business).

This is at Year, Month and Week level.

For example, 31/12/2024 falls into:

Calendar Year 2024
Calendar Period 12
Calendar Week 52

Business Year 2025
Business Period 1
Business Week 01

The requirement is to report off the above in a single cube.

The only way I have so far got to do this is by having 2 dimensions for Year, Period and Week. So 6 time dimensions in the same cube.

Now this appears to work but it seems messy.

Can anyone think of a better solution or had more experience of this and can provide any advice?

Maren

[gtonkin]

I would say this is an opportune use case for an alternate hierarchy.
Because C level members are unique in each hierarchy, you do not need to worry about them clashing.

The only gotcha is how you will be implementing.
Will need PAW/PAfE and URs in PAfE to deal with the alternate hierarchies.

Edit: There are other tools out there like Apliqo, Slice etc. that would help on the client side if not using PAW/PAfE

[MarenC]

Hi George,

I did think about that or using another rollup but the issue is that the value will be the intersection of the 3 different dimensions and the values are held at leaf level.

So the same value, lets say £100, goes into Calendar Year 2024, Calendar Period 12, Calendar Week 52 and Business Year 2025, Business Period 1, Business Week 1.

How would the alternate hierarchies deal with 3 different dimensions and post the value to different leafs in each of those dimensions?

I don't see how it solves the issue?

Maren

[gtonkin]

HI Maren,

Based on the date provided 31/12/2024, I would expect you to be working with a continuous time dimension, not separate dimensions for year, period etc.

I am then specifically talking about Hierarchies, not Rollups. If you were going to use rollups you would have clashes between periods and weeks.

Calendar Rollups vs Hierarchies.png (12.84 KiB) Viewed 772 times

Based on the attachment, I am suggesting the bottom approach where the Leaf members exist in all hierarchies but how they are rolled up within that hierarchy depends on whether you are dealing with Calendar or Business.

When it comes to reading data from the cube you would be reading from Period:Calendar:2024-12 for example to view the monthly values for 2024-12 from a calendar perspective. Similarly, if you read from Period:Business:2024-12, the value retrieved would likely differ as it relates to periods rolled up into the Business hierarchy.

HTH

[MarenC]

Hi George,

Apologies but there is no date dimension in the cube.

The date dimension is in a Staging cube and a TI process would populate the relevant Year, Period and Week based on a Date lookup cube, which would include the dates business, calendar year/period/week.

Maren

[gtonkin]

And I guess this is why many have changed over the years to a "Period" or similar continuous dimension...
Pros and Cons, but you are stuck with a major con unless you can redesign or move to a reporting cube.

Maybe someone else has a smart idea...

[lotsaram]

I don't think it's messy or at all difficult to do assuming that the cube has a continuous time dimension of a low enough granularity such that you can have separate hierarchies for calendar week->month->year and financial week->period->year. This would probably mean the base granularity needs to be date.

If you want to do it with discrete year, month and week dimensions then I would prefer to keep it to 3 time dimensions and an additional FY / CY toggle dimension with one of either FY or CY being the "lead" for data entry and the other rule calculated via a lookup cube that has the calendar to financial period conversion. This will have less data but not perform as fast (in the dependant or follower PoV) due to the rules.