SQL2008: MSOLAP & SSRS Class not registered error with certain dimension.

Question

Our product leverages Analysis Services combined with Reporting Services, we recently made some changed to add compatability for SQL2008. Everything works great except with certain dimensions, drillthroughs or measures added to a report we suddenly get this following error:

Server: The operation has been cancelled. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'Adventure Works DW'. OLE DB error: OLE DB or ODBC error: Class not registered.

Any ideas?

Answer 1

Found the issue.

SQL2008 doesn't come with the SQL Native Client provider which my cube was using to retreive data from the datawarehouse.

Solution:

Change the provider to SQL OLE DB Provider instead.

You can use XMLA if you so wish:

<Object>
    <DatabaseID>Adventure Works DW</DatabaseID>
    <DataSourceID>Adventure Works DW</DataSourceID>
  </Object>
  <ObjectDefinition>
    <DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">
      <ID>Adventure Works DW</ID>
      <Name>Adventure Works DW</Name>
      <ConnectionString>Provider=SQLOLEDB.1;Data Source=s;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDWDW</ConnectionString>
      <ImpersonationInfo>
        <ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>
      </ImpersonationInfo>
      <Timeout>PT0S</Timeout>
    </DataSource>
  </ObjectDefinition>
</Alter>