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:
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:
When we use Subqueries=2 we get the year 2004, the parent of the calculated member, and a null for the measure value:
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:
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:
And for Subqueries=2 you get this:
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.
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….. :>)
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.
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
You might need to edit the connection string in the .odc file itself.
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.
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
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.
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
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.
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?
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
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.
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
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
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.
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]))’,
Thank you Mr. Webb!
http://www.bidn.com/blogs/MMilligan/bidn-blog/2515/ssrs-gotcha-ndash-trying-to-use-ssas-time-intelligence-in-ssrs-reports
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).
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
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
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
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.
Excel is the only client that can force calculated members to not ignore sub-queries? Why can’t we do this in SSMS?
What do you mean, exactly? Anything you can do in Excel you can do in an MDX query in SSMS if you set the right connection string properties.
Alright, then I guess I mean, where can I set this connection string property in SSMS? I don’t see it on the list of properties when I right-click the Server/Connection in Object Explorer.
You need to set them in the dialog for the MDX query window – see http://cwebbbi.wordpress.com/2008/06/09/connection-string-properties-in-sqlms-rc0/
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?
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]
}
);
” 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?
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?
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.
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…
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.
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.
What do you want to do here exactly? You can’t create a sub query on a cube.
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;
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;
Sorry, some editing errors )
This row “scope([All_Julyes],[sales amount]); 0)” throws an exception due to using [All_Julyes] calculation named set..
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?
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)
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;
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?
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?
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..
..yes.. and I need cover “arbitrary shapes” for clearer understanding subject ))…
ok, thanks Chris for you patience )
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.
Hi Chris,
I have a date dimension and I created 2 attributes in it – Aggregations & Comparisons. These two attributes are used to create calculated members(e.g. Aggregations – YTD, QTD, MTD & Comparisons – PY, PQ, PM).
I replaced the DataTools design with this as it was having performance issues. And this did improve performance however now I’m seeing that in Excel after building a sheet with about 8 dimensions and 8 measures I get the error – “Query optimizer generated too many subcubes in the query plan”. When I check SQL Profiler I see that when both the Aggregations and Comparisons are used in a row or column I’m hitting this error.
So in the query I noticed that whenever I drag Aggregations or Comparison it pulls .Allmembers. Is there a way you can load default just one member in them? (e.g. [Date].[Aggregations].[YTD] as opposed to [Date].[Aggregations].Allmembers). I feel this would fix the Excel error or am I totally off with what I’m trying to do to solve the Excel error. Pls help.
Hi Chris,
I m try to link the SSAS 2012 CUbe where i have mdx calculation in the SASS and when i run in pivot table in excel the sum is showing wrong when i choose multi select. I m not sure how to configure the SubQueries in the Excel 2013 Connection String. Would you be able to help me on this.
I don’t think you want to change the connection string – you probably need to change your MDX calculation so it supports multiselect. Mosha’s blog post here on using dynamic named sets to do this is a good place to start: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx
Hi Chris,
Not sure if you’d be able to help given we’ve had several consultants in to look at performance issues on our cube, but since your book and posts have generally helped me more than those guys then I thought I’d chance my arm! Sincere apologies for the length of the post.
We have a financial cube (SSAS 2008 R2) with an Account dimension on it giving us inconsistent performance.
The dimension is made up of Unary Operators and Custom Rollup MDX. The custom MDX is pulling data from different ‘real’ levels all over the account dimension to derive values such as Profit.
When writing MDX queries ourselves the cube is nicely responsive in virtually all scenarios.
Our users query the cube using Excel 2010. They can write acceptably performing queries but also horrendously performing queries that can time out after an hour.
The cube is tiny (< 1GB).
After exhausting many avenues (including the flattening of the parent child hierarchy and aggregations) it seemed that the custom MDX was causing the problem. Furthermore, the performance problems seem to happen when Excel creates a subquery that does not reference the Account Dimension. The outer query then applies the account dimension to make sense of it (In isolation the total of Account is nonsensical). In the badly performing queries we seem to see massive amounts of recursion when watching it through profiler. My assumption was that this recursion was happening in a manner whereby the subquery was having to calculate all pieces of Custom MDX to give a total for Account and do this thousands of times (even though this total is never shown).
In order to try and rectify this I added the following code to the Calculation script.
SCOPE(root([Account]));
THIS = null;
END SCOPE;
I wanted to scope in null whenever the query was at the All level of the Account dimension. The hope being that this would stop the query having to continually add up the calculated values (in the recursion shown in profiler) just to ignore them right at the end when it specifies the actual account members it wants.
This massively improved performance in the problem queries often pulling the timings down by 98% +. It meant that until you pull account onto your Excel report there would be no value, but this is fine as the value is nonsensical until Account is used anyway.
I have since been carrying out reconciliation on many reports as this seems like quite a fundamental change and in general things were looking good.
However, I have come unstuck with the following scenario:
Excel sometimes writes queries in the reverse manner whereby it applies a filter on the Account dimension in the subquery then wants to use the All level in the outer query. I believe this is to deal with showing Excel totals. In this scenario, my scope statement is altering the All level of account in the outer query to be null. However, it seems that this All level would only be using the account lines selected in the sub query. A stripped down example of what I mean is shown below:
select ([Measures].[Value]) on 0,
({[Account].[Account].[All]})on 1
from
(
select ([Measures].[Value]) on 0,
({[Account].[Account].[Category A],
[Account].[Account].[Category B]}) on 1
from MyFinancialCube
where [Date].[Date].[Period].&[201501]
)
where [Date].[Date].[Period].&[201501]
In my Excel report this will show the Category A and B values but the totals lines are null which is an undesired effect. Without the above scope statement, the totals will be the total of Category A and B (not All).
I am struggling to think of a way to cope with this. I assume there is no way to test whether the scope statement is being carried out within a sub query or upon a sub query?
Is it possible to potentially test the number of children of Account and only scope in null when I have a full quota of children? My thought being the outer query would only see two children.
Or could I put an invisible member in account that the users cannot select. In which case if the all member does not contain the invisible member then do not scope in a null value?
Am I barking up the wrong tree completely?!
I suspect this is far too much to try and condense into a comment and without a good look you may not be able to help but I can hope 🙂
Kindest Regards
Dave
Hi Dave,
I’m afraid it’s impossible for me to say what the solution might be without spending a long time looking at your cube. My suspicion would be that tuning your existing MDX, rather than trying to add more MDX to handle special cases, would be the way to go. If the All Member on account doesn’t give a meaningful value, why not just remove it completely by setting the IsAggregatable property on the hierarchy to False, and then set a default member on that hierarchy?
I know you’ve already spent money on other consultants to try to solve this, but realistically this sounds like a day or two’s consultancy (www.crossjoin.co.uk) work to sort out. I would need to run a lot of test queries and look at Profiler traces to work out what’s really happening here.
Hi Chris – really appreciate your response. I am investigating whether I can get the same performance benefits by setting the IsAggregatable property to False. If I struggle to get any further and the powers that be allow us to step outside the usual recommended suppliers I’ll put your group forward.
Thanks again.
Hi Chris,
We have noticed an issue while using default member on any dimension, which appears to be a bug as reported by few others on MS connect. The issue is when we make use of the dimension with default member as a filter in Excel with multi select, the data returned would only be with respect to the default member. It works fine when it is used on the rows or all the members are selected.
We traced the MDX and seems to be the problem with the way Excel generates the query in this case.Have you encountered any such issues or can think of a workaround for the same?
Thanks in advance, any help would be really helpful.
Are you running into this issue? https://blog.crossjoin.co.uk/2011/10/27/scoped-assignments-and-multiselect/
Hi Chris,
Thanks for taking your time out to reply on this. The issue we are facing is almost similar to the below connect post:
https://connect.microsoft.com/SQLServer/feedback/details/2498126
Based on the reading it appears to be a bug, but not sure if a workaround exists for this.
OK, I understand the problem now. My gut feeling is that this is deliberate functionality and not a bug, and I don’t think there’s an easy workaround. Can you tell me a bit more about your cube/dimension design? I might be able to suggest a different way of achieving what you want so you can avoid this problem.
If we need to pre-filter the hierarchy members – but based on a dynamic condition – say which is driven by user input – how would we use the sub-select ? We need to evaluate a running total of a metric – only for each of the dimension members pre-filtered in this subselect – and then want another condition imposed on the value of this running total – for the final computation.
I tried using a Where clause inside the sub-select query – but the outer query (which contains the main running total and condition computation) still runs across the entire set of members of the hierarchy – and not on the subset which was pre-filtered in the sub-select ? Is there a different and more elegant solution here ?
The main use for subselects is to calculate subtotals in queries. Are you trying to use a subselect here to help you calculate the running total? If so, then I think you’re on the wrong track: all of the logic for calculating the running total should be inside the definition of the calculation.
Well – I guess I will provide some more details here. We have a Customer Dimension – With Customer Members identified by Customer Key and Customer ID (Business Side Natural Key) – with about 600,000 unique members. We have a measure group that records the Sales Revenue – and a Measure that records the net sales value – positive (Sales) or negative (refunds / returns) for every record in Sales Fact.
The query I am writing is a two step query –
1) Generate a running total of the net sales revenue (Sales – Returns/ Refunds) per customer – for a given Product Category (user input) and only up to a certain date in the current Sales Year (again a user input).
2) The next step is to provide a Count of Customers – with net sales greater than a fixed amount (again a user input) – across the Store Geography Hierarchy – that consists of State, District and City levels.
This helps us to track our target customers and also compare different store business areas – for the marketing activities – by suitably changing the value of the target net sales amount per customer.
Since we have a very large number of members in the Customer Dimension, I thought of using a sub-select for step 1 – using NONEMPTY() function to only include the Customers – who have at least one sales record – for the given Product Category – as the sub-select inner query. The outer query defines a inline calculated member – that will compute the net sales per Customer – using the Product Category and Date inputs – and then produce the results – slicing by the cross join of all the members of Store Geography Hierarchy each of the level on Rows.
If I combine all of this in a single step – then the query runs very long – and I found out from Profiler that it was running the outer query for all of the Customer Dimension Members – which was probably not required in this case. Hence I introduced sub-select to improve the performance – and limit the outer query – to be executed only for a subset of members from the Customer dimension. It still seems to be running into performance issues – and apparently the outer query is still being executed for all of the members for Customer Dimension – thus runs very slow.
You shouldn’t rely on a subselect to do your calculation for you – all of the logic of the calculation should be inside the calculation definition, not in the structure of the query. It’s very hard to say what the solution is here though without spending a few hours looking at Profiler traces etc, I’m afraid. Have you seen this article?
http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx
Thanks so much, Chris for all your help and information. I will give it another try – based on the material / directions you had provided.
Using the article you suggested, I tried the following query –
WITH SET [CustomersWithSalesEvents] AS NONEMPTY([Dim Customer].[Customer ID].[Customer ID].MEMBERS, {([Dim Product].[Product Category – Product Code].CURRENTMEMBER, [Measures].[Net Sales])})
MEMBER [Measures].[CustomerProductCount] AS ([Measures].[Net Sales] , [Dim Product].[Product Category – Product Code].CURRENTMEMBER)
MEMBER [Measures].[CustomerProductQuarterCount] AS Sum( [CustomersWithSalesEvents] , Iif([Measures].[CustomerProductCount] > 0,1,0))
SELECT {[Measures].[CustomerProductQuarterCount] } ON COLUMNS,
{[Dim Store Geography].[State – City – Store Code].[All] , [Dim Store Geography].[State – City – Store Code].[State].ALLMEMBERS, [Dim Store Geography].[State – City – Store Code].[City].ALLMEMBERS, [Dim Store Geography].[State – City – Store Code].[Store Code].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Sales Cube]
WHERE CROSSJOIN( {[Dim Product].[Product Category – Product Code].[Product Category].&[Clothing]}, {null:[Dmo001 Dim Calendar].[Full Date].&[2015-08-14T00:00:00]} )
I thought of using a named set – to limit the numbers of Customers with Sales in the specific product category – with the same idea to make the query run faster. This seems to have improved the overall time – but it still is running a cell-by-cell computation per my understanding. I used MDX Studio to run and analyze this query – and it shows a large number for Cells Calculated. Would there by any way to adjust this query structure – to introduce block computation instead of cell-by-cell computation ? Thanks so much.
You should be returning null, not 0 here:
MEMBER [Measures].[CustomerProductQuarterCount] AS Sum( [CustomersWithSalesEvents] , Iif([Measures].[CustomerProductCount] > 0,1,null))
I think I am returning NULL in the IIF Statement – if the [Measures].[CustomerProductCount] <= 0 ; and I am returning 1 – if it is greater than 0 – for the SUM Function to operate.
I did replace the IIF statement – returning NULL as you had suggested – where the [Measures].[CustomerProductCount] < = 0. This improved the query time – and now it runs about 8 times faster. It seems though – it is still operating in Cell-By-Cell Computation Mode – looking at the output from MDX Studio: –
Time : 10 sec 610 ms
Calc covers : 0
Cells calculated : 9780
Sonar subcubes : 8
NON EMPTYs : 1
Autoexists : 0
EXISTINGs : 0
SE queries : 13
Flat cache insert : 0
Cache hits : 30
Cache misses : 28
Cache inserts : 12
Cache lookups : 58
Memory Usage KB : 10924
Would there be any way to change the query structurally so that the Block Computation Mode comes into play here – may be thereby further improving the performance ? Thanks so much…
It’s hard to say – I would have to spend some time experimenting with the calculation to be sure. Sorry…