July 28, 2008 at 11:25 am
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.
August 8, 2008 at 2:29 am
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
August 11, 2008 at 3:20 pm
Thank you Susan,
I ended up doing just that.
August 12, 2008 at 7:28 am
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply