Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Named Sets, AutoExists and Katmai

with 6 comments

A couple of months ago Radim Hampel pointed out to me some very weird stuff happening with named sets and the Where clause. Since it turned out that Darren had run into the same issue and also been thrown by it, and since I tested it out on Katmai CTP6 and could see that it was behaving differently from AS2005, I opened an item on Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331186

And now, after a long and detailed email thread involving Mosha, Edward Melomed, Marius Dumitru, Darren and Deepak we’ve got to the stage where I understand what’s going on, Katmai RC0 does roughly what I want, and I can blog about it!

Let me explain what I saw first. What would you expect the calculated member in following query to return?

–Query 1
WITH
  SET mytestset AS
    PeriodsToDate
    (
      [Date].[Calendar].[Calendar Year]
     ,[Date].[Calendar].[Month].&[2004]&[6]
    )
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

Just from looking at the code I would have set the calculated member should return the string representation of the set from January 2004 to June 2004. But if you run the query you will in fact see that it returns the set containing the member July 2004 on both AS2005 and Katmai. To me that made absolutely no sense… Now, take a look at this query:

–Query 2
WITH
  SET mytestset AS
    [Date].[Calendar].[Month].&[2004]&[6]
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

On AS2005 the calculation returns June as I would expect, on Katmai it returns an empty set. Now run this query:

–Query 3
WITH
  SET mytestset AS
    {
      [Date].[Calendar].[Month].&[2004]&[5]
     ,[Date].[Calendar].[Month].&[2004]&[6]
    }
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

and this query:

–Query 4
WITH
  SET mytestset AS
    (
      [Date].[Calendar].[Month].&[2004]&[5]
     :
      [Date].[Calendar].[Month].&[2004]&[6]
    )
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

…which to me should do the same thing. On AS2005 query 3 returns the set May and June but Katmai returns an empty set; query 4 returns an empty set on both AS2005 and Katmai. At this point I could see that something funny was happening that I didn’t like!

What are the practical implications of this? Take the following query from Mosha’s blog entry on ranking:

WITH
  SET OrderedEmployees AS
    Order
    (
      [Employee].[Employee].[Employee].MEMBERS
     ,[Measures].[Reseller Sales Amount]
     ,BDESC
    )
  MEMBER [Measures].[Employee Rank] AS
    Rank
    (
      [Employee].[Employee].CurrentMember
     ,OrderedEmployees
    )
SELECT
  [Measures].[Employee Rank] ON 0
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works]

Run it and you’ll see that the Employee with the key 46, A Scott Wright, has a rank of 18. Now let’s slice by this Employee:

WITH
  SET orderedemployees AS
    Order
    (
      [Employee].[Employee].[Employee].MEMBERS
     ,[Measures].[Reseller Sales Amount]
     ,bdesc
    )
  MEMBER measures.[employee rank] AS
    Rank
    (
      [Employee].[Employee].CurrentMember
     ,orderedemployees
    )
SELECT
  measures.[employee rank] ON 0
FROM [adventure works]
WHERE
  [Employee].[Employee].&[46]

Run this and you’ll see that A Scott Wright has now supposedly got a rank of 1. Whatever the logic behind this, it doesn’t make sense from an end user perspective does it?

So how can we explain what’s happening here? It’s all to do with autoexists: in some cases it makes sense to apply autoexists to named sets, but in others (mostly when the set is intended for use in a calculation) then it doesn’t. Let’s forget about trying to understand what AS2005 does because it tries to guess when it should apply autoexists and gets very confused, but Katmai is mostly consistent and logical: by default it applies autoexists to all named sets. That explains why queries 2,3 and 4 all return empty sets on Katmai: May and June don’t exist with July.

After my initial item on Connect was opened those nice people in Redmond (who agreed with me that the way things were working wasn’t ideal) added a new connection string property, Autoexists, which can have the following values:

0 – default (same as 1)
1 – Apply deep autoexists for query axes and named sets (with WHERE clause and subselects)
2 – Apply deep autoexists for query axes and no autoexists for named sets (with WHERE clause and subselects)
3 – Apply shallow autoexists for query axes with WHERE clause, deep autoexists for query axes with subselects, no autoexists for named sets with WHERE clause and deep autoexists for named sets with subselects

