Often while working with SQL Server Analysis Services (SSAS), developers come across errors that restrict them from deploying or processing the cube. One common error that occurs quite frequently is the Duplicate Key error. This error is usually caused by the incorrect configuration of the key properties of the dimension members.

To understand this error and how to resolve it, let’s replicate the error and then explain the solution. Please note that this article focuses on resolving the error and doesn’t explain how to create an Analysis Services project from scratch.

Reproducing the Error in SSAS

First, let’s prepare our environment by creating a simple table and inserting some records:

CREATE TABLE DupKeyDemo(
[CustomerID] INT,
[Customer] VARCHAR(50),
[Amount] INT
)

INSERT INTO DupKeyDemo 
VALUES
  (1,'John',100),
  (2,'Greg',200)

Now that our base table is ready, let’s quickly build the SSAS project. Fetch this table in the data source view and create a cube from it. For the dimension, select the Key Column as “CustomerID” and the Name Column as “Customer”.

Once the cube is created, we can deploy and process it. After successful processing, we can easily browse the cube.

Now, let’s modify our data and try to reprocess the cube by inserting a record with the same CustomerID:

INSERT INTO DupKeyDemo 
VALUES
(1,'Peter',300)

However, when we try to process the cube, we encounter an error stating that a duplicate key has been found and the cube cannot be processed further.

Solution

To resolve this error, we need to modify the properties of the Customer dimension in SSAS. Right-click on the Customer dimension and select Properties. Click on the browse button next to KeyColumns and select the Customer from the Available Columns. This adds the Customer to the list of Key Columns.

Next, open the cube and go to the Dimension Usage tab. Add the Customer to the Measure Group Columns. This instructs the cube to consider the combination of CustomerID and Customer as a unique combination.

Once these changes have been implemented, try to process and browse the cube again. You will notice that the error has been resolved and all the customers are displayed when browsing the cube.

Conclusion

In this article, we have explained how to resolve the Duplicate Key error in SQL Server Analysis Services. By modifying the key properties of the dimension members, we can ensure that the cube processes without any errors. It is important to understand the cause of the error and implement the appropriate solution to avoid any issues with the cube.