Chris writes "Hello, In SQL Server 2000 I'm developing a DataWarehouse. I have a OrderFact table and a OrderDetailsFact table. Would you recommend duplicating a lot of the data in the OrderDetails table that is in the Order table or is it better to just import the extra data for the details and link the OrderNumber from the OrderDetails to the Order table to get the various header type info. Pardon me if I've already asked you this question, for some reason I'm getting a sense of deja vu. Thanks"
I would NOT recommend duplicating a lot of data from a parent (Order) table to the child (OrderDetails). That would be side-stepping the main concept of a relational database. Join the tables to perform your reporting.
------------------------------------------------------------------- Make mine a Vienti quad-shot half-skinny no foam with vanilla...
quote: I would NOT recommend duplicating a lot of data from a parent (Order) table to the child (OrderDetails). That would be side-stepping the main concept of a relational database. Join the tables to perform your reporting.
True, he would be going against relational database design but if he is creating a fact table for a datawarehouse then he should be optimising for a dimensional db model rather than a relational db model. However my response is based on my very limited knowledge of datawarehouse design so I could be totally off base.
Are you really building a datawharehouse or just trying to make reporting faster.
First up a datawarehouse is just a buzz word to hide the fact that you have denormalised the crap out of your database.
Everything a warehouse can do can be replcated in standard SQL. Of course it is not as easy to write anaylsis type queries in SQL as MDX but it is still possible. (Have you noticed how many posts there are for "cross tab" reports?)
So lets assume that you are building a wharehouse based on your relational data store: With that in mind, the first thing to think of is Dimensions. After you have determined these, create your fact table around them.
Don't worry about relational theory because we have already abandoned it by choosing the datawarehouse path. Denormalise to your hearts content.
Disclaimer: The above comments assume that you will leave your existing data in its relational form and just create a new database for your wharehouse.
DavidM It gets windy at a thousand feet...."Tutorial D"
Fact Detail Table
Forum Locked
Posted - 11/05/2001 : 00:11:14
