Excluding facts having unrelated dimensions
In my Analysis Service OLAP Cube I would like to exclude facts having unrelated dimensions, which during processing produce errors. I would also like to avoid making Named Queries in Data Source View (with specific WHERE conditions).
Is there any way to do this at the level of a cube? What is the easiest way to accomplish this?
(I am using Visual Studio 2008 and SQL Server 2008.)
If the "UnknownMember" propery of the dimension is set to "None" then the non-joined facts should be excluded automatically.
Ideally you should follow a methodology to deal with data like this, I go for the Kimball method, where you have an artificial row in your dimension table named 'Unknown' or 'Bad Data' or some such. Then, during the data warehouse load ETL, any data that doesn't match, convert that key to a key to the artificial row.
Do you have a data warehouse, or are you processing direct from the source system? What sort of data does not match up like this? Any more detail you could give is helpful.