How to create dimension and fact tables usinmg SSIS pacakge?

Question

Hi,

I am new to create Dimension tables and fact tables.

Can any one help me in creating dimension table and fact tables?

My requirement is I have to create dimension table and then fact table from the dimension table.

I have to create dimension table with 3 columns and I want to know how we will be getting identity column ?(ex:salesTypeID Identity(1,1))

can any one help on this?

Thanks.....

---

sudha

Answer 1

Check this blog post: http://sqlblog.com/blogs/jorg_klein/archive/2010/01/04/ssis-package-design-pattern-for-loading-a-data-warehouse.aspx

To load the dimension you should add a Data Flow Task where you need at least one OLEDB source and a Slowly Changing Dimension. For identity, when you create your table you should add one surrogate key column:

SalesTypeID int identity (1,1) PrimaryKey 

This column won't show when you map the columns (source and destination) in the SCD.

The easiest (and fastest) way to create the Fact Table would be to add one DFT, here you add one OLEDB source and one OLEDB destination. But before adding the data from the source into the destination, you should truncate the table.

Answer 2

Hi,

Here we are creating new dimension table with existing table values .I have created dimension table by using source and in destination I have added identity column to destination manually.Now I am ok with dimension table.

What my problem was creating fact table;

Here I am getting 3 keys from 3 different tables;I am not knowing  how to do it in DFT.

For example:BLDGKEY---DimBuilding,TenantKey--DimTenant,SalesTypeID--DimSales

Other than these columns I also want some other columns from one table.

Could any one let me know how to this task??

---

sudha

Answer 3

Sudha,

                Can you please give some more details on the columns / Column values of the dimension table you have created?  Also please give some information on the fact you want to measure. This will help to answer your question better.

Tinto

Answer 4

Hi,

I want to create fact table with

FACTSALES (FactsalesID(identity(1,1), Tenankey(fk), BLDGkey(FK), PeriodDate ,(SlaesTypeID (FK), RPTDATE, AMOUNT)

Here   BLDGKEY---DimBuilding,TenantKey--DimTenant,SalesTypeID--DimSalesType and rest of coulmns are coming form one table.

I want all these into fact table.I have to load them using SSIS pcakge,can you explain the flow how to perform this?

Thanks

---

sudha

Answer 5

You need to do this:

1. Add OLEDB Source to get data for your fact table
2. Add Lookup for BLDGKEY in DimBuilding
3. Add Lookup for TenantKey in DimTenant 
4. Add Lokup for SalesTypeID in DimSalesType
5. Add OLEDB Destination to add your data from the pipeline into the destination table in your DW. Make sure to use the column from the Lookups in your fact table.

Answer 6

Sudha,

  As rusul suggested, please use SSIS look up transformations for loading fact table with dimension Keys. Please look at the below links for more details.

http://consultingblogs.emc.com/jamiethomson/archive/2005/10/21/2289.aspx

http://support.sas.com/documentation/cdl/en/etlug/62233/HTML/default/viewer.htm#p0pcm98e10xf2rn1ag4w0w0qwybp.htm

Tinto

Answer 7

Hi Tinto

I haveworked the rusul suggested way,it worked out well for me.I have gone through the links that you have provided and they also helped me.

Thanks for your response.

Sudha

---

sudha