Time Dimension: How to set Default Member to Current Month

Question:

I want to set current Month as a Default member in my Time Dimension. So that every time i see my data it should display most current data.

Answer:

Here is an example using the Adventure Works cube.  You can add this to the cube script for the Adventure Works cube and it will default the day to the current date using the Now() function.  I had to use (Now() - 1000) to set the date back to 3/25/2004 due to the fact that the Adventure Works cube Date dimension ends at 8/31/2004, but I think you will get the idea.  The other thing to note here is that the [Date].[Date] attribute has a "ValueColumn" defined that is of type "Date".  This allows the filter statement to use a straight date vs. date comparison.

-- Now() = 12/19/2006

-- Now() - 1000 = 3/25/2004

ALTER CUBE CURRENTCUBE

UPDATE DIMENSION [Date],

DEFAULT_MEMBER = Tail(Filter([Date].[Date].Members,[Date].[Date].MemberValue < (Now() - 1000)),1)(0);

HTH,

Steve


Steve Pontello

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1036989&SiteID=1

No comments:

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll