none
SSAS Wrong Until Refresh RRS feed

  • Question

  • We have some simple MDX aggregations in an existing cube that look like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[Final Component Fee Revenue]
    AS 
    (
        [Revenue Type].[Revenue Type Description].&[Final Component Fee Revenue], [Measures].[Revenue Amount] 
    ),
    FORMAT_STRING = "$#,##0.00;($#,##0.00)", 
    NON_EMPTY_BEHAVIOR = {[Revenue Amount]},
    VISIBLE = 1, 
    DISPLAY_FOLDER = 'Component Fees', 
    ASSOCIATED_MEASURE_GROUP = 'Student Revenue Measures'; 

    It shows Revenue Amount, which is a SUM in the cube structure.  AggregateFunction=Sum, DataType=Currency, Format String=$#,##0.00;($#,##0.00)

    Here's the odd thing:

    After the cube refreshes overnight, the person who connects first via Excel will get a nonsensical answer like "35003034.4422" - instead of say "$5,041.93".  The number will be wrong at the grain level, and at the sub-total or grand total level. The format is not the right format, and the number is chaotic.  It will be a brand new Excel connection, created from scratch, not a cached/saved spreadsheet.  If the user then hits refresh on the table, then the figures will update to correct as if they were never wrong.

    We have implemented a Full Cache clear at the end of the nightly job, and at least initially (for 24 hours) the problem went away.

    It only occurs on MDX calculated values, with or without scoping statements.

    We are currently experimenting with AGGREGATE, SUM and even FILTER statements to see if different calculations affect the behaviour.  

    I have found other threads on older versions of SQL server, with the solution being to move the latest Service Packs at the time, if any solution at all. 

    We are on SSAS: 14.01.1439, and SQL Server 14.0.1000.169, and will upgrade to SQL Server 2019 once Visual Studio SSAS and SSIS add-ons are both in production release (I think 1 is, and 1 isn't at current date)

    We do have aggregations in place, but not as many as I'd like.  I do wonder if the real problem is occuring in here rather than the MDX or cube caching.

    Any suggestions welcome.


    • Edited by Peppermallow Sunday, November 10, 2019 11:13 PM Clarifying about older versions
    Sunday, November 10, 2019 10:33 PM

All replies

  • Hi Peppermallow,

    We usually specify the format by using named numeric formats. For example, amount value's format string name is currency.

    CREATE MEMBER CURRENTCUBE.[Measures].[Final Component Fee Revenue]
     AS 
     (
         [Revenue Type].[Revenue Type Description].&[Final Component Fee Revenue], [Measures].[Revenue Amount] 
     ),
     FORMAT_STRING = "Currency", 
     NON_EMPTY_BEHAVIOR = {[Revenue Amount]},
     VISIBLE = 1, 
     DISPLAY_FOLDER = 'Component Fees', 
     ASSOCIATED_MEASURE_GROUP = 'Student Revenue Measures'; 

    Named Numeric Formats

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 3:19 AM
  • Hi, that's great, but the data being random/incorrect is the real problem.


    Monday, November 11, 2019 8:59 PM
  • I think you might need to contact Microsoft Support and raise an official support ticket for this. I've never heard of this sort of behaviour where the initial query was incorrect and the user could correct this by clicking refresh.

    I have seen some of the issues with older releases, but these were due to incorrect cache matches. So the first query was always correct, but sometimes subsequent queries could return the correct details, but incorrect totals as the totals were hitting the cache from an earlier query. But the user could not fix this issue, an admin had to run a clear cache on the server. (and this was an issue with one of the service packs in the 2012/2014 release timeframe)


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 12, 2019 1:56 AM
    Moderator
  • Clearing the cache manually does fix the problem temporarily, and this is 100% of the time.  We will log a microsoft job, and post back if we get an answer
    Tuesday, November 12, 2019 2:28 AM
  • Clearing the cache manually does fix the problem temporarily, and this is 100% of the time.  We will log a microsoft job, and post back if we get an answer

    But if your first query after processing is incorrect it should already be hitting an empty cache. (as processing operations should force the cache to be flushed when they commit).

    This does sound like a bug. The cache is just meant to speed up performance, clear cache should not alter the results. So I think logging a ticket is definitely the best way to get to a resolution quickly.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 12, 2019 2:39 AM
    Moderator
  • We now think it might be related to this:  https://support.microsoft.com/en-us/help/2932559/fix-totals-wrong-after-filter-on-pivot-table-item-and-remove-filter-in

    We are now in the process of changing one of our Test environments to this version of SSAS to see if the problem goes away.  

    Thursday, November 14, 2019 11:31 PM
  • Hi Peppermallow,

    Thanks for your update.

    Have you fixed that issue by the upgrade of SSAS instance?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 19, 2019 5:49 AM