October 11, 2011 at 7:45 am
Hi
Im getting
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dim_School', Column: 'LEA_CODE', Value: '855'. The attribute is 'LEA CODE'.
For some reason my PC keeps crashing when ever I add all the details to this call so I have added everything to my blog instead (Which also includes the screen shots)
Any help would be appreciated
Thanks
Debbie
Thanks
Debbie
October 11, 2011 at 7:49 am
Check that you do not have a duplicate in your source table for
LEA_CODE = 885
and if you dont, then check that the attribute relationships for the attribute LEA_CODE are setup correctly
October 11, 2011 at 7:54 am
Im not quite sure how I check for duplicates.
If I look at phase and LEAs how its set up I get so 885 can be for any Phase description.
College of Further Education885
Middle (Deemed Primary)885
Middle (Deemed Secondary)885
Primary Phase885
Secondary Phase885
Special 885
Hmmm I wonder if this means that I havent thought about it correctly and It should be LEA CODE and THEN phase description?
October 11, 2011 at 7:57 am
Hmmm I have swapped them round and ensured the keys reflected the change and now Im getting a new error
Errors in the OLAP storage engine:
A duplicate attribute key has been found when processing:
Table dim_School, Column PHASE DESC, Value Primary Phase
Table dim_School, Column 'LEA_DESC', Value Staffordshire'
The attribute is 'PHASE DESC'
October 11, 2011 at 7:59 am
to check for duplicates query your source table where LEA_CODE = 885.
By definition an attribute Key has to be unique otherwise SSAS will not know what value to use when this attribute is selected.
it seems has though you have a key of LEA_CODE and Description to make the row unique, though you could potential use this as your key it would not be advised as it would perform badly.
A better approach would be to create a Surrogate_Key (such as incremental int) on your dim table that makes each rown unique.
October 11, 2011 at 8:07 am
ran another query and I cant see a problem
LEA_CODE DESC Phase
860StaffordshireCollege of Further Education
860StaffordshireEarly Years Setting
860StaffordshireMiddle (Deemed Primary)
860StaffordshireMiddle (Deemed Secondary)
860StaffordshireMultiple Phases (not middle special or PRU)
860StaffordshireNo Establishment
860StaffordshireNursery
860StaffordshirePrimary Phase
860StaffordshirePupil Referral Unit
860StaffordshireSecondary Phase
860StaffordshireSpecial
When you say every attribute key has to be Unique.... If you have the above I dont understand how you could only have 1 staffordshire in the dimension as this groups everythig under a specific phase. At the lowest level my School_IDs are unique. I feel like Im being very stupid here :blink:
October 11, 2011 at 8:13 am
if you have a unqiue code for each row then you should be fine,
I assume that you have a User Defined Hierachy setup?
If so, could you check how the attibute relations are defined as this is what could be causing your problem
October 11, 2011 at 8:18 am
This was the issue. I created dim_School with no NULLS.
I then created the hierarchy and it worked.
Its when I try and sort out the attribute relationships that it all goes wrong.
I was hoping the blog I created with screen shots would make some sense.
http://my.opera.com/DebzE/blog/another-attempt-at-delivering-the-last-12-months-of-data-in-a-report
BTW I was using the following as advice when I was putting it together
October 11, 2011 at 8:31 am
This is quite common, the best advice i can give would be to remove all your attribute relations for the hierachy , it will complain that it is inefficent but it will still work, and then add them one by one and process the dimension each time.
each level on the hierachy needs to have a key that is unique otherwise SSAS will not know which was to follow the data in the hierachry.
to do this have a look at each level on the hierachy and then right click on the column in the Dimension Structure tab and have a look at the key column properties to see if the values are unqiue, you may need to add more than one value in there to make it unique.
A good example is a City --> State --> Country hierachy , where the City name may not be unique as you can have the same city name in different states, so for the City attribute the Key value would be , City and State.
October 11, 2011 at 8:42 am
Its certainly a lot more difficult to set up that I first thought.
I need to have a good old think about the hierarchy
Primary Phase has LEA code of 893 and secondary phase has an LEA of 893
OR LEA code 893 splits into Primary phase, secondary phase and special?
Is it possible to creaste the hierarchies and do nothing to the attribute relationships. As in leave every single data itm with the warehouse Key as its key?
October 11, 2011 at 9:04 am
It just got worse. I deleted the dimesnion and recreated and then processed and now its failing based
on where SCH_DFEE= '3805'
the school DFES is used within 3 LEAs 888, 335 and 895. But its key is the warehouse ID so its Unique based on this.
key phase LEA DFEE
60407Primary Phase8883805
64272Primary Phase3353805
69273Primary Phase8953805
I really dont understand why this is erroring now . This is the error
Errors in the OLAP storage engine:
A duplicate attribute key has been found when processing
Table dim_School Column SCH_DFEE Value 3805 The attribute is SCH DFEE
:crazy:
October 11, 2011 at 9:07 am
Debbie Edwards (10/11/2011)
Its certainly a lot more difficult to set up that I first thought.I need to have a good old think about the hierarchy
Primary Phase has LEA code of 893 and secondary phase has an LEA of 893
OR LEA code 893 splits into Primary phase, secondary phase and special?
Is it possible to creaste the hierarchies and do nothing to the attribute relationships. As in leave every single data itm with the warehouse Key as its key?
yes create the hierachy without any attribute relations is possible, it will affect performance but it is a good way of isoalting where the issue is.
October 11, 2011 at 9:08 am
Debbie Edwards (10/11/2011)
It just got worse. I deleted the dimesnion and recreated and then processed and now its failing basedon where SCH_DFEE= '3805'
the school DFES is used within 3 LEAs 888, 335 and 895. But its key is the warehouse ID so its Unique based on this.
key phase LEA DFEE
60407Primary Phase8883805
64272Primary Phase3353805
69273Primary Phase8953805
I really dont understand why this is erroring now . This is the error
Errors in the OLAP storage engine:
A duplicate attribute key has been found when processing
Table dim_School Column SCH_DFEE Value 3805 The attribute is SCH DFEE
:crazy:
what do your attrbiute relationships look like?
October 11, 2011 at 9:11 am
I may have figured out this one. It looks like a source error has caused there to be 2 most recent rows instead of one because the name has changed.
59016Secondary Phase3714029Test School and Performing Arts College
68437Secondary Phase3714029Test High School
At least I can figure out how this has happened in the source dimension..... Ill be back
October 11, 2011 at 9:57 am
Ive added some script to the dim SP to resolve the source error so at least thats now sorted.
Ive added my hierarchy and it works.
Does this mean I can just leave attribute relationships alone?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply