Multiple date attributes in Fact table

Thomas Dodds · Sep 1, 2016 8:43AM

Build the counts in each of the logical fact tables (each with its own source) then co-locate them in the same fact folder in your presentation subject area...

Or have one logical fact with 3 logical table sources

Hitachi ServiceDesk · Sep 1, 2016 12:23PM

Thanks Thomas,

So you want to say we need to have three different sets of measures?

Data_Quality_Fact - Created Date

#Created Date - Events

#Created Date - Open Events

#Created Date - Closed Events

Data_Quality_Fact - Corrected Date

#Corrected Date - Events

#Corrected Date - Open Events

#Corrected Date - Closed Events

Data_Quality_Fact - Modified Date

#Modified Date - Events

#Modified Date - Open Events

#Modified Date - Closed Events

Thanks

Hesh

Thomas Dodds · Sep 1, 2016 1:39PM

Yes, however you plan on using the information will drive your design ... you can combine the measures into a single fact presentation table for simplicity.

Joel · Sep 1, 2016 4:43PM

The alternative, as mentioned previously by @Thomas Dodds would be to have a simplified presentation layer whereby you'd have 3 measures only. In the BMM layer, you'd also have 3 measure columns only but the logical fact will have 3 logical table sources mapping to the underlying fact aliases in the Physical Layer.

Dependent on which date dimension is used in reports, OBIEE should be able to determine the correct logical table source to use and in turn the correct fact alias.

Hitachi ServiceDesk · Sep 2, 2016 1:29AM

Thanks Thomas and Joel.

In Presentation layer , if I have single set of facts, only 3, using different Logical Table Sources, in this case we have to have three different time dimension folders?

If no how to know whether we are referring to which date dimension?Created Date,Corrected Date,Modified Date

Date Dim

Day

Month

Year

Data_Quality_Fact

#Events

#Open Events

#Closed Events

Thanks

Hesh

Joel · Sep 2, 2016 1:35AM

You are correct. You'll need 3 date presentations folders.

Sent from my iPhone

Hitachi ServiceDesk · Sep 2, 2016 1:46AM

Thanks Joel

[Deleted User] · Sep 2, 2016 5:05AM

If this concludes the thread then can you please close it by marking the appropriate answers? Right now it's still "Not Answered" which is not very helpful for other forum users when they browse or search the forum. Thanks.

Thomas Dodds · Sep 2, 2016 10:54AM

In Presentation layer ....

Date Dim

Day

Month

Year

Data_Quality_Fact

#CreatedEvents

#ModifiedOpen Events

#ModifiedClosed Events

#CorrectedOpen Events

#CorrectedClosed Events

Now I pick Date = 9/1/2016 and I can see:

#CreatedEvents = all new events on that date

#ModifiedOpen Events = all open events that were modified on that date

#ModifiedClosed Events = all closed events that were modified on that date

#CorrectedOpen Events = all open events that were corrected on that date

#CorrectedClosed Events = all closed events that were corrected on that date

--DO NOT CREATE 3 DIMS (use the canonical time approach) ...

Hitachi ServiceDesk · Sep 2, 2016 11:56AM

Thanks Thomas, that helps!.

I conclude here that we have two options #1 is to create three Time Dimensions and one Fact #2 is to one Create Canonical time dimension and use three sets of Facts.

It is highly recommend to use #2 approach.

Thanks all for your great support and advise.

Regards

Hesh

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Multiple date attributes in Fact table

Answers