hi
in analysis services when i process a cube i got an error that
data from the fact table isnt exists in the dimenstion table
for example : fact table sales has column suplier that one of its row
has data(suplier id = 170) that doesnt match with the dimension table ( in the dimension table there isnt such supier id)
how can i handle this error ?
can i insert instead someyhing else so that the proccess can continue and not failed ?
Thanks
Eyal
Ideally, you've addressed this problem in your data warehouse/data mart. I recommend to my clients they never allow NULLs in their foreign keys. This requires you to place a NULL member in each dimension -- we usually assign that member the surrogate key value of -1 -- and eliminates this issue.
It sounds like in your example, you have a valid key in your partition, so I would suggest you make sure you dimension is processed before you process your cube/partitions.
If that is not an option, you use use the UnknownMember and ErrorConfiguration properties in SSAS to handle this issue. In the dimension that is causing problems (the supplier dimension in your example?) set the UnknownMember property to either visible or hidden. It defaults to None which makes the UnknownMember inaccessible.
Then, on the measure group that points to this dimension table, change the error configuration to replace missing key values with unknown (using the KeyErrorAction and KeyNotFound properties). Be certain to also set the KeyErrorLimitAction property to Stop Logging when the max error is hit.
One important gotcha on this. If your dimension member lands in your dimensions, the facts assigned to the unknown member do not revert to your new dimension value. To line your facts up correctly with the values in your dimension, you will need to do a full reprocess of the partition.
B.
