question

DerosierCindy-2865 avatar image
0 Votes"
DerosierCindy-2865 asked dgosbell answered

Odd OLAP cube set behavior question

I am currently working with a client that has several OLAP cubes that contain date sets. When some users are accessing the cubes and pulling in the sets the numbers are not correct. So for example a user is pulling in 13 wk set and sales calculation the numbers are not correct. When they change to 26 week set the numbers are correct. I have tried to recreate the issue and cannot reproduce it in Excel, on the server. I have run profiler to examine the MDX and the queries are identical between mine and the user. The permissions are the same we both have full access to the data. Different users have issues with different sets. One user is 8 wk and 13 wk and another user is 52 wk and current year. The set are all created identically with the exception of the number of weeks.

Also if the same user pulls in an additional dimension such as sku the row level data when selected will total to the correct number but the subtotal line or grand total line is still not correct.

Does anyone have any idea what could be causing this odd and random behavior?

sql-server-analysis-services
· 3
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image CarrinWu-MSFT ·

Hi @DerosierCindy-2865, welcome to Microsoft Q&A! Could you please tell me which version of SSAS are you using? And what is the model, tabular or multidimensional model?

0 Votes 0 ·
DerosierCindy-2865 avatar image DerosierCindy-2865 CarrinWu-MSFT ·

The server version is 12.0.2000.8 and its Multidimensional.

0 Votes 0 ·
CarrinWu-MSFT avatar image CarrinWu-MSFT DerosierCindy-2865 ·

Hi @DerosierCindy-2865, thanks for your replying. I think it's some internal optimization estimates the relative costs of possible execution plans. Please confirm whether the measures includes some unnecessary values or columns. For more information, please refer to this thread.

0 Votes 0 ·

1 Answer

dgosbell avatar image
0 Votes"
dgosbell answered

So that version number is SSAS 2014 RTM. There was a rare issue in roughly that timeframe with certain combinations of features where you could get an incorrect cache hit. I only ever saw this once and it was on one of the SSAS 2012 updates, but it was in that 2014 timeframe. In our scenario when two similar queries were run one after the other the subtotals from the first query were incorrectly returned in the second query. If you ran the queries in the other order you got the reverse behaviour. We also found that running an XMLA clearcache command before running the second query temporarily "fixed" this issue. So that is one test you can do. If running a clear cache, then re-running the query fixes the issue then you are hitting this bug and you should update to the latest CU

5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.