Prologika Forums
Making sense of data
Nasty SSAS 2008 Bug

Blogs

Prologika (Teo Lachev's Weblog)

Books

Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling)Learn PowerPivot and Analysis Services-Tabular at your own pace with our latest book Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling). It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Training

Applied Microsoft SQL Server 2008 Reporting Services

We offer onsite and online Business Intelligence classes!  Contact us about in-person training for groups of five or more students.

For more information or to register click here! 

Syndication

Archives

There is a nasty known bug with SSAS 2008, where if a query that requests two measures with Sum and LastNonEmpty aggregate functions side by side and the query uses NON EMPTY (which all OLAP clients use by default), the server forces the measure with the SUM function to display empty.

Steps to repro:

Use the Analysis Services Tutorial cube (Lesson 10) which is included in the SSAS samples. Don't use the Adventure Works cube because it has measure expressions for certain measures, so changing a measure aggregation function to LastNonEmpty and deploying the cube results gives the error "Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of 'Internet Sales', cannot be verified against the source object." That's a different issue that has been around since SQL Server 2005 but apparently didn't get fixed.

1.    Open the Analysis Services Tutorial project from Lesson 10

2.    Open the Analysis Services Tutorial.cube in the Cube Designer.

3.    In the Cube Structure tab, expand the Internet Sales measure group and select the Internet Sales-Order Quantity measure.

4.    In the Properties pane, change the AggregateFunction to LastNonEmpty.

5.    Deploy the cube.

6.    Browse the cube with the cube browser or Excel. Create a report that requests Internet Sales Amount and Internet Sales-Order Quantity measures sliced by the Subcategory attribute of the Product dimension.

Notice that the query forces Internet Sales Amount to empty. While waiting for an official hotfix from Microsoft, you can use one of the following workarounds meanwhile:

1. Don't use the NON EMPTY clause in the query 

or

2. Change the following property in the \Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini file.

        <DisableCalcExpressNonEmpty>1</DisableCalcExpressNonEmpty>

Not sure what DisableCalcExpressNonEmpty does as it is not documented but appears to work.

UPDATE 8/18/2008

DisableCalcExpressNonEmpty is a new property in AS 2008. Severe performance degradation is likely to result when set to 1, but it should be comparable to AS 2005 performance if not a little better. A hotfix better come out quickly Sad


Posted Fri, Aug 15 2008 8:11 AM by tlachev

Comments

Prologika (Teo Lachev's Weblog) wrote Cumulative Update Package 1 for SQL Server 2008
on Tue, Sep 23 2008 1:20 PM

Microsoft has released the first for SQL Server 2008 cumulative update package. Among other issues, it