SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dimension Attribute Name Column


Dimension Attribute Name Column

Author
Message
Jay_Noob
Jay_Noob
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 236
I am new to data warehousing and SSAS, so forgive me if the answer is kicking me in the face. I am attempting to create a cube from a relational data source and one of my dimensions is giving me a headache over the namecolumn property of one of my attributes. The abbridged data source view goes something like this.

FACT_ORDER
(PK)order_id
.
.
(FK)emp_it
.
.
-------------

// employee dimension
EMPLOYEE TITLE
(PK)emp_id (PK)title_id
. title_name
. .
. .
(FK)title_id -------------
-------------
The EMPLOYEE table has a foreign key to the TITLE table on title_id.

My fact table has a regular relationship to the employee dimension on emp_id. The emp_id of the employee dimension has Usage set to Key.

The emp_id attribute of the fact table will never be null.
The title_id of the EMPLOYEE table can be null.

I have set the UnknownMember property of the Employee dimension to "Visible", and the NullProcessing of the Title_Id KeyColumn property to "UnknownMember".

I would like to display the title_name of the title table instead of the title_id, so I set the NameColumn of the Employee.title_id attribute to the title_name column of the TITLE table. When I process the dimension, the processing of each attribute is completed, but I get the following error despite the success of processing the attributes.

"Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'EMPLOYEE', Column: 'title_id', Value: '9'. The attribute is 'Title Id'."

I have verified that the value 9 does exist in the TITLE table and that there are numerous records in the EMPLOYEE table with a title_id of 9.

If I set the NameColumn property of the Title Id attibute to (none), the dimension will process without error.

If I set the NullProcessing property of the NameColumn property of the Title Id attribute, I get a build error that says "...The 'NameColumn' property has NullProcessing set to 'UnknownMember', but this is only allowed on KeyColumns of dimension attributes and measure group attributes and only if UnknownMember is Visible/Hidden."

I suspect that I am receiving this error because when BIDS encouters a null value for the Title Id attribute and tries to retrieve the name column value it fails and throws an error. I thought that setting Null Processing on the attribute key would somehow carryover to the Name Column as well. For example if SSAS encouters a null for the key it wouldn't bother to attempt to find a name column.

My question is of course how can I get the dimension to process and still assign a name column despite the fact that the title_id attribute can have a null value.

My knowledge of how SSAS works is limited and I would greatly appreciate any assistance that I can get. If my explanation is not clear enough I would be happy to elaborate.
Susan.Campher
Susan.Campher
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 104
You could combine the EMPLOYEE and TITLE tables in the Data Source View with a Named Query and handle the null values there with something like

SELECT EMPLOYEE.emp_id,
ISNULL(EMPLOYEE.title_id,-1) AS title_id,
ISNULL(TITLE.title_name,'Unknown') AS title_name
FROM EMPLOYEE
LEFT OUTER JOIN
TITLE
ON EMPLOYEE.title_id = TITLE.title_id
Jay_Noob
Jay_Noob
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 236
Thank you Susan,

I ended up doing just that.
Dave Balsillie
Dave Balsillie
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 430
Actually, you don't need to do that. I use this configuration routinely. You seem to have covered all the bases, but I suspect you've missed something small. Here's my checklist:

- UnknowmMember property for dimension set to: Visible
- UnknownMemberName property for dimension is not empty.
- KeyColumns property for the attribute has the property NullProcessing set to: UnknownMember. (Actually, set all the attributes thus way.)
- the KeyColumns property for the attribute points to the field on the dimension table (EMPLOYEE.title_id in your case).
- the NameColumns property for the attribute points to the field on the off board table (TITLE.title_name in your case).

If you still get the error, grab the SQL statements generated during the processing and examine them carefully. Most likely, based on your settings, you have a shortened resultset somehow. This is partially because MSAS only generated inner joins. Often, an unrelated join can filter results unexpectedly. Check your RI!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search