ssasmdxmultidimensional-cube

MDX for concatenated dimension level based on result of another calculated member in cube design


Lastyear I had to migrate a cube to MS SSAS for a fixed dataset of two days, T1 and T2 and they have an epoch of 'SOD' (for Start of Day).

Now I am drip-feeding real-time data into the cube with changing epochs (to denote the latest).

Epoch has become it's own dim table with INT ID, VARCHAR Name. All good.

I have created some new calculated members:

(As the incoming data appears as a 'series' it has its own new epoch name and is always the max ID.

Epoch_Max as (works):

MAX([Epoch].[ID].MEMBERS,[Epoch].[Epoch].CurrentMember.MEMBER_KEY)

Notonal_SOD here, locked to the Epoch known as 'SOD' (works):

[Measures].[Notional_SOD] = ([Epoch].[Epoch].[SOD],([Date],[T].[T1],[Measures].[Notional_SUM]))

I need some advice on this one please, as I want to have Notional_LATEST (for the new and latest realtime delivery of data) so we use the (working) Epoch_MAX calc member:

(How do I do something like the below (this does not work):

[Measures].[Notional_LATEST] = ([Epoch].[ID] + ".[" + CSTR([Measures].[Epoch_MAX]) + "]",([Date],[T].[T1],[Measures].[Notional_SUM]))

Solution

  • Cracked it!

    So after reading around and searching for various concatenation and string functions I stumbled across StrToMember. Note - I had also made the result of my concatenation visible, so in an Excel pivot I could see that it was a 'string' which made me realise I needed to convert the string to a proper object/dimension with which to use in my actual measure.

    So I place my concatenated string + dynamic ID into a measure called MAXID and then this is called by my Notional_LATEST measure which is where we wrap the string to convert to a Member.

    (Construct our epoch using the ID part, and max obained from our calculated member.

    '[Epoch].[ID].[' + [Measures].[Epoch_MAX] + ']'
    

    And the final measure to return the notional for the latest epoch:

    [Measures].[Notional_LATEST] = (StrToMember([Measures].[MAXID]),([Date,[T].[T1],[Measures].[Notional_SUM]))