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

Date Dimensions Part 2 Expand / Collapse
Author
Message
Posted Wednesday, February 18, 2009 12:27 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
Comments posted to this topic are about the item Date Dimensions Part 2
Post #659121
Posted Wednesday, February 18, 2009 7:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:39 PM
Points: 216, Visits: 422
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
Post #659337
Posted Wednesday, February 18, 2009 8:47 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
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];
Post #659503
Posted Thursday, February 19, 2009 7:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:39 PM
Points: 216, Visits: 422
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
Post #660326
Posted Thursday, February 19, 2009 9:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:39 PM
Points: 216, Visits: 422
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
Post #660477
Posted Friday, February 20, 2009 3:57 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
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
Post #661177
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse