I have a field which is the primary key in table A, and in table B i have th
e
same field which is the foreign key.When it comes to deleting a record I
should kill the child first.If i try to delete the parent then i get an
error, which is expected. But if i turn the delete cascade option on, it let
s
me delete the parent first without giving an error.Why?
Thanks in advance.On Tue, 15 Feb 2005 08:21:11 -0800, PH wrote:
>I have a field which is the primary key in table A, and in table B i have t
he
>same field which is the foreign key.When it comes to deleting a record I
>should kill the child first.If i try to delete the parent then i get an
>error, which is expected. But if i turn the delete cascade option on, it le
ts
>me delete the parent first without giving an error.Why?
>Thanks in advance.
Hi PH,
Because the on delete cascade options tells the SQL Server engine that it
should automatically delete all "orphaned" childs when you delete a
parent. After SQL Server has done that, there are no more rows violating
the foreign key constraint, so there's no reason to give you an error.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What did you think that cascade would do? The error is not there because
it deleted the child first, then deleted the parent.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"PH" <PH@.discussions.microsoft.com> wrote in message
news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>I have a field which is the primary key in table A, and in table B i have
>the
> same field which is the foreign key.When it comes to deleting a record I
> should kill the child first.If i try to delete the parent then i get an
> error, which is expected. But if i turn the delete cascade option on, it
> lets
> me delete the parent first without giving an error.Why?
> Thanks in advance.|||Only use cascade delete when you are always sure that it never makes sense
to have a child when the parent is deleted. There are cases where it makes
sense for a child to have a null in the FK column, usually this is when
there are multiple Fk's in the child table, BUT, the is usually always ONE
Master table, as in Invoice and InvoiceItem when you must decide to either
cascade delete the InvoiceItem when and Invoice is deleted or to DENY
deleting and Invoice if it has InvoiceItems. This is a business rule that
you must decide on.
JIM
"PH" <PH@.discussions.microsoft.com> wrote in message
news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>I have a field which is the primary key in table A, and in table B i have
>the
> same field which is the foreign key.When it comes to deleting a record I
> should kill the child first.If i try to delete the parent then i get an
> error, which is expected. But if i turn the delete cascade option on, it
> lets
> me delete the parent first without giving an error.Why?
> Thanks in advance.|||Thanks for your answers
"james" wrote:
> Only use cascade delete when you are always sure that it never makes sense
> to have a child when the parent is deleted. There are cases where it make
s
> sense for a child to have a null in the FK column, usually this is when
> there are multiple Fk's in the child table, BUT, the is usually always ONE
> Master table, as in Invoice and InvoiceItem when you must decide to either
> cascade delete the InvoiceItem when and Invoice is deleted or to DENY
> deleting and Invoice if it has InvoiceItems. This is a business rule that
> you must decide on.
> JIM
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>
>
Showing posts with label record. Show all posts
Showing posts with label record. Show all posts
Monday, March 26, 2012
Parent ID in a Slowly Changing Dimension
Hi There,
Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".
The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.
Thnx, Jeroen.
Jeroen Alblas wrote:
Hi There,
Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".
The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.
Thnx, Jeroen.
I don't understand the problem or question. I think if you define ParentID as 'historical' the SCD component will issue a new 'version' of the rows every time it detects a change in parentID value and it will 'expire' the previous 'version'. It’s just simple like that. It may be helpful is you provide an example of what you are trying to accomplish
|||Why would your Id column change if you have a type2 change?
Are you using a typ2 column to lookup your parentID?
|||Okay, let me try to give an example:
Lets say we have an employee named Doopie and his boss named Barkie, both in the dim_Employee table. In our case we work with start- and enddates.
ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 12/31/9999
20 Barkie NULL 4000 1/1/1900 12/31/9999
Okay, now, in the source system, Barkie gives himself a raise of 500. We want to track history of Salary. Now say we start the Data flow task with the Slowly changing dimension object. It could very well be, that Doopie's record is processed first. No changes there so Doopie's record remains unchanged. Now Barkie record which introduces a new row:
ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 31/12/9999
20 Barkie NULL 4000 1/1/1900 12/7/2006
21 Barkie NULL 4500 12/8/2006 31/12/9999
If Doopie's record was processed after the insert of Barkie's record, the lookup would return record 21 as the active Boss record, and introduce a new record for Doopie as well...
Thnx again!
Jeroen
|||
I see the problem now. Basically you need to make sure every child record point to the latest version of its parent.
Well, I don't see an easy way to do this at one pass. You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent. That could be safer in the case that an unchanged child row is not in your set of rows to be processed (assuming you are pulling only changed/new rows from the source). That is my best shot considering that I have not drunk my first coffee this morning
Why don't you normalize that table?
Hi Phil, I normalized some columns of it (name of parent), but the parent / child hierarchy goes about six or seven levels. So then I would have to add quite a few columns...
You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent.
I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...
Jeroen
|||I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...
So let's define 'historical'. Defining a column as 'historical' to me is intended to track changes on a particular attribute. In the example you gave at the beginning you said that no changes were made to the child row:
“No changes there so Doopie's record remains unchanged.”
So, why do you want to issue a new version of the child row? The only change that occurred was to the parent; hence you should generate a new row to the parent, expire the existing (parent)one and update all its children to point to new parent ID. After all, there is not change to track when you look at the child record.
In your example, I would issue a new row for Doopie only if I detected that Barkie is not anymore his manager; that way I would be tracking historical changes of the managers.
Under your approach, have you figured out what would happened if a change occurs to the topmost manager? You would have to generate a new row for the whole organization…kind of messy to me. BTW, the only way I see you could implement this is through recursive queries…if you are using SQL Server 2005 you could use Common table expressions for that.
|||Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record. And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...
But I agree; if the topmost record would change I would get the whole tree inserted again 
So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.
Still, I think I must conclude that the dimensional modelling technique, at least to my knowledge, does not provide a good method of combining "tracking history" in combination with "recursive relationships".
Thnx, Jeroen
|||The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
If you have a single source for your employees, you can use that id, otherwise you have to generate one. Then, you
use this key for the relationship like so
ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 12/31/9999
20 200 Barkie NULL 4000 1/1/1900 12/31/9999
After the change the data would look like this:
ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 31/12/9999
20 200 Barkie NULL 4000 1/1/1900 12/7/2006
21 200 Barkie NULL 4500 12/8/2006 31/12/9999
Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record.
Indeed, I would not consider it an attribute of the child row.
And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...
I disagree. It does not seem like Doopie's parent always have had a salary of 4500; what it actually seems is the current salary of her parent is 4500. You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.
But I agree; if the topmost record would change I would get the whole tree inserted again
That is certanly not practical...
So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.
That was pretty much my original sugestion...good luck with that
The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
Thnx David. I do have the business key of the employee in the dimension. However, if you create a parent-child dimension in Analysis Services, you need a key which identifies a unique row in the dimension table. I didn't mention this before, but this is one of the things for which I use the column.
Jeroen
|||
You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.
Although I'm still convinced the ParentID is not quite as useful if it doesn't give the same result as joining using the business key in combination with start- and enddate, I think this a quite a good conclusion to wrap this up :) thnx.
Jeroen
Parent ID in a Slowly Changing Dimension
Hi There,
Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".
The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.
Thnx, Jeroen.
Hi There,
Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".
The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.
Thnx, Jeroen.
I don't understand the problem or question. I think if you define ParentID as 'historical' the SCD component will issue a new 'version' of the rows every time it detects a change in parentID value and it will 'expire' the previous 'version'. It’s just simple like that. It may be helpful is you provide an example of what you are trying to accomplish
|||Why would your Id column change if you have a type2 change?
Are you using a typ2 column to lookup your parentID?
|||Okay, let me try to give an example:
Lets say we have an employee named Doopie and his boss named Barkie, both in the dim_Employee table. In our case we work with start- and enddates.
ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 12/31/9999
20 Barkie NULL 4000 1/1/1900 12/31/9999
Okay, now, in the source system, Barkie gives himself a raise of 500. We want to track history of Salary. Now say we start the Data flow task with the Slowly changing dimension object. It could very well be, that Doopie's record is processed first. No changes there so Doopie's record remains unchanged. Now Barkie record which introduces a new row:
ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 31/12/9999
20 Barkie NULL 4000 1/1/1900 12/7/2006
21 Barkie NULL 4500 12/8/2006 31/12/9999
If Doopie's record was processed after the insert of Barkie's record, the lookup would return record 21 as the active Boss record, and introduce a new record for Doopie as well...
Thnx again!
Jeroen
|||
I see the problem now. Basically you need to make sure every child record point to the latest version of its parent.
Well, I don't see an easy way to do this at one pass. You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent. That could be safer in the case that an unchanged child row is not in your set of rows to be processed (assuming you are pulling only changed/new rows from the source). That is my best shot considering that I have not drunk my first coffee this morning
Why don't you normalize that table?
Hi Phil, I normalized some columns of it (name of parent), but the parent / child hierarchy goes about six or seven levels. So then I would have to add quite a few columns...
You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent.
I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...
Jeroen
|||I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...
So let's define 'historical'. Defining a column as 'historical' to me is intended to track changes on a particular attribute. In the example you gave at the beginning you said that no changes were made to the child row:
“No changes there so Doopie's record remains unchanged.”
So, why do you want to issue a new version of the child row? The only change that occurred was to the parent; hence you should generate a new row to the parent, expire the existing (parent)one and update all its children to point to new parent ID. After all, there is not change to track when you look at the child record.
In your example, I would issue a new row for Doopie only if I detected that Barkie is not anymore his manager; that way I would be tracking historical changes of the managers.
Under your approach, have you figured out what would happened if a change occurs to the topmost manager? You would have to generate a new row for the whole organization…kind of messy to me. BTW, the only way I see you could implement this is through recursive queries…if you are using SQL Server 2005 you could use Common table expressions for that.
|||Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record. And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...
But I agree; if the topmost record would change I would get the whole tree inserted again
So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.
Still, I think I must conclude that the dimensional modelling technique, at least to my knowledge, does not provide a good method of combining "tracking history" in combination with "recursive relationships".
Thnx, Jeroen
|||The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
If you have a single source for your employees, you can use that id, otherwise you have to generate one. Then, you
use this key for the relationship like so
ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 12/31/9999
20 200 Barkie NULL 4000 1/1/1900 12/31/9999
After the change the data would look like this:
ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 31/12/9999
20 200 Barkie NULL 4000 1/1/1900 12/7/2006
21 200 Barkie NULL 4500 12/8/2006 31/12/9999
Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record.
Indeed, I would not consider it an attribute of the child row.
And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...
I disagree. It does not seem like Doopie's parent always have had a salary of 4500; what it actually seems is the current salary of her parent is 4500. You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.
But I agree; if the topmost record would change I would get the whole tree inserted again
That is certanly not practical...
So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.
That was pretty much my original sugestion...good luck with that
The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
Thnx David. I do have the business key of the employee in the dimension. However, if you create a parent-child dimension in Analysis Services, you need a key which identifies a unique row in the dimension table. I didn't mention this before, but this is one of the things for which I use the column.
Jeroen
|||
You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.
Although I'm still convinced the ParentID is not quite as useful if it doesn't give the same result as joining using the business key in combination with start- and enddate, I think this a quite a good conclusion to wrap this up :) thnx.
Jeroen
Wednesday, March 21, 2012
Partitioning
Hello
I have a table containing 100,000 record for each year, and every year a new 100,000 record are inserted,
I need to know how to make partion this table by year
I need to know the syntax
Thank you
Any Ideas??
|||Hi JRICE,
what do you mean by "make partion this table"? A simple "order by year" will sort your table based on the "year" column, but i'm not sure if it is what you want.
Also, could you please elaborate more information on the structure of your table? thanks
|||Im talking about horizantal partitioning and i need some help in the syntax
Thank you
|||100k records per year is really not big of a number that requires partitioning. SQL Server 2005 has some advanced options for partitioning and gives move flexibility. In 2000 its more of a simulated partitioning. You'd have to have a view UNIONing the partitions. Basically its a bit painful. Check this white paper on partitioning by Kimberley Trip:http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
|||Hi JRICE,
Which verison of sql server are you using? Based on my understanding, only enterprise edition support partition. If you have got one, you can try the following example:
CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES (1, 100, 1000) ;GOCREATE PARTITION SCHEME myRangePS1AS PARTITION myRangePF1TO (test1fg, test2fg, test3fg, test4fg) ;GOALTER TABLE PartitionTable (col1 int, col2 char(10))ON myRangePS1 (col1) ;GO
This is one code example from msdn document, and it should work (i've only got a standard edition installed so i cannot test it on my side. sorry)
Hope my suggestion helps
Subscribe to:
Posts (Atom)