January 29, 2013 at 8:38 am
I have a question about some weird behavior we’re seeing in SSAS 2008 (SP3), and I was wondering anyone out there would be able to help me?
Short Story:
SSAS seems to be doing some level of caching on its own. However, this caching is resulting in inaccurate results to be returned in some queries for our business users. That is leading them to question the data, and lose confidence in the queries that they are running. I’m wondering if there are settings that we can change with SSAS to keep it from caching results like this so the business will see accurate data when running their queries.
Long Story:
We have a measure group set up with about 47 million rows in it that we are trying to query through Report Builder. We pulled in one amount from that measure group. We then pulled in an attribute from a coverage dimension. Everything is good here, and the results are based on the 47-million rows in our measure group.
Next we pull in an attribute from a coverage-specific table (fire in this case). That fire table only contains 5 million rows. That table also is not a direct foreign key to the measure/fact table. This is a referenced dimension through the coverage table that I mentioned earlier. Anyway, pulling in that attribute from the fire table limits the number of rows coming back to only the 5-million rows in our measure that have an associated row in the fire table. Again, everything is working as expected.
Then we add another attribute from another dimension. This pulls in just fine.
Then we realize that we really didn’t want that fire attribute added. So we removed the fire attribute from our query. However, our results didn’t expand back to look at all 47-million rows. Instead, it is still limiting the result set to only the 5-million rows that have a relationship with the fire table, even through fire is no longer included in our query.
I tried this multiple times with the same results. I then opened a new report builder session and tried to create the report from scratch. I did not pull in anything from fire this time, but the results were still limited to just the 5-million rows from fire.
I copied the MDX from the query designer window and ran that in Management Studio. Same results (limited to only fire rows). I verified that there is no reference to the fire dimension anywhere in the MDX. Then I took that MDX and ran it on a different computer, and the results were still limited to just the 5-million rows from fire. And finally I asked a co-worker to do the query, and still the same results.
I did a little internet searching, and I found a way to clear the SSAS cache. I ran that on the SSAS database, and then we got the correct results.
We are not using proactive caching on any of our dimensions or measures.
So, I’m wondering if there is a setting (or settings) I can change with SSAS so it will not cache results like it appears to be doing? And if not, do you have a recommendation on how often we should be clearing out the cache to try to prevent the business users from seeing situations like I described again? It is crucial that the business have confidence in the data and the results of their queries run against the cube!!!!
Thanks a lot for any help anyone may be able to provide!!!
March 1, 2013 at 5:59 am
We opened a support issue with Microsoft on this problem, and they have determined it is a bug in SSAS. We don't know yet which versions of SSAS will be fixed, nor do we know when.
To work around the issue, we are changing these non-materialized reference dimensions to be materialized. This forces us to include unknown member handling for the rows that currently do not tie to the non-materialized referenced dimension. This is the workaround that we came up with while waiting for Microsoft to look into our issue -- and it is also the workaround Microsoft asked us to use this week.
If/when I ever hear that Microsoft is releasing a fix for this issue, I will try to remember to post that out here as well. 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply