THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

How to get the today's date in MDX

The question in the title of this post is a popular one. There are many scenarios, where it is desirable to navigate to the member of the Time hierarchy which corresponds to the current day, month or year. Sometimes, there is a desire to set the default member to be aligned with the current date. It is especially relevant, when the Time dimension contains non aggregatable attribute, such as Year (i.e. there is no member 'All Years'). By default, Analysis Services sets the default member to one of the years, but which one is undefined. So rather then override it with static member such as [Time].[Year].[2005], one may want to point to the current year. Another common scenario is around KPICurrentTime property, which is often set to today's day. Or, perhaps, calculations in the cube need to refer to today's date etc.

Usually, when this question is asked, the typical answer involves calls to VBA functions Now() or Date() combined with some clever formatting (which usually requires additional VBA functions such as Format, CStr, CDate, Month, Year, Quarter, Day etc) to build a string which looks like either fully qualified or even unique member name and then feed it to StrToMember MDX function. While these solutions do usually work, I am not fond of them. Building manually an unique member name goes against the spirit of MDX, since unique names are provider specific and have no specific format. But even building fully qualified member name is dangerous, especially in solutions which freely use ampersand sign (&) as a prefix for member key - certainly undocumented behavior. Finally, I really dislike StrToMember function for many reasons. For the havoc it wrecks in the query optimizer, for the unpredictable caching guarantees, for the very dynamic binding by means of reparsing its input.

The alternative that I propose is instead of trying to build a member name in the specific format - scan the members for the match with current date. Let's demonstrate this with the examples from Adventure Works. We will perform the following steps:

1. Obtain today's date using VBA!Date function

2. Since this article is written in 2007 and Adventure Works's Time dimension goes only as far as 2004, we will go 4 years back using DateAdd function, to get into year 2003.

3. Go over all the days and look for one which has MemberValue the same as today's (four years ago) date. In properly designed Time dimension, the Date's member value will be of type DateTime.

4. There should be no more than one tuple in the result set if the Time dimension was properly design, so take the first tuple of the set, which will be the desired member, or NULL if the result set came empty.

The MDX expression which does it will look the following:

Due to a bug in the blogging software, the literal Date in square brackets cannot be used in the blog post, therefore here and later I replaced it with [Date_] instead. If you will be trying these examples, please change it back by removing _.

Filter([Date_].[Calendar].[Date_], [Date_].[Calendar].MemberValue = vba!dateadd("yyyy", -4, vba![Date_]())).Item(0)

Or to use it in MDX query to see results

select {} on 0
,Filter([Date_].[Calendar].[Date_], [Date_].[Calendar].MemberValue = vba!dateadd("yyyy", -4, vba![Date_]())) on 1
from [Adventure Works]

There aren't many days in the Time dimension. Even if we kept 10 years in the cube, there would be no more than 3660 days. Running Filter over such a small number of members is instantaneous. However, we do note, that for every single day we call VBA function, which seems redundant, since the the current date is a constant. Moreover, it is somewhat dangerous, since if we were to run this query in the evening, at 11:59pm, the result of VBA!Date function could change in the middle of execution ! To prevent that the query can be rewritten as

with member Measures.Today as vba!dateadd("yyyy", -4, vba![Date_]())
select {} on 0
,Filter([Date_].[Calendar].[Date_], [Date_].[Calendar].MemberValue = ([Date_].[Calendar].[All Periods],Today)) on 1
from [Adventure Works]

this is quite a common trick - shift coordinate to constant member (i.e. All Periods) in order to make Filter to request same coordinate - ([Date_].[Calendar].[All Periods],Today) for every iteration. This way the hope is that it will be computed only first time, and cached afterwards. Even better way to do it is to write

with member Measures.Today as vba!dateadd("yyyy", -4, vba![Date_]())
select {} on 0
,Filter([Date_].[Calendar].[Date_], [Date_].[Calendar].MemberValue = Root(Today)) on 1
from [Adventure Works]

Here, by using Root(Today) we shift coordinates in all dimensions and attributes to the constant, so even if we had more axes in the query, or other coordinate shifting calculations, they won't matter, and VBA!Date would be called only once.

Similar trick can be done also inside MDX Script. It relies on the fact that named sets are static and computed only once. Therefore, the MDX Script could contain the following line:

