Loading Dimensions and Facts


  • I am new to SSIS so therefore trying to improving my knowledge by performing some basic tasks like loading dimension and facts.  I have some question and tried googling to find answer.  Below are my questions and answers (my assumption). Please try to provide light on this matter.

    1. Does AdwentureWorksDW2012 data populated from AdventureWorks2012.

    Yes. . the above package explains populating data to adventureworksDW.  This package leads to my second question

    2  The above package populates the fact table (FactInternetSales) directly from adventureworks database rather than using lookup from the dimension data and keeping the surrogate keys. I’m confused now to understand the right way to loading fact table.

    I assume this is one of the right ways to load fact table

    Tuesday, March 05, 2013 1:01 PM

All replies

  • Hi.

    Once you have populated all your dimensions with their latest data sets.. you will run your Fact ETL.

    Remember your fact dataset is just the stored measures which you will be extracting from you source, and the business keys... So this is what you will use for your source query... then you need to lookup the surrogate keys from the dimensions by joining to them with your business keys associated to  the fact, in SSIS you will perhaps use the Lookup transform and grab the surrogate unique key for that row.

    If your dimensions are of SCD type 2 then ensure you will only receive a single key on lookup by qualifying the join using some factdate between  the SCD2 startdate | enddate or whatever metadata values you are using to support the SCD2 dimension.

    MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 SQL Training

    Tuesday, March 05, 2013 1:34 PM
  • Hej

    Thanks a lot for explaining about the dimension and fact loading. could you help by explaining how adventuresworksdw2012 is populated. I have truncated FactInternetSales from adventureworksDW2012 and tried to populate by the procedure u explained but i could not do so. the ETL process which i did is

    salesorderdetail (AW2012)-->  Merge--> DimProduct(AWDW2012)--> DimCustomer(AWDW2012)-->FactInternetSales(AWDW2012)


    Tuesday, March 05, 2013 2:34 PM
  • Hi - it seems a bit cheety to have the final solutions but I think just here on codeplex seems to be the SSIS packages for loading Adventureworks2008DW data... - It will obviuosy help with learning (not cheeting at all!)

    MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 SQL Training

    Tuesday, March 05, 2013 3:47 PM