Power BI – Configuring MS Analysis Server as a Data Source

 

Background

As part of a recent deployment cycle, we needed to provision connectivity from Power BI to our internal MS Analysis Server database \ cube.

 

History

A lot of the plumbing predates me and so I will not have an opportunity to talk about setting up the Gateway, opening up the firewall ports to allow Web Service traffic from the Power BI Internet Portal to our internal Gateway Host.

Knowing that these things were already setup and that we were set up to consume traditional SQL Server relational data meant most of the connectivity work was already done.

 

Error Message

Image

error_ssas (cropped up)

 

Textual

  1. Status Code : 400
  2. Error Code :- DM_GWPipeline_Gateway_DataSourceAccessError
  3. Underlying Error code :- -1055391722
  4. Underlying Error message :- The user does not have permission to call the Discover method
  5. DM_ErrorDetailNameCode_UnderlyingHResult :- -2146233088

 

 

TroubleShooting

SQL Server Profiler

Image

DoesNotHaveDiscoverPermission-20170310-1202PM ( BrushedUp)

 

Textual

  1. Event :- Server State Discover Begin / Session Initialize / Server State Discover
  2. Event :- Error
      • TextData :- The user does not have permission to call the Discover method

 

Remediation

Security

Using SQL Server Management Studio ( SSMS ), connect to SQL Server Analysis Service, review and augment access privileges to the designated account.

Tabulated

Page Definition Granted
General Define the Role, it’s name, general description and database permission.  The general database permissions are Full Control, Process Database, and read definition At the database level granted all listed permissions – Full Control, Process database, and read permission
Membership List principals ( OS or Active Directory Accounts) that are earmarked for this role.  Added our Service\Proxy granted
Data Source Permissions on Data Source to read data and definition Granted our account permission to read data and definition
Cubes Lists all cubes and allow us to define read, write, and drillthrough, and process Granted our account permissions to read, write, drillthrough, and process all cubes
Cell Data Reading of cell content, reading of cell content contingent on cell security, and reading & writing of cube’s content Granted read, read based on cell contingent security, and write ability
Dimensions List all dimensions.  And, allow permissioning of Read & Write data, definition, and processing. Granted ability to read data, read definition, and process dimensions.
Dimension Data Allow global database permission to be granted or granular permission to individual dimension \ attributes. Settled on default global permissioning
Mining Structures List all mining structures We have not enabled mining ability and so did not have that option enabled

 

 

 

ScreenShot

Analysis Service Instance Hierarchy

MSAnalysisService-Hierarchy [CroppedUp]

 

 

Role

General

 

Role Membership

Here we add our surrogate account to the define role, rolePowerBI

Role-PowerBI-Membership [CroppedUp]

 

Data Sources

We grant our Role ability to read both data and definition on the DataSource.

Role-PowerBI-DataSources

 

Cubes

Granted Read, Write, Drill Through, and Process access to the Local Cube.

Role-PowerBI-Cubes

 

Cell Data

Granted Read,  Read-Contingent, and Read-Write permissions to the Cube’s content.

Role-PowerBI-CellData (Brushed Up)

 

Dimensions

All of the Cube’s dimensions are itemized.

We accepted the default choice which is to be able to Read Data, Read Definition, and Process Data.

Role-PowerBI-Dimensions

 

Dimension Data

List the various dimensions and attributes.

Again we accept the default of granting permissions to all members.

Role-PowerBI-DimensionData (Brushed Up)

 

Mining Structures

In our case we did not have mining structures defined and consequently there was nothing to accept nor customize.

Role-PowerBI-MiningStructures

 

Summary

Unfortunately, this is a public place holder.

Our error was unable to call the Discover method.

I worked with the Subject Matter Expert, SME,  who came to us.

I was afraid it was a Network Connectivity Issue, but he assured me it was not.

He assured with me that though Power BI is publicly hosted in the clouds it relies on Web Services over HTTP & HTTPS to communicate with the gateway.

And, since our gateway service is hosted on the same box as our two data sources, SQL Server relational and OLAP, we should not be encumbered by the Network Firewall.

So we went in and reviewed the Data Source setup for relational engined.

Noted that it connects via SQL Authentication.

Knowing that SQL Server Analysis Service does not support SQL Authentication, just Integrated OS Authentication, we configured the data source to use one of our Service Accounts.

The steps listed above are the ones we took to grant permissions to the SSAS Cube.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s