Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, December 18, 2013 10:53 PM
I have a fact table with a composite key of 5 columns. Two of the columns are FKs to the date dimension. I was setting the delete/update rules for the FK relationship in SSMS and it had a problem with me creating cascade action on the FKs that connected to the date dimension.
What is the proper way to set up FK relationships in fact tables with SSMS when you have composite keys as most fact tables do?
Wednesday, December 18, 2013 11:25 PM | 1 vote
Hi,
When you design FACT & Dimension, You have to keep in mind that All DIMENSIONS should be loaded first and then FACT tables. When coming to deleting data, If you have referential integrity between FACT & Dimension (Primary keys of Dimensions are refereed in FACT table and called as Foreign key) then first you need to delete data from FACT and then only you can delete data from Dimensions.
FACT=Father
Dimension=Child
Father first feed child and then eat:)
Father protects child so in war first someone can kill father then only can kill child:).
http://technet.microsoft.com/en-us/library/ms189049.aspx
http://stackoverflow.com/questions/4994587/sql-server-2005-cascading-delete
Thanks Shiven:) If Answer is Helpful, Please Vote
Thursday, December 19, 2013 4:07 PM
Yeah I understand all that. What I'm trying to do is to protect my database from RI violations that occur by production support people blowing away stuff in a dimension table but forgetting to blow away related records in the fact table. I want those fact records deleted automatically so we don't have orphan records which was a real issue at a previous engagement. Production support is usually just people that know SQL and some relational modeling. It's not too likely they will understand the details of dimensional modeling enough such that they would know that they had to blow away the fact record first.
My problem is I have a FK to a role playing dimension (the date dimension in this case). So basically I have to columns in the fact table that have a FK relationship to the PK of the date dimension. When I create both relationships SSMS and try to have both of them cascade delete SSMS has an issue with it.
The error I get is:
Unable to create relationship '[relationship name]'
Introducing Foreign Key constraint '[constraint name]' on table '[table name]' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other foreign key constraints.
I can go ahead and put no action and the table will save fine. The question now becomes how does the cascade delete actually work. Can I just set one part of the key to cascade delete?
Actually I just realized that this is an even bigger design issue. What DOES happen to a fact record when one of it's dimensions gets deleted and I've got full RI set up on the table?
Or am I totally thinking about this wrong. Do you set up cascade deletes in a dimensional model? Is there a way to prevent deletes from the dimension table if there are related fact records?
Thursday, December 19, 2013 4:26 PM
Let me explain what I am doing in my star schema:
I have scd type 1 dims. The fact tables has the FK from dim and one pk of its own which is auto incrementing. My fact tables granularity is day level. If I have associate data, I have one associate record per day in fact table. Now when my source data is chaging for old dates, I need to reload the fact table for that dates so that souce data and fact data is in sync. I just use DELETE on fact for that speicifc date and then reload the data from source. We dont need to delete any data from dims. Rather update/insert them. What ever dim SIDs are referred in fact they are deleted and fact is reloaded with correct dim reffered SIDs.
Thanks, hsbal
Thursday, December 19, 2013 5:14 PM
Yeah I really don't know what scenario would happen where dimension records would have to be deleted. It's really the scenarios that I CAN'T think of that I'm trying to plan for. This would be easy if I was going to be the only one ever doing maintenance but that's clearly not going to be the case.
Thursday, December 19, 2013 9:04 PM
...
Unable to create relationship '[relationship name]'
Introducing Foreign Key constraint '[constraint name]' on table '[table name]' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other foreign key constraints.
I can go ahead and put no action and the table will save fine. The question now becomes how does the cascade delete actually work. Can I just set one part of the key to cascade delete?
Actually I just realized that this is an even bigger design issue. What DOES happen to a fact record when one of it's dimensions gets deleted and I've got full RI set up on the table?
Or am I totally thinking about this wrong. Do you set up cascade deletes in a dimensional model? Is there a way to prevent deletes from the dimension table if there are related fact records?
You are not thinking wrong.
It just so happens to be the case that you can only have a "cascading" foreign key action one time between two tables. You will have to handle this programatically and work by the usual practice of "first delete facts, then dimensions".
And you will certainly not use SSMS for deletes but rather some store procedure. Therefore it's just a little bit more work.
Andreas Wolter | Microsoft Certified Master SQL Server
Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com | www.SarpedonQualityLab.com
Thursday, December 19, 2013 10:00 PM
"And you will certainly not use SSMS for deletes but rather some store procedure. Therefore it's just a little bit more work."
No that's a false assumption. At my last job we had people blowing away records from SSMS all the time. That's what I'm trying to avoid here. Bullet proofing. A trained monkey shouldn't be able to blow stuff away by accident.
Saturday, December 21, 2013 2:24 PM
"And you will certainly not use SSMS for deletes but rather some store procedure. Therefore it's just a little bit more work."
No that's a false assumption. At my last job we had people blowing away records from SSMS all the time. That's what I'm trying to avoid here. Bullet proofing. A trained monkey shouldn't be able to blow stuff away by accident.
In see.
Well, I do not know exactly at which level of agreement and responsibility you are.
So let me just say, that in general this is a matter of both permissions and clearly defined processes.
For example in Datawarehouses that I have designed, it is clearly prohibited to do any manual ad-hoc changes to the data. The least that I ever would “support” is a saved script that is run at a defined time and therefore is documented. But that’s already an exception.
So in general, putting those data updates/deletes into their own processes and tools (can be as simple as a job running a stored proc) is indeed a good idea.
Andreas Wolter | Microsoft Certified Master SQL Server
Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com | www.SarpedonQualityLab.com