Over the past couple of years, our organization has used Power BI to build several self-serve analytics dashboards for our clients. For those of you who may be unfamiliar, Power BI is a business analytics software for developing interactive reports and sharing them with users in the organization who are looking for data-driven insights. Microsoft dubs Power BI as a tool that allows you to “Enable everyone at every level of your organization to make confident decisions using up-to-the-minute analytics.” Released to the public in July of 2015, Power BI is a relative newcomer to the business analytics toolkit and is Microsoft’s answer to other popular visualization software tools, such as Tableau and MicroStrategy.
Creating Power BI dashboards for numerous clients has provided valuable learning opportunities and the chance to assess the software’s strengths and weaknesses. As Kelsey Hightower, principal engineer for Google Cloud, wrote, “You haven’t mastered a tool until you understand when it should not be used.” Now that I understand what Power BI can and cannot do, I would like to share some of my takeaways with you.
Pros of Power BI
Power BI’s biggest selling point is its interactive content and self-service capability. Traditionally, business intelligence (BI) analysts create a set of reports, which are updated with a regular cadence. These reports are saved in PDF or Excel format and shared through an email blast or by uploading them to a shared location. Because the reports are static, the BI teams who produce them frequently get user requests for modifications similar to “Mike from sales needs this report for region X only” or “Emily from Marketing needs this report excluding all unsubscribed customers,’ etc.
As a result, analyst resources are over-taxed, and it becomes difficult for decision-makers to have timely access to data. Because they alleviate this issue, self-serve reporting tools, like Power BI, have been increasing in popularity. With Power BI, an analyst can build a report and add options for users to drill down and see the results for subsets of the data in which they are interested. The analyst has control over how to gather the data and how to present it, but the users also get some flexibility to make supported modifications. Mike and Emily can now get the information they need instantaneously, without requiring an analyst to help. Therefore, the analysts can now focus their energy on new and exciting work. In addition to the time it saves, self-service access also encourages exploration––which can often yield significant results.
Easy to learn
Released in 2015, Power BI is still considered a new tool and its developers are frequently updating it with new features. That means a lot of people, including the author, are self-taught. At first glance, Power BI can be confusing. However, there is a lot of online information (videos, blogs, etc.) to help guide a motivated user. In our experience, analysts find this is an easy tool to get the hang of. There are, of course, advanced features, functions, and add-ons that only regular users would learn; however, it’s quite straightforward to find out how to create a simple interactive dashboard. If someone already knows how to use Excel formulas and has a good understanding of relational databases (star schema and table joins), they will intuitively be able to learn Power BI.
Power BI’s free version is very appealing. There are features that are only available with a paid license, but the free version may be enough for a small organization with only a few people who use reports. Even in a large organization, the free version allows a user to explore Power BI first before investing in it. Report sharing is the most attractive feature of the Pro (or paid) version. With the free version, anyone who wants to view the dashboard needs to have Power BI installed, and they might need additional setup to connect to databases. When users are working with large sets of data in the unpaid version, the dashboard can take several minutes to load. With the Power BI Pro, a developer can create a dashboard and publish it to the Power BI web portal, where other Pro users can view it online like a regular web page.
Analysts who work for large organizations that have IT departments and Information Security officers usually do not have admin rights to download and install software. They need IT’s approval first. That can create difficulty in getting new tools approved which, in turn, often results in analysts gluing together solutions with what they already have. Because Microsoft developed Power BI, Microsoft’s license and security policies also cover Power BI. This means that in the probable event that a company is already using Microsoft products, the IT Team will likely approve another Microsoft software without having to conduct extensive security risk analysis. For the developers and consumers of the reports, using a Microsoft product also offers the convenience of logging in with existing credentials.
In most organizations, the proliferation of reports that must be run and maintained has become a significant problem. Many may be obsolete, but no one knows if it is okay to remove them. It’s not uncommon for BI professionals to resort to using the “scream test:” remove it and wait for someone to scream! If no one does, it’s good riddance! However, a Power BI Pro developer, an expert who uses the paid version, can view usage metrics for the reports instead of waiting for screams. This feature can help BI teams plan which solutions should be supported and maintained and which can be removed or changed to something more useful.
Data source connection
Power BI connects with many out-of-the-box data sources. As an example, we are currently building a dashboard for one of our clients, and we are connecting directly to their Microsoft SQL database that is hosted on AWS. It was really easy to connect to it, and by connecting to it, it means we don’t actually have to import the entire dataset into Power BI, only the aggregated data that visualizations need. Power BI connects to many more databases other than Microsoft SQL without any special installations or add-ons. That’s a big plus for analysts. The official list of data sources currently has 116 listed data sources and includes all of the major cloud and on-premise databases. You have two options for connecting to the data.
The first option is to import the data directly into Power BI. The import approach works well for smaller data sets. However, when the data is large (>0.5GB), the import approach requires extensive wait-time for data refresh and a long time to open the file for free users who work with the file directly; generally, the user experience becomes tied to how well the computer works.
This is why power BI offers a second option of using DirectQuery. With DirectQuery, users can query data from a database without having to bring the data over; the server will do all the heavy lifting, instead of the desktop. This does mean that free version users need to have a connection to the database set up from their local machines; and for reports published with Pro, they will likely need IT’s help to install and configure an on-premise data gateway to allow the published report to interact with the database.
But not everything is perfect…Power BI also has some cons!
Cons of Power BI
Power BI is a point-and-click software where users access the options to edit the dashboard through icons and drop-down menus. Therefore, building a Power BI dashboard requires a lot of mouse-clicking. To dashboard developers with programming skills, the inability to automate repetitive tasks with code is a major disadvantage. A recent project required customized interactions between hundreds of visuals. This should have taken a few lines of code; however, in Power BI, it took thousands of mouse clicks.
As another example, changing all titles that display the year as 2020 to 2021 requires manually finding each one, clicking to get to the title menu, and changing it. With coding, it could be accomplished with a quick search-and-replace or by setting a variable that only has to be defined once.
Repetitive tasks are disheartening to analysts and prone to human error. No coding interface also means fewer opportunities to reuse previous work
Missing basic features
There are some notable features that are missing from Power BI. They are likely part of the growing pains of a new software, but they do seem common enough to raise an eyebrow as to why they are missing.
Histograms are a good example. If you have a numeric field and you want to create a histogram of the values, there is no easy way to do so without a lot of brute force. There are some add-ons by Microsoft and third parties, but they leave much to be desired––as evident from the low ratings they get from reviewers. Another feature that appears to be missing is an option to select the top N categories in a bar chart and combine all other values under ‘Other’. Creating new data tables to do this feels arduous and like it should be handled by the software. Power BI’s point-and-click interface means that the features are limited to the number of icons that can be comfortably placed on the window. This limited number of options is the trade-off between a versatile, hard-to-learn programming language and an easy-to-learn, point-and-click software.
Power BI’s own language, DAX, calculates metrics and variables. DAX makes Power BI data processing not entirely manual. Nonetheless, compared to other programming languages, DAX is hard to grasp. There is a lot of function nesting required, which makes it difficult to debug. One must click each variable separately to see its DAX code, and to update the code, one needs to wait for the changes to take effect before doing anything else. The experience is similar to working with functions in Excel cells. While there are likely third-party tools to help make DAX easier, most analysts will likely default to doing the data work upstream with SQL.
At the end of the day, most users decide the benefits of Power BI outweigh its challenges. It’s easy to get started and create powerful visualizations. Power BI will enable many analysts to create self-serve reports for the first time. Like all tools, it has its limitations, but it is the right tool for many applications. Like comparing online shopping to the bricks and mortar experience, you might miss some elements of the full-code experience, yet the simplicity and convenience might still captivate you.
We invite you to contact us for a no-obligation conversation about your self-serve analytics dashboard needs.