.NET with BI development tips, tricks and experiences.

I have been repeatedly asked to produce queries in MDX across a number of SSAS cubes that use the query execution date/time and I have reused this little gem repeatedly:

StrToMember("[Date].[Date].[" + Format(now(), "yyyyMMdd" + "]")

Here it is in a complete query that returns a measure over the last 4 days from the time of execution:

SELECT
    [Measures].[Menace]
ON COLUMNS,
    LastPeriods(4,
        StrToMember("[Date].[Date].[" + Format(now(), "dd-MM-yyyy") + "]")
    )
ON ROWS
FROM [CubeOfDoom]

MDX has no GETDATE() function to obtain the member associated with the current date/time as it is up to each implementation how the date/time dimension is populated and how its members are named. If you populate your date/time dimension up to the time period of execution you can make use of DefaultMember or CurrentMember but what if the way the dimension is populated has to change in the future or the dimension is populated ahead of time?

This method may not be the most efficient but it is the most robust I have come across to date. If anyone has a better way drop me a comment. Always looking to improve/evolve.

I was recently playing about with hiding the All member for several dimension attributes in my SSAS project. In order to hide the All member we set the IsAggregatable property to False. This immediately had the effect of my queries not returning any results. In hindsight my problem should have been blindingly obvious.

When we no longer aggregate the members on a dimension attribute, SSAS does not know what member/members to use unless you tell it explcitly, or set the DefaultMember attribute. Doing one or the other will stop your measures and calculations from vanishing.