It’s been a while since we have written about Power BI, and in that time Power BI has greatly expanded its capabilities and added new features. If you are not familiar with Power BI, let’s go through what Power BI is and how it works, and then touch on what’s changed and what’s new.
Overview of Power BI
Power BI is Microsoft’s business analytics solution that allows users to connect to hundreds of data sources, visualize the data found there, and publish visualizations securely to the cloud to be accessed by your organization, whether on their mobile phone, tablet, or desktop computer. When our company president, David Hammond, wrote about Power BI back in 2015, he mentioned four different Excel plug-ins (Power Query, Power Pivot, Power Map, Power View), which provided the foundation of Power BI. Those four have now been integrated into Power BI.
There are two main components in Power BI to model and visualize data: Power BI Desktop and Power BI service. Similar to using a Microsoft Office application like Word in the browser, Power BI service is cloud-based and is great for easy viewing and quick edits but is limited compared to the desktop version. If you need to make major changes or create a brand-new set of visualizations, then Power BI Desktop provides a full range of actions needed to connect to data, transform it and create dashboards.
Figure 1. How Power BI works
As an example, at Abel Solutions we leveraged Power BI to better visualize and track the weekly and monthly project time tracking of our employees. To start, we hooked up Power BI Desktop to the SQL database of the back-end time tracking software to get the raw data needed to build visualizations. Still using Power BI Desktop, we narrowed the amount of data we brought in and created some new columns and connections to create dashboards that gave a whole new insight into where and how hours were being spent. We then published the dashboard up to the cloud on the Power BI Service and, using a Power BI gateway, set up a daily scheduled refresh to keep our dashboards accurate. Once we were satisfied with how it looked and worked, we shared those dashboards organization-wide. Power BI took what was previously a time-consuming exercise using multiple canned reports and Excel spreadsheets and made it into a streamlined process that gave us new insights into this data.
Now that we have the basics of Power BI covered, let’s talk about a few new items that have changed or are new in Power BI.
One of the biggest increases in value for using Power BI is how many different data sources with which it can connect. Microsoft is regularly adding or updating different data sources. Whether you want to connect to a simple Excel sheet with a couple columns, a SQL database or a third-party cloud solution like Salesforce, Power BI can connect and allow you to combine this data together with other data sources. The number of data sources continues to grow. Follow this link to see a current list from the Power BI Desktop. Once you’ve brought in the data, Power Query will then help you merge and transform the data into the desired format for your dashboards.
Q&A Natural Language Query Engine
Another powerful feature in Power BI is the Q&A feature that utilizes common phrases and natural language to ask questions and get answers from your data. The Q&A feature works by auto-completing recognizable words while matching words that are used in your dataset to dynamically create charts and graphs that answer your query. It even restates your question based on the dataset so that you can make sure that your question was understood.
This feature allows those building dashboards to not have to worry about creating charts and graphs for every conceivable relationship between the data. Rather, it enables the analyst to build what is most needed and let the end-user query any ad-hoc items where they want to delve deeper. In this way, the Q&A feature allows enormous flexibility for users.
Figure 2: Q&A Feature on Power BI Service
In the example above, we typed “Total sales by product category” into Q&A and can see that Power BI created a bar graph visualization to show the answer to our question showing the number of sales in the millions by the four sales categories. On the bottom of the picture is the question restated.
SharePoint Modern Page Embed
Let’s say that you created a great dashboard using Power BI, and you want it to be seen both inside and potentially outside of your organization. The easiest way to accomplish this would be to embed that dashboard on a SharePoint modern page. Power BI makes this easy by generating a URL that can be copied into a modern SharePoint page, and in a few clicks with a modern web part, you can embed the dashboard onto a SharePoint page for others to see without needing to go to Power BI.
Power BI Report Server
Another new capability in Power BI is the Power BI Report Server which allows you to use Power BI on-premises. The report server has both capabilities of the Power BI online service by hosting Power BI reports and SQL Server Reporting Server by hosting paginated reports. The report server is a great tool for those not currently on the cloud and want Power BI reports on premise or a stepping stone for organizations who have SQL Reporting Services and plan to move to the cloud in the future.
Figure 3. Power BI Report Server – web portal
There are two tiers of licensing available: Power BI Pro and Power BI Premium. The Pro level is priced per user per month while Premium is per node per month for larger deployments.
For Power BI Report Server, you need one of two different licenses: Power BI Premium or SQL Server Enterprise Edition with Software Assurance. If you are considering a hybrid deployment with the cloud and on-premises computing, a Premium license will allow that as well.
Power BI has come a long way from when it first debuted and continues to grow and add features. It is a powerful tool that, when leveraged properly, can be a big benefit for organizations of any size to provide new insights.
This tip co-written by Abel Solutions Associate Consultant Dushyant Raichur and Senior Consultant Rich Kumpitsch.