Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX for last year values


MDX for last year values

Author
Message
umas
umas
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
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
stevefromOZ
stevefromOZ
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: Moderators
Points: 1929 Visits: 3754
Can you post the entire mdx?

Steve.
umas
umas
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
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
hrvoje.piasevoli
hrvoje.piasevoli
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 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 Smile
Cheers,
Hrvoje Piasevoli

Hrvoje Piasevoli
umas
umas
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
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
hrvoje.piasevoli
hrvoje.piasevoli
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 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
umas
umas
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
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
hrvoje.piasevoli
hrvoje.piasevoli
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 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
hrvoje.piasevoli
hrvoje.piasevoli
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 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
umas
umas
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search