How to connect Power BI Desktop to a SSAS OLAP cube using the HTTP Data Pump

[LuisQuedas]

Hi Community,

After some time trying and failing to connect Power BI Desktop to a SSAS OLAP cube using the HTTP Data Pump, finally I have a solution, and I'm sharing this information with you because I believe there are several people trying to do the same but without success due to the lack of information on the web.

So, some  good news:

• We might have several cubes running on the same server and according to the user we use on the client side we only access a specific Cube where that user is role membership;
• We can use both  PBI Desktop options to connect the OLAP cube, the import option and the connection live option.

About my step-by-step Test:

SERVER Actions:

1. I created a Windows Server 2012 R2 VM running SQL Server 2012 SP4;
2. I’ve configured the  HTTP Data Pump on it according the document: https://docs.microsoft.com/en-us/sql/analysis-services/instances/configure-http-access-to-analysis-s...
3. Very important: I’ve enabled the Windows Authentication Security on the OLAP Web Application:
4. I’ve created two local windows accounts on the server: OlapUser and OlapUser2;
5. I’ve restored two OLAP DBs on the Server:

I’ve created one role on the OLAP DB AdventureWorks and added the user OlapUser as membership of that role:

 

Then, I’ve created one role on the OLAP DB AdventureWorks_2 and added the user OlapUser2 as membership of that role:

 

Client Actions:

To access the OLAP database AdventureWorks:

We need to store the Credentials of user OlapUser on the Windows Credentials of the client machine:

 

Connect the AdventureWorks Cube using the Import Option

1. Use the HTTP data pump as the server name, select import option and click next
2. Select option “Use my credentials” and click connect, (PIB Desktop will use the credentials you stored locally on windows credentials instead your domain credentials)
3. It’s done.. you are able to import data from the Adventure Works Cube to your BPI desktop file:

Connect the AdventureWorks Cube using the Connection Live Option

1. Use the HTTP data pump as the server name, select Connection Live option and click next
2. It’s done.. you are able to connect live the Adventure Works Cube from your BPI desktop file and have access to the cube metadata:

To access the OLAP database AdventureWorks_2:

To connect the AdventureWorks_2 the only thing you need to do is to change the Creadentials associated to the Web Server SQLServer2012 we have stored on the client machine.

 

 Connect the AdventureWorks_2 Cube use the Import Option

1. Use the HTTP data pump as the server name, select import option and click next
2. Select option “Use my credentials” and click connect, (PIB Desktop will use the credentials you stored locally on windows credentials instead your domain credentials)
3. It’s done.. you are able to import data from the Adventure Works_2 Cube to your BPI desktop file:

Connect the AdventureWorks_2 Cube using  the Connection Live Option

1. Use the HTTP data pump as the server name, select import option and click next
2. It’s done.. you are able to connect live the Adventure Works_2 Cube from your BPI desktop file and have access to the cube metadata:

Hope this information helps you to avoid waste several hours to understand how we can use the HTTP data pump with the PBI Desktop and allow your customers to take advantage of the OLAP cube metadata.

Cheers, Luis 

[gracylayla]

Hello,

Nice blog with having good information. It’s very useful for everyone. Thanks and keep posting this type of blog.

[Anonymous]

In my case I could able to connect from power bi desktop, but iam facing problem when adding SSAS cube - http data pump as data source in the enterprise gateway.  It throw error 400

Status code: 400

Error Code: DMTS_PublishDatasourceToClusterErrorCode

Time: Fri Aug 17 2018 13:17:34 GMT-0700 (Pacific Daylight Time)

Version: 13.0.6289.161

Report: Invalid connection credentials

[solvisig]

I´m facing the same issue. Have you figured out a way to connect with the gateway?

[v-chuncz-msft]

@LuisQuedas,

Thanks for sharing the information.