Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Multiple date attributes in Fact table
Hi,
I have a requirement here for data quality dashboard.
I have three date columns in Data_Quality_Fact table with names Created Date, Corrected Date and Modified Date.
My client wants to have all three columns in the Calendar Dimension.
Now my confusion is how can I model this in RPD? Do I need to create three date dimensions? like
Dim Date Created
Dim Date Modified
Dim Date Corrected
Please suggest what is generally we can do here?
Thanks
Hesh
Answers
-
Yes, you need three date dimensions. You have three completely different dates, each of them is a separate dimension.
0 -
Just to add to @Andrew Fomin. previous post above, you'll need to alias your date dimension in the Physical Layer and for each of the 3 dates, add the corresponding date dimension join between the fact and date dimension alias.
You'll then need to add the aliases to BMM layer, hierarchies, logical joins etc before exposing the new date dimensions to the Presentation layer.
0 -
The reason for having three aliases is they all are different. So, their column values can be different. Just having one dimension will give you results only when all three day values are the same (and that may not always be true). Your Client's business model itself needs three different Date dimensions to answer business queries.
Hth.
Regards.
0 -
Just my 2 cents:
Personally I prefer a canonical time dimension and role-playing facts to central facts with multiple time dimensions...
0 -
Thanks Andrew,Joel, Manoj and Christian.
If my understanding is correct after Physical layer and BMM joined with table aliases I will be having three different folders in Presentation layer?
Christian: what is canonical time dimension? I tried google but did not get the right answer, how can it be useful here?
Regards
Hesh
0 -
Hi,
With regards to the second question, here's a useful link.
https://greatobi.wordpress.com/2011/06/17/advanced-metadata-presentation/
Regards.
0 -
Here's another https://community.oracle.com/thread/2290549?tstart=0
(Don't know why it didn't allow me to edit the previous reply to insert this URL.)
0 -
Canonical means one MAIN one ...
src:Oracle BI Applications: Canonical Time Dimension in OBIEE
so you would alias your fact table and join the create date key in one to time. the modified date key to time, the corrected date key in time ... now you can relate facts through the time dimension (should you need to) --- pick a date from time and get all the create, modified and corrected facts for that day...
0 -
Yes. That. Sorry I'm posting from my mobile and not really the best medium to write endless explanations.
0 -
Thanks Manoj, Thomans and Christian.
Canonical time dimension is a very interesting discovery for me! I am glad that I know this one existing now!
I am able to understand up to creating fact aliases and use them in the Fact table Logical Table Source.
But what about measures? Do I need to create more measures? I have following measures for DQ currently.
#Events
#Open Events
#Closed Events
Regards
Hesh
0
