Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LuisQuedas
Advocate I
Advocate I

PowerBI Desktop using HTTP endpoint to connect an OLAP Cube... Why "Connect Live" isn't allowed?

Hi All,

 

I have an OLAP cube that due to some security restrictions is only available through an HTTP endpoint, (aka HTTP data pump). Before PowerBI we I've been using Excel as a thin client to create our reports and it worked fine, now with Power BI and all the good visualizations it has, we started using it... but using the HTTP endpoint we are only allowed to import data... when we select a connect live option, and we use the HTTP endpoint, e.g. "https://<Server Name>/OLAP/msmdpump.dll", we get always the error:

"We could not connect to the Analysis Services Server because the connection timed out or the server name is incorrect".

 

Am I missing something? Does it have a technical reason to don't allow the Connect Live Option?

 

Many Thanks, Luis 

1 ACCEPTED SOLUTION

Hi All,

After several hours following the HTTP configuration steps, reading the community posts and trying all the proposed solutions without success, creating, configuring and deleting several Azure VMs… I tried another approach to eliminating any Azure missing configuration. I created a VM locally running SQL SERVER 2016 SP1 dev edition and IIS 10, where I installed the HTTP data pump and Finally worked… I was able to connect Power BI Desktop to the Cube, getting all the metadata using the HTTP endpoint…

 

I believe the main issue was that our cube is running on SQL Server 2012....  and when using the Azure VMs with SQL Server 2016 SP1 to do my tests using the public IP address Power BI returned always the error shown in the previous post, maybe I miss some Azure configurations... (note if I'm in the Azure Machine and use the internal IP address the HTTP endpoint works)

 

Before you start using the Power BI Desktop connection live with the HTTP endpoint you need to add a Windows credential with the user and password required by the HTTP end point….

 

Please got to Control Panel > User Accounts > Credential Manager, and click “Add a Windows Credential”,

2017-04-30 08_52_00-Sent Items - l.quedas@8over8.com - Outlook.jpg

 

After I have the HTTP data pump working and create the first Report using Power BI Desktop Edition I tried to Publish it to the Power BI Service, but once is an Analysis Services on- premises... I needed to install the Power BI on-Premisses Gateway... to do that I added to the VM the AD Role... after promoting the VM to a domain controller and set the gateway configuration, everything worked fine... 🙂 

 

the following image depicts all components together,powerBI.jpg

 

Now, the Power BI on-premisses Gateway working with the HTTP endpoint:

powerBI2.jpg 

 

Finally, the dataset using the gateway always up to date,

powerBI3.jpg

 

Hope this information helps others with the same issue I had 😉

 

Cheers, Luis

 

 

 

View solution in original post

17 REPLIES 17

Hi Luis,

 

I have the same issue as reported by @tannera. Do we have any update on this?

 

Thanks,

Hitesh

Hi All,

 

sorry only now reply, but I needed to refresh my mind and repeat the POC to have 100% sure that it works, so the conclusion is... it works!!!... (against what Microsoft told you ;)).... apart from all the information already posted, the only thing you need to do is enable the Windows Authentication of your OLAP web application on IIS, (maybe because SSAS only works with Windows Authentication ?????)

 

photo1.jpg

 

If you have created  a new Windows Credential on your laptop with an Admin User of the SSAS Server that you want to connect via http endpoint,

photo2.jpg

 

 

 

You will be able to connect the Cube with an HTTP endpoint using the  Connect live option:

photo3.jpg

 

 

photo4.jpg

 

Hope this helps, if you have other questions please let me know.

 

Cheers, Luis

 

Hi Luis,

 

Thanks for your prompt reply. I see that you have both Basic and Windows Authentication enabled on your virtual directory and perhaps that is what was missing from my set up.

 

I will give that a try and post back with my outcomes.

 

Thanks for your help so far.

 

Hitesh

Hi Luis,

 

So, yes I was able to confirm and get Power BI Desktop to connect to OLAP cube using HTTP, but it doesnt seem to work with HTTPS.


Are you aware of any such issues? Have you tried using HTTPS?

 

Thanks,

Hitesh

Hi Luis,

 

Actually, I did work for me when using HTTPS, I just didnt have the credentials stored for the right web server.

 

It is all working as we would expect it to work.

 

Thanks again.


Hitesh

Hi Guys,

Can you confirm that its working with Basic credentials from Another Domain ?

Is it mandatory to have local accounts ?

 

For now i can authenticate via Excel, but PBI Desktop do now want to work even with import.

 

thanks in advance,

Oleksiy

Anonymous
Not applicable

Thank you for your response. 

 

I does work with windows authendication (Same domain) using NTLM.

 

What we are tyring to achive here is through ADFS login screen via External IDP.

 

Thanks

Kay

