In an OLAP project, you have good chances to reach semi-additive measures. Measures that measure quantities on stock or different levels are good examples: you can use the SUM aggregate function across all dimensions except Time. As you can see, you probably reach semi-additive measures in nearly any OLAP project. However, Microsoft decided that semi-additive measures are supported through specific aggregate functions in Enterprise edition only (check the “Features Supported by the Editions of SQL Server 2005” topic in Books OnLine, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm). I find this a very sinister decision, a decision that denies “BI for masses” marketing term.
Fortunately, things are not that black. Books OnLine are not completely correct. Some semi-additive aggregate functions do work in Standard edition. For different levels, the most useful semi-additive aggregate function is LastNonEmpty; this one is limited to Enterprise edition only. However, function LastChild works in Standard edition as well. With this one, you can simulate LastNonEmpty function.
You have two possibilities. The first one is quite simple: always populate the data warehouse SSAS database, the cubes and dimensions, especially the Time dimension, only up to date you have the data. It means adding a new row to the Time dimension every time you process SSAS database; if your processing happens once per day (over the night, I guess), that it means a new row per day. This way you do not have, for example, a member for Nov 30 when the data is Nov 8. If you had a member for Nov 30, then you would get with LastChild function aggregate for November equal to zero, because you do not have fact table rows yet.
The first solution has a big drawback. In a cube with solution like this, you cannot support plans. For plans, you definitely need Time dimension members for the future. I did not hear much about plans for the history. You can still use the LastChild function in this case. Whenever you populate the fact table, you simply have to add rows for all points in the future till the end of some business period with the values for semi-additive measures from the current point in time. For example, on Nov 8, you would add rows for Nov 9 until Dec 31, if your logical business period is calendar year, with values from Nov 8. This way you can support plans and get correct results for semi-additive measures.
Final note: for adding these fictitious future rows in a data warehouse in a RDBMS, you do not need a loop or a cursor. You can add all rows you need with Insert..Select with a non-equi join to the Time dimension.
