locked
schema design suggestions for Hierarchy RRS feed

  • Question

  • We have a data warehouse designed for Program Management and below is the architecture -

    Source system -> Staging -> Warehouse. There are five tables that are loaded from flat files. Contract -> TaskOrder -> Project Portfolio -> Project -> Chargecodes.

    The relationship between them is One-Many ( One contract can have many task orders and one task order can have multiple project portfolio's). Everytime, any new record or update to existing records are made in the flat file and sent it to us from the Program managers. We would like to eliminate the manual process.

    For the sake of automation, we created some Sharepoint forms with 5 different tables so that users can use the sharepoint site to provide the required data instead of manually entering the information in an excel. I would like to take suggestion in designing the schema that holds the information that is populated from Sharepoint ( Any Inserts or updates). Should I need to use the same staging tables populated from the sharepoint or what is best approach in feeding the information from Sharepoint and performing the daily ETL load? Please provide your valuable suggestions.

     

     

    • Moved by Kent Waldrop _ Wednesday, June 15, 2011 5:55 PM Because it is a "Database Design" question (From:Transact-SQL)
    Wednesday, June 15, 2011 5:14 PM

Answers

  • For the sake of automation, we created some Sharepoint forms with 5 different tables so that users can use the sharepoint site to provide the required data instead of manually entering the information in an excel. I would like to take suggestion in designing the schema that holds the information that is populated from Sharepoint ( Any Inserts or updates). Should I need to use the same staging tables populated from the sharepoint or what is best approach in feeding the information from Sharepoint and performing the daily ETL load? Please provide your valuable suggestions.

     

     

    Hi Akash,

    I have some questions. For your 3-tier data warehouse architecture 'Source system -> Staging -> Warehouse', does it mean you have source database for source system in the first tier, staging database in the second tier and  Warehouse Database in the last tier? Is the initial data firstly stored by SharePoint in the first tier, and then filtered and transferred to second tier by ETL load, at last the staging data will be extracted into the third tier for business analysis use?

    If yes, for your above question, you could first post the five tables schema here .If all 5 entities have one to many relationship among themselves, using 5 tables are OK. And you could try SSIS as the daily ETL load tool for those 5 tables.

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Peja TaoEditor Thursday, June 23, 2011 5:36 AM
    • Marked as answer by Stephanie Lv Friday, June 24, 2011 1:16 AM
    Friday, June 17, 2011 4:07 AM
    Answerer

All replies

  • Moving post to "Database Design" forum.
    Wednesday, June 15, 2011 5:54 PM
  • Can some one suggest me with the question?
    Wednesday, June 15, 2011 7:42 PM
  • For the sake of automation, we created some Sharepoint forms with 5 different tables so that users can use the sharepoint site to provide the required data instead of manually entering the information in an excel. I would like to take suggestion in designing the schema that holds the information that is populated from Sharepoint ( Any Inserts or updates). Should I need to use the same staging tables populated from the sharepoint or what is best approach in feeding the information from Sharepoint and performing the daily ETL load? Please provide your valuable suggestions.

     

     

    Hi Akash,

    I have some questions. For your 3-tier data warehouse architecture 'Source system -> Staging -> Warehouse', does it mean you have source database for source system in the first tier, staging database in the second tier and  Warehouse Database in the last tier? Is the initial data firstly stored by SharePoint in the first tier, and then filtered and transferred to second tier by ETL load, at last the staging data will be extracted into the third tier for business analysis use?

    If yes, for your above question, you could first post the five tables schema here .If all 5 entities have one to many relationship among themselves, using 5 tables are OK. And you could try SSIS as the daily ETL load tool for those 5 tables.

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Peja TaoEditor Thursday, June 23, 2011 5:36 AM
    • Marked as answer by Stephanie Lv Friday, June 24, 2011 1:16 AM
    Friday, June 17, 2011 4:07 AM
    Answerer