Calculated measure in linked measure group is not refresh after cube writbeck

Dorota Papiernik 1 Reputation point
2020-11-06T11:26:19.81+00:00

Hi All,
I have two SSAS cubes:
1.Cube C1 with measure m1, writeback enabled partition
2.Cube C2 with measure m2

I have created cube C3 with two linked measure groups: C1 (measure m1) and C2 (measure m2).
In cube C3, I have created calculated measure called m1_m2 with MDX definition: [Measures].[m1] + [Measures].[m2]

In Excel users write new data to measure m1 (cube C1) and deploy changes. Writeback is committed.

New data are immediately in cube C3 measure m1. But calculated measure m1_m2 is not changing. Measure m1_m2 still shows value before deploy changes by users. It looks like calculated measure m1_m2 has data from cache, that is not updated after writeback operation.

Calculated measure m1_m2 has correct value, when I clear cache by query:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>DEMO</DatabaseID>
</Object>
</ClearCache>

What should I do, to help my users have correct value m1_m2 after deploy changes.

Please help me.

Many Thanks!!

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,284 questions
0 comments No comments
{count} votes

8 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,821 Reputation points
    2020-11-09T08:36:09.65+00:00

    Hi,

    You could try to workaround this by making the measure physical see if this could work.
    New define your query using method in Measure Tool – Making SSAS measures physical see if it makes difference.

    Regards,
    Lukas


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet ----Hot issues October

    0 comments No comments

  2. Dorota Papiernik 1 Reputation point
    2020-11-10T11:44:45.593+00:00

    Hi ,
    Thank you very much for your answer. I have made measure physical, but it didn't help.

    In my cube C3 I have created measure group C3 with measure m1_m2 and MDX SCOPE:
    38765-p1.jpg
    38816-p2.jpg

    But still when users write new data to measure m1 in cube C1 and deploy changes (writeback is committed):
    38755-p3.jpg

    New value in cube C3 is only in measure m1, but measure m1_m2 is not updated, it has old value:
    38817-p4.jpg

    But when I clear cache by query:
    <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">;
    <Object>
    <DatabaseID>DEMO</DatabaseID>
    </Object>
    </ClearCache>

    measure m1_m2 has correct value:
    38720-p5.jpg

    Do you have idea why SSAS works like this? How to force SSAS to refresh data for measure m1_m2 immediately after writeback on measure m1?

    This is a big inconvenience for users :-(
    Please help us. Thanks in advance!!!

    Regards,
    Dorota

    0 comments No comments

  3. Lukas Yu -MSFT 5,821 Reputation points
    2020-11-11T08:12:09.517+00:00

    Hi,

    Thanks for the explanation and the pics, they are very helpful.

    I currently have not found a good way to automatically refresh the cache, also it could impact the whole performance of the cube. I suggest you to try setting the storage mode of the M1_M2 measure group, set it to ROLAP, see if this works.
    38867-image.png

    Regards,
    Lukas

    0 comments No comments

  4. Dorota Papiernik 1 Reputation point
    2020-11-11T13:33:06.433+00:00

    Hi,

    Thank you very much for your answer. I have changed StorageMode to ROLAP for measure group C3 (measure m1_m3):
    39018-p6.jpg

    But It didn't help :-( Do you have any other suggestions what to do?

    Thanks in advance!!!

    Regards,
    Dorota

    0 comments No comments

  5. Dorota Papiernik 1 Reputation point
    2020-11-13T09:31:57.557+00:00

    Hi,

    Thank you very much!!!

    My Analysis Server version is 13.0.5850.14. (SSAS 2016). The production server version is 14.0.1.439 (SSAS 2017).
    The problem occurs on both servers.

    Thank you for your help in advance!!!

    Regards,
    Dorothy


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.