I’ve made mention before of a large data warehousing project I’ve been involved with, using the SQL Server 2005 tools. Like a lot of developers on a lot of projects, I was thrown in the deep end and had to learn a lot of new technologies in a short order of time. Fortunately I’m a glutton for punishment.
I found a lot of material on the various tools, and quickly became competent in their use. I also quickly got up to speed on star schema versus snow flake schema, and the divvying up of data into facts and dimensions.
The thing that always puzzled me was, how do you decide what goes into facts versus what goes into your dimensions. Having a short breather I decided to do some reading up to make sure my understanding of theory was up to my understanding of the tools.
The best explanation I’ve found is in “The Microsoft Data Warehouse Toolkit” by Joy Mundy and Warren Thornthwaite. http://shrinkster.com/r4n (Good book, highly recommend it, and standard disclaimer I don’t get any kickbacks so buy it where ever you want.) It covers the Kimball method for warehousing. To quote…
“It may help to think of dimensions as things or objects. A thing such as a product can exist without ever being involved in a business event.”
Ah, grasshopper, enlightenment begins.
As the book describes, a dimension is your noun. It is something than can exist independent of a business event, such as a sale. Products, employees, equipment, are all things that exist. A dimension either does something, or has something done to it.
Employees sell, customers buy. Employees and customers are examples of dimensions, they do.
Products are sold, they are also dimensions as they have something done to them.
Facts, to carry on another concept from the book are the verb. An entry in a fact table marks a discrete event that happens to something from the dimension table. A product sale would be recorded in a fact table. The event of the sale would be noted by what product was sold, which employee sold it, and which customer bought it. Product, Employee, and Customer are all dimensions that describe the event, the sale.
In addition fact tables also typically have some kind of quantitative data. The quantity sold, the price per item, total price, and so on.
Knowing this makes it much clearer in my mind how to start designing my own warehouses. Or at least ready to take the first step.
March 26, 2008 at 7:37 am
Great. Thanks a lot.
May 25, 2008 at 6:29 pm
Interesting views on data warehousing. You might find http://www.infonitive.com an interesting forum.
September 2, 2008 at 12:46 am
Thanks, this is very precise info.
September 2, 2009 at 3:33 pm
good stuff.. easy to understand!
December 9, 2009 at 5:38 am
Excllent info
February 13, 2010 at 6:21 am
is gud for me as a begginer…can u please suggest me some further steps on this way
February 23, 2010 at 3:49 am
Man tat was some gud explaination…Thx bro !!!
February 26, 2010 at 8:00 am
[...] Dimensions [...]
August 19, 2010 at 1:54 am
thanks a lot, that hepls..
August 23, 2010 at 2:41 am
brilant … i mean now i have an idea about this new aproch
June 2, 2011 at 12:30 pm
Thank you for more explanations.
November 29, 2010 at 10:04 pm
The “Quantative Data” you refer to that is mentioned in Facts which are not listed as a Dimension are normally called “Measures” (you describe them above as quantity sold, the price per item, total price, etc).
February 27, 2011 at 11:21 pm
Very good explanation for Non techie ppl .
June 2, 2011 at 12:19 pm
It is perfect.Thank you so much.
Cannot be better than this.
June 30, 2011 at 4:25 pm
Nice explanation. I like your examples, helps me understand quickly what’s a dimension and what’s a fact.
July 2, 2011 at 2:04 am
thanks. very enlightening. and thank u to Brad too!
July 6, 2011 at 8:40 am
Awesome springboard of an explanation!
September 17, 2011 at 11:59 am
hello thanks its really nice but can you explain more as giving different examples so that i can get clear idea about it.
September 20, 2011 at 1:57 am
Sanoka
Thanks
November 16, 2011 at 11:38 am
Thanks!
November 21, 2011 at 1:27 pm
I must say that tabular comparison could have helped me alot
July 19, 2012 at 10:02 pm
Great explanation, thank you
August 10, 2012 at 3:41 am
Refer http://dwhlaureate.blogspot.in/
August 19, 2012 at 6:58 pm
Can dimension rows (nouns) exist without a FK reference in the fact table? As an example a customer may be a dimension but may not have purchased yet. Would/should the customer be in the dimension for customer only and not on the fact table?
August 23, 2012 at 6:45 pm
Lisa, yes it’s entirely possible to have a row in the Dimension table that is not used in the Fact table. If you were to use the data to build a cube, the orphaned dimension wouldn’t appear as the core of a cube is the fact table. However you could still reference it from more traditional reporting that connects directly to the database as opposed to a cube.
September 25, 2012 at 12:28 pm
Thanks for the information and also data warehousing site, http://dwhlaureate.blogspot.in/ , really helpful
November 17, 2012 at 1:25 pm
The explanation is good!!
I have a doubt
We have a staging layer which extracts tables from different source.It is just a copy of other sources.
In the workflows , facts are being run before dimensions.
This is against the normal view that dimensions should run before facts.
Please suggest what could be the reason?
Thanks in advance
January 8, 2013 at 10:06 pm
gr8 help….thanks a lot
March 4, 2013 at 5:02 am
thanks. short and sweet.