THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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: http://blogs.lobsterpot.com.au/2015/11/10/whats-driving-your-data-model/

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement