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.
OK
So I tried out MDX functions, as mentioned, in the cube design and this caused weird behaviour.
In the end I did it in C# from the ExcelDNA side, as I use this alot. ExcelDNA has a funky RTDServer one can subclass, which I had done before to great effect. This code could obviously be made cleaner and reusable however for now I'll paste my first working version here which successfully updates the 'currentPageName' value of my desired slicer field (Epoch in this example). It also assumes that only pivot tables on the activeSheet are to be changed. Of course one could pass it whether they wanted entire workbook scanned, activeSheet, range(s), cells etc. The beauty of this is that you do not even need to refresh the pivot table as this will do that for you too as you update the slicer periodically and not a jot of VBA in sight! :)
This is how you would call it:
[ExcelFunction(Name = "SetMaxEpochId", Description = "Periodically update slicer value")]
public static Object SetMaxEpochId(String environment, String intervalInSeconds)
{
//Array wrapper for sending to RTD
String[] args = new string[2];
args[0] = environment;
args[1] = intervalInSeconds;
return XlCall.RTD(RTDSlicerUpdater.ServerProgId, null, args);
}
And here is the sub-classed RTD:
using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;
using Microsoft.Vbe.Interop;
using static ExcelDna.Integration.Rtd.ExcelRtdServer;
using System.Collections.Generic;
using System;
using System.Reflection;
using ExcelDna.Integration.Rtd;
using System.Linq;
using System.Numerics;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace TilleyTechExcelDNA
{
[ComVisible(true)]
// Required since the default template puts [assembly: ComVisible(false)] in the AssemblyInfo.cs
// If ProgId is not specified, change the XlCall. RTD call in the wrapper to use namespace + type name (the default ProgId)
[ProgId(RTDSlicerUpdater.ServerProgId)]
public class RTDSlicerUpdater : ExcelRtdServer
{
public const string ServerProgId = "RTD.RTDSlicerUpdater";
// Using a System.Threading. Time which invokes the callback on a ThreadPool thread
// (normally that would be dangeours for an RTD server, but ExcelRtdServer is thrad-safe)
Timer _timer;
String environment;
List<Topic> _topics;
String strEpoch = "[Epoch].[Epoch]"; //We know this is the MDX structure
Microsoft.Office.Interop.Excel.PivotTables pivotTables;
protected override bool ServerStart()
{
_topics = new List<Topic>();
return true;
}
protected override void ServerTerminate()
{
_timer.Dispose();
}
protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues)
{
_topics.Add(topic);
int interval = 0;
Int32.TryParse(topicInfo[1], out interval);
environment = topicInfo[0];
GetPivotTables(); //Need to access Excel on UI thread only
_timer = new Timer(OnTick, null, 0, interval * 1000);
return "RTDSlicerUpdater: enabled";
}
private void GetPivotTables()
{
//Go looking for pivot tables
Microsoft.Office.Interop.Excel.Application xlApp = ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application;
if (xlApp != null)
{
Microsoft.Office.Interop.Excel.Worksheet activeWorkSheet = xlApp.ActiveWorkbook.ActiveSheet;
if (activeWorkSheet != null)
{
//Trying to do stuff when Excel is not ready doesn't end well :) LTilley
//Pass to its queue and let it get to it when ready
ExcelAsyncUtil.QueueAsMacro(() =>
{
pivotTables = (Microsoft.Office.Interop.Excel.PivotTables)activeWorkSheet.PivotTables(Type.Missing);
});
}
}
}
protected override void DisconnectData(Topic topic)
{
_topics.Remove(topic);
}
void OnTick(object _unused_state_)
{
PerformUpdate();
}
private void PerformUpdate()
{
if (pivotTables != null && pivotTables.Count > 0)
{
int maxEpochId = (int)DatabaseTools.Functions.GetMaxEpochId(environment);
//Queue so we do not blow up Excel :)
ExcelAsyncUtil.QueueAsMacro(() =>
{
//Have to start at 1 not ☺
for (int pivotCounter = 1; pivotCounter <= pivotTables.Count; pivotCounter++)
{
Microsoft.Office.Interop.Excel.PivotTable pt = pivotTables.Item(pivotCounter);
try
{
var pf = pt.CubeFields[strEpoch]; //we're assuming they have Epoch as a slicer
if (pf != null)
{
pf.CurrentPageName = strEpoch + ".&[" + maxEpochId + "]"; //We're usin the dimension ID/Key hence the &
}
}
catch (Exception e)
{
//Don't do anything, they obviously haven't got the dimension in the pivot yet.....
}
}
});
}//only bother if we have pivot table(s)
}
}//class
}//ns