none
MDX - strange behaviours in slicer area

    Question

  •  

    Hi all,

     

    I made few MDX queries for the purpose of solving puzzle: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3904810&SiteID=17 . During that, I noticed some strange behaviours which I can not interpret. So I'm asking for your help.

     

    Query 1.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Calendar Quarter].&[2003]&[4],

     [Date].[Calendar].[Calendar Quarter].&[2003]&[3]}

    *

    ([Date].[Calendar Year].&[2003])

     

     

    If you run this query on SSAS 2008, you'll get 6 months as expected (2003 M7 - M12). But if you run it on SSAS 2005, you'll get only 3. Which one? It depends on what comes first! I don't consider that expected behaviour.

     

    For given query, you'll receive M10 - M12, but if you switch quarters, you'll get M7 - M9. And if you replace "4" with "5" (quarter key), then it will take first good after that (since there is no 5th quarter, and option for MDX member errors is in default state for AW demo cubes), and that is 3rd quarter. So, we get M7 - M9 again. That goes for SSAS 2005 and SSAS 2008 and it's expected behaviour.

     

    But what if we change year key? If we say &[2002]&[4] instead of &[2003]&[4] (meaning, we go one year back for the first member), we get an error. On both SSAS versions. I'll copy-paste it here:

     

    Executing the query ...

    The MDX function failed because the coordinate for the 'Fiscal Year' attribute contains a set.

    Execution complete

     

    I bolded a part that is refering to something not available in the query - Fiscal Year. I don't have an explaination for it, do any of you readers of this post do?

     

    Query 2.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Month].&[2003]&[4]}

    *

    {[Date].[Calendar Quarter of Year].&[CY Q1]}

    --([Date].[Calendar Year].&[2003])

     

     

    When you run Query 2, you'll receive (null), (null) in SSAS 2005, and {}, (null) in SSAS 2008. If you change parenthesis of CY Q1 from {} to (), you'll receive (null) also in SSAS 2008 instead of {} as in first shot. But, if you comment that set and uncomment the one below, you will get your M4 as expected. On both SSAS versions. How come the first two shots returned nulls?

     

    I did noticed similar things (not that about Fiscal year though) on other dimensions, namely Customers.

     

    Query 3.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Customer].[Customer Geography].[City])

    SELECT

    { X, [Measures].[Internet Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Customer].[Customer Geography].[City].&[Paris]&[75],

     [Customer].[Customer Geography].[City].&[San Francisco]&[CA],

     [Customer].[Customer Geography].[City].&[Los Angeles]&[CA]}

    *

    {[Customer].[Country].&[United States]}

    --([Customer].[Country].&[United States])

     

     

    The above query returns SF and LA (cities). Also on both SSAS versions. But if you comment USA country and uncomment USA country with () prenthesis, you'll receive all cities in USA.

     

    To summarize, what exactly is going on in slicer during query evaluation? How do {} and () differ there (if you move slicer part to an axis (rows for example), it behaves as expected, no strange things for any query demonstrated)?

     

    In the end, few remarks on environment. All tests were performed using SSMS 2008 and latest AW demo databases, each on its own SSAS server (2005 not as v2005 on SSAS 2008).

     

    Thank you all in advance for any effort made regarding these issues,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Monday, September 22, 2008 10:56 AM
    Answerer

