SSAS - role playing dimension - limiting the irrelevant
Hi, I've been using several role playing dimensions for my cube. One for "Sales Turnover", "Sales date", "Inventory turnover", etc. There is a fact table that has Valid From and Valid To information and I am trying to have one other dimension "Time Period" to help users play around with one dim rather than 2. Found a solution on
http://www.purplefrogsystems.com/blog/2013/04/mdx-between-start-date-and-end-date/ and implemented it. I am having a difficulty here : The SQL Profiler queries show me that the base query uses "Time Period" dimension but the overlaying querying uses "Inventory turnover" dimension and eventually results in NULL. I realized that its the other role playing dimensions that are causing issues so: 1. I wrote a scope to make the calculated measure null by default 2. Then a scope for "Time Period" dim to calculate based on the valid from and valid to ranges as mentioned in the website above. This doesnt help as well. I also played around with "IgnoreUnrelatedDimension" property but this doesnt change the query. To debug even a little more, I removed the other role playing dimensions one by one and the MDX changed to take the one thats left. So I got rid of all role playing dims except "Time Period", "Valid From", "Valid To" (Which are all used in the calculation). Now the query was fine. I would not be able to chuck the other dims(I'd have to bring them back). Please could anyone suggest me a solution to limit the calculated measure to the role playing dimension thats used in it(or only the ones used in dimension usage) ?
Could you please post screen shots of your dimensions and relationships with the fact table, as well as the dimension usage tab in your cube? Also please post your scoped assignment, query, results and expected results. Adding all of the info would help us help you.