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
Answers
-
The primary key that I mentioned here is the key at month level in the calendar hierarchy. OBIEE uses that key as part of group by column in the sql. The PK in the fact table is week, not month. Sorry for the confusion.
0 -
This is why I suggested a physical approach and not the logical ... the hierarchy is a purely logical construct
0 -
Hi Tom, do you mean the Canonical approach (physical approach)? It does not work for month level either as the query from OBIEE will use the same columns to group by, so you will always get the same sales value for that month no matter it is in TY or LY.
0 -
the most consistent approach to the 53rd week in your date dimension and accounting for it in TY vs LY type analysis is to have the dimension properly formed such that each row has its corresponding LY keys set in ETL logic. this removes the functionality from the BI logic layer and places it in the database layer - not flexible (you've set the LY key for this row in your dimension; it can't be changed) but it performs (no logic to figure it out at run-time)
Use of information drives design ... keep that in mind - and never shy away from creating physical structures that allow for the analysis to be done. All too often people are trying to retrofit via the BI layerto a design that didn't take into account the intended use of information. Sometimes it can be done (flexible) but doesn't perform well.
The constant trade-off is:
flexibility high, performance low
flexibility low, performance high
You have a Ferrari, I have a Ford F-150 truck ... we're gonna go golfing ... want your Ferrari? No room for golf clubs. Want my Ford? No 150mph down the highway. What was the intention? Golfing - we take the Ford.
^ silly example ... but use drives design - always.
0 -
Thomas Dodds wrote:You have a Ferrari, I have a Ford F-150 truck ... we're gonna go golfing ... want your Ferrari? No room for golf clubs. Want my Ford? No 150mph down the highway. What was the intention? Golfing - we take the Ford. ^ silly example ... but use drives design - always.
^-- This. So much this.
0 -
I don't think you understand the solution. Grouping by Month is exactly what you want, that's what you are reporting right? Totals by month...
Follow these steps and you'll get just that:
Physical Layer
- Map TY weeks to LY in your period dimension (Alter table)
- Join Fact to TY week in period dimension
- Join Alias LY Fact to LY week in period dimension
- Join all other dimensions to both facts
Logical Layer
-Create a logical fact with both TY and LY facts as LTS
-Map TY columns to TY fact, Map LY columns to LY fact
Its not flexible but it gets you to the Golf court to put it in Thomas' words. :-)
0 -
Time dimension more than any other will impact performance if not optimal.
I would generate two new pseudo keys to use for your requirement using a rank function in the ETL on the combinations of values as shown in Martin van Donselaar's post.
0 -
Martin van Donselaar wrote:Its not flexible but it gets you to the Golf court to put it in Thomas' words. :-)
Plus it is what Oracle (Siebel acutally) themselves delivered in their pre-built RPDs prior to TimeSeries functions and it's s totally valid approach. For some scenarios even preferable to just TimeSeries.
0 -
When the logical doesn't seem to work .. fall back to the physical -- it's where it all started (and, yes I age myself!)
0
