I have an MS SSAS Cube and I have constructed a C# service which retrieves data from another system and feeds it to my cube. I use the notion of an IntradaySeries and each new series of data results in a new max ID for the ‘epoch’.

My epoch table has:

ID (int)
Name (varchar)
Processed (bit)

For newer reports these are constructed automatically using a custom C# MDX xll layer I wrote, and this uses ExcelDNA RTD tech to obtain the latest epoch ID to use in MDX queries. All good.

My users also want to use traditional Excel pivot tables too, and I’ve been trying to ensure that when they hit refresh on the pivot table(s) it jumps to the latest epoch.

In cube design, I came up with this for the Default Member in MDX section:

StrToMember('[Epoch].[Epoch].&[' +MAX(([Epoch].[ID].Members,[Epoch].[Processed].&[1]), [Epoch].[ID].CurrentMember.MEMBER_KEY)  + ']')

It compiled and deployed and as you can see I’m attempting to use the MAX ID on Epoch ID where processed. I did make a calculated member and it correctly showed the max ID.

The above doesn’t work though and it seems to default to some random epoch in the middle of the epochs.

As I just typed this I realised I may also need to set default member on ID too. I will try that whilst I wait for comments here.