Data Modeling Information

Question

Data Modeling Information

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
April 19, 2023 at 12:00 am

#4171900

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

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

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)

David.Poole SSC Guru Points: 76000 More actions · Answer 2

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.

LinkedIn Profile

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

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)