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

Question

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!!

Answer 1

It's okay, I'm waiting .

Thank you for your help in advance!!!

Regards
Dorota

Answer 2

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

Answer 3

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