Unanswered: Filtering Problems in Analysis Services
Ok, I am getting pretty frustrated. Hope I can get a little help here. I am new to Analysis Services (just spent all of last week in class) so please excuse me if these are stupid questions.
I have a fact table on SALES_INVOICE and dimensions on Branch, Cust_No, Time, etc.
My problem revolves around filtering out data based on dimension values but I would prefer not to use the Source Table Filter because I would like to see the values, just not have them in the rollups. For instance, some of the Customers and branches are not considered "normal" business so I want to keep them out of the normal roll-ups but I'd still like them in the cube.
I put in Unary Operators on the Customer Dimension with tilde's (~) on the invalid customers (below "00000101" and above "799999999") and that worked fine. However, when I did the same on the Branch dimension, there was a problem.
The Cube processed fine but it hangs when I try to view the data. I tried this several times with different dimensions and get the same result. Apparently it does not like two tilde operators in two different dimensions.
After much hair pulling, I figured out that pass one was not going to work. I tried a different tact. I created a cube based on the above dimensions with ALL of the data in all of the tables. I then tried creating a virtual dimension on the cube and putting dimension level security on it for the EVERYONE role. I can not seem to get the MDX Filter statement to work.
I need the filter statement to be some thing similar to this but this, of course, does not work.