Non Additive measures in OLAP Cube

[RSB]

My Cube has all Non-Additive measures, My requirement is such that the Value for any measure for any combination with any Level of the Time Dimension would be that of the last day of that Level. For Example. the Value for the year 2008 for a particular measure would be the Value of the measure on 31 Dec 2008, the same applies to the Quarters and Months.

I have created external summary tables which are used to create aggregation at various levels these are giving expected results.

I was wondering if there is a better way to do this since the number of summary tables that have to be created grows exponentially on the number of Dimensions and Levels in the cube.

Regards,
Raj

[TSBruce]

The best option for non-additive measures is to store the data as a member property. Member properties are not aggregated across any dimension and can be retrieved as calculated members for use in calculations. MDX supplies some functions such as ClosingPeriod that can be used for semi-additive measures, or those that are aggregated across some but not all of the dimensions in the cube.

Examples of the PROPERTY statement in PROC OLAP can be found in the SAS OnlineDoc under SAS OLAP Server -> SAS OLAP Server: User's Guide -> The OLAP Procedure -> PROPERTY Statement.

Also in the SAS OnlineDoc is the SAS OLAP Server: MDX Guide which includes syntax and examples for the MDX functions available in SAS 9 OLAP. I also find that http://msdn.microsoft.com is a very useful site for syntax questions and examples on the MDX language.