Here’s the explanation I got from Marius about what’s meant by ‘deep’ and ‘shallow’ autoexists:

Suppose a query axis or named set involves a set expression of the form F(G(s)), with F and G being set functions (e.g. TopCount, Tail etc.)
Let SSW denote the Where clause slice and Subselect restrictions present in the query.
“Shallow autoexists” evaluates the set expression as Exists(F(G(s)), SSW).
“Deep autoexists” evaluates the set expression as Exists( F( Exists( G( Exists(s, SSW) ), SSW ), SSW) – it applies autoexists with the Where and Subselects at every intermediary step. Deep autoexists applies to all set-valued subexpressions being evaluated in the root context of the query (i.e. the context of default member coordinates, or Where clause coordinates, if a Where clause is present).
For many/most set functions (e.g. Union), the rules above produce the same result.
For others (e.g. TopCount, Head/Tail), the results differ in the general case.

So the behaviour I describe above for RC0 is also what you get when you put Autoexists=1 in the connection string. But what about Query 1 – why does the PeriodsToDate function return July with this setting? Hmm, well I think this is a bug and it should return an empty set. I opened another Connect about this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356193
and I’ll update this post when I get an answer on it.

What happens with Autoexists=2 and Autoexists=3 then? In both cases, Query 1 returns the set of months from January to June; Query 2 returns June, Query 3 and Query 4 both return May and June. To see the difference between these two settings take a look at this query:

WITH
  SET mytestset AS
    (
      [Date].[Calendar].[Month].&[2004]&[5]
     :
      [Date].[Calendar].[Month].&[2004]&[6]
    )
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM (SELECT
  [Date].[Calendar].[Month].&[2004]&[7] ON 0
FROM [adventure works] )

With Autoexists=1 and Autoexists=3 the set here is empty; with Autoexists=2 then it contains May and June.

Written by Chris Webb

July 15, 2008 at 5:08 pm

Posted in MDX

6 Responses

Subscribe to comments with RSS.

  1. Hi Chris,
     
    To achieve Autoexists=2 behavior  in AS 2005, could LookupCube() provide a temporary work-around (albeit with poor performance)  – see query below?
     

    WITH
    MEMBER measures.test AS
    LookupCube
    (
    "Adventure Works"
    ,
    "SetToStr
    (
    [Date].[Calendar].[Month].&[2004]&[5]
    :
    [Date].[Calendar].[Month].&[2004]&[6]
    )"
    )
    SET mytestset AS
    StrToSet(measures.test)
    SELECT
    measures.test ON 0
    FROM [adventure works]
    WHERE
    [Date].[Calendar].[Month].&[2004]&[7]
     
     - Deepak

    Deepak

    July 17, 2008 at 12:21 am

  2. Hi Chris,
     
    I ran Query-2 in AS2005 (SP2 + CU6) and it returns empty set. So it\’s same with Katmai.
     
    Cheers,
    Uzzi

    XLNT

    July 17, 2008 at 3:06 am

  3. Hi Deepak, yes, I guess LookUpCube could work – as Darren found in that mega-thread, there are various functions like Generate which can be used to make the difference between a \’top level\’ set and a \’lower level\’ set.

    Chris

    July 17, 2008 at 9:41 am

  4. [...] on a un jour dans le slicer). C’est ce qu’explique cet excellent article de Chris Webb (http://cwebbbi.wordpress.com/2008/07/15/named-sets-autoexists-and-katmai/) qui va beaucoup plus loin).   C’est le comportement par défaut de l’autoexists, [...]

    • When I use the link to the connect site for the reported bug,

      I am getting an error
      “The content that you requested cannot be found or you do not have permission to view it. ”

      This error still exists.

      ob123

      November 28, 2012 at 10:36 pm

      • It has been marked as private, sorry – and I can’t change the visibility for some reason.

        Chris Webb

        November 29, 2012 at 9:04 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: