Power BI Insights & Updates: DirectQuery on a REST API; Training; New visuals

April 27 2021

Power BI pros share tips on building a DirectQuery dataset on a REST API and training, and Microsoft offers two new updates for the product.

Building a Power BI DirectQuery dataset on a REST API

Chris Webb explored a question that he gets often.  Many users want to know how to use a Power BI DirectQuery atop a REST API. While this seems achievable, almost everyone who tries it fails and he set out to explain why.

Power BI users interact with the offering through the Import and DirectQuery modes. The Import mode caches data in Power BI's internal database, answering DAX queries from there, whereas DirectQuery stows no data in the program, querying the data source instead. He showed how to apply slicers to a basic database and the SQL output.

Although Power BI Premium sets no cap on the number of refreshes a user can trigger with the API used by Power Automate, there are problems. Users will find that refreshing an Import mode dataset is relatively slow and commercial APIs are limited in the number of times they can be called. Additionally, there are no straightforward ways to pass end user selections back to the API as parameters, and refreshes by end users can cause a host of disruptions. Users can try to work around these limitations by creating an ODBC provider atop the API and setting up a Power BI custom connector.

He wrote:

The good news is that some third-party vendors, for example CData and Progress (there may be others too) sell Power BI custom connectors that allow you to create DirectQuery datasets on top of REST APIs. These connectors are thin wrappers for ODBC providers and work by caching the data returned from the API within the provider and providing a SQL query interface on top of that. I haven’t tested these products so I can’t comment on how well they work or what their performance is like. You’d need to pay extra to use them, of course, and since they are custom connectors you’d need to have an on-premises data gateway to use them.

Building Power BI skills

Writing on Power BI Tips, Seth Bauer discussed ways to build Power BI skills. Setting aside time is important. With schedules cleared up, users can move through books, blogs and documentation and practice doing important tasks in Power BI like connecting to data sources, cleaning data, modeling, running DAX queries, and creating visualizations.

He encouraged users to try out a Skills Matrix he shared and swap experiences with other users.

Power BI is the culmination of technologies that MSFT has had for many years. You may have some idea of this already. There has been a ton of work done to make sure Power BI has the full enterprise features from its originating products. Therefore, knowing how all these technologies work together is important. Especially something like Analysis Services. Understanding these concepts and tools is key to understanding how to work and interact with them. What does O365 have to do with Power BI? What is Azure AD? Where do I purchase licenses depending on the business needs? Why are there all these External Tools and how do I use them? All these are questions you should have an idea of how to answer.

Recent Microsoft Power BI announcements

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About MSDW Reporter

More about MSDW Reporter