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.

0 comments

8 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Dorota Papiernik 1 Reputation point
    2020-11-20T17:12:51.15+00:00

    It's okay, I'm waiting .

    Thank you for your help in advance!!!

    Regards
    Dorota

    Was this answer helpful?

    0 comments
  2. Lukas Yu -MSFT 5,826 Reputation points
    2020-11-25T09:51:28.83+00:00

    Hi Dorota,

    Sorry for late responding.

    I've worked on this and replicated this issue also in my SSAS 2019 environment.

    My thought is that in this particular scene, the calculated measure (the sum of two linked measure group) value is processed during the cube processing and stored in the cache (cache of Cube3). When doing writeback in Cube 1 , the linked measure would get its value from linking measure, but this operation could not affect the cached result. So that the m1_m2 measure was not changed.

    This is by design , and since linked measure group and writeback are both depreciated feature, I think this would not be reinforced in the future.

    Best workaround, my suggestion is, that avoid using linked measure in the cube 3, just physically add direct feature. Since in complicated scenario, linked measure would have a lot of shortages. See: Usage Limitations

    If you insist on this structure, the workaround would be like following :
    Script a scheduled task in the SQL Server agent, you could set a job to run the cache clear command or cube processing(for Cube3) task as scheduled.

    Notice ： Clearing the cache to often would affect the cube performance.

    Hope this helps a bit.
    Regards,
    Lukas

    Was this answer helpful?

    0 comments
  3. Dorota Papiernik 1 Reputation point
    2020-11-25T13:01:52.73+00:00

    Hi Lukas,

    This is not good news :-( I understand that there is no way to correct this SSAS bug.

    Thank you very much for your work.

    Regards,
    Dorota

    Was this answer helpful?

Your answer

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