none
Load Data from excel sheets to database table and then fact and dimensions table. RRS feed

  • Question

  • Hi,

    I've multiple excel sheets and one database table with the same columns name. So I want to load data from excel sheets to that table and then from that staging table, I want to load data into fact and dimension tables. 

    I've loaded data from multiple excel sheets to one table but I am not getting how can I continue to this package for fact and dimension tables.

    Please help me for this.



    Wednesday, October 31, 2018 5:53 AM

All replies

  • Hi Rasik Chauhan, 

    Welcome to SSIS forum. 

    From your description, you are trying to load data from Staging table to Fact table and Dimension table.

    So what problem you have come across? Any error? 

    The design of a ETL package is depend on your data, source/destination table structure and business logic. Without these information, we can't provide very useful suggestions...

    Hope these links helpful:

    Fact Table Loading

    Dimension Table Loading In SSIS


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Yang.Z Wednesday, October 31, 2018 6:55 AM
    Wednesday, October 31, 2018 6:54 AM
  • Hi Rasik,

    If you have only one stage table and from it you need to load dimension and facts then you have to do the exercise of identification of dimension and facts from this stage table, creation of your target schema (dimension & facts) and then only you will be able to design your ETL architecture.

    Moreover designing ETL & data model is a time consuming & iterative activity involving business users, technical team and other stakeholders thus can not be completed within few line of text.


    Cheers,

    Wednesday, October 31, 2018 7:15 AM
  • Your staging table may have a column name named sheet so you will be able  to differentiate your data 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 31, 2018 7:25 AM

  • To create two copies of the input data from staging you can use multicast transform. Then link them to two lookup tasks separately based on the business key defined for your fact and dimension tables you need to have the lookup logic from your staging to dimension and fact tables.

    Then using the match and non match outputs you can perform required actions to the tables like say for match you need update using OLEDB command, for non-match insert using OLEDB destination etc



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, October 31, 2018 7:30 AM
  • Hi,

    Thanks for reply!
    Actually I have loaded data into one table from 10 excel sheets. Now I have one table(xyz) in my database with the data. Now I've breaked that table into fact and dimensions. I've created fact and dimension table in my database. Now I want to load these fact and dimension tables. So, my question is How can I write the SSIS Package for this. 

    Wednesday, October 31, 2018 9:45 AM
  • Hi,

    Thanks for reply!
    Actually I have loaded data into one table from 10 excel sheets. Now I have one table(xyz) in my database with the data. Now I've breaked that table into fact and dimensions. I've created fact and dimension table in my database. Now I want to load these fact and dimension tables. So, my question is How can I write the SSIS Package for this. 

    Wednesday, October 31, 2018 9:46 AM
  • Hi,

    1. Identify the source key/business key which will be used for Upsert (insert/update) in the target tables

    2. Identify the delta columns (to identify the delta data) for incremental loading, most of the times these are the timestamp columns i.e. Last_update_date 

    3. Now you have business keys & delta columns. Design SSIS as below

    • In source transformation write query like select * from [source table] where Last_update_date > (select max(Last_update_date ) from target table), it will give you all the newly created & updated records (delta data)
    • Do the data transformations (if any) by using SSIS tasks.
    • Use Lookup transformation and create lookup logic from source to target table using business keys, it will create two flows match & non match
    • For flow "match" do data update using oledb command and for "non match" do data insert using oledb destination in the target table 

    Cheers,

    • Proposed as answer by Yang.Z Thursday, November 1, 2018 6:28 AM
    Wednesday, October 31, 2018 10:01 AM
  • Hi Rasik Chauhan, 

    Please have a look at the links I post in my last reply. 

    Fact table loading:

    Dimension table loading: 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 1, 2018 6:33 AM