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


Date Dimensions Part 2


Date Dimensions Part 2

Author
Message
VincentRainardi
VincentRainardi
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 191
Comments posted to this topic are about the item Date Dimensions Part 2
Rick Todd
Rick Todd
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 441
Thanks for the great series (if 2 = series) on Date Dimensions. It certainly relates to me, as to just about any Data Warehouse developer. I definitely learned some great tricks that I use regularly now, in both articles.
One question: have you found a good solution for moving dates that you can use in Excel 2007 Pivot Tables? Basically a way to tag "yesterday", "this month", "last week", etc., that a user can then create and publish a report with one of those dates as a filter, and when they reload and refresh the report it will maintain the correct date range?

Thanks again,


Rick Todd
VincentRainardi
VincentRainardi
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 191
Thanks Rick.
We can create a calculated member such as Yesterday and Last Month like this:

with member [Transaction Date].[Date].[Yesterday] as
members("[Transaction Date].[Date].&[" + Format(DateAdd("d",-1,now()),"yyyy-MM-dd") + "]")
select [Transaction Date].[Date].[Yesterday] on rows,
[Measures].[Amount] on columns
from [Cube];

with member [Transaction Date].[Month].[Last Month] as
members("[Transaction Date].[Month].&[" + Format(DateAdd("m",-1,now()),"MMMM yyyy") + "]")
select [Transaction Date].[Month].[Last Month] on rows,
[Measures].[Amount] on columns
from [Cube];
Rick Todd
Rick Todd
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 441
Anything simple like that for This Week? Not having the "ww" type functionality in the FORMAT(NOW()) makes it a lot more complicated, and googling doesn't seem to bring up anything useful.


Rick Todd
Rick Todd
Rick Todd
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 441
It looks like to build this into the cube, I have to build them as Sets. This is nice from a presentation perspective, in that they show up in the Sets section in Excel 2007 for their respective dimensions, but means I can't use more than one at the same time.
Am I missing something, and I don't have to build them as Sets?


Rick Todd
VincentRainardi
VincentRainardi
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 191
Hi Rick,
1. If the week attribute is in yyyy-ww format, we can use datepart() for [This Week]:
with member [Transaction Date].[Week].[This Week] as
StrToMember("[Transaction Date].[Week].&[" + cstr(datepart("yyyy",now())) +
"-" + right("0"+cstr(datepart("ww",now())),2) + "]")
select [Transaction Date].[Week].[This Week] on rows,
[Measures].[Amount] on columns
from [Cube];

2. It's member not set. The member definition is between "as" and "select".

Ref:
1. http://msdn.microsoft.com/en-us/library/20ee97hz.aspx
2. http://technet.microsoft.com/en-us/library/ms146084.aspx
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