Building a Data Warehouse using SSIS

Following are the normal steps to build a Data Warehouse.

1st Step: Add databases for raw tables, staging tables, and dimensional tables. 

The raw tables are usually the exact copy of the original table. The raw tables can be totally different than the raw tables. You apply business rules, flatten two or more tables into one, mark records for filtering, for raw tables, so that your data are ready for the data warehouse. Dimensional tables can be fact tables or dimension tables that come together in star, snowflake, and constellation schemas. You can place the Raw and Stage tables in the same staging database. And place dimensional tables into Data Warehouse database, which will be used by SSAS cubes directly.

2nd Step: Install BIDS for Visual Studio.

You need to build BI development environment under Visual Studio. If you worked with SQL Server 2012, it's better to install BIDS 2012 for Visual Studio 2012, which is by far the most stable and popular BI development environment. BIDS will install SSIS, SSAS, SSRS packages for all of your BI developing requirement.

3rd Step: Move source data into Staging DB raw tables using SSIS.

You need to create a new SSIS project at first. Then you need to add new connections for DestinationConnectionOLEDB and SourceConnectionOLEDB within the SSIS project for the Staging DB and Source DB. 

4th Step: Design Staging tables using demoralization on raw tables.

Transactional database always do normalization into 3NF (The Third Normal Form) because loading data should be very quick. Unlike transactional database, data warehouse don't need quick data load, the primary usage is reporting and ad-hoc query, thus denormalization or flatting would be required. 

For designing data  warehouse, how to identity fact table is essential. As a general rule, transactions and events from the transactional database become fact tables,whereas lookup tables and profile data become dimension tables. The fact tables becomes the center of the data warehouse model.

Following figure showed a transactional database model.





















Following figure showed a data warehouse database model.

During the denormalization process, Color and Color Group have been combined into one Color table. Product, Line, Brand, and Manufacturer have been combined into one Brand table. Order and OrderDetail has been combined into FactOrders table. In addition, because report staff needs orders by zip codes rather than by individual customers, thus a new Geography table is created based on Customers and CustomerAddress table, and the left data of the last two tables has been combined into FactOrders table. Finally, Pattern and Style have very little change during the demoralization process. 

Note denormalization can improve query performance. This link can show you more detailed information. 

5th Step: Move raw tables into staging tables using SSIS.

Source:

http://sqlmag.com/sql-server-2008/ssis-novices-guide-data-warehouses-moving-data-data-warehouse
http://sqlmag.com/sql-server-integration-services/ssis-novices-guide-data-warehouses-flattening-while-staging-data