Chris Webb's BI Blog

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

Subselects and Calculated Members in R2

with 48 comments

As Darren noted the other week, there was a recent thread on the MSDN Forum that detailed the few enhancements for traditional SSAS users in SQL 2008 R2. I thought I’d pick up on the one MDX-related change, which is to do with calculated members and subselects. From a pure language point of view you’d be right in thinking that this doesn’t sound all that exciting, but it does fix one long-running issue that has caused a lot of people a lot of pain over the last couple of years – namely the way that Excel can’t handle calculated members on non-measures dimension. For a bit of background, see:
http://sqlblog.com/blogs/marco_russo/archive/2007/01/31/excel-2007-pivottable-with-calculated-members.aspx
http://sqlblog.com/blogs/marco_russo/archive/2007/03/07/ssas-2005-sp2-breaks-excel-calculated-member-selection.aspx
http://sqlblog.com/blogs/marco_russo/archive/2008/12/08/ssas-2008-calculated-members-still-don-t-love-excel-2007.aspx
http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

If you’re using Excel 2010 with Analysis Services 2008 R2 you’ll now be able to filter on individual calculated members again. Frankly, it’s a disgrace that it’s taken this long to fix and that you have to use Office 2010 and R2 before it works (especially when every other client tool worth its salt does not have this problem), but I suppose we should be grateful that it at least has been fixed.

Anyway, let’s take a look at how this change has actually been implemented. First of all, you only get the new behaviour when you use the Subqueries connection string property. It’s been possible to set Subqueries=1, which allows the use of calculated members in subselects, since SSAS 2008 (subqueries=0, which is the default, does not allow this) but I’m told this didn’t solve all of the Excel team’s problems; therefore the new setting Subqueries=2 was introduced in R2.

Now let’s add a calculated member to the Adventure Works cube as follows:

CREATE MEMBER CURRENTCUBE.[Date].[Calendar].[Calendar Year].&[2004].CALC1
AS 111;

As you can see, it’s on the Calendar hierarchy of the Date dimension, on the Calendar Semester level underneath the year 2004.

If we run the following query with no special connection string properties set:

