July 23, 2010 at 9:49 am
Hi,
I have a task to construct a profit and loss datawarehouse. I need to collate information from databases and spreadsheets on Income and expenditure by customer and by vehicle.
I've read a few books on the subject(Kimball etc) and just need clarification on how to structure it. I've used SSIS for other minor projects.
Would you have Customer,vehicle and the Income/Expenditure type as the Dimensions and the actual value(measure) of the Income or expenditure as a Fact table?
Also, is it worth adding all details I have on the customer to the Customer dimension(i.e. customer address)? They haven't asked for it, but I'm betting they do, and completely denormalising the customer details into one table?
Thanks in advance,
Paul
July 23, 2010 at 1:04 pm
Denormalize, yes.
Add as many details as you think may be useful in the long run, or have been requested.
Quick rule of thumb:
If you want to add it, sum it, average it or manipulate it numerically in almost any way, put it in the fact table. Put everything else in dimensions.
July 27, 2010 at 12:23 am
Quick rule of thumb:
If you want to add it, sum it, average it or manipulate it numerically in almost any way, put it in the fact table. Put everything else in dimensions.
That's awesome...;-);-);-)
Raunak J
July 28, 2010 at 2:14 am
It might prove cost effective to talk to an experienced designer as you might find this saves you much effort in the long run.
At the least have a read of Kimball's datawarehouse toolkit as this will cover the info in the required detail.
August 5, 2010 at 5:45 am
Paul Monaghan (7/23/2010)
Also, is it worth adding all details I have on the customer to the Customer dimension(i.e. customer address)? They haven't asked for it, but I'm betting they do, and completely denormalising the customer details into one table?
Be careful with the customer dimension. If you have SCD type 2 on it, it can explode into a monster dimension (this sounds like a good movie :-)). Kimball's book explains how the handle this in great detail.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply