THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

What’s driving your data model?

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Tuesday, November 10, 2015 6:57 PM by Rob Farley



Greg B said:

I agree with the thrust of your post entirely, and recently put together a "45 minute lightning talk" on this sort of topic - how you look at your business drives your model design.

I would like to highlight this point you made:

> The core of the warehouse is not necessarily the main fact table, but could be one of the main dimensions. If you’re a store, do you care about sales, or do you care about customers?

I think the primary fact table still is the core of the data warehouse. If the customer relation is the primary concern, then there should be multiple fact tables around customer engagement and relationship. The measures and KPIs from these fact tables should be presented more prominently than those from the sales fact table.

Or, for another example. Say you have a slowly changing sales hierarchy (Sales Manager > Sales Team > Sales Rep). If all you do is report point-in-time sales according to that hierarchy, then it is a slowly changing dimension (and there's likely a better model than including all those attributes in a single sales rep dimension).

If you report on headcount frequently and length of time that a sales rep exists on a specific team, and the number of team transitions a rep makes, you can do this from the same SCD table (but again there's a better model for this specific question), but at that point it becomes not a dimension, but a fact table.

I think, ultimately, we probably agree pretty much on this point, but I'm just making the point that if you care about something, you should measure it, and if you're measuring it, it's now one of your fact tables. Even if you have the same physical table acting as a dimension to the sales fact and its own headcount fact, the logical separation is there.

November 10, 2015 3:56 PM

Rob Farley said:

I think we do agree.

I'm not suggesting that facts not be measured. I'm suggesting that the most important part of your model might not be the obvious fact table. Additional fact tables would be introduced to give a better picture of the information about the core concept - even to the point of turning a traditional dimension table into a 'fact dimension' table.

The gist is around where your design starts and where the focus should be. I see too many organisations go through the motions because of the standard for their industry or because of the software they use. And I just wish they would design their models based on their individual core values.

November 10, 2015 4:10 PM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement