Solved

What would be the best way to load a data warehouse with star schema from a staging database that has been loaded from a  flat file(delimited text file) IN SSIS 2012

Posted on 2014-03-18
4
191 Views
Last Modified: 2016-02-10
Hi!

My production datawarehouse on one server has a star schema structure, where all the dimension and fact tables get loaded on a monthly basis, from the staging tables on a different server.

The data gets dumped directly into the staging tables, as is, from a file on a shared drive, without any transformations.

The staging tables get truncated and loaded every month. But we cannot do the same with the tables on our data warehouse as the key values get changed in the fact table, if the fact table gets truncated every month as the foreign keys of the fact table are mapped to the primary keys in the dimension tables.

We need to either insert new records or do an upsert or the like, which would make the fact table eventually grow in size. Could you please suggest the best approach to follow, in order to load the above? what would be the best approach to truncate and reload the full table or insert or upsert or the like?

Thanks a million in advance! I greatly appreciate it! Any code/logic is greatly appreciated.

I need a quick help! It's really, really urgent!
0
Comment
Question by:amukta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 39938725
Is your monthly loads or 'Delta' (incremental) loads or eveytime are you getting whole data and deleting and reloading the DWH?

If it is incremental load, once the Staging Area is loaded you can use T-SQL Merge with your destination DWH Table.  And it depends on the type of Dimension table (SCD1, SCD2).

And coming to Fact Table there shouldnt be any deletes ( only in execptional business cases you may require update / delete) as this is the source of historical truth.

For further details on using MERGE look at this http://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/
0
 

Author Comment

by:amukta
ID: 39940083
Currently, all the data from staging is being loaded into the DWH without deleting anything, with a date field  showing the date the data was loaded.

My question is, would it be a good idea to truncate the fact table and load it every month, by using the SCD concept for dimensions or would it be a good idea to add the records to the fact table every month as it's being currently?

Appreciate your help! Thanks!
0
 
LVL 17

Accepted Solution

by:
MIKE earned 500 total points
ID: 40387347
Use an Incremental SNAPSHOT type of process, meaning, update the FACT and DIM tables EACH DAY and on the LAST DAY of the Month, the data will be a MONTH END SNAPSHOT for the month.
0
 

Author Closing Comment

by:amukta
ID: 40556979
Thank you !
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 to SQL Server 2016 24 57
SQL Lag Function DateDiff 2 26
Question about Common Table Expressions 3 45
Query Task 8 23
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question