An approach to Load fact and Dimension tables

Friends,

Today I got into mood of testing Jamie Thomson's way of loading a fact table using sort and merge join as we see tenfold improvement in performance as opposed to the use of both I/O & memory intensive Look up in SSIS 2005 [You will soon run out of memory or experience unexpected delays while using look ups on bulk dimension on a system with low configuration (2GB, Intel P-IV X86)] . You can see how monthly Ticket Transactions, the excel sheet archive is loaded into Ticket Fact table using sort and inner join on each of the Dimensions as shown below:

For Incremental load of Type 1 Dimensions from the same excel archive before loading fact tables, Andy Leonards method really helps. The same Ticket Transactions Worksheet contain dimensional data which might be updates or new records. shown below is a typical incremental load of Dimension (Here an alternative method I suggest is to multicast the same source to parallel load Dimension as well as Fact table from the same worksheet). what you see below is incremental loading of two dimensions using look ups on the target Dimension sourced from the same worksheet.

In my forthcoming posts, I'd like to cover in brief about the pratical approach taken to test all the new features availed at the end of Feb CTP- 6 SSIS 2008 . Another important post that I wish to include is the SSIS Future Version Wishlist.

Hope everyone is enjoying good friday.

Cheers, Subhash Subramanyam