November 11, 2011 at 3:53 am
Hello,
I have a problem with data integrity.
Basically a column on my fact table has some values that are not present in the dimension table.
Reading this article http://msdn.microsoft.com/en-us/library/ms345138(v=sql.90).aspx
the solution is to modify the ErrorConfiguration on the measure group or partition (see section "Referential Integrity Issues in Fact Table").
I have done the steps described there but the deployment keeps failing.
I dont understand why.
Any idea how to handle this kind of issue?
Thank you very much.
November 11, 2011 at 7:10 am
There are 2 basic approaches you can take with inferred members in your fact table.
1. Use a generic "Unknown" reference to your dimension. If your dimension has surrogate keys starting at 1 and incrementing up from there an Unknown member could have a value of 0 or -1. Something that would clearly indicate that it is out of the normal set of values. Personally I think every dimension should have an unknown member like this just as a matter of course.
2. During your fact table load test all the values going in (you should do this anyway), if there is a dimension member missing, branch that record out, create a placeholder record in your dimension with a surrogate key that does not yet exist and mark it somehow for a later update, then let your fact record proceed to the insert to the fact table. Then during a separate ETL go back to the placeholder record in your dimension and update it with the appropriate data.
The first option is the easier of the 2 to implement however the second option will ultimately give you the best data but is substantially more complicated to implement.
November 11, 2011 at 7:44 am
Hello Daniel,
thank you for the reply.
I have followed the first approach but my problem was that it was not working. The article here is somewhat confusing.
To solve this issue they write "The solution is to modify the ErrorConfiguration on the measure group or partition" instead they have to write "The solution is to modify the ErrorConfiguration on the cube or partition of the cube" because the measure group does not even have that property.
Now it's working.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply