- March 25th, 2010
- Write comment
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:
StrToMember("[Date].[Date].[" + Format(now(), "dd-MM-yyyy") + "]")
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.