Karan Gulati (SSAS Maestro)

Issue:

Using Excel 2010 when try to save a file to SharePoint Gallery it fails with error stating:

Excel 2010 –>File –>Save to SharePoint

Error

Resolution –

When accessing these files from a SharePoint document library, the ACE provider will attempt to connect to the library using a WebDAV client connection, which is not available on Windows Server 2008 and Windows Server 2008 R2 by default. You can install the WebClient service on Windows Server by enabling the Desktop Experience feature set.

How to enable:

http://support.microsoft.com/kb/947036

WebDAV is the protocol that SharePoint uses to make a SharePoint site look like a file share. And unfortunately it is the way that many of the Excel features use for browsing a web site.

When processing SSAS Data Mining Structure which is based on a Cube (DataSource is Used as Cube) in such a case Analysis Services uses service Startup Account while processing Mining Structure.

If Analysis Services Startup Account doesn’t have Full Permission on OLAP Cube then processing will fail with below mentioned error:

Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: The UseTransaction and MasterTransaction request properties are reserved for database administrators..
Errors in the OLAP storage engine: An error occurred while processing the ‘Internet ~1 ~MG’ partition of the ‘Internet ~1 ~MG’ measure group for the ‘Customer Clusters ~MC’ cube from the Adventure Works DW 2008R2 database.
Server: The operation has been cancelled.
Errors in the OLAP storage engine: An error occurred while processing the ‘~CaseDetail ~MG’ partition of the ‘~CaseDetail ~MG’ measure group for the ‘Customer Clusters ~MC’ cube from the Adventure Works DW 2008R2 database.

To work around this issue –
Ensure that <ServiceAccountIsServerAdmin> Property in msmdsrv.ini file is set to 1 (True)
Default – Location of msmdsrv.ini file (C:\Program Files\Microsoft SQL Server\MSAS10_50.SQL2008R2\OLAP\Config)
Or
Create a Role in Database – Grant Full Control to this Role and Add Service Account Member of this Role