Named Sets – Date

Here are a few examples of Named Sets we use for a date dimension in our cubes. Named sets for dates are a must so the user doesn’t have to reselect a different day every time (for example, they always look at today’s data) they open a saved book/worksheet. Adding these named sets can greatly increase efficiency for a user.
 
We load data for the current day, so the last member with data is always the current day.
 

CREATE SET CURRENTCUBE.[Today] AS
{Tail(Filter([Date].[Day].Members, Not IsEmpty(([Date].CurrentMember))), 1)};    

CREATE SET CURRENTCUBE.[Yesterday] AS 
{(Head(Tail(Filter([Date].[Day].Members, Not IsEmpty(([Date].CurrentMember))), 2) ,1) )};   

CREATE SET CURRENTCUBE.[3 Days Ago] AS 
{(Head( Tail(Filter([Date].[Day].Members, Not IsEmpty(([Date].CurrentMember))), 4) ,1))};   

CREATE SET CURRENTCUBE.[7 Days Ago] AS 
{(Head( Tail(Filter([Date].[Day].Members, Not IsEmpty(([Date].CurrentMember))), 8) ,1))};   

CREATE SET CURRENTCUBE.[Last 30 Days (by day)] AS 
{Tail(Filter([Date].[Date].[Day].Members, Not IsEmpty(([Date].[Date].CurrentMember))), 30)};

CREATE SET CURRENTCUBE.[Current Month (by day)] AS 
Descendants({Tail(Filter([Date].[Month].MEMBERS, Not IsEmpty(([Date].CurrentMember))), 1)},[Date].[Day]);   

CREATE SET CURRENTCUBE.[Last Month (by day)] AS 
Descendants({( Head( Tail(Filter([Date].[Month].Members, Not IsEmpty(([Date].CurrentMember))), 2) ,1) )} ,[Date].[Day]);

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s