SQL SSAS Cube from a single view, getting the attribute key cannot be found when processing cube?

Question

I have the need to create a simple cube from a single table (view), no dimensions and facts star schema type stuff..

I have a large flat table (100+ columns). This table is a straight import from a CSV file, so I then create a view that includes a ID column...

As an example...

CREATE VIEW [dbo].[v_dw]
AS
SELECT
newId() Id,
x.[customer]
FROM dwdump as x;
GO

In SSAS designer I create my DSV from the view and all the int columns end up as fact data and all the varchar columns end up in a single dimension.

I try to process this cube and it throws duplicate record exist, so I set it to ignore this error, then it throws

The attribute key cannot be found when processing

The full error is...

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: '[dbo].[v_dw]', Column: 'Id', Value: '{D0B94A2D-7024-4634-844F-64768ED4B203}'. The attribute is 'Id'. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found.

I know that building a cube without proper fact/dimensions defined in the table is against best practices, but I need something simple and quick.

Can we not create a cube from a single table and use a arbitrary [Id] key column.

Seems that setting "Processing dimensions by: Table" helps in this situation where the cube is built from a single table and the trade-off is processing performance. — user1709091
Newid() is the culprit because it produces a different ID each time it runs. SSAS Multidimensional issues several queries and the data must tie out between the queries. The ByTable suggestion should solve this. But what are you trying to accomplish with that ID? — GregGalloway
Try to process the dimensions separately than process the cube. — Amira Bedhiafi

userfl89 userfl89 · Accepted Answer · 2019-01-16T17:55:07

This can be the result of measures being processed before dimensions, leading the corresponding key not found in the dimension. As you indicated in your comment, processing the dimensions doesn't pose any problems. Since this post is tagged with SSIS I'm assuming that you're either using an Analysis Services Processing task or processing via commands such as XMLA. When you define how the cube is processed set the dimensions to process before the fact table containing the measures is processed.

SQL SSAS Cube from a single view, getting the attribute key cannot be found when processing cube?

2 Answers