Parameterized Expression: relationship

Showing posts with label relationship. Show all posts

Monday, March 26, 2012

Parent-Child relationship

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...
>
>

Parent? Child? Distant Relative? Casual Aquaintance?

Hi all, I have a question that relates to a relationship in my stock performance database. It is a conceptual one, but is the only aspect of my database design that I am "losing sleep over" *LOL* (as if that could happen with me... ;) )

Anyway, Here are my tables/Keys, as setup

1) CurrentList - Primary Key is PortfolioID, StockID, BuyDate, Selldate.
- essentially, this table defines stock portfolios. A PortfolioID has one-to-many StockIDs (stocks in the portfolio), and each PortfolioID/StockID pair can further be grouped by BuyDate and Selldate (because a single stock may come in and out of a single portfolio over time).

2) StockProperty - Primary Key is StockID, CreateDate. This table is built on a daily basis from rows in the Currentlist. It represents the affected stocks currently in ANY portfolio. For example, the IBM stock may exist in more than one PORTFOLIO (PortfolioID) but there will still only be ONE row for IBM on a particular date (CreateDate) in the StockProperty table (since the stock is the same stock, regardless of how many portfolios it is in).

I am trying to figure out which is the parent table, and which the child...or if there IS an "enforceable" relationship at all!?!? None of the non-key data is the same in either table, so the only columns with a relationship is StockID, and StockProperty.Createdate - which must be between CurrentList.BuyDate and CurrentList.SellDate.

I guess that's my quandry (or one of myriad quandries in my life ;) ) - I can't really think of how to enforce (via foreign keys, etc) the relationship in the date range...AND...how to explain the relationship between the two tables (I lean toward CurrentList being Parent to StockProperty, but can't come to grips with what the probably obvious "standard" relationship description would be.

Any (preintable) thoughts?
Thanks!This is known as a many-to-many relationship, and is pretty common. An intermediary table (CurrentList, in your case) contains the primary keys of two unrelated parent tables (PortfolioID and StockID). In your example, your current list will have additonal composite key fields (BuyDate and SellDate) that describe a historical record of the many-to-many relationship.

That said, I suspect that you are going to run into more problems with your design. This is a very complicated business process to model, and you will likely run into many more many-to-many relationships and even more obscure structures before your get something robust and workable. I strongly recommend that you scale back the scope of your project if possible, and then add features as you verify your design through use.|||Okee-dokee, thanks for burning a few brain cells on this...

It's actually working in production, and as you probably guessed, this is just about 1/5th of the project's database, and the overall production database is working with no apparent design issues so far. I THINK I'm ok in that respect. As with all things, time and unforseen keystroke sequences will tell.

There are really only TWO tables involved at this part of the design though...the CurrentList and the StockProperty tables. A Currentlist row, however, can only be associated with ONE stockproperty row (in my example above, if the same stock is in the same currentlist multiple times, the BuyDate and SellDate in the CurrentList row will be different, resulting in a new and separate CurrentList row). (or were you saying that I SHOULD have the intermediary table?) Conversely, a StockProperty row can be associated with one-to-many CurrentList rows.

Anyway, I know it works, I'm just trying to step back and look at this segment of the design and try to figure out if it's designed the way it SHOULD be. Any time I see something like this that gives me trouble mapping out relationships onto paper - just is a design "uh-oh" red flag to me. (hey, Daddy, what's "paper"?)

I'm thinking that it really would be enough at one level to add a foreign key on StockID using CurrentList as the child, and StockProperty as the parent. That seems too simple though, and disregards the stockproperty.date to currentlist.daterange relationship.

Hmmm...I appreciate any insight anyone can provide...but no big rush or urgency. It's more to me like a "OK, it's out there, NOW what could I have done differently?" thing. Yeah, I know...NOT how to develop ideally, but The Machine coerced me...and now I just wanna make SURE, in retrospect, that my kid won't see this someday and think "Good God, Dad...WTF were you THINKING?" (she does that enough NOW ;) ).

Parent Child transversing

I have a parent/child relationship in a relational database broken out like this:

Table Name: categories
[category_id] int (primary_key NOT NULL),
[category_name] varchar(50),
[parent_fk] int

The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this:

select category_id, category, parent_fk
from categories
where
category_id not in (
select parent_fk from categories
)

Each bottom-level category has a count attached to it.

The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts).

My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything).

Thanks!

Attached is a diagram that might help:
http://www.cnsar.com/images/diagram.gif
|||

This procedure (below) is based on the following table layout:

CREATE TABLE categories (
category_id int,
desc_name varchar(35),
parent_fk int
)
-----------------------
CREATE PROCEDURE GetChildren
@.category_idint --pass in the parent.id in which you want to get the children
AS

SET NOCOUNT ON
CREATE TABLE #temp_children1(category_idintNOT NULL)
CREATE TABLE #temp_children2(category_idintNOT NULL)
CREATE TABLE #temp_allchildren(category_idintNOT NULL)

INSERT INTO #temp_children1VALUES(@.category_id)-- Parent
WHILEEXISTS(SELECT *FROM #temp_children1)
BEGIN
DELETE FROM #temp_children2
-- Save current level children
INSERT INTO #temp_children2
SELECT c.category_id
FROM categories cJOIN #temp_children1 t
ON c.parent_fk = t.category_id
WHERE c.parent_fk <> c.category_id

DELETE FROM #temp_children1
INSERT INTO #temp_children1
SELECT *FROM #temp_children2
-- Add to the list of all children

INSERT INTO #temp_allchildren
SELECT *FROM #temp_children2

END

--return the children ids
select category_id
from #temp_allchildren

--cleanup
DROP TABLE #temp_children1
DROP TABLE #temp_children2
DROP TABLE #temp_allchildren

|||

Based on your diagram, I made the assumption that the count you want is the number of images that are associated with each category. If you just wanted the number of categories that are subchildren, you'll need to change the function a little bit, returning 1 in the first part of the if and returning SUM(dbo.ChildrenImageCount(CategoryID))+1 in the second part of the if in the ChildrenImageCount function (The commented out lines). It will probably be one more than you are expecting because it counts itself as well, but a small price to pay for ease of use. If you want the count of end-nodes, remove the +1.

Just an FYI -- Your root node should have a ParentFK of NULL, otherwise it'd be it's own parent. If you must continue to use 1, don't use the Foreign key constraint (It won't work -- I don't think), and don't ever select the count for the root node (It will loop forever until it blows up) -- unless of course you change it so it won't do that ;-)

GO
/****** Object: Table [dbo].[Categories] Script Date: 01/20/2006 01:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATETABLE [dbo].[Categories](

[CategoryID] [int]IDENTITY(1,1)NOTNULL,

[ParentFK] [int]NULL,

[Name] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_ASNOTNULL,

[ImageCount]AS([dbo].[ChildrenImageCount]([CategoryID])),

CONSTRAINT [PK_Categories]PRIMARYKEYCLUSTERED

(

[CategoryID]ASC

)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]

)ON [PRIMARY]

SETANSI_PADDINGOFF

ALTERTABLE [dbo].[Categories]WITHCHECKADDCONSTRAINT [FK_Categories_Categories]FOREIGNKEY([ParentFK])

REFERENCES [dbo].[Categories]([CategoryID])

GO
/****** Object: Table [dbo].[Images] Script Date: 01/20/2006 01:42:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Images](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[Image] [image] NULL,
[ImageMime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

GO
/****** Object: UserDefinedFunction [dbo].[ChildrenImageCount] Script Date: 01/20/2006 01:42:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ChildrenImageCount]
(
-- Add the parameters for the function here
@.CategoryID int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @.Result int

IF NOT EXISTS(SELECT CategoryID FROM Categories WHEREParentFK=@.CategoryID)
BEGIN
SELECT @.Result=COUNT(*) FROM Images WHERECategoryID=@.CategoryID

-- SELECT @.Result=1

END
ELSE
BEGIN
-- Add the T-SQL statements to compute the return value here
SELECT @.Result=SUM(dbo.ChildrenImageCount(CategoryID)) FROM Categories WHEREParentFK=@.CategoryID

-- SELECT @.Result=SUM(dbo.ChildrenImageCount(CategoryID))+1 FROM Categories WHEREParentFK=@.CategoryID

END

-- Return the result of the function
RETURN @.Result

END

Once you make those changes, you can just select ImageCount from the categories table for any record and it will have the right answer for you.

Friday, March 23, 2012

Parent Child relationship column hint?

I've got a dilemma which I hope someone has a solution to.

Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".

There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.

My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.

I was hoping to find some sort of parent-child hint in the column properties but found none.

What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.

Geof

You can still use two columns. The first is the type, the second is type+series:

TypeTypeSeries

737 737-100

737 737-200

…

747 747-100

This solution is basically an extension of your proposed one. Please let me know if this works for you.

Thanks,

|||

Of course! Thanks, I thought I was close.

It worked just fine.

Geof

Parent Child relationship column hint?

I've got a dilemma which I hope someone has a solution to.

I was hoping to find some sort of parent-child hint in the column properties but found none.

What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.

Geof

You can still use two columns. The first is the type, the second is type+series:

TypeTypeSeries

737 737-100

737 737-200

…

747 747-100

This solution is basically an extension of your proposed one. Please let me know if this works for you.

Thanks,

|||

Of course! Thanks, I thought I was close.

It worked just fine.

Geof

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

MardoSuppose you have the following columns in your table:
ID - Integer. Primary key column of your table
ParentID - Integer. The id of the parent object.
Name - String. The name of the object identified by ID.
Since things already work for you then you already have the key attribute and parent attribute set in your dimension. To resolve your problem select the parent attribute oin Dimension Structure page of the dimension editor and press F4 to activate the property grid displaying the properties of the selected attribute. Locate NameColumn property. Edit that property and make it binding to the Name column in your table.
|||When I do that, I get the following error after entering it...

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Is this a bug in a version of BIDS? I have been struggling with this all day and I finally have found the issue. I am running what I think is a hotfixed version Microsoft SQL Server Analysis Services Designer Version 9.00.2047.00 of the AS Tools.

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

<NameColumn>
<NullProcessing>ZeroOrBlank</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_membership_dim</TableID>
<ColumnID>membership_dim_key</ColumnID>
</Source>
</NameColumn>

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

I actually got it working by changing all of the attributes of the Hierarchy set to use the membership attribute, not just the Parent attribute/hierarchy. This stuff is interesting to say the least :)

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

The table structures for the related tables are. I am doing the membership_dim related through the account_dim to the sales_fact. I built these tables using select...into from adventureWorksDw to try to approximate our structures and was wanting to try out the logical keys in the DSV, hence the lack of relationships, pkeys, etc). I am trying to build a demonstration cube to demonstrate all of the different constructs we need (this all got started as I tried to figure out what I was doing here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=522521&SiteID=1&mode=1)

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

CREATE TABLE [dbo].[membership_dim](
[membership_dim_key] [int] NOT NULL,
[parent_membership_dim_key] [int] NULL,
[membership] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED ( [membership_dim_key] ASC )
)

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

CREATE TABLE [dbo].[account_dim](
[account_dim_key] [int] IDENTITY(1,1) NOT NULL,
[account_number] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[geography_dim_key] [int] NULL,
[marital_status] [nchar](1) COLLATE Latin1_General_CI_AS NULL,
[yearly_income] [money] NULL,
[total_children] [tinyint] NULL,
[education_level] [nvarchar](40) COLLATE Latin1_General_CI_AS NULL,
[occupation_type] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[commute_distance] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
[customer_first_purchase_date_dim_key] [int] NULL,
[membership_dim_key] [int] NULL
)

CREATE TABLE [dbo].[sales_fact](
[sales_fact_key] [bigint] NULL,
[product_dim_key] [int] NOT NULL,
[ship_date_dim_key] [int] NULL,
[order_date_dim_key] [int] NULL,
[account_dim_key] [int] NOT NULL,
[discountAmount] [float] NULL,
[unit_price] [money] NULL,
[sales_order_number] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[sales_order_line_number] [tinyint] NOT NULL,
[sales_territory_dim_key] [int] NULL
) ON [PRIMARY]

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

MardoSuppose you have the following columns in your table:

ID - Integer. Primary key column of your table

ParentID - Integer. The id of the parent object.

Name - String. The name of the object identified by ID.

Since things already work for you then you already have the key

attribute and parent attribute set in your dimension. To resolve your

problem select the parent attribute oin Dimension Structure page of the dimension editor and press F4 to

activate the property grid displaying the properties of the selected

attribute. Locate NameColumn property. Edit that property and make it

binding to the Name column in your table.|||When I do that, I get the following error after entering it...

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

parent child relationship

I am using SQL 2000. I have a table with which has both the parent row and child rows.

Pid type status

- - --

1 P 0 -Parent row

2 C 0

3 C 1

4 C 0

I added a new column "Pstatus" to the table. I have to update the table to show the status of parent row against the child row in new col as below.... There are a million records with different parent/child.

Pid type status Pstatus(new col)

- - -- -

1 P 0 0 -Parent row

2 C 0 0

3 C 1 0

4 C 0 0

Thanks...........

How do you know which Parent the Child should be associated with?

It is not clear what you are attempting to accomplish. Please offer more explanition, and perhaps sample data (in the form of INSERT statements).

|||Hey.. What you want to do here? Give more Details?|||

I apologize for the mistake in providing the complete info....The Parent Child relation is defined by Pid..which i have now corrected as below. I hope this helps.....

Pid type status Pstatus(new col)

- - -- -

1 P 0 0 -Parent row

1 C 0 0

1 C 1 0

1 C 0 0

|||

I apologize for the mistake in providing the complete info....The Parent Child relation is defined by Pid..which i have now corrected as below. I hope this helps.....

Pid type status Pstatus(new col)

- - -- -

1 P 0 0 -Parent row

1 C 0 0

1 C 1 0

1 C 0 0

|||

IF PID refers to the Parent, is the record with PID=1 AND Type=P its' own parent? This design only allows for 2 levels.

So, there is no unique identifier for each Child?

Something important is missing here. It seems like there 'should' also an [ID] PRIMARY KEY column. A common design issue is to provide each row with a unique method of distinguishing that row from any other row, and in the case of hierachical designs, also a column to indicate which record is the parent. In this case, while you can create a suposition about parentage by using the [Type] column, you would not be able to tell row 2 from row 4 (using your data above)? Even identical twins have names -and different fingerprints, etc.

Otherwise, as I ask before, how do you determine

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

Parent Child index?

I have a table that has recursive relationship with itself. The primary key
is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier). I
would like to create an index for these two fields, including cascading
deletes (regardless of the number of decendants). Is this possible? If so,
can someone step me thru it?
Thanks,
Craig BuchananHi
You can't create a FK with cascading deletes when the FK refers to the same
table. If you implemented this with a trigger you would be restricted to 32
levels. What you may want to do is to run a batch process that clears up the
table.
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message
news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> I have a table that has recursive relationship with itself. The primary
key
> is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier).
I
> would like to create an index for these two fields, including cascading
> deletes (regardless of the number of decendants). Is this possible? If
so,
> can someone step me thru it?
> Thanks,
> Craig Buchanan
>|||John-
How would I create a trigger between two tables where PK and FK are
uniqueidentifiers?
Thanks,
Craig
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> Hi
> You can't create a FK with cascading deletes when the FK refers to the
same
> table. If you implemented this with a trigger you would be restricted to
32
> levels. What you may want to do is to run a batch process that clears up
the
> table.
> John
> "Craig Buchanan" <someone@.microsoft.com> wrote in message
> news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> key
(uniqueidentifier).
> I
> so,
>|||Hi Craig
Something like:
CREATE TABLE ParentChildTable ( id int not null CONSTRAINT
PK_ParentChild PRIMARY KEY,
parentid int not null )
DROP TABLE ParentChildTable
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
SELECT * FROM deleted
DELETE FROM ParentChildTable
FROM ParentChildTable P JOIN DELETED D ON P.Parentid = d.id
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
sp_configure 'nested triggers'
/*
Needs to be 1
name minimum maximum
config_value run_value
-- -- --
-- --
nested triggers 0 1 1
1
*/
sp_dboption testdb, 'recursive triggers'
/*
Needs to be ON
OptionName CurrentSetting
-- --
recursive triggers ON
*/
DELETE FROM ParentChildTable WHERE id = 3
/* id parentid
-- --
3 2
(1 row(s) affected)
id parentid
-- --
4 3
(1 row(s) affected)
id parentid
-- --
5 4
(1 row(s) affected)
id parentid
-- --
6 5
(1 row(s) affected)
id parentid
-- --
7 6
(1 row(s) affected)
id parentid
-- --
8 7
(1 row(s) affected)
id parentid
-- --
9 8
(1 row(s) affected)
id parentid
-- --
10 9
(1 row(s) affected)
id parentid
-- --
11 10
(1 row(s) affected)
id parentid
-- --
12 11
(1 row(s) affected)
id parentid
-- --
13 12
(1 row(s) affected)
id parentid
-- --
14 13
(1 row(s) affected)
id parentid
-- --
15 14
(1 row(s) affected)
id parentid
-- --
16 15
(1 row(s) affected)
id parentid
-- --
17 16
(1 row(s) affected)
id parentid
-- --
18 17
(1 row(s) affected)
id parentid
-- --
19 18
(1 row(s) affected)
id parentid
-- --
20 19
(1 row(s) affected)
id parentid
-- --
21 20
(1 row(s) affected)
id parentid
-- --
22 21
(1 row(s) affected)
id parentid
-- --
23 22
(1 row(s) affected)
id parentid
-- --
24 23
(1 row(s) affected)
id parentid
-- --
25 24
(1 row(s) affected)
id parentid
-- --
26 25
(1 row(s) affected)
id parentid
-- --
27 26
(1 row(s) affected)
id parentid
-- --
28 27
(1 row(s) affected)
id parentid
-- --
29 28
(1 row(s) affected)
id parentid
-- --
30 29
(1 row(s) affected)
id parentid
-- --
31 30
(1 row(s) affected)
id parentid
-- --
32 31
(1 row(s) affected)
id parentid
-- --
33 32
(1 row(s) affected)
id parentid
-- --
34 33
(1 row(s) affected)
Server: Msg 217, Level 16, State 1, Procedure TRG_ParentChildTable,
Line 7
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).
*/
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message news:<eOG$Y6BEEHA.2404@.TK2MSFTNGP
11.phx.gbl>...
> John-
> How would I create a trigger between two tables where PK and FK are
> uniqueidentifiers?
> Thanks,
> Craig
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> same
> 32
> the
> key
> (uniqueidentifier).
> I
> so,|||Hi Craig
This may be an alternative, but if it may cause contention on this table and
you may be better off running a scheduled task to do the deletions in a qui
et period.
sp_dboption testdb, 'recursive triggers', false
sp_dboption testdb, 'recursive triggers'
/*
Needs to be off
OptionName CurrentSetting
-- --
recursive triggers off
*/
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
WHILE @.@.ROWCOUNT > 0
DELETE FROM ParentChildTable
FROM ParentChildTable
WHERE ParentId <> 0
AND ParentId NOT IN ( SELECT Id FROM ParentChildTable )
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
DELETE FROM ParentChildTable WHERE Id = 3
SELECT * FROM ParentChildTable
John

Tuesday, March 20, 2012

parent -> child relationship (same table)

Is it best to store the parent/child relationships in seperate tables? What are the pro/cons to this situation vs. storing the parentid in the same row (ie: id-parentid-itemname)

For an online catalog where you'd like to display a single item in multiple categories, are different tables the best way to do this?

How about an online directory listing (similar to yahoo.com's main page)?

Links, pre-developed products, etc. welcome. I'm new to doing this type of thing!For your example:

Product Table:
ProductID
ProductDescription
-- etc.

Category Table:
CategoryID
CategoryName
-- etc.

CategoryProductLink Table:
CategoryID
ProductID
-- etc.|||Putting the relationship in another table will allow you to assign multiple parents for a given node. Beside that you'll encounter the same problems in both situation like getting the children or parents of a given node.

Monday, March 26, 2012

Parent-Child relationship

Parent? Child? Distant Relative? Casual Aquaintance?

Parent Child transversing

Friday, March 23, 2012

Parent Child relationship column hint?

Parent Child relationship column hint?

Parent Child Relationship

Parent Child Relationship

parent child relationship

Parent Child Relationship

Parent Child index?

Tuesday, March 20, 2012

parent -> child relationship (same table)

Parameterized Expression

Blog Archive

About Me