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.
// employee dimension
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.