Date Table?

[rmcgrath]

I have a monster table I am trying to apply Star Schema to and the table contains a large number of dates.  I deal with animals, so for example there are dates for molting, dates for being placed into a barn, dates when they get sold, etc.

What is your best advice on how to handle all these different dates within the data model?  Here is a screenshot of a lot of them:

[Ashish_Mathur · Accepted solution]

Hi,

May be you should select all columns other than the Date columns and then select "Unpivot Other Columns".  Now you will get all dates in a single column called Value.

[rmcgrath]

Completely forgot about that approach!!  Thank you!

[Ashish_Mathur]

You are welcome.

[DivkLearner]

Hello @rmcgrath, 

We can create a data table or think about various other options. The question is what is the end result or the desired out of data analysis? If that becomes clear, we can give you more educated response. 

Best regards,
DivKlearner – A Bit Forward Daily
Join us as we explore and learn IT together.
Discover simplified IT learning on YouTube

[rmcgrath]

Understood.  Let's say I would like to be able to show data by different views.  For example, I would like to show a "Molt View", which would then use the Molt Dates.  How do I get all those dates into a dimension date table and relate it back to the Fact Table? Or is that not the best way to go?

[DivkLearner]

Since there are four dates for Molt, I would want more information on what you will display in the View. 

1. Do you want to show the report based on House_Molt1 Start time and House_Molt1 end Time?
2. Do you want to show the report based on House_Molt2 Start time and House_Molt2 end Time?

Does "Molt" mean repairing? if yes, then one house can have multiple repairs. If that is the case, your process is fine. But if means something else, please elaborate. 

Best regards,
DivKlearner – A Bit Forward Daily
Join us as we explore and learn IT together.
Discover simplified IT learning on YouTube

[rmcgrath]

Absolutely spot on...what you described is definitely a view I would want to display.  Yes - Molt means the birds are "recharging their batteries" so to speak.  So they do not lay eggs during that time.

There are several other views I would like to be able to display as well - that's why I don't want to just remove the columns.  I'd like to have access to all of them.  

With that said, how would I set up a dimension table that would relate back to the fact table?  I'm struggling to understand how that would work with so many different dates?