CODEWITHSUNDEEP
sqlsql-serverssisetl
Vaslo
Vaslo

Reputation: 511

SSIS Error on insertion - Error when new primary keys in Dimension table

I am using SSIS to bring in a bunch of large files each month into one single table, which I insert records to SQL Server tables. My fact table is actual financial transactions that occur during the month. It looks something like:

FactTransactions

'Acct Number'    'Product Number'  'Total Value'
000001             1A                 1000            
000002             1A                 2000            
000001             3B                 3000

I'd like to track this information against some manually generated information in a table about accounts where 'Acct Number' is the primary key in the Dim Table

DimAcct

 'Acct Number'    'Acct Name'  'Acct Type'
   000001             Sales         Revenue               
   000002             Returns       Revenue (Contra)

My process is: 1) Clear the transaction table 2) Reload all the transactions included anything new or corrected 3) Do Analysis through Joins, etc

When I went to run the tables in a new month, I received the following error in SSIS:

"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_GLTransaction_List_Master_DimAccount". The conflict occurred in 
database "GLTransactions", table "dbo.DimAccount", column 'Acct_Number'.".

I am guessing this is because new accts have been made and used in Transactions, but I haven't manually updated my Dim file and had no warning about them. This is going to happen every month, because new Transactions accounts get added when they find new Accounting items to track separately in their own accounts. I also manually update the table to add the few accounts. Is there a way to avoid this, or better, what should I do before/during the SSIS run to handle these new accounts and avoid the error?

Upvotes: 1

Views: 364

Answers (2)

s_allegrini
s_allegrini

Reputation: 94

The message you receive tells you that in the data you are trying to load in the fact table, there is at least one record referencing a record that doesn't exists in the dimension table. How do you load the dimension table? Do you load it only before running the fact load ? If this is the case you should consider to manage the so called "inferred dimension", that is dimension that you do not know before loading the fact table. This situation is also referred to as "early arriving facts".

Therefore, you should scan the facts you are trying to load, looking for dimension records that are not in your dimension table. Then you will insert this records in the dimension table and flag it as inferred. At this stage you will load the fact.

Note that flagging these record as "Inferred" will enable you to refine the dimension record at a later time. Let's say you will insert an inferred account for which you know only the account number (the business key) but not the others information such as account description etc ... You can update these information at a later time.

Notice that in SSIS SCD component you can define a proper Inferred Dimension management.

Hope this helps.

Upvotes: 2

Piotr Palka
Piotr Palka

Reputation: 3159

You mention that you are only manually inserting dimension tables, that needs to be changed.
You should insert all new accounts as "New/Unknown" in the account dimension as a 1st step of the SSIS process.
Then you will have a report that prints these new accounts and you will manually or in some other way update these accounts to contain correct data.
You can read more about possible solutions at: https://www.matillion.com/blog/late-arriving-dimension/

Upvotes: 1

Related Questions