Archive for category: Blog

Enterprise Edition of Analysis Services 2005 and 2008 supports semi-additive functions, such as LastNonEmpty, LastChild, and so on, to facilitate working with semi-additive measures, such as account balances and inventory snapshots. For example, the screenshot below shows LastNonEmtpy in action. To demonstrate this, I added a Last Sales measure to the Internet Sales measure group in the Adventure Works cube and set its AggregationFunction property to LastNonEmpty. Of course, this doesn’t make much sense because sales amounts are fully additive but it’s OK for demo purposes.

As expected, the quarter and year totals show the last non empty sales. The customer totals are correct too because semi-additive measures sum across any dimension except Time. But what about the grand total of $14.98? Shouldn’t we get the sum of the quarter (or year) subtotals?

To answer this question, we need to understand how LastNonEmpty operates. LastNonEmtpy works across time and not across other dimensions. Because the last customer recorded sales are in June 2008, LastNonEmpty gives you Q2 total of $14.98 which becomes the YTD total as well. So, does it make sense to sum the totals vertically? The philosophical answer is “it depends”. If this was inventory balances, the question is why we have missing balances in the last day. In other words, to get the vertical sum right we need to record balances for each product for each period. One may say that in this case LastNonEmpty is correct because it makes no assumptions about what happened to the “missing” values for products, customers, etc. It could be that we didn’t record them or it could be that there we discontinued that their ending balance is indeed not applicable.

But, there could be cases when we need LastNonEmpty to sum vertically instead of getting the last values. For example, the cube may have test scores and you may need to get the last score, such as to calculate the average student or school score. To address this requirement, our first impulse could be to use a scope assignment that overwrites the (Root(Customer), Root(Date)) tuple to server SUM aggregate function (the one you specify in the AggregateFunction property). But, as it stands, SSAS doesn’t support plugging in server aggregate functions in scope assignments.

So, we will try the reverse approach:

1. Add a new fully-additive measure Sales with SUM aggregate function.
2. Overwrite the Sum with LastNonEmpty at the customer level.
3. Hide Last Sales and use Sales instead.

Scope

(

[Date].[Date].[All],

[Customer].[Customer].[Customer].Members, /*or Leaves(Customer)*/

{

[Measures].[Sales]

}

);

this = [Measures].[Last Sales]; /*assign Last Sales to Sales to make it semi-additive at customer level*/

End Scope;

Note that [Customer].[Customer].[Customer]. Members exclude the Customer All member. Since the server uses the All member to aggregate up, aggregating at a higher level, such by product will work fine because All member will contain the SUM of the last sales.

It will be great if a future SSAS release supports:

1. Using server aggregate functions in scope assignments
2. Give the modeler an option to control the behavior of the semi-additive measures.

The Microsoft CAT team has released a new whitepaper Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques.

“Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique. It demonstrates that optimizing many-to-many relationships by compressing the common relationships between the many-to-many dimension and the data measure group, and then defining aggregations on both the data measure group and the intermediate measure group yields the best query performance. The results show dramatic improvement in the performance of many-to-many queries as the reduction in size of the intermediate measure group increases. Test results indicate that the greater the amount of compression, the greater the performance benefits—and that these benefits persist as additional fact data is added to the main fact table (and into the data measure group).”