Error occurred while the attribute of the dimension from the database was being processed–snowflake dimension

Problem

While designing a new snowflake dimension in an Analysis services project, I received the following error message during deployment with Visual Studio of the project to the Analysis Server:

Errors in the OLAP storage engine: An error occurred while the 'Plant' attribute of the 'Device' dimension from the '<projectname>' database was being processed.

The snowflake dimension ‘Device’ is a combination of related tables: Country – Region – Plant – Device. The Dimension the same hierarchy as in the one-to-many relationship.

Each table is composed of a primary key Id column, a name column and various other properties. The snowflake dimension will be used for grouping and totalling production data, so that users can drill down from Country level to Regional, to Plant or to Device. The users do not know the Id values, they are shown the names.

The dimension attribute properties are setup to use the Id as Key, but to display the name and order the listed values by name, e.g. for Region

• KeyColumns: Region.id
• NameColumn: Region.Name
• OrderBy: Name

After the initial design of this dimension I wanted to deploy to the server, so as to be able to test my design in the Dimension Browser. Then I received the abovementioned error message.

SOLUTION: look for NULL values in Foreign key column that refers to parent table

The error message was displayed in the Error List window in Visual Studio. The Output window shows some more information about the error.

Warning -2128674815 : Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_Region', Column: 'Id', Value: '0'. The attribute is 'Region'.

When I queried SQL database table Plant for foreign key RegionId with value 0, none were found. When I queried for foreign key field being NULL, I found one such record in the Plant table. This record happens to be the main office of the company and is there for other purposes, so it must not be selected.

• Go to the Data Source View, right click on Plant table and select ‘Replace Table –> With New Named Query…
• In the query designer add the condition WHERE RegionId IS NOT NULL
• Save the query.
• Deploy the project

The project will now deploy without any further error messages, unless you have other data to clean up.