MDX

Now() and the Formula Cache

You know, I have the nagging feeling I’ve already blogged about this before… but I can’t find the post (I have been at this for over four years now) so it’s worth mentioning again…

Anyway, a common question asked on the MSDN Forums is how to get the current date and then use it in an MDX calculation – see this thread for example. Usually this is because users want calculations that show the current day’s sales, or something similar. One answer is of course to use the Now() function, but what people don’t often realise is how this can impact the ability of Analysis Services to cache the values returned by calculated members, and therefore reduce overall query performance.

To understand why, let’s look at some examples in Adventure Works. First of all create a calculated measure on the AW cube as follows:

CREATE MEMBER CURRENTCUBE.[MEASURES].[NOWDEMO] AS NOW();

Then run the following query a few times:

SELECT {[Measures].[NOWDEMO]} ON 0
FROM [Adventure Works]

As you’d expect, every time you run this query you see the current date and time – and every time you run it, you see a different value. But, you may be thinking, doesn’t Analysis Services cache the results returned by calculated members? Well, yes it does in most cases, but for non-deterministic functions (functions that could return a different result every time they’re called) like Now() no caching takes place, because otherwise the value returned from the cache might be different from the one the function actually returns.

The next problem is that if you create any other calculated members that depend directly or indirectly on the value returned by a non-deterministic function, their values can’t be cached either. One problem I see sometimes in my consultancy work is poor query performance resulting from SSAS being unable to use the formula cache, because a large number of calculations have a dependency on a single calculation that uses the Now() function. Here’s a greatly simplified example of two calculated members, the first of which finds the current year and the second which returns a sales value for the year eight years before the current year:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Current Year]
AS YEAR(vba!format(now(),"MM/dd/yyyy")),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[SHOW SALES FOR A YEAR]
AS ([Measures].[Internet Sales Amount],
STRTOMEMBER("[Date].[Calendar Year].&[" + CSTR([MEASURES].[Current Year] – 8) + "]", CONSTRAINED));

If you run a query that references the second calculated measure on a cold cache, such as:

SELECT [MEASURES].[SHOW SALES FOR A YEAR] ON 0
FROM [Adventure Works]

The first time you run it you’ll see SSAS going to disk as you’d expect; the second time you run it though you’ll see SSAS is able to use the Storage Engine cache but not the Formula Engine cache, as this Profiler trace shows:

NowNoFE

The highlighted Get Data From Cache event shows data being retrieved from the measure group cache. This is ok and can be beneficial for query performance, but if the calculation we’re doing is very expensive then it can still mean our query takes a long time to run. We’ll only get an instant response on a warm cache if we can work out how to use the formula cache somehow.

Luckily, in most cases where Now() is used, we don’t usually want the system date and time, we just want the date. That means that we only want to return a different value when the date changes, once every 24 hours. What we can do therefore is use a named set to somehow store the value returned by Now(), for example like this rewrite of the calculation above:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Current Year]
AS YEAR(vba!format(now(),"MM/dd/yyyy")),
VISIBLE = 1;

CREATE SET CURRENTCUBE.MYYEAR AS
{STRTOMEMBER("[Date].[Calendar Year].&[" + CSTR([MEASURES].[Current Year] – 8) + "]", CONSTRAINED)};

CREATE MEMBER CURRENTCUBE.[MEASURES].[SHOW SALES FOR A YEAR – CACHEABLE]
AS ([Measures].[Internet Sales Amount],
MYYEAR.ITEM(0));

What we’re doing here is finding the year we want, then finding the member for the year 8 years ago and storing that in a named set. Because a static named set is only evaluated once, when we do our final calculation we can reference the single member stored in the named set and therefore make use of the formula cache as the following Profiler trace shows:

We’re now getting data from the flat cache, which is one part of the formula cache (which isn’t ideal either as it indicates the calculation is being evaluated in cell-by-cell mode, I guess because we’re referencing a named set inside it) and so warm-cache performance will be better.

The next problem is that when the date does change, we need to clear the cache. This can be accomplished easily by running an XMLA ClearCache command, perhaps direct from SQL Server agent, every night at midnight or whenever necessary.

To be honest, though, I’m not sure using the Now() function at all is a good thing – apart from the issues described here there are a lot of other risks involved, such as the time or date on your server being wrong or confusions with time zones and date formats. I think a better approach to the problem is to have an extra attribute in your Time dimension which flags up a date as ‘today’, and which changes every day. Of course this means you need to do some extra ETL and processing on your Time dimension as a result, but I think it’s a much cleaner solution than Now() and leads to much more efficient MDX calculations.