CREATE HIDDEN TodayDate = vba!dateadd("yyyy", -4, vba![Date_]());
CREATE SET Today AS Filter([Date_].[Calendar].[Date_], [Date_].[Calendar].MemberValue = ([Date_].[Calendar].[All Periods],TodayDate));

And afterwards, whenever we need to reference today's date - we would use Today.Item(0), or even shorter notation of Today(0).

The catch here is that evaluated MDX Script is cached, so unless there is some sort of refresh to the cube, the named set Today won't change from day to day and will become outdated. But as long as new data is loaded into cube daily - it will be OK, since any kind of processing will trigger reevaluation of MDX Script.

Yet another solution for the scenarios where application cannot depend on the specific MDX Script, is to use stored procedures. While it won't be as performant as previous one, it could be more universal.

Below is the code of stored procedures which returns today's day:

        public Member GetToday(Level lvl)
        {
            // Get today's date from the system
            System.DateTime today = System.DateTime.Today;
            System.DateTime fouryearsago = today.AddYears(-4);

            // The only way to get set out of the level. Direct cast won't work
            Expression lvlexp = new Expression(lvl.UniqueName);
            Set lvlset = (Set)lvlexp.CalculateMdxObject(null);

            // Build the string in the form
            // [Date_].[Calendar].[Date_].MemberValue = CDate("5/21/2007")
            Expression exp = new Expression(
                    lvl.ParentHierarchy.UniqueName 
                +   ".MemberValue = CDate(\"" 
                +   fouryearsago.GetDateTimeFormats('d')[0] 
                +   "\")");
            Set filterset = MDX.Filter(lvlset, exp);

            // Iterate only one step - this is better then checking 
            // the count and indexing the 0's item
            foreach (Tuple t in filterset.Tuples)
                return t.Members[0];

            // If today's date wasn't found - return NULL member
            // Since Member object doesn't have ctor - this is the only way
            Expression nullmbr = new Expression("NULL");
            return (Member)nullmbr.CalculateMdxObject(null);
        }

Due to several limitations of AdomdServer object model, there is an excessive use of Expression object in the code above. It could've been much simpler if the Member object exposed MemberValue property, because then none of the dynamically built expressions would've been needed, and CDate wouldn't have to be evaluated over and over again. Simple loop over lvl.GetMembers() comparing value of today variable with Member.MemberValue would've done the job. Alas, not in current version. The typical call to such sproc would look like

select {} on 0
,ASSP.ASStoredProcs.Util.GetToday([Date_].[Calendar].[Date_]) on 1
from [Adventure Works]

The sproc requires passing the level as its argument, but this can be improved too. The version below finds the Day level in the cube automatically:

        public Member GetToday()
        {
            CubeDef cb = Context.CurrentCube;
            Dimension timedim = null;
            foreach (Dimension dim in cb.Dimensions)
            {
                if (dim.DimensionType == DimensionTypeEnum.Time)
                {
                    timedim = dim;
                    break;
                }
            }

            if (null == timedim)
                throw new System.ArgumentException("No Time dimension in the cube");

            foreach (Hierarchy h in timedim.Hierarchies)
            {
                foreach (Level lvl in h.Levels)
                {
                    if (lvl.LevelType == LevelTypeEnum.TimeDays)
                        return GetToday(lvl);
                }
            }

            throw new System.ArgumentException("No Day level in the Time dimension");
        }

This sproc will work with the simpler call, like this one

select 
{} on 0
,ASSP.ASStoredProcs.Util.GetToday() on 1
from [Adventure Works]

However, in cube like Adventure Works which have multiple Time dimensions, sproc will return member from the random one. Also, it turns out that there is a slight mismatch between AMO attribute types and ADOMD.NET level types, so marking Date attribute with type 'Date' in AMO will translate into type 'Regular' in ADOMD.NET. (And Adventure Works cube has a small bug, the level that gets marked with Day type is the 'Day Name' which really should be marked as DayOfWeek type).

So we saw several different methods of determining the current date through MDX. But none of them is ideal. They all rely on the non-deterministic VBA functions such as Now and Date, which can have really bad caching implications. So the best solution, which is also the simplest one in terms of MDX, is to have a dedicated process, which will update MDX Script daily with the following line:

CREATE SET Today AS { [Date_].[Calendar].[Date_].[May 21, 2003] };

Where the name of today's date is hardcoded and changed every day. This will have the best performance, but it will also add a little management burden on the cube maintainer.

Published Wednesday, May 23, 2007 11:39 AM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement