ssasmdxolap

MS SSAS - Calculated member to aggregate count of a dimension appearing for every row of underlying data

I usually work with a custom Java in-memory OLAP cube however my client has asked me to model a cube in MS SSAS. I haven't used it before but I'm a few weeks in and most of it is straightforward (based on me organising fact and dim tables beforehand in SQL Server).

I want to create two standard count measures (BuyCount and SellCount) based on a string field in the underlying fact table (BuySell which is [B | S] as a string).

In my Java I just create a measure using a count with a filter against ="B" (which I know would get turned into MDX to be something like COUNT(FILTER(...).

So I found the calculations area in VS 2022 Designer.

I have added a calculated member as a measure and it's assigned to the Measures group I have (where standard measures are correctly aggregating).

I wrote this to test out BuyCount:

COUNT(FILTER([Deal].[Buy Sell].MEMBERS,[Deal].[Buy Sell].CURRENTMEMBER.MemberValue ="B"))

and this publishes and works however it returns only 1, which is correct in that there are only 2 members of the dimension.

I just tried this too:

COUNT(FILTER([Deal].[Buy Sell].Children,[Deal].[Buy Sell].CURRENTMEMBER.MemberValue ="B"))

So what am I missing and need to change in order for this to work and aggregate the Buy count of the underling rows returned for a given query?

E.g.

Given that Book and Country are dimensions and we should see the buy count for that dimension.

Thanks

Leigh tilleytech.com

Solution

  • So I carried on trying stuff but also, at the same time I asked the question here, I posted on the Microsoft Tech Forums as after all it is one of their products! :)

    So I had correctly realised my above calc was merely counting the members of the dimension based on the filter.

    The answer was to add a core RowCount measure in the Measures group (select count of rows in dropdown when creating new measure).

    I also separated out my B/S strings into a table with IDENTITY column and then in my fact table replaced the B/S with the IDs (1 and 2) so it's cleaner and i think more performant (based on the way stuff gets stored in the fact table).

    Then combine a mix of your dimension with the measure as a tuple to be placed in the MDX expression window of a CalculatedMember:

     ([DimBuySell].[BuySell].&[B], [Measures].[Row count])

    and

     ([DimBuySell].[BuySell].&[S], [Measures].[Row count])

    from my MS question: https://learn.microsoft.com/en-us/answers/questions/914130/ms-ssas-calculated-member-to-aggregate-count-of-a.html?childToView=916215#answer-916215