Answers

  •  Tomislav Piasevoli wrote:

    I made few MDX queries for the purpose of solving puzzle: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3904810&SiteID=17 . During that, I noticed some strange behaviours which I can not interpret. So I'm asking for your help.

     

    Query 1.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Calendar Quarter].&[2003]&[4],

     [Date].[Calendar].[Calendar Quarter].&[2003]&[3]}

    *

    ([Date].[Calendar Year].&[2003])

     

     

    If you run this query on SSAS 2008, you'll get 6 months as expected (2003 M7 - M12). But if you run it on SSAS 2005, you'll get only 3. Which one? It depends on what comes first! I don't consider that expected behaviour.

     

    For given query, you'll receive M10 - M12, but if you switch quarters, you'll get M7 - M9. And if you replace "4" with "5" (quarter key), then it will take first good after that (since there is no 5th quarter, and option for MDX member errors is in default state for AW demo cubes), and that is 3rd quarter. So, we get M7 - M9 again. That goes for SSAS 2005 and SSAS 2008 and it's expected behaviour.

    It does look like a bug in SSAS 2005, I don't have a good explanation for this one.

     

     Tomislav Piasevoli wrote:

    But what if we change year key? If we say &[2002]&[4] instead of &[2003]&[4] (meaning, we go one year back for the first member), we get an error. On both SSAS versions. I'll copy-paste it here:

     

    Executing the query ...

    The MDX function failed because the coordinate for the 'Fiscal Year' attribute contains a set.

    Execution complete

     

    I bolded a part that is refering to something not available in the query - Fiscal Year. I don't have an explaination for it, do any of you readers of this post do?

    The change you made to the slicer means that because of the attribute relationships you have implied the setting of a set of Fiscal years, this is what is know as attribute overwriting. When you have two Fiscal years in the current context the references to [Date].[Fiscal].CurrentMember fail with the above error, because there is no one single "current" member.

     

     Tomislav Piasevoli wrote:

    Query 2.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Month].&[2003]&[4]}

    *

    {[Date].[Calendar Quarter of Year].&[CY Q1]}

    --([Date].[Calendar Year].&[2003])

     

     

    When you run Query 2, you'll receive (null), (null) in SSAS 2005, and {}, (null) in SSAS 2008. If you change parenthesis of CY Q1 from {} to (), you'll receive (null) also in SSAS 2008 instead of {} as in first shot. But, if you comment that set and uncomment the one below, you will get your M4 as expected. On both SSAS versions. How come the first two shots returned nulls?

    This is the autoexists kicking in. When SSAS built the date dimension, it knows that [CY Q1] only exists with months 1-3, therefore crossjoining month 4 with Q1 will end up with an empty set. When you crossjoin 2003 and April 2003 SSAS knows that they exist together.

     

     Tomislav Piasevoli wrote:

    I did noticed similar things (not that about Fiscal year though) on other dimensions, namely Customers.

     

    Query 3.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Customer].[Customer Geography].[City])

    SELECT

    { X, [Measures].[Internet Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Customer].[Customer Geography].[City].&[Paris]&[75],

     [Customer].[Customer Geography].[City].&[San Francisco]&[CA],

     [Customer].[Customer Geography].[City].&[Los Angeles]&[CA]}

    *

    {[Customer].[Country].&[United States]}

    --([Customer].[Country].&[United States])

     

     

    The above query returns SF and LA (cities). Also on both SSAS versions. But if you comment USA country and uncomment USA country with () prenthesis, you'll receive all cities in USA.

     

    To summarize, what exactly is going on in slicer during query evaluation? How do {} and () differ there (if you move slicer part to an axis (rows for example), it behaves as expected, no strange things for any query demonstrated)?

    Still playing with this one, but I don't even have a theory on it yet.

     

    Wednesday, September 24, 2008 11:29 AM
    Moderator
  • Hi Darren,

     

    thank you very much for taking the time to test this queries I have and for sharing your thoughts on this subject.

     

    Here's what I think.

     

     Darren Gosbell wrote:

     Tomislav Piasevoli wrote:

    Query 1.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Calendar Quarter].&[2003]&[4],

     [Date].[Calendar].[Calendar Quarter].&[2003]&[3]}

    *

    ([Date].[Calendar Year].&[2003])

     

     

    If you run this query on SSAS 2008, you'll get 6 months as expected (2003 M7 - M12). But if you run it on SSAS 2005, you'll get only 3. Which one? It depends on what comes first! I don't consider that expected behaviour.

     

    For given query, you'll receive M10 - M12, but if you switch quarters, you'll get M7 - M9. And if you replace "4" with "5" (quarter key), then it will take first good after that (since there is no 5th quarter, and option for MDX member errors is in default state for AW demo cubes), and that is 3rd quarter. So, we get M7 - M9 again. That goes for SSAS 2005 and SSAS 2008 and it's expected behaviour.

    It does look like a bug in SSAS 2005, I don't have a good explanation for this one.

     

    I forgot to tell you last time, if you enclose year 2003 in {} like this {([Date].[Calendar Year].&[2003])}, then you get expected behaviour, all 6 months.

     

    It is either a bug or a feature in SSAS 2005. I recently posted 2 questions on Connect (not about this subject), but haven't got any reply since. Is that normal? I ask because I haven't used Connect before. Maybe I should wait more. I can post them here also, but I thought better to ask them since I read in few posts it's the way we should do when we encounter an issue. So, what shall I do with behaviour of this query, go to Connect again or wait until someone here explains it?

     

     Darren Gosbell wrote:
     Tomislav Piasevoli wrote:

    But what if we change year key? If we say &[2002]&[4] instead of &[2003]&[4] (meaning, we go one year back for the first member), we get an error. On both SSAS versions. I'll copy-paste it here:

     

    Executing the query ...

    The MDX function failed because the coordinate for the 'Fiscal Year' attribute contains a set.

    Execution complete

     

    I bolded a part that is refering to something not available in the query - Fiscal Year. I don't have an explaination for it, do any of you readers of this post do?

    The change you made to the slicer means that because of the attribute relationships you have implied the setting of a set of Fiscal years, this is what is know as attribute overwriting. When you have two Fiscal years in the current context the references to [Date].[Fiscal].CurrentMember fail with the above error, because there is no one single "current" member.

     

    Hm, I think it's definitely a question of multiple members because of attribute relations. But, is this a Date dimension design issue or expected thing to happen? Let me provide some pictures of AW 2005 Date hierarchies.

     

    Picture 1 - AW_2005_Date_Hierarchies.png

    Picture 2 - AW_2005_Date_Hierarchies_Tree.png

     

    Let's have a look at only Calendar hierarchy at the moment.

     

    Calendar quarter, in MDX example and pictures above, is a level below Calendar Semester and Calendar Year. If you disregard Fiscal attributes in Calendar hierarhy, all attributes are related as the should - Calendar Semester inside Calendar Quarter level, Calendar Year inside Calendar Semester level. Others below are not important now.

     

    Relations are 1:N meaning one year has N semesters, one semester has N quarters, etc. But, it can be interpreted vice versa. One quarter has only one semester he belongs to, and likewise, one semester has only one year above.

     

    Now, what happens if we introduce Fiscal attributes?

     

    Calendar Semester and Calendar Quarter have appropriate fiscal versions as their related attributes. Notice Fiscal Quarter. He is positioned under Calendar Quarter. In my oppinion, he shouldn't be there, but on month level.

     

    ... here I tried to redesign Date dimension, but without any luck regarding multiple members ...

     

    I continue to change Date dimension, but it ended up that that error notified me about same thing, sometimes on fiscal, sometimes on calendar attributes. I even deleted fiscal hierarchy and all fiscal attributes, yet still it gives errors on calendar semester of year. Ups, that's it. Another one positioned wrong. Hm, ...

     

    I don't know why are attributes related that way in AW. I mean, I always use 1:N for levels, but all other attributes go to their native level. Meaning Month in Year goes to Month level, Semester of Year to Semester level. Although they could go below, it's not the way I make them. They fit at best either on their level or on leaf (if their level is not in that hierarchy, namely weeks). That's how I think and do in projects.

     

    ... even after this modification no luck - multiple years error ...

     

    I stopped with Date dimension modification with conclusion that you just can not have set with multiple ancestors on tuple's level crossjoined with a tuple in slicer. Without a tuple ok, but tuple and set with multiple ancestors - no go.

     

     Darren Gosbell wrote:

     Tomislav Piasevoli wrote:

    Query 2.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Month].&[2003]&[4]}

    *

    {[Date].[Calendar Quarter of Year].&[CY Q1]}

    --([Date].[Calendar Year].&[2003])

     

     

    When you run Query 2, you'll receive (null), (null) in SSAS 2005, and {}, (null) in SSAS 2008. If you change parenthesis of CY Q1 from {} to (), you'll receive (null) also in SSAS 2008 instead of {} as in first shot. But, if you comment that set and uncomment the one below, you will get your M4 as expected. On both SSAS versions. How come the first two shots returned nulls?

    This is the autoexists kicking in. When SSAS built the date dimension, it knows that [CY Q1] only exists with months 1-3, therefore crossjoining month 4 with Q1 will end up with an empty set. When you crossjoin 2003 and April 2003 SSAS knows that they exist together.

     

    My mistake. Tested too many things, so it slipped me (I was mislead that quarter has 4 months). Perfectly understandable.

     

     Darren Gosbell wrote:

     Tomislav Piasevoli wrote:

    I did noticed similar things (not that about Fiscal year though) on other dimensions, namely Customers.

     

    Query 3.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Customer].[Customer Geography].[City])

    SELECT

    { X, [Measures].[Internet Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Customer].[Customer Geography].[City].&[Paris]&[75],

     [Customer].[Customer Geography].[City].&[San Francisco]&[CA],

     [Customer].[Customer Geography].[City].&[Los Angeles]&[CA]}

    *

    {[Customer].[Country].&[United States]}

    --([Customer].[Country].&[United States])

     

     

    The above query returns SF and LA (cities). Also on both SSAS versions. But if you comment USA country and uncomment USA country with () prenthesis, you'll receive all cities in USA.

     

    To summarize, what exactly is going on in slicer during query evaluation? How do {} and () differ there (if you move slicer part to an axis (rows for example), it behaves as expected, no strange things for any query demonstrated)?

    Still playing with this one, but I don't even have a theory on it yet.

     

    Query 3 behaviour is also related to Query 1 issue. I think that it goes like this.

     

    If you provide a tuple (something in () brackets), it has higher priority than any set in slicer, meaning everything else is submerged to that tuple. If we have no tuple, we have normal set intersection that somehow comes out (is evaluated) as a final tuple for slicer.

     

    Now, we have several scenarios. In Query 1, set was 2 levels below tuple and it had only one ancestor in tuple's member level (year 2003). In that scenario, engine takes first member of the set and places it inside a tuple. I think it overrides existing (All) quarter's member in tuple with first item in that set. Constructed, final tuple is then evaluated as 3 months. In case we don't provide a tuple, but rather 2 sets, engine performs autoexists between them and we get 6 months. That's also how SSAS 2008 works, I guess. It doesn't prefer tuples over sets but rather converts everything to sets and then the result of intersection goes as a tuple in slicer. Does anyone in forum know of any changes in engine concerning slicer? Was SSAS 2005's behaviour feature or a bug?

     

    In modification of Query 1, when it has 2 years in set (quarters of 2 different years), we have problems. I didn't refer there to any currentmember. Engine did, perhaps. I also found that mentioning of fiscal attribute is only by circumstance that it failed on that attribute first while generating overriding members for that tuple. When I removed fiscal attributes and hierarchy, it failed on calendar year/semester, which is the first attribute above quarters in hierarchy that has more than one member evaluated as ancestor of provided set of quarters. So, you were right. It's about attribute relations. Yet, how come engine doesn't perform autoexists with tuple here? Like Except(SET, {tuple}) or NonEmpty(SET * {tuple}). It does so with sets, as I stated before. It all points out that tuple in slicer is treated special, as a base for final tuple, not as a part equal to sets in evaluation of slicer content. Why so?

     

    Query 2 you solved.

     

    Query 3 is similar to Query 1. We have set and a tuple. But in Query 1 first member in set survives, while in Query 3 no member from set influences that tuple - we always get all cities. How come?

     

    ... after some time a light turn on ...

     

    I think I got the answer. I found it by coincidence, after thinking about why all and not few. When does that happen, when do we get all members ...

     

    To give proper explaination I need Dimension Usage tab and Cube structure tab. Notice that default measure for AW cube is Reseller Sales Amount. Now, if you go to Dimension Usage tab, you'll see that measure group is not related to Customer dimension. Ring a bell? Of course.

     

    What that tuple does is it includes default cube measure. Always. In query 1 and query 3 and any query with tuple in slicer (when no measure is specified inside it). That's why we get all customers regardless of set crossjoined with tuple. The default measure survives and set has no influence on tuple, meaning they all come out (because IgnoreUnrelatedDimension is set so). Now, that wasn't the thing with Date dimension, since it is included in all measure groups, most important - Reseller sales where that default measure is.

     

    Wow, now, that spinnes a head, doesn't it?

     

    If we wanted to specify another measure in tuple, it wouldn't go easy. Measures are already on columns and we can not make any simple join or tuple that would force evaluation on another measure. Yet I tested it so, I'll provide an example as how it could be written if needed:

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Customer].[Customer Geography].[City])

    SELECT

    { X, [Measures].[Internet Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    NonEmpty

    ({[Customer].[Customer Geography].[State-Province].&[VIC]&[AU]} *

     {[Customer].[Country].&[Australia]},

     {[Measures].[Internet Sales Amount]})

     

 

That gives cities in your state/province - Victoria (Australia). And appropriate amount for them.

 

 

 

I think we have something. Let's sumarize.

 

Query 1 behaviour (taking only first itme in set) is either feature or bug of SSAS 2005. It needs verification from Microsoft. SSAS 2008 on the other hand behaves correctly.

Query 2 is expected behaviour of autoexists - attributes intersect and only resulting set survives.

Query 3 is same as Query 1 but with default measure not related to dimesnion used in set. Hence the set had no influence and they all came out.

 

Ok, not bad. One out of three not determined completely and a few things learned by the way.

 

Thank you for your effort so far. I'll leave this topic unanswered few more days or until your next post (I'm currious whether you have some more comments), so that you or/and anybody else can add their thoughts to it. After that I marking your (and this) post as answered because answer is (so far) distributed among these two posts.

 

PS: I left the pictures in case we'll discuss it later.

 

Best regards,

 

 

Tomislav Piasevoli

Business Intelligence specialist

http://www.softpro.hr

Thursday, September 25, 2008 12:52 AM
Answerer

All replies

  •  Tomislav Piasevoli wrote:

    I made few MDX queries for the purpose of solving puzzle: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3904810&SiteID=17 . During that, I noticed some strange behaviours which I can not interpret. So I'm asking for your help.

     

    Query 1.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Calendar Quarter].&[2003]&[4],

     [Date].[Calendar].[Calendar Quarter].&[2003]&[3]}

    *

    ([Date].[Calendar Year].&[2003])

     

     

    If you run this query on SSAS 2008, you'll get 6 months as expected (2003 M7 - M12). But if you run it on SSAS 2005, you'll get only 3. Which one? It depends on what comes first! I don't consider that expected behaviour.

     

    For given query, you'll receive M10 - M12, but if you switch quarters, you'll get M7 - M9. And if you replace "4" with "5" (quarter key), then it will take first good after that (since there is no 5th quarter, and option for MDX member errors is in default state for AW demo cubes), and that is 3rd quarter. So, we get M7 - M9 again. That goes for SSAS 2005 and SSAS 2008 and it's expected behaviour.

    It does look like a bug in SSAS 2005, I don't have a good explanation for this one.

     

     Tomislav Piasevoli wrote:

    But what if we change year key? If we say &[2002]&[4] instead of &[2003]&[4] (meaning, we go one year back for the first member), we get an error. On both SSAS versions. I'll copy-paste it here:

     

    Executing the query ...

    The MDX function failed because the coordinate for the 'Fiscal Year' attribute contains a set.

    Execution complete

     

    I bolded a part that is refering to something not available in the query - Fiscal Year. I don't have an explaination for it, do any of you readers of this post do?

    The change you made to the slicer means that because of the attribute relationships you have implied the setting of a set of Fiscal years, this is what is know as attribute overwriting. When you have two Fiscal years in the current context the references to [Date].[Fiscal].CurrentMember fail with the above error, because there is no one single "current" member.

     

     Tomislav Piasevoli wrote:

    Query 2.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Month].&[2003]&[4]}

    *

    {[Date].[Calendar Quarter of Year].&[CY Q1]}

    --([Date].[Calendar Year].&[2003])

     

     

    When you run Query 2, you'll receive (null), (null) in SSAS 2005, and {}, (null) in SSAS 2008. If you change parenthesis of CY Q1 from {} to (), you'll receive (null) also in SSAS 2008 instead of {} as in first shot. But, if you comment that set and uncomment the one below, you will get your M4 as expected. On both SSAS versions. How come the first two shots returned nulls?

    This is the autoexists kicking in. When SSAS built the date dimension, it knows that [CY Q1] only exists with months 1-3, therefore crossjoining month 4 with Q1 will end up with an empty set. When you crossjoin 2003 and April 2003 SSAS knows that they exist together.

     

     Tomislav Piasevoli wrote:

    I did noticed similar things (not that about Fiscal year though) on other dimensions, namely Customers.

     

    Query 3.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Customer].[Customer Geography].[City])

    SELECT

    { X, [Measures].[Internet Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Customer].[Customer Geography].[City].&[Paris]&[75],

     [Customer].[Customer Geography].[City].&[San Francisco]&[CA],

     [Customer].[Customer Geography].[City].&[Los Angeles]&[CA]}

    *

    {[Customer].[Country].&[United States]}

    --([Customer].[Country].&[United States])

     

     

    The above query returns SF and LA (cities). Also on both SSAS versions. But if you comment USA country and uncomment USA country with () prenthesis, you'll receive all cities in USA.

     

    To summarize, what exactly is going on in slicer during query evaluation? How do {} and () differ there (if you move slicer part to an axis (rows for example), it behaves as expected, no strange things for any query demonstrated)?

    Still playing with this one, but I don't even have a theory on it yet.

     

    Wednesday, September 24, 2008 11:29 AM
    Moderator
  • Hi Darren,

     

    thank you very much for taking the time to test this queries I have and for sharing your thoughts on this subject.

     

    Here's what I think.

     

     Darren Gosbell wrote:

     Tomislav Piasevoli wrote:

    Query 1.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Calendar Quarter].&[2003]&[4],

     [Date].[Calendar].[Calendar Quarter].&[2003]&[3]}

    *

    ([Date].[Calendar Year].&[2003])

     

     

    If you run this query on SSAS 2008, you'll get 6 months as expected (2003 M7 - M12). But if you run it on SSAS 2005, you'll get only 3. Which one? It depends on what comes first! I don't consider that expected behaviour.

     

    For given query, you'll receive M10 - M12, but if you switch quarters, you'll get M7 - M9. And if you replace "4" with "5" (quarter key), then it will take first good after that (since there is no 5th quarter, and option for MDX member errors is in default state for AW demo cubes), and that is 3rd quarter. So, we get M7 - M9 again. That goes for SSAS 2005 and SSAS 2008 and it's expected behaviour.

    It does look like a bug in SSAS 2005, I don't have a good explanation for this one.

     

    I forgot to tell you last time, if you enclose year 2003 in {} like this {([Date].[Calendar Year].&[2003])}, then you get expected behaviour, all 6 months.

     

    It is either a bug or a feature in SSAS 2005. I recently posted 2 questions on Connect (not about this subject), but haven't got any reply since. Is that normal? I ask because I haven't used Connect before. Maybe I should wait more. I can post them here also, but I thought better to ask them since I read in few posts it's the way we should do when we encounter an issue. So, what shall I do with behaviour of this query, go to Connect again or wait until someone here explains it?

     

     Darren Gosbell wrote:
     Tomislav Piasevoli wrote:

    But what if we change year key? If we say &[2002]&[4] instead of &[2003]&[4] (meaning, we go one year back for the first member), we get an error. On both SSAS versions. I'll copy-paste it here:

     

    Executing the query ...

    The MDX function failed because the coordinate for the 'Fiscal Year' attribute contains a set.

    Execution complete

     

    I bolded a part that is refering to something not available in the query - Fiscal Year. I don't have an explaination for it, do any of you readers of this post do?

    The change you made to the slicer means that because of the attribute relationships you have implied the setting of a set of Fiscal years, this is what is know as attribute overwriting. When you have two Fiscal years in the current context the references to [Date].[Fiscal].CurrentMember fail with the above error, because there is no one single "current" member.

     

    Hm, I think it's definitely a question of multiple members because of attribute relations. But, is this a Date dimension design issue or expected thing to happen? Let me provide some pictures of AW 2005 Date hierarchies.

     

    Picture 1 - AW_2005_Date_Hierarchies.png

    Picture 2 - AW_2005_Date_Hierarchies_Tree.png

     

    Let's have a look at only Calendar hierarchy at the moment.

     

    Calendar quarter, in MDX example and pictures above, is a level below Calendar Semester and Calendar Year. If you disregard Fiscal attributes in Calendar hierarhy, all attributes are related as the should - Calendar Semester inside Calendar Quarter level, Calendar Year inside Calendar Semester level. Others below are not important now.

     

    Relations are 1:N meaning one year has N semesters, one semester has N quarters, etc. But, it can be interpreted vice versa. One quarter has only one semester he belongs to, and likewise, one semester has only one year above.

     

    Now, what happens if we introduce Fiscal attributes?

     

    Calendar Semester and Calendar Quarter have appropriate fiscal versions as their related attributes. Notice Fiscal Quarter. He is positioned under Calendar Quarter. In my oppinion, he shouldn't be there, but on month level.

     

    ... here I tried to redesign Date dimension, but without any luck regarding multiple members ...

     

    I continue to change Date dimension, but it ended up that that error notified me about same thing, sometimes on fiscal, sometimes on calendar attributes. I even deleted fiscal hierarchy and all fiscal attributes, yet still it gives errors on calendar semester of year. Ups, that's it. Another one positioned wrong. Hm, ...

     

    I don't know why are attributes related that way in AW. I mean, I always use 1:N for levels, but all other attributes go to their native level. Meaning Month in Year goes to Month level, Semester of Year to Semester level. Although they could go below, it's not the way I make them. They fit at best either on their level or on leaf (if their level is not in that hierarchy, namely weeks). That's how I think and do in projects.

     

    ... even after this modification no luck - multiple years error ...

     

    I stopped with Date dimension modification with conclusion that you just can not have set with multiple ancestors on tuple's level crossjoined with a tuple in slicer. Without a tuple ok, but tuple and set with multiple ancestors - no go.

     

     Darren Gosbell wrote:

     Tomislav Piasevoli wrote:

    Query 2.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Date].[Calendar].[Month])

    SELECT

    {X, [Measures].[Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Date].[Calendar].[Month].&[2003]&[4]}

    *

    {[Date].[Calendar Quarter of Year].&[CY Q1]}

    --([Date].[Calendar Year].&[2003])

     

     

    When you run Query 2, you'll receive (null), (null) in SSAS 2005, and {}, (null) in SSAS 2008. If you change parenthesis of CY Q1 from {} to (), you'll receive (null) also in SSAS 2008 instead of {} as in first shot. But, if you comment that set and uncomment the one below, you will get your M4 as expected. On both SSAS versions. How come the first two shots returned nulls?

    This is the autoexists kicking in. When SSAS built the date dimension, it knows that [CY Q1] only exists with months 1-3, therefore crossjoining month 4 with Q1 will end up with an empty set. When you crossjoin 2003 and April 2003 SSAS knows that they exist together.

     

    My mistake. Tested too many things, so it slipped me (I was mislead that quarter has 4 months). Perfectly understandable.

     

     Darren Gosbell wrote:

     Tomislav Piasevoli wrote:

    I did noticed similar things (not that about Fiscal year though) on other dimensions, namely Customers.

     

    Query 3.

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Customer].[Customer Geography].[City])

    SELECT

    { X, [Measures].[Internet Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    {[Customer].[Customer Geography].[City].&[Paris]&[75],

     [Customer].[Customer Geography].[City].&[San Francisco]&[CA],

     [Customer].[Customer Geography].[City].&[Los Angeles]&[CA]}

    *

    {[Customer].[Country].&[United States]}

    --([Customer].[Country].&[United States])

     

     

    The above query returns SF and LA (cities). Also on both SSAS versions. But if you comment USA country and uncomment USA country with () prenthesis, you'll receive all cities in USA.

     

    To summarize, what exactly is going on in slicer during query evaluation? How do {} and () differ there (if you move slicer part to an axis (rows for example), it behaves as expected, no strange things for any query demonstrated)?

    Still playing with this one, but I don't even have a theory on it yet.

     

    Query 3 behaviour is also related to Query 1 issue. I think that it goes like this.

     

    If you provide a tuple (something in () brackets), it has higher priority than any set in slicer, meaning everything else is submerged to that tuple. If we have no tuple, we have normal set intersection that somehow comes out (is evaluated) as a final tuple for slicer.

     

    Now, we have several scenarios. In Query 1, set was 2 levels below tuple and it had only one ancestor in tuple's member level (year 2003). In that scenario, engine takes first member of the set and places it inside a tuple. I think it overrides existing (All) quarter's member in tuple with first item in that set. Constructed, final tuple is then evaluated as 3 months. In case we don't provide a tuple, but rather 2 sets, engine performs autoexists between them and we get 6 months. That's also how SSAS 2008 works, I guess. It doesn't prefer tuples over sets but rather converts everything to sets and then the result of intersection goes as a tuple in slicer. Does anyone in forum know of any changes in engine concerning slicer? Was SSAS 2005's behaviour feature or a bug?

     

    In modification of Query 1, when it has 2 years in set (quarters of 2 different years), we have problems. I didn't refer there to any currentmember. Engine did, perhaps. I also found that mentioning of fiscal attribute is only by circumstance that it failed on that attribute first while generating overriding members for that tuple. When I removed fiscal attributes and hierarchy, it failed on calendar year/semester, which is the first attribute above quarters in hierarchy that has more than one member evaluated as ancestor of provided set of quarters. So, you were right. It's about attribute relations. Yet, how come engine doesn't perform autoexists with tuple here? Like Except(SET, {tuple}) or NonEmpty(SET * {tuple}). It does so with sets, as I stated before. It all points out that tuple in slicer is treated special, as a base for final tuple, not as a part equal to sets in evaluation of slicer content. Why so?

     

    Query 2 you solved.

     

    Query 3 is similar to Query 1. We have set and a tuple. But in Query 1 first member in set survives, while in Query 3 no member from set influences that tuple - we always get all cities. How come?

     

    ... after some time a light turn on ...

     

    I think I got the answer. I found it by coincidence, after thinking about why all and not few. When does that happen, when do we get all members ...

     

    To give proper explaination I need Dimension Usage tab and Cube structure tab. Notice that default measure for AW cube is Reseller Sales Amount. Now, if you go to Dimension Usage tab, you'll see that measure group is not related to Customer dimension. Ring a bell? Of course.

     

    What that tuple does is it includes default cube measure. Always. In query 1 and query 3 and any query with tuple in slicer (when no measure is specified inside it). That's why we get all customers regardless of set crossjoined with tuple. The default measure survives and set has no influence on tuple, meaning they all come out (because IgnoreUnrelatedDimension is set so). Now, that wasn't the thing with Date dimension, since it is included in all measure groups, most important - Reseller sales where that default measure is.

     

    Wow, now, that spinnes a head, doesn't it?

     

    If we wanted to specify another measure in tuple, it wouldn't go easy. Measures are already on columns and we can not make any simple join or tuple that would force evaluation on another measure. Yet I tested it so, I'll provide an example as how it could be written if needed:

     

    Code Snippet

    WITH

    MEMBER X AS

    SetToStr(Existing [Customer].[Customer Geography].[City])

    SELECT

    { X, [Measures].[Internet Sales Amount]} on 0

    FROM

    [Adventure Works]

    WHERE

    NonEmpty

    ({[Customer].[Customer Geography].[State-Province].&[VIC]&[AU]} *

     {[Customer].[Country].&[Australia]},

     {[Measures].[Internet Sales Amount]})

     

 

That gives cities in your state/province - Victoria (Australia). And appropriate amount for them.

 

 

 

I think we have something. Let's sumarize.

 

Query 1 behaviour (taking only first itme in set) is either feature or bug of SSAS 2005. It needs verification from Microsoft. SSAS 2008 on the other hand behaves correctly.

Query 2 is expected behaviour of autoexists - attributes intersect and only resulting set survives.

Query 3 is same as Query 1 but with default measure not related to dimesnion used in set. Hence the set had no influence and they all came out.

 

Ok, not bad. One out of three not determined completely and a few things learned by the way.

 

Thank you for your effort so far. I'll leave this topic unanswered few more days or until your next post (I'm currious whether you have some more comments), so that you or/and anybody else can add their thoughts to it. After that I marking your (and this) post as answered because answer is (so far) distributed among these two posts.

 

PS: I left the pictures in case we'll discuss it later.

 

Best regards,

 

 

Tomislav Piasevoli

Business Intelligence specialist

http://www.softpro.hr

Thursday, September 25, 2008 12:52 AM
Answerer
  •  

    Well,

     

    since there are no more comments, I consider this topic closed. Issue about SSAS 2005 behaviour still remains unclear as whether it's a bug or a feature. Until someone demistifies that, it's documented here so you all can be aware of it.

     

    Once again, thanks Darren for your time,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Friday, September 26, 2008 10:58 PM
    Answerer