Friends,
This is my 100th post and hence decided to go with the most useful stuff in this post. n this post we are gonna discuss about the most frequently occurred error in SSAS. The error message looks like below (table and column names may vary).
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_DimProduct_test’, Column: ‘ProductKey’, Value: ‘227’.
Firstly let’s see what is this error message and then we can discuss about FIX for the same. If you see the above error message, it is clearly saying that TABLE DimProduct_test ,which is used a as dimension ,has duplicate key and need to be fixed. Here we have to understand couple of things before thinking about fix and lets start with those.
Identifying the Issue – Most of the guys are not aware HOW IT LOOKS when SSAS engine throws an error. It looks like the below given screenshot.
As seen in above pic, you will get 100’s of icons in RED(Saying Error). You don’t waste time by looking at all those. Just concentrate on the LAST one in Yellow which has YELLOW WARNING icon. This will have ACTUAL error. Remaining are just BULL SHIT.
Reason for Issue – Now select the warning message and click on View Details button present at the bottom of the wizard to see the full description of the issue as shown below.
It is pretty clear from the message that the table used to create dimension has DUPLICATE RECORDS in it. As per the functionality of SSAS, It doesn’t support duplicate values in the dimension tables.
Reproduce the Error – If you are not getting this error then you are lucky because the data present in your datawarehouse is pretty good. To know more about this let’s try to reproduce this issue. PFB the steps to be followed.
- I have taken “Adventure Works DW” database to demonstrate the same. You can download the same from Microsoft site free of cost.
- Take Dimproduct table and crate a duplicate table with same structure but with out IDENTITY and CONSTRAINTS. PFB the query of the same.
CREATE TABLE [dbo].[DimProduct_test](
[ProductKey] [int] NOT NULL,
[ProductAlternateKey] [nvarchar](25) NULL,
[ProductSubcategoryKey] [int] NULL,
[WeightUnitMeasureCode] [nchar](3) NULL,
[SizeUnitMeasureCode] [nchar](3) NULL,
[EnglishProductName] [nvarchar](50) NOT NULL,
[SpanishProductName] [nvarchar](50) NOT NULL,
[FrenchProductName] [nvarchar](50) NOT NULL,
[StandardCost] [money] NULL,
[FinishedGoodsFlag] [bit] NOT NULL,
[Color] [nvarchar](15) NOT NULL,
[SafetyStockLevel] [smallint] NULL,
[ReorderPoint] [smallint] NULL,
[ListPrice] [money] NULL,
[Size] [nvarchar](50) NULL,
[SizeRange] [nvarchar](50) NULL,
[Weight] [float] NULL,
[DaysToManufacture] [int] NULL,
[ProductLine] [nchar](2) NULL,
[DealerPrice] [money] NULL,
[Class] [nchar](2) NULL,
[Style] [nchar](2) NULL,
[ModelName] [nvarchar](50) NULL,
[LargePhoto] [varbinary](max) NULL,
[EnglishDescription] [nvarchar](400) NULL,
[FrenchDescription] [nvarchar](400) NULL,
[ChineseDescription] [nvarchar](400) NULL,
[ArabicDescription] [nvarchar](400) NULL,
[HebrewDescription] [nvarchar](400) NULL,
[ThaiDescription] [nvarchar](400) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Status] [nvarchar](7) NULL
)
- Now load the data from MAIN DimProduct table using the below query.
insert into DimProduct_test select * from DimProduct
- Execute the above query more than once so that you will get DUPLICATE records into the newly created table.
- Now add this table to DSV in the SSAS cube solution.
- Create a dimension with this table ans execute this dimension. You can get the .above given error.
- Now you have this error in your plate and hence let’s see the FIX now.
Issue Fix – The column what you use as KEY in Dimension creation should not have DUPLICATES and SSAS doesn’t bother about remaining columns. It bother about ONLY the column which you use as KEY and hence the FIX for this issue is to ELIMINATE duplicates from the KEY column. If you have backup of data then you can delete the data in this table and insert only UNIQUE records is one way to fix this issue. Now let me show what will happen when duplicates are removed.
To eliminate the duplicates do the following –
- Delete the records from the table DimProduct_Test using the below given query.
Truncate table DimProduct_test
- Insert the data from DimProduct table using the below given query and this time EXECUTE ONLY ONCE as we don’t want duplicates in our table.
insert into DimProduct_test select * from DimProduct
- Now process the dimension and you can see the dimension executed successfully.
That is it .. This is the reason and we also discussed the FIX. I am pretty sure that this info will be very very helpful to you guys who are new to SSAS. Enjoy Fixing and mind to drop a comment if this helped you !!
Regards,
Roopesh Babu V
