DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!