cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LuisQuedas Advocate I
Advocate I

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

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:

image_1.gif

 

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

 

 image_f.jpg

 

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

 image_g.jpg

 

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:

image_a.jpg 

 

Connect the AdventureWorks Cube using the Import Option

 

  1. Use the HTTP data pump as the server name, select import option and click nextimage_b.jpg
  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)image_c.jpg
  3. It’s done.. you are able to import data from the Adventure Works Cube to your BPI desktop file:image_c1.jpg

 

 

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 nextimage_ba.jpg
  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:

image_bb.jpg

 

 

 

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.

 image_2a.jpg

 Connect the AdventureWorks_2 Cube use the Import Option

  1. Use the HTTP data pump as the server name, select import option and click nextimage_2b.jpg
  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)image_2c.jpg
  3. It’s done.. you are able to import data from the Adventure Works_2 Cube to your BPI desktop file:image_2d.jpg

 

 

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 nextimage_3a.jpg
  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:

image_3b.jpg

 

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 

 

 

4 REPLIES 4
Community Support
Community Support

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

@LuisQuedas,

 

Thanks for sharing the information.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gracylayla Advocate I
Advocate I

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

Hello,

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

rgnanaprkaash Helper IV
Helper IV

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

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 Advocate I
Advocate I

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

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors