October 11, 2011 at 10:03 am
the hierachies will still work without attribute relationships but the perfromance would not be optimal.
I would suggest setting them up, but do them one at a time starting from the bottom so this way you can isolate the error
October 11, 2011 at 10:11 am
so far Ive recreated and it processes fine.
Ive then set phase desc after LEA code and I immediately get the error.
Errors in the OLAP storage engine:
A duplicate attribute key has been found when processing:
Table: dim_School, LEA_CODE Value 860
The attribute is 'LEA CODE'
and this is where I cant find any issues again. Ive created the key column of phase desc and phase dcesc and LEA code.
Ive spent the whole day on one dimension. Thats worrying :blush:
October 11, 2011 at 12:36 pm
Is the LEA_CODE unique across the whole table..
as in does
SELECT LEA_CODE , COUNT(*)
FROM Dim_School
group by LEA_CODE
having COUNT(*) > 1
Return any rows? if it does then you need to change the Key Value of LEA_CODE to be unique, something like using the columns LEA and Phase might work.
This is a good outline of what the problem is (ignore the title though)
http://sqlserverpedia.com/blog/sql-server-bloggers/idiots-guide-to-ssas-attribute-relationships/%5B/url%5D
Most of the SSAS cubes i come across the relationships have not been setup correctly or at all as the developer has given up on it.
October 12, 2011 at 2:20 am
Hiya,
It does return rows but then the LEA_Code contains multiple schools. Each snould be unique. So if IDENTITY change to:
SELECT LEA_CODE , SCH_DFEE,COUNT(*)
FROM Dim.School
WHERE [Most Recent Transaction Indicator]=1
group by LEA_CODE,SCH_DFEE
having COUNT(*) > 1
I dont get any rows which is now as it should be.
So I have Phase desc going to LEA_CODE which drops down to School DFEE. All seems present and correct Im sure
Made sure the keys in each attribute go down all the way to School_ID (The unique identifier)
and go again and this time for some bizarre reason it works.
However now I have another issue. When I go and have a look at my hierarchy the phase descriptions duplicate.
For example we have 4 schools for Scotland so there are 4 Scotlands in the first drop down list.
Scotland
Primary Phase
Example School 1
Scotland
Secondary Phase
Example School 2
Scotland
Secondary Phase
Example School 3
Scotland
Secondary Phase
Example School 4
It should be (Like it was originally with just the hierarchy set)
Scotland
Primary Phase
Example School 1
Secondary Phase
Example School 2
Example School 3
Example School 4
Ive googled this but cant find anything about it. Again any help would be really appreciated.
Debbie
October 12, 2011 at 3:06 am
What did you end up setting the key for LEA_CODE ?
The duplicates that you are experiencing are normal as the key is now different for each of the values so now they are seprate rows, I assume that when you browse to LEA_CODE through the hierachy that there are no duplicates when you drill down?
You may notice that there is a blue squiqqly line underneath the dimension name, this may be giving the warning that you should avoid having visible hierachies where the attribute is a level in the hierachy. This is because of the duplicates as it makes sense to view the attrbute in the context of the hierachy, but not directly.
You can always setup another attribute that is not part of the hierachy that will not contain duplicates
I hope this makes sense, I find it helped a lot to look at the adventure works cube that is available on copeplex.
October 12, 2011 at 3:15 am
LEA_CODE is LEA_CODE and School_ID,
PHASE_DESC is PHASE_DESC, LEA_CODE and School_ID
SCH_DFEE is SCH_DFEE, PHASE_DESC, LEA_CODE and School_ID
So what Im thinking is: Do I get rid of the other data items in every Code column?
Or should I Just have:
LEA_CODE is LEA_CODE,
PHASE_DESC is PHASE_DESC, LEA_CODE
SCH_DFEE is SCH_DFEE, PHASE_DESC
e.g. the attribute before it?
I understand why its showing wrong in the browser though and I have had the message on the arrows.
Design hierarchies for each incoming relationship path where attributes have multiple incoming relationships or, if any of the incoming relationships are unnecessary, you should delete them.
So I know Im still doing something wrong!
October 12, 2011 at 3:51 am
you dont need to get rid of the data items, just make them not visible..
does your hierachy go from
SchooId -->LEA_CODE --> Phase_desc --> SCH_DFEE
I dont think you need all the attributes as your key, just what makes it unique which is usually the attribute key + the key of the level above it. so for
city --> state --> country
city should have city and state, but shouldn't need country to make it unique
if possible post a sample of your Dimension source data and i can have a look, please mask any sensitive data.
Also I would recomend renaming your attributes to use a business name rather than use the source column names,.
October 12, 2011 at 4:21 am
When you say “you dont need to get rid of the data items, just make them not visible..”
Where would you hide the data items?
I did a small sample table from my dimesnion
SELECT '893'AS LEA_CODE, 'Primary Phase'AS [PHASE DESC], '3003'AS SCH_DFEE into dim_school UNION
SELECT'830', 'Primary Phase', '4003'UNION
SELECT '893','Primary Phase', '3070'UNION
SELECT '830','Secondary Phase', '4191'UNION
SELECT '830','Secondary Phase', '4144'UNION
SELECT '891', 'Primary Phase', '3690'UNION
SELECT '891','Secondary Phase', '4106'UNION
SELECT '935','Middle (Deemed Secondary)', '4502'UNION
SELECT '936','Primary Phase', '2050'UNION
SELECT '677','Secondary Phase', '4061'UNION
SELECT'830', 'Early Years Setting', '8951'
“does your hierachy go from
SchooI_Id -->LEA_CODE --> Phase_desc --> SCH_DFEE”
Yes it does. I have just dropped all the key columns and reprocessed which of course failed. I have readded just the items to make it unique but I get this error
Errors in the OLAP storage engine:
A duplicate attribute key has been found when processing:
Table: 'dim_School', Column: 'PHASE_x0020_DESC',
Value: 'Primary Phase'; Table: 'dim_School',
Column: 'LEA_CODE', Value: '878'. The attribute is 'PHASE DESC'.
I run a query on this and there are 315 schools in the primary phase for LEA 878.
This seems fine as PHASE_DESC has the key of LEA_CODE
SCH_DFEE also has the key column on LEA_CODE as it is this which makes it unique. Im stuck in a rut again not being able to see the issue.
October 13, 2011 at 6:48 am
Right, The only way that the school table will process is with these keys set for each level:
LEA_CODE = LEA_CODE, School_ID (2 keys)
PHASE_DESC = PHASE_DESC, LEA_CODE, School_ID (3 keys)
SCH_DFEE = SCH_DFEE, PHASE_DESC, LEA_CODE, School_ID (4 keys)
I have tried just creating the key as the attribute and the one below it in the hierarchy but it doesn’t work. I have to set each one to go down to the lowest level.
I then Change the names of each attribute to friendly descriptions.
I then set the hierarchy as:
Local Authority Code (With Local Authority Description as the Name)
Phase Description
School DFES number (With School name as the name)
Process and browse the hierarchy and it shows as incorrectly
I have for example
Scotland
>Secondary phase
> Academy 1
Scotland
> Secondary phase
>Academy 2
So everything is duplicating. I have been working on this 2 days now and Im nowhere near the answer. I would just reset everything and create the hierarchy and it seems to work but I wouldn’t be able to answer questions regarding setting the attribute relationships.
A NEW TEST:
I have now created a unique AS Key so the ID will be this for all of the data items.
CAST(LEA_CODE as varchar(100))+' '+CAST([PHASE DESC] as Varchar(150))+' '+CAST(SCH_DFEE as Varchar(150)) AS Unique_AS_Key
Processed and it was fine. Checked the browser again and nothing had changed.
NEXT TEST:
Change each key to the attribute and JUST the School_ID.
This also didn’t work. The hierarchy now shows for example
1134
>Primary Phase
School 1
1135
Primary phase
>School 2
HEEEEEEEELP, Ive attempted to read about this issue but I just can find anything on the internet.
🙁
October 13, 2011 at 7:15 am
I know these things can be frustrating, though it does seem like you are close to having it working.
I don't have any time at the moment to look at this but will have a look tonight for you and try and sort something out..
October 13, 2011 at 7:19 am
I cant thank you enough,
Im doing lots of other testing including installing BIDS helper to see what happens. Although every time I run it BIDS shuts itsself down so not very helpful.
Im reading everything I can about it at the moment, thank goodness I didnt start off with child attendance :crying:
October 13, 2011 at 9:23 am
I found one think I did wrong within attribute relationships
I had
School_ID, Local Authority, Phase, School
Where it should be
School_ID, School, Phase, Local Authority
because Local Authority is the top level of the hierarchy.
Ive updated my OPERA blog
Its still now working but at least I have figured that out
October 13, 2011 at 9:58 am
YEEEEEEEEEES Finally I have sorted it with the keys as follows
(And it has to be in this order to work)
SCHOOL DFEE
LEA CODE
SCH DFEE
LEA_CODE
LEA CODE
PHASE DESC
LEA CODE
LEA CODE
And the relationships are in this order
School_ID, School, Phase, Local Authority Code
Thankyou so much for going through this with me. I can go home happy tonight 😀
October 14, 2011 at 2:43 am
Fantastic, glad that it is all sorted..
Don't let this put you off building cubes as they are really useful when it comes to reporting.
October 14, 2011 at 2:48 am
Thank you again forall you help.
Im going to tackle the date dimension next. I feel a fun day coming on 🙂
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply