Oracle Communities

Oracle Analytics and AI

Child Item

how to build dynamic week/month relationship in time dimension/hierarchy

I have a requirement in which a dynamic calendar is needed. For example, 2012week 5 belongs to month 2 for current year report (report month sales in year 2012), but the same 2012week 5 will belong to month 1 in the last year metric (report month sales in year 2013). This is because 2012 has 53 weeks and it has to be restated in the last year metric. So a calendar year with 53 weeks has different week/month relationship when it is used in a regular metric and when it is used in a last year metric (ago function). Any recommendation on how to build this time dimension and hierarchy?

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

Build the row's corresponding last year surrogate key at ETL/ELT time ... then you can build using a canonical date approach. Leaving this to the logic layer of OBIEE might prove difficult and non-performing.

User729543-Oracle

Can you give it in more detail? For canonical date approach, do I need multiple dates in the fact table to join with dim on different keys? Actually what i think is if the time hierarchy used in ago can be a different time hierarchy used in the report?

Thomas Dodds

Canonical approach has ONE date dimension and you alias the fact multiple times (you join the date key in the fact once to the current date key in calendar and one to the previous period key on the same row in dimension);

logically you've conformed the date dimension across two facts - even though you only have one (each fact/dim) physical table.

the more hierarchies you build the more you manage ...

asim cholas

What exactly you are trying to report? Could you provide some samples?

User729543-Oracle

So, in this way, we will not use AGO function for last year metric. Instead the LY metric will come from the fact alias that join the date key on previous period key in the dimension. Is that right?

Actually this may not work. The data is stored at week level. The report is at period level. The group by columns will be the same no matter which alias is used.

User729543-Oracle

Hi Asim

What we are trying to report is TY sales vs LY sales. The data is stored at week level and the report is at week or month level. In the result, sales in week 2012week5 should belong to month 2 if 2012 is TY and same week sales in 2012week5 should belong to month 1 if 2013 is TY and 2012 is LY. This is because 2012 has 53 week and the first week of 2012 should be shifted out in LY metric and the entire week/month relationship in year 2012 has to be adjusted.

Thomas Dodds

Period is a logical aggregation of week, right? so add the corresponding keys to your dimension ... you need a well formed time/calendar dimension.

"This is because 2012 has 53 week and the first week of 2012 should be shifted out in LY metric and the entire week/month relationship in year 2012 has to be adjusted."

^ do that in your ETL logic when building the dimensional keys

Martin van Donselaar

Your period dimension should be like this if you want to use thomas' approach:

Year Month WeekNr WeekNr_LY

2012 01 201201 201101

2012 01 201202 201102

2012 01 201203 201103

2012 01 201204 201104

2012 02 201205 201105

...

2013 01 201301 201201

2013 01 201302 201202

2013 01 201303 201203

2013 01 201304 201204

2013 01 201305 201205

Then you can join the Fact table to WeekNr and an alias of the Fact to WeekNr_LY to get LY values.

Map the fields in your logical layer accordingly.

User729543-Oracle

There is no issue when the report is at week level where the data is stored. The problem is when the report is at month level. The sql will group by the primary key at month level. So if the primary key at month level is 2012||02 then the week 201205 will be always the second month of year 2012. The requirement is that the sales in this week should included in the first month of the year when 2013 is on report (shown in LY metric), The sales in the same week should be included in the second month of the year when 2012 is on the report (shown in TY metric).

Example:

Month Sales TY Sales LY

201201 6 1 -----TY include week 201201 to week 201204

201202 6 1 ------TY include week 201205 to week 201208

...

.....

201301 8 7 ---- LY include week 201202 to week 201205

201302 10 8 ---LY include week 201206 to week 201209

From the example above you can see the same month sales (201201, 201202) are different when it is in TY metric and LY metric. This is because shifted calendar is used when 2012 is in LY metric, but the regular calendar should be used when 2012 is in TY metric. So 2 calendars have to be used in the same report, 1 is for TY metric and 1 is for LY metric.

Martin van Donselaar

How can the Primary key be at Year Month if the data is stored at week level?