Support SSAS connection using HTTP data pump (msmdpump.dll)
I know its a bit strange that we would want to configure the Power BI SSAS connector to point to an HTTP msmdpump, but it allows us to do something we can't do when going directly to the server. However, when I get to the last phase where it creates the connection in Power BI, it returns a strange "The remote server return an error: (404) Not Found....." that I do not get when going directly to the server. Could you validate my results and perhaps fix?
Jakub Marsik commented
Hello guys, if you are ok with MDX and would like to get data from SSAS instance that is outside of AD domain and published with MSMDPUMP HTTP interface into Power BI Service with scheduled refresh capability, check out my little helper proxy application:
It simply gets parameters from URL and/or request body, executes MDX query (connection to SSAS can be direct with AS protocol or through MSMDPUMP) and returns back results in JSON format. You can use it from Power BI with Web.Contents M function.
pato lobos commented
As stated in one of the comments below this is supported since day one :
You need to add an entry in Credential Manager under 'Windows Credentials'.
Just go to :
Control Panel\All Control Panel Items\Credential Manager
And "Add a Windows Credential"
Internet or network address:
Alvin Lau commented
Is Live Connection from PBI report server to msmdpump.dll supported as of today?
As of July 2017, the live connection to a msmdpump.dll is NOT supported.
Excel and Tableau support this, so the question is: is the Power BI team working on this issue?
Sylvain Driancourt commented
In order to avoid opening port with fixed IPs we are also trying to connect Power BI Desktop in live connection to SSAS 2016 Multidim through http://..../msmdpump.dll
It globally works, but we have frequent errors (Can't display visual) with the message: "The http server returned the following error: Service unavaible".
-->This is strange, because this seems to be randomed. When we click on Refresh we can get the visual to work.
Has anyone experienced this?
Wouter Zijlstra commented
i didn't see a comment where someone got a working connection with a datapump (msmdpump.dll). Well, it can be done!!
First you need an entry in de Credential Manager under 'Windows Credentials'. You can add one with the connection string, username and password.
In Power BI Desktop you can make a connection with SSAS by using the datapump connection string as server and make a live connection with your model. Start building your report!!
In PowerBI.com you can make a gateway on the same connection string and datamodel and it will work in Power BI service too!
Scott Miller commented
Our organization does not allow us to use SSAS directly due to security concerns.
I have the same scenario in my organization - Users are not allowed to access SSAS directly because it is in another security zone. We must use http for this communication.
What is your scenario? Can you share more details? Can you access the AS server from Excel or SSMS?
we have a custom authentication flow (IHTTPMODULE), which we manage as a modules over MSMDPUMP.
we fork the request's made to analysis services (AS), enrich the requests with custom data. before AS can process them.
it just does not make sense to have this module anywhere else. MSMDPUMP makes it easy to set up custom pipelines before and after AS handles the request.
Bryan Bedard commented
Our corporate network security rules insist on a 3 tier architecture and require us to put our SSAS server in a database DMZ and we have to go through a midware layer or web proxy to reach it from applications. We can't connect to it directly. We have data pump configured and it works great from Excel and we can import into Power BI Desktop but live query is what we need for our end users. This is the single barrier preventing us from rolling it out enterprise wide.
Tony Valentine commented
Well msmdpump.dll is not supported but it appears Power BI has added the capability to map usernames. If you go to the Data Source Settings and toggle to the Users tab, there is a button called Map User Names which lets you alter the EffectiveUsername string via custom mappings. Thank you MSFT for adding this feature!!
this requirement is really a big need for large ISV's hosting lots of customers. Row level security is simple and the amount of licenses that MSFT can sell would be large. Please consider this feature request. At least let the Enterprise Gateway support it without having to do the UPN handshake which will not work for hosted customers that are not on their domain... this is a basic problem.
I have the same requirement, I am developing a SAAS application based on SSAS data and I would like to let external users connect to my cube without the process of creating them / syncing them in my AD. In my case there should be hundreds of users and creating an AD account for each of them in my domain is not a good option.
Hi, I have few customers who wants to buy power bi but with possibility to connect via msmdpump.dll (https)
pato lobos commented
Hi, you can connect with Power BI desktop, but onl browsing not "Connecting Live" because they somhow forgot to put the auth dialog box in it. So open Pbi desktop and browse it, you can retrieve some info, but it will crash you AS instance, rapidly consuming all you memory if you choose any measure, you might get away with tables and dimensions if they are not too big. Please Power BI team implement this scenario! :)
Tony Valentine commented
This still does not work with the release version of Power BI Analysis Services Connector. It fails with the same 404 error. Any update on where this feature is on the priority list?
Rob Hogue commented
I asked Power BI uservoice about msmdpump.dll support, and the response was as follows:
"At the moment we don't support connecting via msmdpump. Please go ahead and suggest it in our forum!"
We are using the Telerik Pivot Grid which uses msmdpump.dll, and I also believe infragistics uses it also. Having this support in Power BI is also good for demos and testing SASS scenarios without going through a Federation process.
Excel supports msmdpump.dll. msmdpump.dll can be setup as anonymous for quick testing of a new SSAS service, and have basic authentication added.
You should see an option to vote next to the main title of this suggestion.
John Bacan commented
Where do I vote for this ?? This is something we really could use ?
Thank you for the feedback. We understand the consistency part and this feature is on the backlog. User voice is a great way for us to figure out the priority in which we should execute & release features. So keep voting :)
Mapping identities is a tricky feature but we understand the power/benefit it has. We hope to enable it soon.