Solved

Load fact table SSIS

Posted on 2015-02-09
4
114 Views
Last Modified: 2016-02-15
Im trying to load a Fact table from a Stage table in SQL Server. This is an existing package that is using the Slowly Changing Dimension which is incredibly slow. The process starts off with a Query to an ODBC table and then does many Lookups and then finally a SCD to Insert or Update the fact table. Now, I dont see how any of these records will be updated but just in case I want to check. I am not going to use the SCD to check though, it takes hours to complete. I was thinking about using a Conditional Split? My only problem is that there are no Primary keys in the source table so I have to use many fields to see if its an actual duplicate? I do see there is Line numbers that seem to be unique to the document number so maybe I can go off of that. Can someone help me out with the Conditional Split to check for a duplicate and to go to one OleDB destination for Insert and another for Update. This way I can evaluate if I need this. FYI, the Fact table has millions of rows so I dont think I should be doing any kind of Truncate and then reload the table. I just need a way to check the Destination before I do anything. Can anyone help me with this real quick???   Thank you!!!
0
Comment
Question by:jknj72
[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
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 40598987
Slowly Changing Dimension transformation should only be used for small dimensions (less than 10,000 rows). comparing all the columns is not optimal. a hash function is usually used to store information about all the needed attributes, so that the process can quickly check whether there has been a change.

To implement a hash function Write a SELECT statement and add a calculated column using the T-SQL HASHBYTES
function when reading the data.

Faster Slowly Changing Dimension with Hash Function

and consider using T-SQL MERGE statement.
0
 

Author Comment

by:jknj72
ID: 40599026
So are you saying I should be using the SCD with a hash function to do what I need to do or should I be using a T-SQL Merge statement for what I need to do? I need help either way ;)

Thanks
JK
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 500 total points
ID: 40599334
Using Hash function is what will improve the performance. Use hash function to find out which records should be updated and which records need to be inserted.

then you can use MERGE statement if you like to insert and update in one statement.
0
 

Author Closing Comment

by:jknj72
ID: 40757388
Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Moving away from Access 2003 adp files 4 48
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
SQL Job Failed 6 31
sql server query 9 27
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

751 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