?
Solved

Dimension Duplicate Key Error

Posted on 2011-09-20
14
Medium Priority
?
676 Views
Last Modified: 2013-11-16
when i try to process the date dimension, i get these errors when processing.

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_dim_Date', Column: 'YearName', Value: '2006'; Table: 'dbo_dim_Date', Column: 'QuarterName', Value: 'Q1 2006'. The attribute is 'Quarter Name'.

Almost every dimension that i have , is getting similar errors when processing. Any ideas ?
0
Comment
Question by:Auerelio Vasquez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 36573395
What database are you using?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 36574000
What indexes do you have on the table in question?  You need to look at them closely to make sure that you have not defined a Unique Index that doesn't allow the particular insert if that insert is actually needed.
0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 36574077
I'm using SQL Server2008R2.

As far as indexes, one is a date dimension, so it's not indexed, and the others just uses a primary key, all of which are used soley for identity.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 15

Expert Comment

by:Tim Humphries
ID: 36574311
Hi,

I suspect you have multiple members in your date dimension that have the same quarter name (makes sense) but that you have also marked the quarter name atttribute as needing to be unique.

Check the MemberNamesUnique property for the QuarterName atribute and try setting this to False.

Presumiably you could have problems with yearName, MonthName etc.

Depending on how you've structured your other dimensions / attribute hierarchies you may have problems elsewhere too.

Tim
0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 36574525
I thought that may be the case as well, after diong further reading, but the hierarchy that i'm using, the columns are all MemberNamesUnique = False....
0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 36574609
let me see if i can explain his better:

so, for attributes of yearName, Quartername, MonthName i want to use as hiearchy. I set the visible property to false. Also i set up the keys like this:

YearName -
Properties - KeyColumns (collection) wk_Date, YearName: NameColumn = YearName

QuarterName-
Properties - KeyColumns(collection) yearname, quartherName NameColumn = QuarterName

MonthName
Properties - KeyColumns(collection) QuarterName, MonthName NameColumn = MonthName

I attached a screenshot of the attribute Relatinship:.... I still get a squiggly line in the hierarch saying no relationships have not been defined.... Also the reltinshiptypes are all flexible.

Thanks for your help.


dimTime.PNG
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 36574896
In your image there is a golden key next to QuarterNumber.  That induicates that it is a Primary Key . . . which means that it has to be unique.  In other words, you can have only one entry with any given querter number.  

Add an Identity column as your Primary Key and then make a Non-Unique Index on your QuarterNumber column.  alternatively, you could make a Unique Index on YearNumber, QuarterNumber, MonthNumber, DayNumber.
0
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 2000 total points
ID: 36574973
Ok, from your screenshot I think you may have your attributes defined incorrectly. You have Quarter Name -> Month Name. I've checked a date hierarchy I have set up and the relationship is in the other direction : Month Name -> Quarter Name.
This could cause the non-unique error as the engine won't be able to resolve what's a parent and what's a child correctly.

Try changing these around so the relationships always read from child to parent.

Tim
0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 36574976
i thought the golden key is on wk_Date ? i don't see the key your are referring to in the image ??
0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 36574986
i got that to process by not having the key column collection in the relatinoship attributes. I'm confused by this, becuase it seems to be not using the best practices....
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 36575020
In the Attribute Relationships panel (centre bottom) the first relationship listed is Quarter Name -> Month Name. This is the wrong way around. You could try removing it and relying on relationships from your wk_Date attribute, but i think you may need to add Month Name -> Quarter Name to get it to process.

Tim
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 36575040
8080 Diver: the key is next to wk_date, I think (although it's not fully visible). This looks correct to me.
There's no need to add an identity column if the date is unique.

Tim
0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 36575317
Ok so what i don't understand, (i fixed the relationship). I have the memberNamesUnique property set to false, so why then do i have to associate the primary key, with the column and create the name column? becuase ifi i don't do this, i get anerror when i process the dimension. haven't even started to develop the cube yet
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question