Archive for category: Blog

Scenario

You won’t enable SSAS HTTP connectivity via a SharePoint site.

Issue

You followed the instructions in the Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 whitepaper but you get ” Failed to start monitoring directory changes” error when you try to access the SSAS server by its pump URL, such as http://www.adventure-works/olap/msmdpump.dll.

Solution

Ignore the step where you had to create an olap pool and assign the OLAP vroot to it. Instead, assign the vroot to the same IIS application pool as the one the SharePoint site belongs to, such as SharePoint -80. In addition, make sure you enable the web extension to the pump dll, as explained in the above article.

If you have created non-domain Windows accounts on the server for testing, make sure that the server and local Windows accounts (the one the user will use to log in to the user machine) are synchronized, that is, have the same name and password. For example, if I create a non-domain local account Bob on the server where SSAS is installed, Bob must log in to his machine as Bob and both accounts (user and server) must have the same password.

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.