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

Question

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

Dorota Papiernik 1

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:

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

Please help me.

Many Thanks!!

0 comments

8 answers

Your answer

Answer 1

Dorota Papiernik 1

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

Lukas Yu -MSFT 5,826 Reputation points

2020-11-20T08:58:42.953+00:00

I am terribly sorry I've forgot this .... I will come back asap

Answer 2

Dorota Papiernik 1

Hi,

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

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

Thanks in advance!!!

Regards,
Dorota

0 comments

Answer 3

Lukas Yu -MSFT 5,826

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.

Regards,
Lukas

0 comments

Answer 4

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:

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

New value in cube C3 is only in measure m1, but measure m1_m2 is not updated, it has old value:

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:

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

Answer 5

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