April 23, 2009 at 10:41 am
I have a cube dimension named [Order Date] with a hierarchy [Year-Month-Day].
This dimension is based on the DimDate table in the data warehouse.
The DimDate table has an attribute named "Same Weekday Last Year Date" (not included in the [Year-Month-Day] hierarchy.
I want to create a calculated measure in my cube for [Order Date] that uses the "Same Weekday Last Year Date" to provide a year-over-year comparison in the same [Year-Month-Day] hierarchy.
It won't work to just use "date minus one year" because April 23, 2008 was a Friday and April 23, 2008 is a Thursday. Thursdays perform different than Fridays, so that comparison would be useless.
I think there has to be a way to do this, I just can't seem to figure it out.
Thanks in advance!
Dan
April 23, 2009 at 11:26 am
Did you try ParallelPeriod? If so (and it didn't work) what was/were the issue(s) encountered?
Steve.
April 23, 2009 at 11:35 am
Hi Steve.
I tried, but it didn't work. What period would I use to run "parallel"? If I use the year or month, I would get the same date last year, but it doesn't fall on the same weekday. (My example from below.)
Maybe I can identify whether we are in a leap year and do ParallelPeriod-365/366 days?
April 23, 2009 at 1:00 pm
d'uh, skipped that bit too quickly, sorry.
Maybe i'm going the wrong way here, but am not sure it makes sense to try and force this for use within the existing Yr-Mth-Day hierarchy. In thinking of the business question, it doesn't make sense to me that I am looking at April 23 2009 and then, I wan to see a figure for last year, i am really seeing the result for April 22??
Can't your users see the same/required result by putting the DOW dim on (say) columns, and then nesting the Year (attribute) within it?
Like i said, possibly off base here :S
Steve.
April 23, 2009 at 2:03 pm
I think I understand what you're saying, but I need to let it sink in a little...
As for the business purpose for the scenario, I'll give it another shot.
Our sales week has a pattern of being heavy on Mondays and Tuesdays and gets lower as the week goes on. As we go through the year, we are interested in how we did last year, but April 20, 2008 was a Sunday which is historically our lowest sales day any given week. If we were to compare April 22, 2009 with April 20, 2008 - it would look like we are shattering what we did a year ago, when in actuality last year was a Sunday so we're comparing apples to oranges.
Hopefully that makes sense.
I'll try your suggestion.
Dan
April 23, 2009 at 2:37 pm
Yep, i think i get what you're saying the intraweek cycles when compared with other temporal analysis.
Am wondering if you almost need to put in another hierarchy, like a 4-4-5 (or 4-5-4 or ... , these are weeks in a 'month' where month really means 'period' and you have 13 for the year), where the days week are constant, or perhaps put another way, each year always starts on the same day. This would allow for clean use of parallelperiod, you *couldn't* (or should i say, wouldn't) use this dim for (say) financial reporting if your fiscal year has to run (say) Jan 1 thru Dec 31.
What i was trying to say earlier was it would seem strange to me, that a user is looking at say April 23 2009 and the comparative value is really coming from April 22 2008. To me, it makes sense to compare April 23 with April 23, although this doesn't give you what you want. The other time dim *would* give you what you want, as in generic terms you're comparing Day 4 of Week 3 of Period 2 in Year 2009 with D4 W3 P2 of 2008. Hopefully this makes some sense :hehe:
Steve.
April 25, 2009 at 6:49 am
You might want to take a quick peek at this discussion in getting the same day of week for previous year here - Get date for last year for same day and day of week.. .
I am assuming you have time intelligence or a shell dimension setup that you can use to perform these type of calculations in your cube. How is the key column setup for the date attribute? Is this referencing an identity value, the actual date, or is it a smart key like YYYYMMDD? If you have the date that you need to reference you should be able to setup the SCOPE statement on the date members to get that date aggregate referencing that previous year date member property. Would just need a little more information on how you have your time intelligence setup and also the key column on your date attribute.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
April 27, 2009 at 4:42 am
I'll take a look at the link you included, thanks.
I understand some of what you're asking, but not all. (I'm much stronger @ SSIS than I am @ SSAS.)
I have a DimDate table that uses YYYYMMDD as the key. From what I understand of shell dimensions, yes I believe it is set up that way. I have several date dimensions set up in my cube that use the same DimDate table, linked to the fact table through various DateKey foreign keys.
The DimDate table has a column called "Same Weekday Year Ago Date" which is a datetime datatype.
I've never dealt with a SCOPE statement - I'll see what I can find out. I'd appreciate any additional help you can provide.
Thanks Dan!
April 27, 2009 at 5:59 am
As an example then since you have your date key setup in the yyyymmdd format you could then setup a calculation referencing that information by doing the following:
with member Measures.[Last Year Same Day Revenue] as
(Measures.[Revenue], strtomember("[Date].[Date].&[" +
vba!format(vba!cdate([Date].[Date].CurrentMember.Properties("Same Weekday Year Ago Date")),
"yyyyMMdd") + "]"))
select {Measures.[Revenue],
Measures.[Last Year Same Day Revenue]} on 0,
[Date].[Date].members on 1
from [Sales]
And I added a column to my date dimension table and populated it with the following t-sql:
update d set SameWeekdayYearAgoDate =
DATEADD(week, ROUND(52.1775 * -1, 0),
d.[Date]) from DimDate d
After that I added the new column in my date dimension in the SSAS database and now I can reference that as a member property. If you only want this to be available for the date members then you can look at adding in a SCOPE statement so that the calculation is only available at that particular level.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply