Thoughts about the “Current Day” problem in SSAS
Well, I think it’s time for a new post here in my blog... Yes, I’m still alive and still working in the MS BI space at Avanade... Many things changed in the last months so this blog was not on my top priority list, but maybe this will change again...
So I just want to publish some thoughts about the “Current Day” problem in SSAS... There are quite some articles about that and what I want to tell you is nothing more than bringing two posts together...
Mosha wrote quite something about this task... http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx. How to use VBA to find out the current date and how to map this with your dimension... At the end he mentions performance issues with this... And that might be right... Since this is a calculation which will be run in each MDX you send to the server there is nearly nothing it can cache... Mosha’s recommendation is to use some fixed member you change daily...
So what can you do? I came across this article... http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/8/Default.aspx. Here you find some code how to build up a simple application which changes your actual MDX script on your server...
I didn’t have time to test it but it should be pretty straight-forward to build up a tiny tool which updates a set with the actual date in your MDX script. So you have that fixed set and your performance should get better...
If someone likes to implement it and give some feedback, I’ll be more than happy to publish it...