Data Modeling Information

  • Comments posted to this topic are about the item Data Modeling Information

  • As a bit of a sidebar...

    Even when GETDATE() was really the only show in town, I've never understood the propensity of people to make the terrible mistake of using DATE_IDs or dates in the form of integers.  Except for the case when you need to know what's a workday or not, I've never understood the reasoning behind the creation and use of Calendar tables to get date parts, etc.  It just doesn't seem necessary to me and it's another join with another set of reads, etc, etc.

    --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.


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

  • Calendar and Clock tables make life a lot easier when used as part of a Dimensional model.  Especially if it is implemented in a column store.

    If you've seen the queries people run against a data warehouse those date dimensions start to make a whole lot more sense. That is before you start to consider all the different organisation specific dates.

    Now that column stores are prevalent, for analytics purposes, we denormalise quite heavily so tables are very wide.   Performance is astonishingly quick while the compression and encoding available from a column store prevents denormalisation exploding your storage consumption.

    It feels wrong but it works.

  • Sometimes having dates, or parts of dates, as integers work well. But not often. A dimension can make sense, but in most transactional tables, I think dates ought to be dates.

  • David.Poole wrote:

    Calendar and Clock tables make life a lot easier when used as part of a Dimensional model.  Especially if it is implemented in a column store.

    If you've seen the queries people run against a data warehouse those date dimensions start to make a whole lot more sense. That is before you start to consider all the different organisation specific dates.

    Now that column stores are prevalent, for analytics purposes, we denormalise quite heavily so tables are very wide.   Performance is astonishingly quick while the compression and encoding available from a column store prevents denormalisation exploding your storage consumption.

    It feels wrong but it works.

    Thanks for your points, David.

    --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.


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

  • Steve Jones - SSC Editor wrote:

    Sometimes having dates, or parts of dates, as integers work well. But not often. A dimension can make sense, but in most transactional tables, I think dates ought to be dates.

    Thanks for the feedback, Steve.

    --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.


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

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

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