Useful Dates: The Many Uses of Date Tables

  • CheeseMan316

    SSC Veteran

    Points: 240

    Comments posted to this topic are about the item Useful Dates: The Many Uses of Date Tables

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article. Useful one...

  • Tobar

    SSCarpal Tunnel

    Points: 4767

    Have you considered combining your records? Instead of 1 record for every hour in a day, and one record for the every day, and one record for every month, etc. each record would contain the hour, the day, the month, the quarter, the year. You do save a little space on records and indexes, but the main benefit is that you have all the information about the "period" you are looking at. You do need more logic in the where to make sure you don't over select records.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Hey Joe, don't you have an article or two posted on the subject.

    It might be a good place to put a link to it!

    TIA.

  • Leonard Rutkowski

    SSCrazy

    Points: 2615

    Just one suggestion, make the key an int, and populate with the date yyyymmdd (20081216), instead of an identity column.

    You might want to create a separate table, for fiscal calendar, that could also be joined to your date table. Again, using the date as an int key. This would contain data that indicates which fiscal week, quarter, year, etc.

    Leonard

  • jrose-664261

    Grasshopper

    Points: 16

    I actually prefer using the numbers method, as in your util_nums article. it allows more flexibility and doesn't require storage of set values. The only disadvantage to the numbers method is that you have to store enough numbers for a maximum range.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Same problem with dates!!!

    You need to store all the data for the minimum safe range needed... which is often a buttload of data!

  • mike.byrnes

    Grasshopper

    Points: 19

    If my calculations are correct, then if each year took 5Mb then 200 years would be 1 Gb worth of data, not 100Mb.

  • Jeff Moden

    SSC Guru

    Points: 996847

    Brandon,

    Not sure what the text editor you used may have done to you, but there's a lot of missing spaces in your code... for example CREATE TABLE has become CREATETABLE and SET @variable has become SET@variable. There are many other accidental concatenations throughout the code. It might be why there's not much discussion or rating on this article, so far... people just don't wanna take the time to fix posted code.

    Also, you may want to add a link to your article on how to build your numbers table... it would help people that just don't know about such things, a lot. Either that, or it's short enough to just use in the code.

    Contact Steve Jones for how to edit your article if you're interested in doing such a thing. It's not difficult and Steve is very happy to help on these things.

    I've not played with the idea of an NVP (Name Value Pair) table like this for a calendar table. It's an interesting and different idea... it certainly makes indexing it a breeze and I'm gonna play with it and see how it does against more traditonal calendar tables. One of the things I'm going to play with, is the idea of using the clustered index to "partition" the dates in groups by the dimension column whilst including the date as the second column and, maybe, trying to do a partioned view on it. Like I said, it's an interesting and different idea.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • rkonopka-828284

    Grasshopper

    Points: 14

    Instead of double converting (first to varchar then back to datetime), wouldn't it be simpler and "cleaner" to do this:

    select dateadd(dd,datediff(dd,0,getdate()),0)

    Enjoyed the article

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Jeff, I am very interested in your findings! Perhaps this is one of those "step outside the box" situations where we all come out with something very useful and powerful.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Jeff Moden

    SSC Guru

    Points: 996847

    rkonopka (12/17/2008)


    Instead of double converting (first to varchar then back to datetime), wouldn't it be simpler and "cleaner" to do this:

    select dateadd(dd,datediff(dd,0,getdate()),0)

    Enjoyed the article

    Yes... and faster to boot.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996847

    TheSQLGuru (12/17/2008)


    Jeff, I am very interested in your findings! Perhaps this is one of those "step outside the box" situations where we all come out with something very useful and powerful.

    I was thinking the very same thing, Kevin... my first impression was "What, are you kidding me? How'z that gonna work?" Then I remembered the saying I have framed in my cube... "Before you can think outside the box, you must first realize... you're in a box!" So, I'm gonna find out, "How'z that gonna work" because like Sergiy says, "A Developer must not guess... a Developer must KNOW!" Because of the denormalized state of the table, it could be very fast...

    ... it'll take a bit to do the kind of testing I'm thinking of...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CheeseMan316

    SSC Veteran

    Points: 240

    Joe Celko (12/17/2008)


    The calendar date is the natural key, so the magically vague "id" is not needed. The table is not normalized to 1NF -- you have data and metadata in it. What do the NULLs mean?

    The reason that I added the date_id column is to allow the dimension to be referenced by a 4-byte value instead of an 8 byte value if the application calls for linking data to a coordinate.

  • CheeseMan316

    SSC Veteran

    Points: 240

    mike.byrnes (12/17/2008)


    If my calculations are correct, then if each year took 5Mb then 200 years would be 1 Gb worth of data, not 100Mb.

    I guess I was incorrect that it's 500KB per year then because here is the space used by my table.

    name rows reserved data index_size unused

    -------------------- ----------------- -------- ---------- ------------------

    util_time_coordinate 1829625 99792 KB 52400 KB 47296 KB 96 KB

Viewing 15 posts - 1 through 15 (of 23 total)

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