Friday, June 29, 2012 4:39 AM
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?
Friday, June 29, 2012 6:24 AM
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.
- Edited by irusulMicrosoft Community Contributor Friday, June 29, 2012 6:27 AM
Friday, June 29, 2012 7:25 AM
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.
Other than these columns I also want some other columns from one table.
Could any one let me know how to this task??
Friday, June 29, 2012 1:45 PM
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.
Monday, July 02, 2012 6:38 AM
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?
Monday, July 02, 2012 9:43 AM
You need to do this:
- Add OLEDB Source to get data for your fact table
- Add Lookup for BLDGKEY in DimBuilding
- Add Lookup for TenantKey in DimTenant
- Add Lokup for SalesTypeID in DimSalesType
- 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.
Monday, July 02, 2012 1:36 PM
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.
Wednesday, July 04, 2012 9:24 AM
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.