PowerView is one of the shiny new features that are available to SharePoint users with the implementation od SQL Server 2012. To be sure, you don’t need to convert your database engine over to SQL Server 2012, but you do need to install PowerPivot for SharePoint, and/or the new Reporting Services service application for SharePoint, both only available in SQL Server 2012.
PowerView requires a connection to an xVelocity Analysis Services engine (formerly known as tabular mode or VertiPaq), or to an Excel spreadsheet with an embedded PowerPivot model in order to function. When connecting to the Analysis Services engine, there are a couple of permissions that need to be set on that engine that you should be aware of.
If you wish to connect to the Analysis Services engine, you have two choices. Firstly, you can use a Reporting Services connection (.rds) and select “Microsoft BI Semantic Model for PowerView” as the Data Source type.
Once this is done, it can be used to create PowerView reports. This approach works without any additional configuration. The reason that this is so is that Reporting Services takes advantage of the EFFECTIVEUSERNAME connection property for user impersonation. This is something that BISM does as well. When using an RS connection, the stored credential (in this case) is used to establish a connection with the data source, and then all queries are executed with the identity of the requesting end user (provided that the “Set execution context…” option is not selected.
For further reading on how Reporting Services uses the EFFECTIVEUSERNAME property, check this article. Since BISM uses the same property, it’s highly relevant to PowerView.
However, the problem arises if we choose the second option, and want to work with a BISM file directly. If we want to use a basic BISM file, we first add it by creating a document with the the BI Semantic Model content type.
However, the connection form is far simpler than the one used to configure the Reporting Services connection. You’ll notice immediately that there is no option for storing credentials. For a server connection, all that is necessary is a name for the connection, the name of the server, and the name of the database.
Unfortunately, if you haven’t gotten everything configured just so and you click ok, you’ll receive the error “Cannot connect to the server or database”.
This is due to the fact that when the server performs the validation check against the Analysis Services server, it uses a system account to first connect to the AS server. This is documented in this Connect forum entry. The corrective action is to add the account to the Analysis Services Server Administrator role:
What account needs to be added? The forum article I referenced above indicates the PowerPivot service account, which is now listed in SharePoint as the “SQL Server Analysis Services” account. However, my testing has indicated that the farm account is the one that is used to verify connections with Analysis Services, so that’s what I’m recommending here. This is only used for validation purposes, and to get around this problem without adding the account to the administrators role, you can simply select the “Save the link file without connection validation” option. This may however be unsettling to users.
Once you’ve created your BISM connection, you should then be able to use it to launch Excel, or create a PowerView report. When you launch Excel, the BISM is referenced by an Excel connection, which then connects directly to the data source with the credentials of the end user. No problem. However, if you attempt to create a PowerView report, you may find yourself faced with “An error occurred while loading the model for the item or data source”.
In the body, it misleadingly leads you to believe that it’s the end user (btidwell in this case) that doesn’t have access. What should happen is that, as explained above, the Reporting Services service makes a connection with the AS server, and then uses the EffectiveUserName property to impersonate the user. For more detail on how BISM authenticates to back end data sources, see this MSDN article.
If you’re seeing this error, the chances are that the service account that is being used for Reporting Services doesn’t have access to the Analysis Services database.
As in the verification step above – granting the account access to the specific database doesn’t do it, you need to add the service account that is used by Reporting services to the Server Administrators role. Once you’ve done that, PowerView should behave smoothly.
4 Comments