Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
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?
Answers
-
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.
0 -
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?
0 -
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 ...
0 -
What exactly you are trying to report? Could you provide some samples?
0 -
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.
0 -
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.
0 -
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
0 -
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.
0 -
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.
0 -
How can the Primary key be at Year Month if the data is stored at week level?
0