11 thoughts on “Now() and the Formula Cache

  1. Hey Chris,The last paragraph is what I thought all along when reading the post. The Now() function should be avoided as much as we can. As you said – If we need the current day or time according to the dimension\’s last process we can use the flag in the time dimension (described here: http://www.miky-schreiber.com/Blog/PermaLink,guid,2efa7c5b-826e-43c4-af42-5608fdd6c188.aspx) or you can use external application to write the current day or time in the MDX query.Miky Schreiber.

  2. Hi Chris,Been a follower for years :). I was just seeing your profiler screenshot , maybe you are interested in a poc sample from a session few months ago. It uses sql man studio custom reports, trace service sample & some views & functions to see the trace data in a more intuitive way. You can see/download it here:http://biresort.net/blogs/rquintino/archive/2009/08/07/devdays-dat302-tuning-amp-understanding-mdx-query-performance-in-sql-server-2008-analysis-services.aspxThanks for such great content!Rui

  3. HI Chris ,
    Thanks for all your great posts in SSAS and mdx.I am a good follower of your blog .
    Recently i am struck up with a situation in cube about the Formula engine cache. Can you please help out of this issue. Below is the problem in detail.
    I have a cube in which there is calculated measure which does the below MDX

    Aggregate(
    (
    NULL:LinkMember(ClosingPeriod([Date].[Calendar Hierarchy].[Date]),[DimEffectiveBeginDate].[Calendar Hierarchy]),
    LinkMember(OpeningPeriod([Date].[Calendar Hierarchy].[Date]),[DimEffectiveEndDate].[Calendar Hierarchy]):NULL,
    Root([Date])
    )
    ,[Measures].[Product Key Distinct Count]
    )
    we have a fact table which has 70 millions data ,and we run our ETL daily where there would be <200k rows added to our system. The above query is performing very slowly with complex filters , It took around 20 mins for some complex queries . So to make this query faster we thought of implementing Cache warming as suggested by you,but i was surprised on the process of implementation.

    The Formula Engine Cache is not being shared across users when they run the same query,Every user is able to use cache data only when they had run the query though excel .But it is not able to use one user's cache across all Users when they run the same Query.

    I looked for un-deterministic functions in the above mdx as suggested by you in the video, but i didn't find that . And i also searched whether queries are being made with SubSelects as suggested you in video .
    But when i look in profiler the queries are formed with Subselects. I use excel 2010 for querying the cube.Below is the sample MDX which i got from profiler trace .

    SELECT NON EMPTY Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownLevel({[Products].[Product Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)}}, {[Products].[Product Hierarchy].[Category Name].&[Games]},,,INCLUDE_CALC_MEMBERS)}}, {[Products].[Product Hierarchy].[Sub Category Name].&[Games]&[Shooter]},,,INCLUDE_CALC_MEMBERS)}}, {[Products].[Product Hierarchy].[Parent Product Name].&[Games]&[Shooter]&[N/A]},,,INCLUDE_CALC_MEMBERS)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Products].[Product Hierarchy].[Sub Category Name].[Category Name],[Products].[Product Hierarchy].[Parent Product Name].[Sub Category Name],[Products].[Product Hierarchy].[Product Name].[Parent Product Name],[Products].[Product Hierarchy].[Product Name].[Product Name IDX] ON COLUMNS FROM [DevCube] WHERE ([Product – Has Active Sku].[Flag].&[1],[End User Can Buy].[End User Can Buy].&[1],[Date].[Calendar Hierarchy].[Calendar Quarter].&[20123],[Measures].[Distinct Product Count By Offer]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    Can any one suggest how to over come this ?. We want to implement cache warming for queries to be faster.Waiting for a reply . Thanks in advance.

      1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        Hi Rajesh,

        Sorry for the late reply. If you are suffering from subselects in Excel 2010, probably the only thing you can do is to see if you can use named sets instead, as I show here: http://cwebbbi.wordpress.com/2011/04/06/excel-2010-subselects-named-sets-and-the-formula-cache/

        HTH,

        Chris

      2. Hi Chriss ,

        I checked the queries that are executed in Excel using SQL profiler . There i see the queries are not executed as subselects , I see a normal MDX query. I am wondering why formula cache is not being shared , Do you think the MDX i used is causing the problem?. I see the LinkMember , closingPeriod and OpeningPeriod are not non-deterministic functions to check whether it is causing the problem , as suggested by you in SqlBits video.
        Can you help me why this causing ?

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.