nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Multiple date attributes in Fact table

Hitachi ServiceDesk

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

Find more posts tagged with

Accepted answers

All comments

Andrew Fomin.

Yes, you need three date dimensions. You have three completely different dates, each of them is a separate dimension.

Joel Acha

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.

Manoj Dixit

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.

[Deleted User]

Just my 2 cents:

Personally I prefer a canonical time dimension and role-playing facts to central facts with multiple time dimensions...

Hitachi ServiceDesk

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

Manoj Dixit

Hi,

With regards to the second question, here's a useful link.

https://greatobi.wordpress.com/2011/06/17/advanced-metadata-presentation/

Regards.

Manoj Dixit

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.)

Thomas Dodds

Canonical means one MAIN one ...

2016-08-31 15_17_55-Oracle BI Applications_ Canonical Time Dimension in OBIEE.png

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...

[Deleted User]

Yes. That. Sorry I'm posting from my mobile and not really the best medium to write endless explanations.

Hitachi ServiceDesk

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