Timeout Error from Excel Services – “Unable to refresh data for a data connection in the workbook”

Applies to: SharePoint 2010 Excel Services, SQL Server 2005, 2008, 2008 R2, 2012 Analysis Services If you ever get the following error while using Excel Services to kick off a document to query the data from an Analysis Services database, you may wonder where the error come from and how to control it. “Unable to…


Slice on a Calculated Member that uses the Aggregate function

Applies to: SQL Server 2005 Analysis Services, SQL Server 2008 Analysis Services, SQL Server 2008 R2 Analysis Services, SQL Server 2012 Analysis Services Multidimensional Model A user reported seeing two different behaviors by running two slightly different MDXs statement with a where clause on a calculated member that used the Aggregation function. In this post,…


SQL Server Analysis Services Multidimensional Model – Merging Partitions with Slice

According to SQL Server Books Online: Partitions can be merged only if you meet all of the following criteria: http://technet.microsoft.com/en-us/library/ms175318.aspx. They are in the same cube. ·        They have the same structure. ·        They have the same storage modes. ·        They contain identical aggregation designs. ·        They share the same string store compatibility level (applies…


MDX: SET overwrite and the use of EXISTING function

The SET overwrite is largely impacted by the attribute relationships and the behavior does depend on whether the attribute is below or above or no relationship in the relationship chains.   For illustrate the point, I run a test base on the following test environment: ·        SQL Server 2012 Analysis Services Multidimensional Model (Download Center:…


Side effects of storing an Analysis Services partition data in a non-default location

In 2005 and 2008, SQL Server Analysis Services allows users to store partition data in a non-default location. That is something we could not do in 2000 version. To store a partition data in a non-default location means that you can have the partition storage location set to some folder other than the default data…


How to use Partition Processing Destination Task to pump data into a SSAS partition via SSIS

To load and process the data into Analysis Services, SQL Integration Services provides two data flow components: Partition Processing Destination and Dimension Processing Destination. However, I found very few users know how to use them or use them correctly.  Some said the features look nice but can’t find any good example to follow. That makes…


Sum of a SQL Analysis Services calculated measure within a Reporting Services report gives #Error

Summing a SQL Analysis Services calculated measure result within a Reporting Services report gives #Error but the same calculated measure output can be summed within Analysis Services perfectly fine. Environment: SQL Server 2008 Analysis Services and Reporting Services + SP1, Report builder 2.0 sp1. This should be applicable to all SQL 2008 and 2005  Reporting…