Forum Discussion

rmcgrath's avatar
rmcgrath
Advocate II
1 year ago
Solved

Date Table?

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:

 

Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

7 Replies

  • DivkLearner's avatar
    DivkLearner
    Resolver I
    1 year ago

    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's avatar
      rmcgrath
      Advocate II
      1 year ago

      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's avatar
        DivkLearner
        Resolver I
        1 year ago

        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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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.