Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
How should I store dates of configurable granularity in my data warehouse?

Unanswered How should I store dates of configurable granularity in my data warehouse?

  • Wednesday, February 02, 2011 5:12 PM
     
     
    Sign In to Vote
    0

    Hi everyone, 

    In the front end of my application, the user can capture information against a date.

    The user can also create new levels of granularity at which to capture this information. Currently, they only use Year and Quarter.

    In my operational schema, I have a "Period" table, linked to a Period Type. Each Period entity has a type {currently, Quarter or Year} and a parent {Null for the year values and the year associated to each quarter)

     

    So...

    How best should I model this in my data warehouse dimension? The bredth of the table is configurable, so I can't really have the standard year and quarter column easily. And yet the parental relationship is obviously important.

     

    Any help much appreciated.

     

     

     

     

     

All Replies

  • Saturday, April 21, 2012 3:22 AM
     
     
    Sign In to Vote
    0

    MrMadders,

    It sounds like a parent/child dimension structure is the right fit here.  And it lines up with how you're storing data in the Period tale already.

    Querying data from a parent/child dimension can be tricky, of course, but the techniques are well established in DW practices.  If you want to incorporate this data in a cube, SSAS multidimensional mode directly supports parent/child, and you should have a pretty straightforward cube implementation.

    Rob Kerr SQL Server MVP CTO, BlueGranite