About year ago i opened a ticket with MS, and they confirmed that its not possible at that moment. They also said that this functionality is in roadmap, but i haven`t heard about it since then.

 

br

Oleksiy

v-qiuyu-msft
Community Support
Community Support

Hi @LuisQuedas,

 

Based on my test in desktop version 2.45.4704.722, we can successfully connect to SSAS cube use HTTP data pump via Connect Live. Please try to update the desktop version to the same version.

 

Also from the error message, the issue can be caused by IIS server. Please check settings follow S-Driancourt's reply in this thread: Using HTTP data pump (msmdpump.dll) leads to (can't display visual) errors.

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Qiuyun Yu,

 

Thanks for your reply, unfortunately it didn't work.. or I'm doing something wrong... 😞

 

 

My Power BI Version:

 

powerBIVersion.jpg

 

Following the suggested post, I made the following changes:

Application Pool changes:

Apppool-setting.jpg

 

INI changes:

ini_configuration.jpg

 

 

After Restart the IIS... the error still appears... :S

 

Power BI Desktop-error.jpg

 

Any Idea?

 

FYI... the Data pump is using basic authentication due to the SSAS is running on the cloud where we only have access to it by HTTPS.

 

Thanks, Luis

 

 

Hi Again,

 

few more tests...

 

I created two Azure VMs with SQL Server 2016 SP1 Dev Edition and with Power BI Desktop on both, (let's call them 2016SP1Server and 2016SP1Client), one of them I configured the HTTP Data Pump and restored an OLAP cube. 

 

> I'm able to connect the cube from both machines using the HTTP Data pump and the import option...

> When I'm in the 2016SP1Server I can connect the cube using the HTTP data pump and the Connect live 🙂 .. the URL used : 

2017-04-26 01_30_04-Reply to Message - Microsoft Power BI Community.jpg

 

When I'm in the 2016SP1Client I can't connect the cube using the HTTP data pump and the Connect live :(.. with the same error I got before:

Power BI Desktop-error.jpg

 

Any suggestion?

 

Cheers, Luis

 

 

Hi All,

After several hours following the HTTP configuration steps, reading the community posts and trying all the proposed solutions without success, creating, configuring and deleting several Azure VMs… I tried another approach to eliminating any Azure missing configuration. I created a VM locally running SQL SERVER 2016 SP1 dev edition and IIS 10, where I installed the HTTP data pump and Finally worked… I was able to connect Power BI Desktop to the Cube, getting all the metadata using the HTTP endpoint…

 

I believe the main issue was that our cube is running on SQL Server 2012....  and when using the Azure VMs with SQL Server 2016 SP1 to do my tests using the public IP address Power BI returned always the error shown in the previous post, maybe I miss some Azure configurations... (note if I'm in the Azure Machine and use the internal IP address the HTTP endpoint works)

 

Before you start using the Power BI Desktop connection live with the HTTP endpoint you need to add a Windows credential with the user and password required by the HTTP end point….

 

Please got to Control Panel > User Accounts > Credential Manager, and click “Add a Windows Credential”,

2017-04-30 08_52_00-Sent Items - l.quedas@8over8.com - Outlook.jpg

 

After I have the HTTP data pump working and create the first Report using Power BI Desktop Edition I tried to Publish it to the Power BI Service, but once is an Analysis Services on- premises... I needed to install the Power BI on-Premisses Gateway... to do that I added to the VM the AD Role... after promoting the VM to a domain controller and set the gateway configuration, everything worked fine... 🙂 

 

the following image depicts all components together,powerBI.jpg

 

Now, the Power BI on-premisses Gateway working with the HTTP endpoint:

powerBI2.jpg 

 

Finally, the dataset using the gateway always up to date,

powerBI3.jpg

 

Hope this information helps others with the same issue I had 😉

 

Cheers, Luis

 

 

 

Hello Luis,

 

We are trying to use PowerBi with the datapump.

We want to use basic authentication but it doesn't work with the live connection.

Do you see and solve this issue ?

 

 

Hi Eric, sorry only reply this post now...

 

Are you connecting directly Power BI desktop to your source using the HTTP datapump? are you tyring to publish your reports on the Power BI Cloud Service?,  would you like to provide me more information about what you are trying to do and your setup?

 

Thanks, Luis

Same issue here. 

Hi tannera

 

Are you connecting directly Power BI desktop to your source using the HTTP datapump? are you tyring to publish your reports on the Power BI Cloud Service?,  would you like to provide me more information about what you are trying to do and your setup?

 

I need to know more details 😉 

 

Thanks, Luis

Hi Luis, thanks for getting in touch. I'm surprised you managed to make the live connection to a msdmpump.dll work because Microsoft responded to me, saying it is not supported in live connection. 

 

At the moment we are just trying to live connect from PBI desktop to our datapump (SSAS cube). The pump is setup on one of our servers running SQL2016SP1CU4 and IIS 8.6. We currently get the problem shown in the below screenshot (Could not connect...).

 

 

pump1.png

 

 

'Import' option works. I think it works because of the addition authentification option compared to live connection mode (we use the 'Basic' username and password option). In Excel and Tableau, our setup works fine for live connection, but at least they prompt for username/password; PowerBI just rejects immediately. 

 

I added the Windows credential, but no luck. 

 

pump5.png

 

 

Any ideas? Does it have anything to do with HTTP/2 you think? (our web server only supports http/1.1)

 

Thanks a lot. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.