Count members based on aggregated measure SSAS MDX

Question

Count members based on aggregated measure SSAS MDX

kiran l 21

I want to count # of claims(Claim dimension) based on a measure(Case Loss). I have a transactional Fact table with the measure(Case Loss), but the value of the measure to be used in the condition has to be at the claim level and not the transaction level. I tried to add the calculation in the DSV but it takes a longer time to process the cube as the query gets complex. I am trying to resolve this the MDX way.

I already have MDX which resolves this but it takes a longer time to show results. The count - filter mDX is slowerthan sum MDX
CREATE MEMBER CURRENTCUBE.[Measures].[ClaimCount_Incurred]
AS Sum(
[Claim].[Claim Number].[Claim Number],
IIF( (abs(round([Measures].[ITD CAse Loss Amount] ,3))>=1)
, 1
, NULL
)
);

I am trying to find a way better way to get the results in an efficient manner. Is there a way to solve this using a dummy dimension?

Update: We also have a distinct claim count in the cube, performance is really good with any query. Could I use the distinct count to get the conditional count?

Thanks

Answer 1

Alexei Stoyanovsky 3,411

You could try declaring the abs(round(.. part as one or two hidden helper measures, which might help the engine, and double-check that the bulk mode is actually used, but this MDX is quite optimized.
One alternative approach is to create a measure group off the Claims table, which will add some M2M relationships to your cube but could be faster.
Note also that query response time is usually more important than processing time.

kiran l 21 Reputation points

2021-02-11T15:20:13.477+00:00

I looked into the Query details and it is being evaluated using in bulk mode. I am just wondering how the m2m relation solves the conditional count? In this case, I already have a distinct count in the cube which has very good performance in the same query. Could I somehow use the distinct count to get the conditional count? Thanks
Alexei Stoyanovsky 3,411 Reputation points

2021-02-12T07:42:51.797+00:00

It's not the M2M per se, it's the count measure in the dimension-based measure group.
There's no getting around the fact that the 3 mio claim numbers need to be separated into those that satisfy your condition and those that don't. But once the separation is done, there are different ways of doing the counting of the satisfying part. Besides the purely MDX count(filter) and sum(iif), which you've covered yourself, there's also the option of having a physical measure. It could be a count off dimension, and yes, it could also be the distinct count off the main fact. Distinct counts are a known weak spot of the multidimensional engine, which is why I haven't suggested it.
Alexei Stoyanovsky 3,411 Reputation points

2021-02-12T07:45:56.063+00:00

(whatever for they limited a comment to 1000 characters....)

Now the physical counts won't help you with the separation task - only with counting. Since your separation criterion is a specific value of another measure, you'll have to do it with MDX, and the engine will need to calculate the value of that measure for each claim number. Which brings me back to the pre-calculation idea. You might be able to achieve a dedicated physical measure in the main fact table, which would return null for claims needed to be filtered out. Then your set to be counted would be nonempty([Claim].[Claim Number].[Claim Number], [Measures].[filter measure]), and that certainly seems faster than doint the >1 comparison on-the-fly.
Lukas Yu -MSFT 5,806 Reputation points

2021-02-15T09:30:32.067+00:00

Any updates on this issue ? Have you implemented Alexei's advice was this helpful?
kiran l 21 Reputation points

2021-02-16T17:09:12.713+00:00

Hello,
Yes I was able to implement the solution that was suggested. But I am missing something as the count doesn't seem to be correct. Following is the calculated measure where I need to count all the claims where the value is <1 and nonempty.
The measure [Measures].[ITD Gross Case Loss And ALAE Reserve Closed] has been updated in the view where it will be null for the claims that need to be filtered out.

Am I doing this correctly? Thanks

CREATE MEMBER CURRENTCUBE.[Measures].[Gross_ClaimCount_Closed1] -- 1.08
AS Sum( nonempty([Claim].[Claim Number].[Claim Number], [Measures].[ITD Gross Case Loss And ALAE Reserve Closed]),
IIF(
(
abs(round([Measures].[ITD Gross Case Loss And ALAE Reserve Closed],3))<1) ,
1,
NULL )),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Gross Premium';
kiran l 21 Reputation points

2021-02-16T17:41:03.237+00:00
Also, I cannot have the entire calculation in the cube as the fact table is transactional and the filter value is at the claim grain, grouping and sum will be very complex as this is a temporal accumulating snapshot table that adds to the complexity.

Following is the calculation in the SQL to remove the not required claims

CASE WHEN NatureTypeKey = 'C' THEN NULL WHEN (abs(round([Case Reserve], 3)) >= 1) THEN NULL ELSE [Case Reserve] END AS [Gross Case Loss and ALAE Reserve Closed]
Alexei Stoyanovsky 3,411 Reputation points

2021-02-17T07:35:24.297+00:00

The NonEmpty idea was that all the comparisons are already done by the time the cube is processed. So the measure was supposed to be a simple count of the nonempty set, not another conditional sum over it.
Do check the formulas, there seems to be some confusion in the <1 vs >=1 part, but that could be just wrong impression because the field names are different.
Note also that for SSAS MD, null<1 evaluates to true.

Answer 2

Lukas Yu -MSFT 5,806

Hi,

How much rows and how long time are you talking here ?

I think your expression is fairly clean and does not leave much space for optimization. I think probably is that there are large amount of rows so it costs a long time for this ?

Lukas

kiran l 21 Reputation points

2021-02-11T13:35:16.53+00:00

We have 14 million in the Fact and 3 million in the dimension. This particular excel report wants to look at all the counts(all data) from the past at the same time and it takes 7 mins for the query.

Answer 3

So finally this has worked for me: Moving the entire business logic to the cube is the best way to go as suggested by @Alexei Stoyanovsky but I had to skip it due to the complexity of the code. I made few changes to the MDX code and moved part of the logic to the Cube. I also made sure the NULL values are preserved in the cube so that helped too. Not including the empty claims numbers in the calculation sped up the results
Following is my MDX code: The time was reduced from 7mins to 1 min which is a step in the right direction.

SQL Code:
CASE WHEN NatureTypeKey = 'C' THEN NULL
WHEN (abs(round([Case Reserve], 3)) >= 1) THEN NULL
ELSE [Case Reserve] END AS [Gross Case Loss and ALAE Reserve Closed]

MDX:
CREATE MEMBER CURRENTCUBE.[Measures].[Gross_ClaimCount_Closed]
AS Sum( nonempty([Claim].[Claim Number].[Claim Number], [Measures].[Gross Case Loss and ALAE Reserve Closed]),
IIF(
(
abs(round([Measures].[ITD Gross Case Loss And ALAE Reserve Amount],3))<1
) , 1, NULL )),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Gross Premium';

Thank you @Alexei Stoyanovsky and @Lukas Yu -MSFT for all the help.
I appreciate it.

Alexei Stoyanovsky 3,411 Reputation points

2021-02-19T07:12:16.493+00:00

As noted above, you should see further performance improvements if you move the 'abs(round([Measures].[ITD Gross Case Loss And ALAE Reserve Amount],3))<1' part to SQL as well

Count members based on aggregated measure SSAS MDX

2 additional answers

Activity