SELECT {[Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].[Calendar Semester].ALLMEMBERS
ON 1
FROM
(SELECT
[Date].[Calendar].[Calendar Year].&[2004].[CALC1]
ON 0
FROM [Adventure Works])

We get the error “A set has been encountered that cannot contain calculated members”. However, as you would expect, when you set Subqueries=1 or Subqueries=2 the query runs successfully and you get the following results:

image

Now, if we change the query to ask for all the members at the Year level as follows:

SELECT {[Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS
ON 1
FROM
(SELECT
[Date].[Calendar].[Calendar Year].&[2004].[CALC1]
ON 0
FROM [Adventure Works])

When we use Subqueries=1 we get an empty set returned on rows:

image

When we use Subqueries=2 we get the year 2004, the parent of the calculated member, and a null for the measure value:

image

Why the null? Although there is data for 2004 in the cube, in our subselect we’ve only included a calculated member child of 2004, and calculated members’ values don’t aggregate up to their parents.

For the following query, where the year is in the subselect and the semesters are on rows:

SELECT {[Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].[Calendar Semester].ALLMEMBERS
ON 1
FROM
(SELECT
[Date].[Calendar].[Calendar Year].&[2004]
ON 0
FROM [Adventure Works])

For both Subqueries=1 and Subqueries=2 you get the following result:

image

Interestingly, if you include a Year and Semester in the subselect as follows:

SELECT {[Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].[Calendar Semester].ALLMEMBERS
ON 1
FROM
(SELECT
{[Date].[Calendar].[Calendar Year].&[2004],
[Date].[Calendar].[Calendar Semester].&[2004]&[2]}
ON 0
FROM [Adventure Works])

For Subqueries=1 you get this:

image

And for Subqueries=2 you get this:

image

I won’t go on (though there are more examples I could give) because I’m sure no-one outside the Excel team will ever care about any of this, but it’s interesting to note nonetheless and I doubt it will ever get properly documented anywhere. I’ve also been told there are some changes to how the DrillDownX family of functions work with regard to calculated members in R2, but I’ll save that for another post.

Written by Chris Webb

February 18, 2010 at 11:15 pm

Posted in MDX

48 Responses

Subscribe to comments with RSS.

  1. that is great news! I run into this daily with the use of shell date calculation dimensions. I have had to change how I implement these sort of calculations if people use Excel 2007. So now I guess I can tell people that non measure calculations work in Excel 2003 and 2010 but not 2007. I guess 2007 was a bad year….. :>)

    David

    February 19, 2010 at 3:33 am

  2. [...] a comment » Earlier this year I blogged about one of the few MDX-related changes in SSAS 2008 R2, to do with the Subqueries=2 connection [...]

  3. I spotted this post through a google search on the issue..I completely agree with the ‘disgrace’ comment you make about Microsoft..7 years for a fix? Just crazy.

    kodaz6n

    July 21, 2011 at 12:49 am

  4. Hi man,
    Could you clarify how did you set the new connection string property in Excel?
    I’ve tried to set it, but Excel doesn’t save this changes.
    I use 2008 R2 + SP1 and Office 2010.

    Thanks

    Valeriy

    August 16, 2011 at 11:44 am

    • You might need to edit the connection string in the .odc file itself.

      Chris Webb

      August 18, 2011 at 8:15 pm

  5. hello,

    There is this know problem that when you filter for example on a date, that the SUM of individual records are not equal to the Grand total. This happens only with calculated members.
    We tried this on excel 2010 but this is still the case? Is there a good working solution with multiple select in conjunction with calculated members?

    thx,

    Paul.

    Paul

    September 16, 2011 at 11:21 am

    • Hi Paul,

      I’m not sure exactly what scenario you’re talking about here, but it sounds like you’ve run into the fact that calculated measures ‘ignore’ subselects in some cases. It’s useful in some cases and a pain in others, but it’s expected behaviour and won’t change.

      Regards,

      Chris

      Chris Webb

      September 16, 2011 at 11:33 am

      • hi Cris,

        I cant believe that End user would accept that the sum of individual lines are not equal to the grand total.
        Infact customers pointed this error out to us, and to be honest it is kinda embarrazing to be pointed out on such behaviour of MDX and Excel.

        rgrds

        Paul.

        Paul

        September 26, 2011 at 11:42 am

      • Hi Chris,

        We have 2 servers, one running SQL 2008 SP1 and the other running SQL Server 2008 R2. We are getting 2 different set of results when connecting from Excel 2007. I have installed OLAP pivottable extensions and the MDX is exactly the same, but for some reason the results on R2 are not filtering the calculated member by the subselect. I am trying to establish if this is what is being described in your post above?

        On SQL Server 2008 SP1, the calculated member created in VBA ([All] member) is filtered by the subselect, whereas the in R2 it is the overall total and is not being filtered by the subselect. Since the MDX is being created by Excel, what are our options if this is known problem?

        Much appreciated
        Alex

        Alex

        March 6, 2012 at 3:28 pm

      • It’s highly likely this is what’s happening, although I’d need to see a more detailed repro before I could say for sure.

        Chris Webb

        March 6, 2012 at 9:04 pm

      • Hi Chris,

        Would you have any thoughts on what could cause a difference in results produced by the exact same MDX on SQL 2008 R2 and SQL 2008 as I described in my question below? The data is identical between the 2 servers. If I take the MDX that is produced by Excel and run it through query analyser I get different results. On SQL Server 2008 R2, the calculated member that is added is returning the overall total for all members in the dimension and doesn’t seem to take the subquery into consideration. On the old server, the calculated member is being filter by the subquery.

        Any suggestions?

        Alex

        March 20, 2012 at 2:05 pm

      • Hi Alex,

        As I said before, it sounds like it could be the issue described in the post but I couldn’t say for sure without spending some time looking at your query and cube and doing some tests of my own. Sorry I can’t be more specific…

        Chris

        Chris Webb

        March 20, 2012 at 2:29 pm

  6. Hi,
    In this example, if I use subqueries=0 I can get the data for members [DATE].[FISCAL WEEKS].[Sum] and MEMBER [DATE].[FISCAL WEEKS].[Agr], but, if I use another value, I get empty.

    WITH

    MEMBER [DATE].[FISCAL WEEKS].[Sum] AS ‘SUM({{([Date].[Fiscal Weeks].[Fiscal Year].&[2006], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)} ,
    {([Date].[Fiscal Weeks].[Fiscal Year].&[2007], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)} ,
    {([Date].[Fiscal Weeks].[Fiscal Year].&[2008], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)} ,
    {([Date].[Fiscal Weeks].[Fiscal Year].&[2009], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)}})’,SOLVE_ORDER = 0
    MEMBER [DATE].[FISCAL WEEKS].[Agr] AS ‘AGGREGATE({{([Date].[Fiscal Weeks].[Fiscal Year].&[2006], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)} ,
    {([Date].[Fiscal Weeks].[Fiscal Year].&[2007], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)} ,
    {([Date].[Fiscal Weeks].[Fiscal Year].&[2008], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)} ,
    {([Date].[Fiscal Weeks].[Fiscal Year].&[2009], [DATE].[CALENDAR WEEKS].CURRENTMEMBER, [DATE].[CALENDAR YEAR].CURRENTMEMBER)}})’,SOLVE_ORDER = 0

    SELECT
    DrilldownMember({{ [DATE].[FISCAL WEEKS].[Agr] } ,
    { [DATE].[FISCAL WEEKS].[Sum] } ,
    {[Date].[Fiscal Weeks].[Fiscal Year].&[2006]} ,
    {[Date].[Fiscal Weeks].[Fiscal Year].&[2007]} ,
    {[Date].[Fiscal Weeks].[Fiscal Year].&[2008]} ,
    {[Date].[Fiscal Weeks].[Fiscal Year].&[2009]}},
    {[Date].[Fiscal Weeks].[All Periods]}) ON COLUMNS,
    DrilldownMember({[DATE].[CALENDAR WEEKS]},{[Date].[Calendar Weeks].[All Periods]}) ON ROWS
    FROM (
    SELECT ({{[Date].[Calendar Year].&[2005]} ,
    {[Date].[Calendar Year].&[2006]} ,
    {[Date].[Calendar Year].&[2007]} ,
    {[Date].[Calendar Year].&[2008]} ,
    {[Date].[Calendar Year].&[2010]}}) ON COLUMNS
    FROM [Adventure Works] )
    WHERE ([Measures].[Sales Amount] )

    Tks,
    Manfred.

    Manfred Mejías

    November 15, 2011 at 5:02 pm

    • Hi Manfred,

      This query looks like it’s a lot more complicated than it needs to be. What are you trying to achieve here? Do you really need to include the Currentmember on the Calendar Weeks and Calendar Year hierarchy everywhere (it could make a difference to the values because of attribute overwrite in some cases)?

      Chris

      Chris Webb

      November 16, 2011 at 10:17 pm

      • Hi Chris.

        I was trying without the currentmember on the Calendar Weeks and Calendar Year and got the same values. So, I don’t if the behaviour of the subqueries is correct.

        Thks,

        Manfred

        Manfred Mejías

        November 21, 2011 at 7:09 pm

      • Ah, OK. Is this a real-world scenario though? If so, then I suspect there’s a much easier way to write this query which may well behave differently with subselects.

        Chris Webb

        November 21, 2011 at 8:22 pm

      • Hi Chris.

        This is a real-world scenario. Users can select different members and they can create new calculated members using one wizard. Yesterday, I got a new case, so I “googled” about the autoexists and attribute relationship. In this case, if the user uses the member [Bikes + Accessories] (it has .currentmeber) he/she gets the correct result, but, if he/she uses [Bikes + Accessories with out .currentmember], he/she gets an incorrect result.

        WITH

        MEMBER [PRODUCT].[PRODUCT CATEGORIES].[Bikes + Accessories] AS ‘([Product].[Product Categories].[Category].&[1], [PRODUCT].[PRODUCT].CURRENTMEMBER, [PRODUCT].[STYLE].CURRENTMEMBER) + (([Product].[Product Categories].[Category].&[4], [PRODUCT].[PRODUCT].CURRENTMEMBER, [PRODUCT].[STYLE].CURRENTMEMBER))’,

        SOLVE_ORDER = 0

        MEMBER [PRODUCT].[PRODUCT CATEGORIES].[Bikes + Accessories with out .currentmember] AS ‘([Product].[Product Categories].[Category].&[1]) + (([Product].[Product Categories].[Category].&[4]))’,

        SOLVE_ORDER = 0

        SELECT

        {[Product].[Style].[Style].MEMBERS} ON COLUMNS,

        NON EMPTY DrilldownMember({[Product].[Product].[Product].MEMBERS},{[Product].[Product].[All Products]}) ON ROWS

        FROM [Adventure Works]

        WHERE ( [PRODUCT].[PRODUCT CATEGORIES].[Bikes + Accessories with out .currentmember] ,[Measures].[Internet Sales Amount] )

        If I rewrite the calculated member using the attribute (the original one was using the hierarchy) it works without .currentmember

        MEMBER [PRODUCT].[CATEGORY].[Bikes + Accessories with out .currentmember] AS ‘([Product].[Category].&[1]) + (([Product].[Category].&[4]))’,

        Manfred Mejías

        December 8, 2011 at 2:47 pm

  7. Very handy post – thanks ! I just wanted to say that I have found (with Excel 2010 and R2) that I can filter by calculated members even if I don’t have Subqueries=2 (or any reference to Subqueries at all) in the connection, for what that’s worth. It even correctly filters the calculated member when I filter by a different dimension. If possible I’d be interested in more concrete info on when this setting is in fact necessary. The only (potential) difference I can see from your example is that my calculated member is defined in the cube script, while yours may have been defined with session scope in your query (it’s not clear where you defined it).

    Mark F

    April 18, 2012 at 3:32 pm

    • Hi Mark,

      I’m pretty sure that with Excel 2010 and R2 this connection string property is applied by default so that explains why you don’t see anything in Excel when you look. The setting is only necessary when you’re building your own applications that connect to SSAS.

      Chris

      Chris Webb

      April 18, 2012 at 3:45 pm

  8. [...] The blog post that gave me the clue was from Chris Webb: Subselects and Calculated Members in R2 [...]

    • same kind of sinario i ma not getting the answer please help me on this task
      PROPERTY I SET AS Subqueries=1
      this my caliculated member
      CREATE MEMBER CURRENTCUBE.[Measures].[ER Visits]
      AS AGGREGATE(
      {
      (
      [Admission].[Admission Type Description].&[ACUTE]
      ,[Admission].[ER Admission Indicator].&[Y]
      )
      ,
      (
      [Admission].[Admission Type Description].&[ER]
      ,[Admission].[ER Admission Indicator].[All]
      )
      }
      ,[Measures].[Admission Count]
      ),

      AND MY QUERY IS

      SELECT {[Measures].[ER Visits]} on columns ,
      ( [Accountable Physician Grouping].[Level1 Abbrev].[Level1 Abbrev].ALLMEMBERS )
      on rows FROM (SELECT {[ER Visits]} on columns ,
      ( ({[Accountable Physician Grouping].[Level1 Abbrev].&[DW Unknown],
      [Accountable Physician Grouping].[Level1 Abbrev].&[KY - Essence LLP],
      [Accountable Physician Grouping].[Level1 Abbrev].&[MO - Essence LLP]}) ) on rows FROM [LumerisDW]) WHERE ([Client].[Client ID].&[2],
      [Client].[Client Sub ID].&[NA],
      [Member Security Group].[Member Security Group Code].&[UMEM2],
      [Provider Security Group].[Provider Security Group Code].&[UPROV2] )

      THIS HAVE DATA I MA GETTING NULLS
      PROPERTY I SET AS

      ER Visits
      DW Unknown (null)
      KY – Essence LLP (null)
      MO – Essence LLP (null)

      SO PLEASE HELP ON THIS

      prashanth

      March 7, 2013 at 11:23 am

  9. same kind of sinario I AM not getting the answer please help me on this task
    PROPERTY I SET AS Subqueries=1
    this my caliculated member
    CREATE MEMBER CURRENTCUBE.[Measures].[ER Visits]
    AS AGGREGATE(
    {
    (
    [Admission].[Admission Type Description].&[ACUTE]
    ,[Admission].[ER Admission Indicator].&[Y]
    )
    ,
    (
    [Admission].[Admission Type Description].&[ER]
    ,[Admission].[ER Admission Indicator].[All]
    )
    }
    ,[Measures].[Admission Count]
    ),

    AND MY QUERY IS

    SELECT {[Measures].[ER Visits]} on columns ,
    ( [Accountable Physician Grouping].[Level1 Abbrev].[Level1 Abbrev].ALLMEMBERS )
    on rows FROM (SELECT {[ER Visits]} on columns ,
    ( ({[Accountable Physician Grouping].[Level1 Abbrev].&[DW Unknown],
    [Accountable Physician Grouping].[Level1 Abbrev].&[KY - Essence LLP],
    [Accountable Physician Grouping].[Level1 Abbrev].&[MO - Essence LLP]}) ) on rows FROM [LumerisDW]) WHERE ([Client].[Client ID].&[2],
    [Client].[Client Sub ID].&[NA],
    [Member Security Group].[Member Security Group Code].&[UMEM2],
    [Provider Security Group].[Provider Security Group Code].&[UPROV2] )

    THIS HAVE DATA I MA GETTING NULLS
    PROPERTY I SET AS

    ER Visits
    DW Unknown (null)
    KY – Essence LLP (null)
    MO – Essence LLP (null)

    SO PLEASE HELP ON THIS

    prashanth

    March 7, 2013 at 11:24 am

    • Hi Prashanth,

      I don’t think this issue is related to subselects at all, actually – it doesn’t look like you even need to use a subselect here. When you remove the subselect (as below) does it still return nulls?

      SELECT {[ER Visits]} on columns ,
      {[Accountable Physician Grouping].[Level1 Abbrev].&[DW Unknown],
      [Accountable Physician Grouping].[Level1 Abbrev].&[KY - Essence LLP],
      [Accountable Physician Grouping].[Level1 Abbrev].&[MO - Essence LLP]
      on rows
      FROM [LumerisDW]
      WHERE ([Client].[Client ID].&[2],
      [Client].[Client Sub ID].&[NA],
      [Member Security Group].[Member Security Group Code].&[UMEM2],
      [Provider Security Group].[Provider Security Group Code].&[UPROV2] )

      It’s going to be difficult to diagnose this problem I’m afraid, it could be caused by lots of different things.

      Chris Webb

      March 7, 2013 at 11:32 am

  10. Excel is the only client that can force calculated members to not ignore sub-queries? Why can’t we do this in SSMS?

    Tab

    October 24, 2013 at 9:49 pm

  11. Ah, ok, I did this, but no joy. I guess my goal is outside the scope of this article’s focus.

    What I’m trying to get is to have the results of this query be limited by my sub-query, but instead it ignores the subquery and gives me the same results as if I had no subquery at all:

    select
    {[Measures].[Extended Service Count]} on 0
    , {[Organization].[Org Tree].[All]} on 1
    FROM (
    SELECT
    {(
    { //construct entity filter
    [Organization].[Org Tree].&[MKCN01],
    [Organization].[Org Tree].&[MKCN02],
    [Organization].[Org Tree].&[MKCN03]
    }
    )} ON 0
    FROM [MyCube]
    );

    Why do I want to do it this way? Because it’s easy to construct dynamically based on the parameters that will be passed by the app, and it’s clean and easy to read/maintain. But is it possible?

    Tab

    October 25, 2013 at 2:17 pm

    • OK, I see what you want to do – presumably Extended Service Count is a calculated measure? No, you can’t do this – subselects are always ignored by calculations, that’s just the way it is. But why not use a WHERE clause instead? Something like this:

      select
      {[Measures].[Extended Service Count]} on 0

      FROM MYCUBE
      WHERE
      (
      { //construct entity filter
      [Organization].[Org Tree].&[MKCN01],
      [Organization].[Org Tree].&[MKCN02],
      [Organization].[Org Tree].&[MKCN03]
      }
      );

      Chris Webb

      October 25, 2013 at 2:29 pm

      • ” subselects are always ignored by calculations, that’s just the way it is.” Now you see, I thought that was what you were saying was changed by Subquery=1 or 2. So it’s not?

        No, actually, Extended Service Count is a regular measure. It’s the .[All] member of the Org Tree that seems to be ignoring the subquery. There are other calculated measures in the full query though, so that’s important, too.

        I don’t use a WHERE clause because it doesn’t like that I’m using two different levels of the same hierarchy (Org Tree) in the same query:

        “The Org Tree hierarchy already appears in the Axis1 axis.”

        You see, I want to filter results at one level of the hierarchy, and then aggregate the sums at another level. And I’m generating the query dynamically, so the simpler I can make it, the better.

        Would creating a SubCube work, or would the calculated measures ignore that, too?

        Tab

        October 25, 2013 at 2:41 pm

      • This post is about using calculated members inside subselects. That’s not the same as using a calculated member in a query and having it recognise the presence of a subselect when it does its calculations.

        Maybe we need to step back a bit here. What exactly do you want to do? What do you want the All member of Org Tree to do when there is a subselect present?

        Chris Webb

        October 25, 2013 at 2:51 pm

      • Aha, well yes the difference is obvious when you put it that way. : )

        So ok, the Org Tree hierarchy is a pretty simple parent-child hierarchy. At the lowest level are stores, then markets, then regions, and finally the whole organization which doesn’t have an entry in the dimension, so we use “[All]“.

        The application allows the user to filter at any level of the hierarchy, and then group the results by any level of the hierarchy. So I need to dynamically create MDX that can say, “Get the results for Markets x & y (which may be in different regions) and show me their aggregate measures on one line (the [All] level).” Or moving in the other direction, “Get the results for Regions B & C, and show the aggregate results for each of their child markets.”

        So I might need to rollup a collection of lower level members, or I might need to breakdown a collection of higher level members. So I’m trying to create a flexible query structure, putting all my filters in a subselect, and the “Group By” on the ROWS axis. Seems to work until I get to the [All] member of the Org Tree. Then again, I hadn’t looked too closely at the values of my calculated members yet. I guess I’m going to find out they are ignoring the subselect, too.

        Tab

        October 25, 2013 at 3:05 pm

      • Hmm, I’m surprised this isn’t working, but it could be something to do with the fact that you’re using a parent/child hierarchy. Have you tried creating a calculated member on the Org hierarchy and using the Aggregate() function instead? That might work better.

        PS I’m just about to head off on vacation for a week, so I might not be able to reply again for a few days…

        Chris Webb

        October 25, 2013 at 6:22 pm

      • I did play with the idea of a calculated member and AGGREGATE(). I passed on it because it only works if I do want to return the entire resultset on one row, and I was still hoping for a single solution that works in all cases. But maybe I will have to solve this by using AGGREGATE when grouping by the entire org, and just using the straight hierarchy level when they don’t.

        Then there’s still the issue of how I’m going to filter on one level, build rows on another level, and not have my calculated members error. I guess I might have to use a big dynamic FILTER() function on each of the calculated members. I was hoping to avoid that kind of ugliness, but there may be no way around it.

        Tab

        October 25, 2013 at 7:39 pm

  12. Hi, Chris

    How can I set “subqueries=N” for SSDT project? (ms SSDT 2010 from sql server 2012 release)
    I can’t deploy it due to error of using calculation member in subcube in mdx script of cube

    Regards.

    Taras

    November 28, 2013 at 1:13 pm

    • What do you want to do here exactly? You can’t create a sub query on a cube.

      Chris Webb

      November 28, 2013 at 1:50 pm

      • CREATE SET CURRENTCUBE.[All_Julyes]
        as
        filter([Date].[Calendar].[Month].members,instr([Date].[Calendar].currentmember.name,’August’) > 0);

        scope([All_Julyes],[sales amount]); 0)
        then
        this = 1
        end if;
        end scope;

        Taras

        November 28, 2013 at 1:59 pm

      • CREATE SET CURRENTCUBE.[All_Julyes]
        as
        filter([Date].[Calendar].[Month].members,instr([Date].[Calendar].currentmember.name,’July’) > 0);

        scope([All_Julyes],[sales amount]); 0)
        then
        this = 1
        end if;
        end scope;

        Taras

        November 28, 2013 at 2:01 pm

      • Sorry, some editing errors )
        This row “scope([All_Julyes],[sales amount]); 0)” throws an exception due to using [All_Julyes] calculation named set..

        Taras

        November 28, 2013 at 2:04 pm

      • What do you want to do with the 0 in that scope statement? The only things that are allowed in a scope are set expressions or named sets. Also you have a THEN and END IF block inside your scope statement but no IF?

        Chris Webb

        November 29, 2013 at 9:18 pm

  13. Right, i was hasty in sample’s code editing )
    But nothing change in term of error sense:
    ======================================

    CREATE SET CURRENTCUBE.[All_Julyes]
    as
    filter([Date].[Calendar].[Month].members,instr([Date].[Calendar].currentmember.name,’July’) > 0);

    scope([All_Julyes],[sales amount]);
    this = 1;
    end scope;
    =========================
    Well, exception is thrown (during deployment) by this row “scope([All_Julyes],[sales amount]);”. It has russian description (crazy russian ms department :( ), but it direct translation is: “Arbitrary form of set is unacceptable in current context”.
    If I change this row with “scope([Date].[Calendar].[Month].members,[sales amount]);” – all is ok.. ([Date].[Calendar].[Month] – ordinary hierarchy)

    Taras

    December 2, 2013 at 6:05 am

    • Ah, ok. You can see an explanation of this error and why you’re getting it in this blog post: http://cwebbbi.wordpress.com/2013/05/23/using-scoped-assignments-to-show-calendar-and-financial-year-to-date-values-in-the-same-calculated-measure/
      I suggest scoping on the attribute hierarchy used to build the month level on the Calendar hierarchy, rather than the month level of the Calendar hierarchy itself. So if the attribute hierarchy was called Month, your code would be:

      CREATE SET CURRENTCUBE.[All_Julyes]
      as
      filter([Date].[Month].[Month].members,instr([Date].[Month].currentmember.name,’July’) > 0);

      scope([All_Julyes],[sales amount]);
      this = 1;
      end scope;

      Chris Webb

      December 2, 2013 at 9:20 am

      • Hmmm.. Thanks..Ok, I’ve seen your ref to the book for interesting about “An arbitrary shape of the sets is not allowed in the current context”.. And i’ve accepted this concrete rule.. But what is the logic of it rule? In case of user hierarchy we just constrict the context of scope. Is there any possible contradiction? In both cases we’ve logically pointed to concrete cube cell, isn’t it?

        Taras

        December 2, 2013 at 11:01 am

      • I mean that, yes, there can be complex cross joins, and every month will be processed by engine more than once.. But it will happen anyway, and in all cases, independently of cell address definition (due to user or attribute hierarchy), it will be one address for concrete cell in cube.. Why one of this cases cant’ be processed by engine? Where is contradiction?

        Taras

        December 2, 2013 at 11:09 am

  14. Hmm.. As discussed at this article as discussed by Mosha “Unnatural hierarchies tend to produce infamous arbitrary shape subcubes. The problem with arbitrary shapes is that they cannot be used in all contexts (for example SCOPE’s cannot deal with them and raise an error)”.. In my case [Date].[Calendar] is unnatural. Only physically, although, logically it is. Is it mean, that making it natural physically due to attributes rel-s creating, lets me use hierarchy for scope? :) Well, I try it in the nearest future, this is just thinking..
    I think it can be more comfortable and clear for code reading to use hierarchy ref on date instead of day attribute hierarchy..

    Taras

    December 2, 2013 at 2:14 pm

    • ..yes.. and I need cover “arbitrary shapes” for clearer understanding subject ))…
      ok, thanks Chris for you patience )

      Taras

      December 2, 2013 at 2:18 pm

    • Your Calendar hierarchy is unnatural? There’s no reason for that to be the case – it’s very important that you make all your hierarchies natural for performance reasons (and a Calendar hierarchy should always be natural), so I would advise that you change your attribute relationships to fix this. This will probably also solve your problems with SCOPE too.

      To answer your other question, it’s just one of those things – SSAS can only handle certain types of sets for scope statements, it’s a limitation of the engine and one that you have to work around.

      Chris Webb

      December 2, 2013 at 2:22 pm


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,070 other followers

%d bloggers like this: