error -duplicate attribute key found creating date hierachy

Question

error -duplicate attribute key found creating date hierachy

robinrai3

SSCrazy

Points: 2810
More actions
October 6, 2010 at 3:40 am

#157238

hi All,
Been playing with creating a date hierarchy on the adventureworks db with microsoft SSAS
I created a very simple dsv on the fact internet sales table only and using the wizard
created a cube and this pulled in the usual dimensions including the time dimension, which has no
hierarchies created, and then deployed it to SSAS
I then deleted the time dimension snd then re-created it, just choosing only the calendar year,
english month name attributes...and also created a very simple year->month
hierarchy with types : years and month. However when i go to re-process it it returns an error :
Errors in OLAP storage engine : a duplicate attribute key has been found when porcessing tables 'dbo_time',
column: 'EnglishMonthName', value 'September. The attribute is 'English Month Name'
Any ideas guys ? what the problem is and how to solve it...i thought on the adventureworks db everything should be normalised

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

You will still need to have a unique key for your dimension, if you have only selected to use the year and month attributes then the cube will have not have anyway of knowing which row is unique as both year and month will repeat.

So you will need to bring trhough the key for the time dimension (timekey?), you can make this not visible if you don't want it selectable, and set this as the key for the dimension.

robinrai3 SSCrazy Points: 2810 More actions · Answer 2

hi steveb,

Thanks for yr input, it was my first foray into playing with adding/deleting dimensions and creating hierachies,i managed to trawl through the web and find out what you have just told me in that all dimension keys in a hierachy have to be made unique...so what i did was to make a composite key on [englishmonthname],[calendar year] which did the trick in that it processed. So going to the

extreme in a 5 level hierachy i imagine we would need a composite key on all 5 keys at the 5th level

However i observed that it displayed in the browser since the order was set on key order in the dim properties i.e.

April 2002

April 2003

April 2004

Aug 2002

Aug 2003

Aug 2004

I got over this by by making the composite key in the order of : [calendar year], [month of year] (instead of englishmonthname) and it then displayed correctly. I've got some issues with the YTD on using this date dimension i've created but shall post it as a new isse..again many thx

RonKyle SSC-Dedicated Points: 31482 More actions · Answer 3

It's more work up front, but I think you would be better served creating a key column in the date dimension table. It would essentially be the number of the month. In my date dimension, for example, Jan 06 is also month 1, Feb 06 is 2, and so on. It avoids the composite key issue, and I can use the key to order the months properly.

I also have a seperate set of two columns for January. The key for these months is always between 1 and 12, and allows for a different kind of analysis than is possible with the uniquely named months.

robinrai3 SSCrazy Points: 2810 More actions · Answer 4

hi Ron,

Thanks for the tip, but what is the best practice here ?

Should you add it as a calculated column to yr data source view or to the underlying data table in the relational table as an extra column,

and also what may be stupid questions

when i do a select mdx on the actual englishmonthname in the date dimension

i.e

SELECT [Measures].[Sales Amount] ON 0,

[Order Date].[English Month Name].[English Month Name].&[2003]&[3] ON 1

FROM Sales

I get a sales value for Mar 2003 ?

but when i the do the same select through the hiearchy i created called "calendarhiearchy"

SELECT [Measures].[Sales Amount] ON 0,

[Order Date].[CalendarHierarchy].[English Month Name].&[2003].&[3] ON 1

FROM Sales

i don't get anything.?

is this because "calendarhiearchy" is a purely a logical view of the relationship between the levels

and nothing else ?

another question...

can

[Order Date].[English Month Name].[English Month Name].&[2003]&[3]

be expressed as

as [Order Date].[Mar 2003] i.e what do you need to do to make this alias work if poss

RonKyle SSC-Dedicated Points: 31482 More actions · Answer 5

Thanks for the tip, but what is the best practice here ?
Should you add it as a calculated column to yr data source view or to the underlying data table in the relational table as an extra column,

I would add it as an extra column in the relation table. As a general rule in data warehousing, you want to minimize calculations.

As for the MDX, you should start a new post and let some one more qualified answer that. I know some MDX, but my knowledge is very specific for my situation. I can tell you that the & refers to the absolute key, or some such, which likely explains why you are getting the third month.

robinrai3 SSCrazy Points: 2810 More actions · Answer 6

thx Ron,

I sorted out those questions ...the month thru the calendarhiearchy is working...must have been a blip