October 2, 2007 at 8:23 am
Hello Folks,
Im currently starting up a data warehouse project. This DW will placed at customer whos in the retail business, which includes a lot of transactions generated by a ERP system.
I would like to hear you opinion and have some good advices on how to design such a DW, there will be number of tables included with some coredate and such things. BUT the table that ofcourse need some extra consedration is the "SalesLines" table, this one is expected to to grove with up to 5GB per month and within next year reach a TB size.
How should one design such a table in order to maintain preformance.
This table will be play a importaint role when it comes to the reports that the user will be using, there will be querries that includes this salesline talbe along with other tables.
I would be very thankful for any ideas.
Thank you.
October 2, 2007 at 12:46 pm
Most of the DW huge fact tables use partitioning, and then create a view for the users to use it.
my 2 cents.
October 9, 2007 at 2:59 pm
Thanks for your advice...
Well, I have been playing around a bit with partitions, functions and schemes.
I collect data from the past two years, now I would like to add a new month to my partiotion at the end of every calender month at the same time I would like to retire/delte move the oldest file group and its data.
Ive been reading about sliding windows but I cant just get the hang of it.
I have managed to move the oldest data to a new table, but how the heck can I delete the file group and the file where this data was stored??
Could this even done automatically?
Best regards
Magnus
December 4, 2008 at 10:08 pm
Probably too late but:
You want to compress as much information on a page as possible.
Things to consider for your fact table:
1) Use small datetime if possible
2) Avoid varchar fields in a fact table - comments go in a dimension table
3) Don't have a wide clustered index - ETL times will be affected with much gain in performance
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply