Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MDX for last year values Expand / Collapse
Author
Message
Posted Wednesday, April 6, 2011 4:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, Visits: 227
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
Post #1089623
Posted Wednesday, April 6, 2011 6:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 1:48 PM
Points: 1,824, Visits: 3,496
Can you post the entire mdx?



Steve.
Post #1089654
Posted Thursday, April 7, 2011 8:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, Visits: 227
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
Post #1089912
Posted Thursday, April 7, 2011 8:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
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
Post #1089962
Posted Thursday, April 7, 2011 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, Visits: 227
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
Post #1089989
Posted Thursday, April 7, 2011 9:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
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
Post #1089995
Posted Thursday, April 7, 2011 9:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, Visits: 227
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
Post #1090010
Posted Thursday, April 7, 2011 9:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
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
Post #1090012
Posted Thursday, April 7, 2011 9:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
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
Post #1090017
Posted Thursday, April 7, 2011 10:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, Visits: 227
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
Post #1090033
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse