28 08 2010

I’ve been dipping my toes into the black art of MDX (or MultiDimensional Expressions) and I had a requirement whilst building a KPI that I needed to get the current date from a Dimension I had called “Date”.

I had been linked to this blog: http://my.opera.com/duncans/blog/using-custom-sets-in-analysis-services-to-deliver-special-date-ranges-to-end-use but couldn’t for the life of me get his first “TodaySet” set to work, partially because his Date dimension structure was different from mine, so I set about another method. What I ended up using was the StrToMember function which takes in a string representation of an MDX member and returns the member. Where the power lies, is that you can use basic string manipulation to build your representation and have that return is a real Member. Here’s the string I used:

I placed this string into a new Named Set (called [TodaySet], so it returns a Set with a single Member which represents a Date Member that is today. Note the custom string formatter, this is the format of the individual Date tuples in my Dimension. To see what your Dates would be formatted like, in an MDX Script editor in SQL Server Management Studio, Navigate to your Dimension, drill right down to your Member that you want and drag it into the script editor, you’ll see an explicit reference to that Member (denoted with the ampersand & symbol).

What we can then do is translate this into a Calendar Set reference that represents today (using the Exists function to return the [Date].[Calendar].[Date] child member for today), using this snippet, placed into another Named Set:

With this Set, you can access the first tuple using .ITEM(0) then apply this to any expression you like where you need the current date.

SharePoint Search API Gotchas HTTP 503 & App Pools That Just Won’t Stay Up (IIS7, Win2k8 x64, IPv6)