April 6, 2011 at 4:01 pm
Hi,
I am using SSAS 2005 and trying to create a MDX for the hierarchy Fiscal Period (Year->Quarter->Month->Day).
The user will be selecting multiple dates.
I have this MDX below but I am getting an error message 'The MDX function CURRENTMEMBER failed because the coordinate for the attribute Fiscal Period.Day contains a set'
((existing [Fiscal Period].[Fiscal Period Hierarchy].[Day],
{Parallelperiod([Fiscal Period].[Fiscal Period Hierarchy].[Year],1,[Fiscal Period].[Fiscal Period Hierarchy].currentmember)}),[Measures].[Quantity])
I also tried with Aggregate(Generate followed by the expression above but did not work.
Your help would be appreciated. Thank you.
Regards,
Uma
April 6, 2011 at 6:22 pm
Can you post the entire mdx?
Steve.
April 7, 2011 at 8:02 am
Hi Steve,
I am creating a calculated measure called [LY Quantity] and this is what I have under the script view for this measure.
CREATE MEMBER CURRENTCUBE.[MEASURES].[LY Quantity]
AS ((existing [Fiscal Period].[Fiscal Period Hierarchy].[Day],
{Parallelperiod([Fiscal Period].[Fiscal Period Hierarchy].[Year],1,[Fiscal Period].[Fiscal Period Hierarchy].currentmember)}),[Measures].[Quantity]),
FORMAT_STRING = "#,#",
VISIBLE = 1 ;
When the user selects multiple days, I am supposed to show the Quantity from corresponding last year for those dates.
Regards,
Uma
April 7, 2011 at 8:50 am
Hi,
This is actually a very common problem with multiselects but there are ways to solve it. It's just that the performance might suffer as the only safe method is to aggregate from the leaf level of the time dimension if the user can pick any members in the calendar hierarchy eg (2 months or 2 dates or even a combination).
Anyway here are the examples on Adventure Works R2:
1. As a lot of clients (excel for instance) generate these kind of queries using subselects you have to use a combination of dynamic sets (2008+) with existing instruction applied to the set and aggregate using generate() function (see measure agg1) or use SUM over a constructed tuple (see measure agg2).
Dynamic sets can be forced to evaluate in the context of subselects and slicers.
Query 1 (please note that when subselects are used the aggregations on parent members are totals of child members in subselect - thus years on rows are just used to show results for comparison):
with
set [months] as
existing [Date].[Calendar].[Month]
member [Measures].[Internet Sales Amount LY agg1] as
Aggregate
(
Generate
(
existing [months]
,{parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)}
)
,[Measures].[Internet Sales Amount]
), Format_String ="Currency"
member [Measures].[Internet Sales Amount LY agg2] as
SUM
(
existing [months]
,
(
parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount]
)
), Format_String ="Currency"
select
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Sales Amount LY agg1],
[Measures].[Internet Sales Amount LY agg2]
} on 0
,
[Date].[Calendar].[Calendar Year]
on 1
from
(
select
{ [Date].[Calendar].[Month].&[2007]&[1] : [Date].[Calendar].[Month].&[2007]&[3]}
+
{ [Date].[Calendar].[Month].&[2006]&[1] : [Date].[Calendar].[Month].&[2006]&[3]}
on 0
from [Adventure Works]
)
2. Multiselect in slicer (where part) - Here dynamic sets are not used as it is enough to apply the existing clause directly w/o using a dynamic set. This is how multiselect in slicer behave. Note also that the above example (1) works also with multiselects in slicer.
Query 2:
with
member [Measures].[Internet Sales Amount LY agg1] as
Aggregate
(
Generate
(
existing [Date].[Calendar].[Month]
,{parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)}
)
,[Measures].[Internet Sales Amount]
), Format_String ="Currency"
member [Measures].[Internet Sales Amount LY agg2] as
SUM
(
existing [Date].[Calendar].[Month]
,
(
parallelperiod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)
,[Measures].[Internet Sales Amount]
)
), Format_String ="Currency"
select
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Sales Amount LY agg1],
[Measures].[Internet Sales Amount LY agg2]
} on 0
from
[Adventure Works]
where
(
{
[Date].[Calendar].[Month].&[2006]&[1]
:
[Date].[Calendar].[Month].&[2006]&[3]
}
)
Just a final note - I have found another optimization with this approach that I intend to blog about soon.
Oh, you might also post this MDX questions on MSDN Analysis Services forum as there is a lot of activity and I usually "help" there.
Hope that helps 🙂
Cheers,
Hrvoje Piasevoli
Hrvoje Piasevoli
April 7, 2011 at 9:13 am
Hi Hrvoje,
Thank you for the detailed reply. I am new to writing MDX scripts and trying to understand the logic and concept.
Based on your suggestion, I modified my measure in the script view as follows. However, I am getting an error message 'The syntax for set is incorrect'.
CREATE MEMBER CURRENTCUBE.[MEASURES].[LY QUANTITY]
AS WITH set [Day] as
existing [Fiscal Period].[Fiscal Period Hierarchy].[Day]
member [Measures].[LY Quantity] as
Aggregate
(
Generate
(
existing [Day],
{parallelperiod([Fiscal Period].[Fiscal Period Hierarchy].[Year],1,[Fiscal Period].[Fiscal Period Hierarchy].currentmember)}
), [Measures].[Quantity]),
FORMAT_STRING = "#,#",
VISIBLE = 1 ;
Regards,
Uma
April 7, 2011 at 9:18 am
Hi,
The example was written to be used in an mdx query.
Here is the definition for the cube mdx script:
CREATE DYNAMIC SET CurrentCube.[Days] as
{existing [Fiscal Period].[Fiscal Period Hierarchy].[Day]};
CREATE MEMBER CURRENTCUBE.[MEASURES].[LY QUANTITY] AS
Aggregate
(
Generate
(
existing [Days],
{parallelperiod([Fiscal Period].[Fiscal Period Hierarchy].[Year],1,[Fiscal Period].[Fiscal Period Hierarchy].currentmember)}
)
, [Measures].[Quantity]
),
FORMAT_STRING = "#,#",
VISIBLE = 1 ;
Regards
Hrvoje Piasevoli
April 7, 2011 at 9:43 am
Hi Hrvoje,
I created a named set called [Day] with the script {existing [Fiscal Period].[Fiscal Period Hierarchy].[Day]}
Then, I created a calculated measure [LY Quantity] as Aggregate
(generate(
existing [Day],
{parallelperiod([Fiscal Period].[Fiscal Period Hierarchy].[Year],1,[Fiscal Period].[Fiscal Period Hierarchy].currentmember)}
)
, [Measures].[Quantity]
)
The SQL 2005 Cube processes okay but brings value error in the browser. When I hover over, I am getting 'Aggregate functions cannot be used on calculated members in the Measures dimensions'
Regards,
Uma
April 7, 2011 at 9:48 am
Hi,
Ok that is true regarding calculated members.
But since you are on 2005 you can't really use dynamic sets so it will not work with approach number 1 as static sets are "created" at connection time, and are not affected by slicers or subselects.
Instead of AGGREGATE use the second approach with SUM(existing ..., (parallelperiod(..), measure))
HTH,
Hrvoje Piasevoli
April 7, 2011 at 9:51 am
Uma,
Which tool will users use when selecting those dates? Do you have any control of the generated mdx like for example in reporting services or no control (excel)?
Hrvoje Piasevoli
April 7, 2011 at 10:02 am
Hi Hrvoje,
We use a reporting tool call Targit BI Suite 2008. The users are not using Excel right now to access cube data but they might choose to in future.
Thank you for helping me with the measure earlier. It worked perfectly.
Now, I will get to try create MTD, YTD and last YTD measure.
Thanks a lot again.
Regards,
Uma
November 26, 2012 at 4:01 pm
Hi Hrvoje,
this post helped me out a lot!
Thanks you for your input.
The only thing i see weird is that the Grand Total in excel 2010 gives the total of the entire year for the Last year value.
For example, it is November 26, 2012 today.
The Values for the Grand Total Last Year is the sum of all the days including those past November 26, 2011 for the Year of 2011.
I just want the Grand Total to show the values through November 26,2011.
Do you know of any way to get past this?
Thanks
John
July 3, 2016 at 7:22 pm
Hi Hrvoje,
Did you blogged about the "another optimization technique"?:-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply