Time-Dynamic MDX

Time-Dynamic MDX

Convention 3: Create the Closed Member Property
The third convention is the most flexible of the four. To use this technique, you create a member property called Closed on the month level of the Time dimension. This member property in the cube corresponds to a column in the Time-dimension table of the star schema. When a month is closed, this column in the star schema is set to 1; if the month isn't closed, the column is set to 0. MDX formulas can test for a 1 in this member property to determine whether the month is closed.

CREATE SET RecentMonth AS
'Filter( Time.Month.Members,
Time.CurrentMember.Properties
("Closed") =
"1").item(COUNT(Filter(
Time.Month.Members, Time.CurrentMember.Properties
("Closed") = "1") 1)'

Convention 4: A Server-Based Solution
The fourth convention for determining the most recent month is a server-based solution. To implement this solution, you use Analysis Manager to create the RecentMonth set on the OLAP server. This set contains one month member, and you use Analysis Manager to update the set manually whenever a new month closes. Although it contains a manual step, this technique isn't hard to use. You can automate this solution by changing the RecentMonth set definition at the end of the scheduled cube-load task. You can change server-based set definitions by using the Decision Support Objects (DSO) object model from Visual Basic (VB). Check the SQL Server Books Online (BOL) topic "DSO" for more information about how to create and update server-defined sets.