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]))
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]))