Post reply

Time Dimension best practice?

  • March 21, 2013 at 3:59 am

    #272377

    Currently I have one table and one date record with a format of (2011-05-25 23:00:00.000) I would like to create a time dimension that includes Years, Quarters and Months as it will be easier for reporting on. What is the easiest way to do this ?

  • March 21, 2013 at 4:11 am

    #1599300

    Create a view in SQL and base your dimension off that. You might want to write this to a table to give you some flexibility to name quarters or semesters etc

    select

    Year(GetDate()) As Year_Key

    , Year(GetDate()) * 100 + Datepart(qq,GetDate()) As Quarter_Key

    , Year(GetDate()) * 100 + Datepart(mm,GetDate()) As Month_Key

    , DateName(mm,GetDate()) + ' ' + Cast(Year(GetDate()) As VarChar) As Month_Name

    Mack

  • March 21, 2013 at 4:12 am

    #1599302

    Here's a script to create a time dimension:

    Script to Populate Date Dimension, without Using a Cursor

    I wouldn't stop at the month level. I would create the dimension to the date level. You never know if the requirements for reporting change and this way you'll be ready. And it aren't that many rows.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 21, 2013 at 4:14 am

    #1599305

    Agree - I got bored writing the SQL.....

  • March 21, 2013 at 4:18 am

    #1599307

    Thanks for the reply's ! I'll let you know how I get one !

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply