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:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
</return>

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.

|||

2.

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]