The new aggregation functions provided out-of-the-box with Analysis Services 2005 are fairly well publicised, but I feel they still warrant a blog post.
Analysis Services 2000 only had Sum, Count, Min, Max and DistinctCount. Analysis Services 2005 also has the following.
- None results in no default aggregation. This is presumably used for custom aggregations like financial ratios where we calculate the aggregation ourselves
- ByAccount is specialised for financial applications and applies only to an account dimension
- AverageOfChildren just saves us creating a Count solely for the purpose of dividing another measure by it
- FirstChild, LastChild, FirstNonEmpty and LastNonEmpty are “semi-additive measures”. They are additive when querying by a normal dimension, but not by time. For example, stock counts viewed at the product-category level should be summed, but when viewed for a week, the value should not be the sum of Mon + Tues + Wed, … It would just be the LastNonEmpty child (maybe Friday).
