THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Create API for Power BI Designer #powerbi

In the last months, I’ve been trying to suggest a direction for Power BI, but I start to realize that in these days Microsoft is really prioritizing features based on customer feedback. This can make things harder for a new idea to be prioritized, because nobody ask for something completely new. A few days ago, Jamie Thompson asked support for PBIX files saved on OneDrive. I think it’s a good idea, Excel is already supported there, and you can add your vote here:

But I want to ask community support for requesting a feature in Power BI Designer that would help thousands of ISV and millions of users. So, Microsoft, please:

Create a Power BI Designer API and support connecting to PBIX from Excel

You can click on the link and vote it. If you are in a hurry, just to these 2 clicks and receive a big thank you! If you have more time, Please, let me elaborate it – I will include the description I published on UserVoice adding a few comments.

Power BI Designer saves a local PBIX file, which can be a file to export data and data model – in other words, it’s a format containing a complete semantic model. All the applications that today export data in several formats (CSV, Excel, XML), might provide a richer semantic model exporting a PBIX file.

Many ISV/SI that have OLTP and other applications that stores data in some database, usually struggle to offer a compelling BI story to their customers. The smaller they are, the more they feel this pressure because probably the effort they can put in their custom software is minimal.

Today these ISV/SI integrate their solution with external vendor technologies (QlikView is a common choice here). However, the cost of such a solution for the end user is not always appealing, and for this reason the MS partner ecosystem always look for components (charts and pivot tables) to integrate in their solutions.

Providing them an easy and inexpensive way to produce PBIX files “ready to use” straight from their product/solution would provide several benefits:

  • Customers would have something ready to be uploaded to Power BI service
  • ISV/SI would be able to provide a BI solution integrated with MS ecosystem
  • ISV/SI can implement solutions like “send a PBIX file via mail every week to all the agents including only the data of their prospects/customers”
    • Today they already do that using the .CUB format, which can be consumed by both Excel and custom applications
  • Microsoft would increase the number of Power BI users very quickly - Small ISV/SI would be able to implement such integration very fast

What I propose to do is, in descending order of importance:

  1. Support Power BI Designer as a local engine with an API that can be used by anyone and officially support local connections by other programs (starting from Excel)
    • The API should provide the ability to create a data model and to populate it with data by just using API, without any manual interaction
    • Providing the ability to connect from other clients (today it is possible but not officially supported) would increase the adoption.
  2. Document and “open” the PBIX file, so that it can be generated by anyone
    • I think that this is easy for the data model, but not for the data.
    • But without the data, this model would be not so useful, requiring a manual refresh to be populated.
  3. Move Power BI Designer to open source
    • Not really a priority in my opinion, but if the first two wouldn’t be possible, this one could be ok

If you think this is a good idea, here is the point. The top ideas are SQL Server on-prem and SQL Server Analysis Services cubes. Very important features. They have more than 1,000 votes. We need to go there to receive attention from Microsoft. Thus, your vote is not enough. Please, forward the message, convince other people to vote, add your comments, talk very loud.

Microsoft is prioritizing cloud services, but getting quick and large adoption for small databases of thousands of applications, each one with hundreds of users, means generating a huge volume of data models ready to use. Yes, we need DAX in Power BI Designer in order to make it useful, but we know it is already in the roadmap. Microsoft released yesterday support for Google Analytics, and by now it works only in Power BI Designer. Working in a desktop app has its own advantages. I never had a so powerful tool to navigate in Google Analytics data. I’m waiting for DAX. But being able to generate a PBIX file from within an application would be a great leverage to Power BI adoption also for people who are not ready to query data or create a new data model, but that want to analyze their data.

And, of course, if you think it’s a bad idea… comments are open, I’d like to hear other point of views.


Published Tuesday, March 31, 2015 12:37 PM by Marco Russo (SQLBI)
Filed under:



Dennis Wallentin said:


Spot on! While I can to some degree understand the move from MS Excel Microsoft need to give something back. APIs play today already a critical role in automation process and to exchange data. That's why I also think You're suggestion is great.

However, I don't believe Microsoft will give it any priority. Looking back the history says that while Microsoft are very sensitive to what customers request they are reserved when it comes what developers ask for.

I welcome any development that support the availability of APIs.



May 23, 2015 6:53 AM

Marco Russo (SQLBI) said:

Thanks for the feedback!


May 23, 2015 2:18 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement