0

I have a fact table with five dimension tables associated to it.Typically, the fact table contains the surrogate keys of each dimension and has no business/surrogate key. I am trying to load the fact table with data resulted of the staging fact table i.e.Insert new records. However, I notice the fact table can also handle other operations such as Update or Delete on data. A conditional split was used in the SSIS Package for this purpose to check if all surrogate keys are 0 then make the new insert. My question is, Can I use the surrogate keys in terms of Update or Delete?

I made an insert on the fact table just to give an idea of how the data will look like.

enter image description here

Dodi
  • 111
  • 4
  • 14
  • If your fact table does not have a primary key then identifying a record to update or delete starts to depend on luck. – Dan Bracuk Jan 20 '17 at 18:12
  • The primary key in the fact table is not necessary and may serve no useful purpose. – Dodi Jan 20 '17 at 19:42

1 Answers1

1

The answer is yes, you can. BUT, will there be a situation where one employee sold the same product, from the same supplier, to the same customer, on the same day? Perhaps a different order on the same day? (this is based on the data you present in the question)

If all the surrogate keys together can uniquely identify a record, update fact records to your hearts content. But, if that is not the case, you could end up updating records when you do not intend to update.

I tend to include an order number in the fact tables I design to help avoid that situation, but you may not have that in your actual fact tables. Including the order number is a pattern referred to a degenerate dimension in the fact table. I have found it to be pretty handy.

Anyway, the answer is the same. You can update fact records based on surrogate keys, as long as all of them together can uniquely identify the row(s) you want to update.

Don't throw caution to the wind, be sure your data warehouse is designed such that you can do this if you need to. Being able to do in place updates of facts can be nice, versus delete and replace, in that there could be fewer steps in the ETL process.

R. Richards
  • 24,603
  • 10
  • 64
  • 64
  • Thank you very much for your informative answer. Yes, I let the ETL process to handle such as update on each dimension. This has created on staging the dimension in case of new entry or updates through the OLDB Command. I then staged the fact table from each dimension then loaded each dimension as a slowly changing based which then can get updated once records are modified. – Dodi Jan 20 '17 at 23:59
  • Sounds like you have thought it through, nice. Good luck!! – R. Richards Jan 21 '17 at 00:05
  • I built the data warehouse but has a problem of deploying it! I tried to seek a help through this site by asking a question of the error type, but has no luck what so ever :_( – Dodi Jan 21 '17 at 00:07
  • I looked at that question when you posted it... the versions are too different, like someone said in the comments. Try updating you packages with a newer version of Visual Studio or BIDS. – R. Richards Jan 21 '17 at 00:11
  • Imagine I built the project again from scratch using the 2015 version. When I tried to deploy it, it throws the exact same error! – Dodi Jan 21 '17 at 00:14
  • Crazy. Wish I could help. I run all my packages from the file system to avoid such things. – R. Richards Jan 21 '17 at 00:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/133686/discussion-between-dodi82-and-r-richards). – Dodi Jan 21 '17 at 00:36