February 5, 2010 at 9:19 am
Hello to everyone,
i have a issue with ParallelPeriod.
My MDX is:
[MEASURES].[GC GSV Finance LY]
AS (
ParallelPeriod
(
[Dim Time].[Finance Date Hierarchy].[Date],
364,
[Dim Time].[Finance Date Hierarchy].CurrentMember
),
[Measures].[GC Gross Value]
)
My Finance Date Hierarchy :
FYear,
FQuarter,
FMonth,
FWeek,
Date
for example:
FYear = FY 2009-2010
FQuarter = FY Quarter 1
FMonth = April
FWeek = Week 1
Date : Sunday, March 29 2009
Monday, March 30 2009
Tuesday, March 31 2009
Wednesday, April 01 2009
Thursday, April 02 2009
Friday, April 03 2009
Saturday, April 04 2009
My client ask me if can compare the Gross Value of a date, vs exactly the Last Year same date.
This script woks fine, but
1. works, only if i expand the finance date hierarch.
2. I haven't any totals on parent levels...
Any Idea?
Dimos Thanasoulas
Business Intelligence Consultant
Dynamic Integrated Solutions S.A
http://www.dissoft.eu
February 5, 2010 at 8:15 pm
For a typical year comparison at any level you would simply using the following:
[MEASURES].[GC GSV Finance LY]
AS (
ParallelPeriod
(
[Dim Time].[Finance Date Hierarchy].[FYear],
1,
[Dim Time].[Finance Date Hierarchy].CurrentMember
),
[Measures].[GC Gross Value]
)
Something that you might want to look at though that is reusable across any measure would be setting up what is known as a shell dimension and some SCOPE statements in your cube. Take a look at the DateTool project out on SQLBI.com - http://www.sqlbi.com/Default.aspx?tabid=87
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
February 6, 2010 at 4:33 am
Thank's denglishbi.
This is for typical use. Unfortunately my case isn't.
Let's explain exactly, what i want.
If we have a GSV Value for Monday, March 30 2009. if we use the "typical" ParallelPeriod MDX such as :
[MEASURES].[GC GSV Finance LY]
AS (
ParallelPeriod
(
[Dim Time].[Finance Date Hierarchy].[FYear],
1,
[Dim Time].[Finance Date Hierarchy].CurrentMember
),
[Measures].[GC Gross Value]
) then we have the comparison with the Sunday, March 30 2008.
This is the typical comparison.
And what about when we need a comparison between Monday, March 30 2009 and Monday, March 31 2008 ?
Dimos Thanasoulas
Business Intelligence Consultant
Dynamic Integrated Solutions S.A
http://www.dissoft.eu
February 6, 2010 at 8:09 am
Okay, now I get what you are looking for. That is a little tricky, but it is doable as long as you have some additional information in your date dimension like 'Day of Week' or 'Day Number of Week' (not sure what you would have called it, but this would provide a value of 1 to 7. You can then leverage this to get at the same exact day of the week in the previous year by doing something like this:
[MEASURES].[GC GSV Finance LY]
AS (
parallelperiod([Dim Time].[Finance Date Hierarchy].[FYear],1,
[Dim Time].[Finance Date Hierarchy].CurrentMember).lag(parallelperiod([Dim Time].[Finance Date Hierarchy].[FYear],1,
[Dim Time].[Finance Date Hierarchy].CurrentMember).properties("Day of Week")).lead([Dim Time].[Finance Date Hierarchy].CurrentMember.properties("Day of Week")),
[Measures].[GC Gross Value]
)
This is still logic that would be good to put in a shell dimension and with SCOPE statements so that the logic is reusable. You would also want to include some logic to evaluate the level of the hierarchy that is being evaluated since this logic would only be leveraged at the date level.
Mosha has a posting here that might be helpful for evaluating different time periods for a calculation Time calculations in UDM: Parallel Period (this doesn't take into account the 'exact' day, but you can see how he scopes the calculations).
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
February 7, 2010 at 9:43 am
Thank's denglishbi.
So the solution was very simple.
1st. I kept the typical MDX.
2nd. I added the [Day_Of_Week] & [Day_Of_Week_Name] fields (The second as name column)as attributes on my DimTime.
I added on the Finance Hierarchy the [Day_Of_Week] attribute over the last level. So my Hierarchy now is:
FYear-->FQuarter-->FMonth-->FWeek-->DayOfWeek-->Date.
And that's it.
Thank's again
🙂
Dimos Thanasoulas
Business Intelligence Consultant
Dynamic Integrated Solutions S.A
http://www.dissoft.eu
February 15, 2010 at 2:59 am
Hi dimos.thanasoulas,
I am having the same issue with parent members showing blank. You mentioned you solved the problem by keeping your "typical mdx"...are you refering to this:
[MEASURES].[GC GSV Finance LY]
AS (
ParallelPeriod
(
[Dim Time].[Finance Date Hierarchy].[Date],
364,
[Dim Time].[Finance Date Hierarchy].CurrentMember
),
[Measures].[GC Gross Value]
)
Not sure how adding new levels to your hierarchy solves the problem. My hierarchy is: Yr -> Qtr -> Month ->Date.
Thanks.
February 16, 2010 at 1:48 am
No the typical mdx is like that:
[MEASURES].[GC GSV Finance LY]
AS (
ParallelPeriod
(
[Dim Time].[Finance Date Hierarchy].[FYear],
1,
[Dim Time].[Finance Date Hierarchy].CurrentMember
),
[Measures].[GC Gross Value]
)
then you must check, if in your DimTime table has column has as DayOfWeek.
(Look at the AdventureWorksDW).
Then you must add this column as attribute on your DimTime and then add the DayOfWeek Column into Hierarchy on a level before the last.
Dimos Thanasoulas
Business Intelligence Consultant
Dynamic Integrated Solutions S.A
http://www.dissoft.eu
February 16, 2010 at 3:57 pm
OK, Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply