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...

ran another query and I cant see a problem

LEA_CODE DESC Phase

860StaffordshireCollege of Further...

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...

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...

Hi,

I cant get any more specific on this one.

I decided to do it all through SQL in an SP. That way I know exactly what is going on. every time...

OF COURSE!:doze:

Fantastic, Everything is in the right place. Thank you so much

Debbie

Who hoo it worked. Ive never used With before so Im going to try and work everything out.

Thanks for that 🙂

Debbie

IT WORKED!! 🙂

And I have a much better handle on how it does what it does.

Thanks everyone!

Ahhhh that makes complete sense. Im going to go for the CASE statement.

Thanks again for the help 🙂

Debbie

I tried that but got

Msg 536, Level 16, State 5, Line 1

Invalid length parameter passed to the SUBSTRING function.

If I take the -1 out I get records beck but...

Lowell (9/29/2010)

---

here's an example of what Wayne refered to:

With MySampleData as

(

SELECT 'Support Centres , Miscellaneous SC' As TheName UNION ALL

SELECT 'Support Centres , Care of tracey Whitehall' UNION ALL

SELECT 'Support...

Have you got an example of that at all?

I have found a solution. 😀

Delete cube to redeploy

Log onto [Server]

Open Services and stop SQL Server Analysis Services (MSSQLSERVER)

Take copy of the analysis services project and then delete everything...

Ive tried two more tests. I took the fact table and created a cube with just this fact table in. Again exploring the fact table shows the data. Going into...

I went into the dimensions tab and ensured all the dimensions were referenced. Is there anything else I should be looking at?