Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_DimProduct_test’, Column: ‘ProductKey’, Value: ‘227’.

By
roopesh.valluru
-
210
17314

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

RELATED ARTICLESMORE FROM AUTHOR

210 COMMENTS

  25. Fantastic goods from you, man. I have understand
    your stuff previous to and you’re just too fantastic.“강남안마”
    I really like what you have acquired here, certainly like what you are
    stating and the way in which you say it. You make it enjoyable and you still take care of to keep it sensible.Thanks for sharing

  103. Here is Sportsmail’s live blog for updates on this year’s Soccer Aid as England and a World XI went head-to-head at the London Stadium. Popular Leagues Ex-Manchester United forward Louis Saha has claimed that Marcus Rashford is the club’s greatest talent since Cristiano Ronaldo. Popular Leagues Take a look back at all the key moments from the year across the whole club Boxing Day 2022 Premier League Action APPADS World Cup Semi-Final Odds, Preview and Tips Find all the results of yesterday’s Football England. And to find out more about the result of a Football match yesterday in England, just click on the link of the match. ISOCountry Code: He’s changed the game since his early introduction. He gave away a penalty before winning one himself and then should score there. Asante, who has also impressed since coming on, finds Chunkz who turns away from his marker and unleashes a shot that whistles past Petr Cech’s left post. 
    https://huzzaz.com/collection/tottenham-hotspur-league-table
    Willie Miller has gushed over the stunning goal Liverpool gem Leighton Clarkson scored for Aberdeen against Rangers. The Reds loanee netted during the Dons’ 3-2 loss on Tuesday. But, even if ‘five to seven minutes’ really was a third of 25 minutes, would it really be such a waste of valuable time? Are they really seconds and minutes better devoted to the wisdom of Steven Gerrard or Marco Silva? Admittedly, we had a nervy few minutes as we found our feet, but in reality, once we got going, there was only ever going to be one winner. Or maybe it’s not. The Japanese international was well known to have played that position, and well, for his old club Kawasaki Frontale but Ange saw him as a missing piece of our midfield jigsaw when he signed him on Hogmanay last year.

  108. Народные средства все же относятся к не совсем доказанным средствам, тем более что возможны побочные эффекты в виде аллергической реакции. Поэтому использование специализированных средств дает более быстрый и значительный эффект. Насчет полночи и тыквы – конечно ничто не вечно и со временем, если перестать пользоваться Дрилашем, состояние ресниц ухудшится. Только зачем, совсем переставать пользоваться? Чтобы ресницы хорошо росли, помоги им с питанием. Тем более, что сегодня тебе в этом помогут различные домашние средства для роста ресниц и куча аптечных и профессиональных средств из магазинов косметики. Хороши народные средства для роста ресниц. Сыворотки, масла, маски – все это поможет сделать взгляд красивым, выразительным. Если сомневаетесь, проконсультируйтесь с косметологом. Он порекомендует специальные средства конкретно под Вашу проблему, расскажет, как пользоваться, даст советы относительно питания, косметики, образа жизни.
    http://agape.ijesus.net/bbs/board.php?bo_table=free&wr_id=59096
    Как утверждают в отзывах девушки, средство Levrana имеет самую удобную щеточку из всех, опробованных ими. Она мягкая и эргономичная, позволяет аккуратно нанести масло на брови и ресницы, а также гарантирует экономичный расход. Продукт идеально подходит для ежедневного применения, его не нужно смывать и можно применять перед сном. Рекомендуем попробовать Levrana всем, кто мечтает о густых натуральных бровях. Однако даже самые эффективные составы для роста не смогут сделать твои ресницы здоровыми и красивыми, если ты нарушаешь несколько правил: Продукт  для питания ресниц Pierre Rene тоже брендовое средство. Оно совсем не дорогое, но действенное и эффективное. Масло идеально питает луковицы, восстанавливает их длину. Страна производства данного средства – Польша. Ряд растительных масел как нельзя лучше подходит для ухода за волосками и кожей бровей. Эффективность их доказана клиническими исследованиями, протестирована большим количеством довольных потребителей. Среди этих масел особого внимания заслуживают средства:

  147. [url=https://www.yeezyadidas.de/]Yeezy[/url]
    [url=https://www.yeezys.co/]Yeezys[/url]
    [url=https://www.nikeairvapormax.us/]Nike Air Vapormax[/url]
    [url=https://www.jordan-1.org/]Jordan 1[/url]
    [url=https://www.air-jordan1.com/]Air Jordan 1[/url]
    [url=https://www.nikejordan1.com/]Nike Jordan 1[/url]
    [url=https://www.jordan-1s.com/]Jordan 1S[/url]
    [url=https://www.jordan1.uk.com/]Jordan 1[/url]
    [url=https://www.pandoracharms.uk.com/]Pandora Charms [/url]
    [url=https://www.jordans-shoes.com/]Jordan Shoes[/url]
    [url=https://www.nikejordan1.us/]Nike Jordan 1[/url]
    [url=https://www.jordans-1.us/]Jordan 1[/url]
    [url=https://www.jordan-shoes.us.com/]Jordan Shoes[/url]
    [url=https://www.nikeuk.uk.com/]Nike UK[/url]
    [url=https://www.yeezy-450.com/]Yeezy 450[/url]
    [url=https://www.jordanretro4.com/]Jordan Retro 4[/url]
    [url=https://www.nikeoutletstoreonlineshopping.us/]Nike Outlet Store Online Shopping[/url]
    [url=https://www.yeezy.uk.com/]YEEZY[/url]
    [url=https://www.adidasyeezyofficialwebsite.com/]Adidas Yeezy Official Website[/url]
    [url=https://www.yeezy350.uk.com/]Yeezy 350[/url]
    [url=https://www.air-jordan4.com/]Air Jordan 4[/url]
    [url=https://www.yeezy-supply.com/]Yeezy Supply[/url]
    [url=https://www.yeezyfoam-runner.com/]Yeezy Foam Runner[/url]
    [url=https://www.yeezysofficialsite.com/]Yeezys Official Site[/url]
    [url=https://www.yeezyslides.us.com/]YEEZY SLIDES[/url]
    [url=https://www.ray-ban-glasses.us.com/]Ray Ban Glasses[/url]
    [url=https://www.adidasuk.uk.com/]Adidas UK[/url]
    [url=https://www.nflshopofficialonlinestore.com/]NFL Shop Official Online Store[/url]
    [url=https://www.yeezys-slides.us.com/]Yeezy Slides[/url]
    [url=https://www.yeezyadidass.us.com/]Adidas Yeezy[/url]
    [url=https://www.350yeezy.us.com/]Yeezy 350[/url]
    [url=https://www.yeezyy.us.com/]Yeezy[/url]
    [url=https://www.yeezy350s.us.com/]Yeezy 350[/url]
    [url=https://www.shoesyeezys.us.com/]Yeezy Shoes[/url]
    [url=https://www.yeezys.uk.com/]Yeezys[/url]
    [url=https://raybansales.us/]Ray Ban[/url]
    [url=https://www.adidasyeezy.uk.com/]Adidas Yeezy[/url]
    [url=https://www.yzyshoes.us.com/]Yeezy Shoes[/url]
    [url=https://www.yeezy-slides.org/]Yeezy Slides[/url]
    [url=https://www.yeezy-shoes.us.com/]Yeezy Shoes[/url]
    [url=https://www.yeezy-700.us.com/]Yeezy 700[/url]
    [url=https://www.yeezyadidas.de/]Adidas Yeezy[/url]
    [url=https://www.yeezys.co/]Yeezy[/url]
    [url=https://www.nikeairvapormax.us/]Air Vapormax[/url]
    [url=https://www.jordan-1.org/]Nike Jordan 1[/url]
    [url=https://www.air-jordan1.com/]Jordan 1[/url]
    [url=https://www.nikejordan1.com/]Jordan 1[/url]
    [url=https://www.jordan-1s.com/]Jordan 1[/url]
    [url=https://www.jordan1.uk.com/]Jordan UK[/url]
    [url=https://www.pandoracharms.uk.com/]Pandora UK[/url]
    [url=https://www.jordans-shoes.com/]Jordans Shoes[/url]
    [url=https://www.nikejordan1.us/]Jordan 1[/url]
    [url=https://www.jordans-1.us/]Jordans 1[/url]
    [url=https://www.jordan-shoes.us.com/]Jordan[/url]
    [url=https://www.nikeuk.uk.com/]Nike[/url]
    [url=https://www.yeezy-450.com/]Yeezy[/url]
    [url=https://www.jordanretro4.com/]Jordan 4[/url]
    [url=https://www.nikeoutletstoreonlineshopping.us/]Nike Outlet[/url]
    [url=https://www.yeezy.uk.com/]YEEZY UK[/url]
    [url=https://www.adidasyeezyofficialwebsite.com/]Adidas Yeezy[/url]
    [url=https://www.yeezy350.uk.com/]Yeezy uk[/url]
    [url=https://www.air-jordan4.com/]Jordan 4[/url]
    [url=https://www.yeezy-supply.com/]Yeezys Supply[/url]
    [url=https://www.yeezyfoam-runner.com/]Yeezy[/url]
    [url=https://www.yeezysofficialsite.com/]Yeezys[/url]
    [url=https://www.yeezyslides.us.com/]YEEZY[/url]
    [url=https://www.ray-ban-glasses.us.com/]Ray Bans Sunglasses[/url]
    [url=https://www.adidasuk.uk.com/]Adidas[/url]
    [url=https://www.nflshopofficialonlinestore.com/]NFL Shop[/url]
    [url=https://www.yeezys-slides.us.com/]Yeezys[/url]
    [url=https://www.yeezyadidass.us.com/]Adidas Yeezys[/url]
    [url=https://www.350yeezy.us.com/]Yeezy 350 V2[/url]
    [url=https://www.yeezyy.us.com/]Yeezys[/url]
    [url=https://www.yeezy350s.us.com/]Yeezy Boost 350[/url]
    [url=https://www.shoesyeezys.us.com/]Yeezys Shoes[/url]
    [url=https://www.yeezys.uk.com/]Yeezy[/url]
    [url=https://raybansales.us/]Ray Bans[/url]
    [url=https://www.adidasyeezy.uk.com/]Yeezy[/url]
    [url=https://www.yzyshoes.us.com/]Yeezys[/url]
    [url=https://www.yeezy-slides.org/]Adidas Yeezy Slides [/url]
    [url=https://www.yeezy-shoes.us.com/]Yeezy[/url]
    [url=https://www.yeezy-700.us.com/]Yeezy[/url]

  148. buy finasteride no prescription [url=https://fenosteride.online/]finasteride 1mg over the counter[/url] finasteride over the counter

  152. online pharmacy australia [url=https://dynamicpharmacyhealth.online/]online pharmacy delivery[/url] canada pharmacy not requiring prescription

LEAVE A REPLY

Please enter your comment!
Please enter your name here

− 2 = 5