Applied BI Semantic Model Course Available

/0 Comments/in /by

Due to the popular demand, I’m adding a new 5-day Applied BI Semantic Model training course to our training classes. This course reflects the growing need of BI developers to implement both Tabular and Multidimensional semantic models and acquire the necessary knowledge in a compressed educational format. I’m teaching this class for 30 developers next month alone.

Targeting BI developers, this intensive 5-day onsite class is designed to help you become proficient with Analysis Services and acquire the necessary skills to implement Tabular and Multidimensional models. Use the opportunity to ask questions and study best practices that will help you implement scalable and secure organizational models.

Power View Can’t Load Power Pivot Models

/1 Comment/in /by

Scenario: You have created an SSRS application in SharePoint and specified that the application pool would use a specific domain service account that is designated to SSRS. When you attempt to create a Power View report from a deployed Power Pivot model, you get the following error:

An error occurred while loading the model for the item or data source ‘<path to Excel file>’. Verify that the connection information is correct and that you have permissions to access the data source.

Here is the full stack:

<detail><ErrorCode xmlns=”http://www.microsoft.com/sql/reportingservices“>rsCannotRetrieveModel</ErrorCode><HttpStatus xmlns=”http://www.microsoft.com/sql/reportingservices“>400</HttpStatus><Message xmlns=”http://www.microsoft.com/sql/reportingservices“>An error occurred while loading the model for the item or data source ‘<path>’. Verify that the connection information is correct and that you have permissions to access the data source.</Message><HelpLink xmlns=”http://www.microsoft.com/sql/reportingservices“>http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.5058.0</HelpLink><ProductName xmlns=”http://www.microsoft.com/sql/reportingservices“>Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns=”http://www.microsoft.com/sql/reportingservices“>11.0.5058.0</ProductVersion><ProductLocaleId xmlns=”http://www.microsoft.com/sql/reportingservices“>127</ProductLocaleId><OperatingSystem xmlns=”http://www.microsoft.com/sql/reportingservices“>OsIndependent</OperatingSystem><CountryLocaleId xmlns=”http://www.microsoft.com/sql/reportingservices“>1033</CountryLocaleId><MoreInformation xmlns=”http://www.microsoft.com/sql/reportingservices“><Source>ReportingServicesLibrary</Source><Message msrs:ErrorCode=”rsCannotRetrieveModel” msrs:HelpLink=”http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.5058.0” xmlns:msrs=”http://www.microsoft.com/sql/reportingservices“>An error occurred while loading the model for the item or data source ‘<path>’. Verify that the connection information is correct and that you have permissions to access the data source.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode=”rsErrorOpeningConnection” msrs:HelpLink=”http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsErrorOpeningConnection&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.5058.0” xmlns:msrs=”http://www.microsoft.com/sql/reportingservices“>Cannot create a connection to data source ‘TemporaryDataSource’.</Message><MoreInformation><Source>Microsoft SQL Server 2012 Analysis Services</Source><Message>SetAuthContext need to be run as sysadmin.</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns=”http://www.microsoft.com/sql/reportingservices” /></detail>

Resolution: The most important information is the message “SetAuthContext need to be run as sysadmin“. This tells us that the SSRS account doesn’t have admin access to the SSAS instance configured in SharePoint mode. Another way to confirm this is to run the SQL Profiler connected to the SSAS instance, e.g. SERVER\POWERPIVOT, and attempt to create a Power View report. You would see the same error message in the Profiler.

How do we fix this horrible issue? Connect to the SSAS instance in SSMS, right-click on the server node and then click Security. Then, add the SSRS account to the list to grant it admin rights to SSAS. This is something SharePoint should have done during the setup of the SSRS application since the SharePoint farm account has admin rights to the SSAS instance but apparently this step has been omitted.