Power BI is one of the leading Data Visualization and Business Intelligence tools on the market today. It was released in 2011 and is aggressively updated with new features every month. It can be used for a wide range of tasks, from creating interactive dashboards of visually attractive data visualizations to enabling big data insights for data science, the platform has a lot to offer.
Most Accounting and Finance teams will be interested in using Power BI to help automate the preparation of Financial Reports, such as the Income Statement and Balance Sheet. While the Power BI Platform can accomplish these tasks and is used quite commonly for it, dashboards are generally more suited for presenting KPIs and summarized interactive data.
If you are familiar with Excel Power Query, Get and Transform, or Power Pivot you will likely be familiar with the way that Power BI ingests data and performs calculations. Because of the overlap with Excel, Power BI can be quick for departments to learn.
Power BI vs Tableau Market Share
Tableau is the most common competitor to Power BI. Between the two business intelligence packages, they cover 96% of the total market. It’s hard to go wrong with either solution. While both have their quirks for accounting teams, Power BI tends to be less expensive and more approachable due to its inclusion with Office 365 and ease of management by IT teams.
Power BI Has 39.6% Market Share Among Leading Data Visualization Tools.
We recently studied the data visualization market share for some of the top tools on the available today. Power BI, Tableau, Cognos, and Qlik are all leaders in the field but Power BI and Tableau easily stand out as leaders in the pack with a combined 96% of the data visualization market among businesses in the United States.
Which Business Intelligence Tool is the Best?
Both Power BI and Tableau are both excellent business intelligence platforms. They each have large existing user bases, and comprehensive training material available. Each platform will help your team increase your teams speed and reporting capabilities. Both also offer a user friendly interface and both have their own quirks when building visuals or financial reports. Power BI might have a slightly steeper learning curve when using advanced features, but both are great options.
In our view, Power BI wins as the better business intelligence solution due to its lower cost and lower learning curve for Excel Power Users. However, Tableau does have some advanced analytics features and complimentary products such as Tableau Prep that allows users to more easily prepare and model data.
Two additional options worth consideration are two Google Analytics solutions. They are a rising competitor with the Google Data Studio and Looker platforms. Data Studio is not as full featured but has a free version. Their Looker platform is great at handling bulk data and has many data governance and lineage features to centralize control to ensure a single source of truth. The ability to work more directly with SQL and powerful features may make this a preferred choice of seasoned data analysts and data scientists.
The Power BI Platform Consists of 4 Components
Power BI is made up of several inter-connected components that are critical to understanding how to utilize it in your organization.
- Power BI Desktop
- Power BI Service
- Power BI Paginated Reports
- Power BI On-Premises Gateway
This type of deployment is also similar to the Tableau Platform, which includes Tableau Desktop, Tableau Prep, and Tableau Server.
Power BI Desktop
The desktop application is installed on a user’s computer and is used to prep and blend data, perform ETL operations, build dashboards, create reports, and ultimately publish them to the Power BI Cloud Service.
You can download Power BI Desktop for Free from Microsoft’s website, or you can download it from the Windows Store.
Dashboards and created in Power BI Desktop through two steps. First, data is modeled and prepared. Data can be brought in from multiple data sources, such as Data Warehouses, or consolidated Excel spreadsheets. Second, reports and dashboards are built using drag and drop tools to create a collection of charts, tables, or graphs for data visualization.
Power BI Service
Once a report is designed, it’s published to the Power BI Service. This is component that allows people to log in with their Active Directory account and view the report. At this point a viewer does not need to use Power BI Desktop.
There is an on-premises Power BI Report Server that can be used to present reports, which in some cases is more cost effective or may be required for security compliance. However, most users will utilize app.powerbi.com which is hosted in Microsoft’s Azure Cloud.
Power BI Paginated Reports
Paginated Reports are an evolution of Microsoft SQL Server Reporting Services or SSRS and can be created with their own unique datasets, or they can be made with datasets shared with other Power BI Reports.
The primary benefit is the additional reporting capabilities that it adds. While Power BI is great for creating dashboards, Paginated Reports is great at creating pixel perfect invoices, financial statements, and more traditional looking layouts that are text or table heavy.
Building reports can be somewhat time consuming, as the interface is not as good as other BI Tools. Microsoft is expected to release an update in the near future to enable drag and drop capabilities.
Power BI On-Premise Gateway
Many organizations have data stored on local networks that are closed off to the outside world. In order to build connections between the Power BI Service and data that’s stored on a local network, the Power BI Gateway can be setup to bridge the connection.
If you’d like to learn more about gateways, read our in-depth article:
A Practical Guide to Power BI Gateways (www.popautomation.com)
Additional Benefits of Power BI for Accounting Teams
All Data Analysts will agree that the most time-consuming part of any project is data modeling. Data can be located in different places such as Excel files saved on SharePoint, OneDrive, or other cloud services. Other times it may be stored in SQL databases or Accounting Software. Wherever it is stored, it must be brought together before any data analysis or reporting can occur. Luckily, Microsoft’s BI tool makes this easy.
1.) Power BI has over 170 built in connectors.
One of the hardest things to do when integrating various business systems is getting them to talk to each other. Luckily Microsoft Power BI does a lot of the heavy lifting for you by having many popular and even many obscure system connections already available. Power BI allows you create your own custom SQL or API connections for systems that don’t have integrations already available.
2.) Power BI uses Power Query and DAX
If you have ever used Power Query in Excel, Power BI is going to be super easy for you to pick up. Microsoft’s data preparation solution is called Power Query. It allows users to extract, transform and load (ETL) data from any of the 170+ connectors that are available out of the box.
Power Query is used across several different Microsoft technologies such as Power BI, Excel, Azure Data Factory and SQL Server Integration Services, making it one of the best documented and widely used solutions for data preparation today.
Formulas utilize the Data Analysis Expression (DAX) formula language, which is already available in Excel Power Pivot. It can be a challenge to learn at first, but tools like Chat GPT can help.
3.) Drag and Drop Charts and KPIs Into Your Power BI Report
After your data model has been established, building reports full of colorful visualizations, charts and KPIs is relatively easy. Each of the visuals are highly configurable to fit your company’s style or brand.
There is some nuance to setting up Financial Reports as part of a dashboard, we wrote a helpful guide to get you started.
Power BI as a Data Analytics Platform
While Power BI can be used for automating financial reports and transform the way that business users interact and explore data Power BI has many advanced features designed with the Data Scientist in mind.
Power BI datasets can be used with AI infused features, such as anomaly detection, or fed into R based Machine Learning models to gain new insights into your data. We anticipate these features growing over the next several years as Natural Language Processing becomes more common place, and is integrated into more Microsoft Products.
Power BI for Internal Business Reporting Insights
One of the most frustrating parts of publishing reports is not knowing who is actually viewing them. As an added benefit to an already great reporting tool, you can run internal usage reports.
This feature enables you to see which reports are being viewed by real users. Your team can use these data points to focus reporting and fine tune your analysis on the topics that people are most interested in.
Many companies utilize Power BI mobile to enable report viewing on phones or tablets. The reports can be a bit tricky and time consuming to setup. The telemetry data collected by the service will let you see how many Power BI users are utilizing these features.
Use Power BI to Centralize ETL and Share Data Sources
In an effort to make it easier to share datasets across teams, and departments Microsoft has a couple of solutions available. Power BI Dataflows have been around for several years and let you build centralized ETL processes. Power BI Datamarts were just recently released and kick things up another notch!
Power BI Shared Data Sources
You could call us lazy, but we love to build once and re-use many times. Once you finish your data prep and publish the dataset to the Power BI Service, it becomes available to share with others. Meaning that crummy complex report that you spent a day cleaning up never has to be done again. Others in your organization can piggyback on your hard work and save massive amounts of time.
Self Service Data Warehousing with Power BI Data Flows
As organizations grow the amount of data being generated grows with them. Eventually there is an inflection point where Excel Spreadsheets don’t cut the mustard and you need to begin consolidating your data into an enterprise grade solution.
Power BI helps you solve this problem with Dataflows.
Dataflows is Microsoft’s solution for self-service ETL. You can create a new Dataflow from the Power BI Service which brings you to an online Power Query editor. The data flow lives on the cloud in the Power BI service and becomes much more easy to share and edit in the future vs. managing individual data sources that get published with your Power BI report.
You can also build out data transformations visually by using the Power BI Diagram View. We expect Microsoft to do a lot of investment into the diagram view in the future. It was recently added to the Power BI Cloud Service and is a much easier way to visualize how your data is being prepped and blended.
Note: Power BI Premium Instance is Required to create Dataflows.
What is the Difference Between a Power BI Dataflow and a Dataset?
These two components of Power BI often get confused. The easiest way to remember the difference is that Dataflows are step 1, and Datasets are step 2 in the data prep or ETL process.
Dataflows
- Data Transformations similar to Power Query in the Cloud
- Dataflows pull data from data sources and push to datasets
Datasets
- Data that is already prepared and stored
- Datasets are used for building report visualizations
Note: Dataflows pull data from data sources and push to Datasets sources, such as SQL server, and push data from Dataflow to a Power BI Dataset. However, if you are scheduling dataset refreshes, you also have to schedule the dataflow because the dataset refresh will not automatically trigger a dataflow refresh.
Use Case: If your reports have overlapping starting points, you could branch multiple datasets off of a single dataflow. This would limit the number of connections going against source data and also make it easier to edit once and deploy changes to many downstream reports.
Power BI Datamarts are the Future of Self-Service Data Warehousing
A level up from Power BI Dataflows are Datamarts.
A Datamart sounds fancy but is mostly a re-branded version of Microsoft Azure SQL. The best part of this is that you don’t have to be an Azure admin, requisition resources, open an Azure SQL endpoint etc.
You still have the capability to ingest data from all of the different data sources you can normally use with Power BI. The added benefit is that once you’ve ingested the data it’s available for others to use across various systems that can also connect to a SQL database.
Power BI Data Governance
There are multiple considerations when thinking about how to protect data within Power BI. The software helps you control and limit data access in the following ways.
- Who gets access to the Power BI Service
- Who gets access to data sources
- Who gets access to specific reports, dashboards, and apps
Controlling Access to the Power BI Service
Reports are built in Power BI Desktop and published for viewing to the Power BI Service. Access is controlled to the Service by your Microsoft 365 business or enterprise account. IT departments love this because they can turn off one account and it turns off access to a users computer, Windows login, and Power BI all in one step.
Controlling Access to Power BI Data Sources
Data access is controlled in a couple of different ways. You could either limit it at the source by giving users access to only specific tables or subsets of a SQL database meaning that they wouldn’t be able to open it in Power BI Desktop or explore it.
Once a Datasource is published to Power BI, you can control which users or user groups are allowed to use it.
Controlling Specific User Permissions Within Power BI Service
A Power BI Admin can assign specific users or user groups with different access levels. For example if you want someone to be able to view a report but not edit it. Or if you want another person to view a report, edit it, and have access to manage dataset permissions there are separate workspace roles that can be assigned.
Microsoft has a detailed list of what each user type can and cannot do available through Microsoft Learn at this link: Roles in workspaces in Power BI – Power BI | Microsoft Learn
Power BI Row Level Security
A major concern with reporting is being able to control who has access to view data within a report itself. Power BI controls data access at the lowest possible level providing several benefits.
- A single report can be published but with different views for different users
- When an employee leaves your company, their Power BI access is tied to their Microsoft account requiring IT to only turn off a single account.
That first bullet is huge! Think about all of the different report versions that you have to publish for different audiences. Then imagine a world where they pull their own report but only have access to see data relevant to them.
Learn more about setting up Power BI Row Level Security here:
Row-level security (RLS) with Power BI – Power BI | Microsoft Learn
You Can Export Power BI Datasets to Microsoft Excel
While Power BI is a great data visualization tool, there are times that you need to utilize the source data in an Excel spreadsheet. The platform supports the capability of exporting full data sets, or portions of a report as an Excel or CSV file.
How Much Does Power BI Cost?
Power BI comes in 2 different pricing models.
- You can either pay per user per month.
- Or you can pay a fixed amount for capacity each month.
Power BI Pro is $9.99 Per User Per Month and includes the following features:
- Mobile app access
- Publish reports to share and collaborate
- Paginated (RDL) reports
- Model size limit of 1 GB
- Refresh rate of 8 per day
- Connect to more than 100 data sources
- Create reports and visualizations with Power BI Desktop4
- Embed APIs and controls
- AI visuals
- Data security and encryption
- Metrics for content creation, consumption, and publishing
- Maximum storage of 10 GB/user
Note: Power BI Pro Is Required to log into the Power BI Service to View Reports
Note: Power BI Paginated Reports previously required a Premium subscription
Power BI Premium is $20 Per User Per Month and includes the following additional features:
- Model Size limit of 100 GB
- Refresh rate of 48/day
- Advanced AI (text analytics, image detection, automated machine learning)
- XMLA endpoint read/write connectivity
- Dataflows (direct query, linked and computed entities, enhanced compute engine)
- Datamart creation
- Governance and administration
- Data security and encryption
- Metrics for content creation, consumption, and publishing
- Application lifecycle management
- Deployment Piplines
Power BI Premium (Per Capacity) is $4,995 per Month and includes the following additional features:
- Multi-geo deployment management
- Bring your own key (BYOK)
- Autoscale add-on availability
The biggest feature difference though is pricing model.
Even with Premium Capacity, users still need a Pro or Premium Per User license to publish reports from Power BI Desktop to the Power BI Service. Pro licenses are not needed to view reports after they’re published.
If you have 500 users or more, it may become more cost effective to pay for Monthly Capacity. Depending on how much the service is used you may have to purchase additional units of capacity but will vary greatly organization to organization because of the differences in data complexity, scale of data, types of reports, etc.
Note: You can mix and match Power BI Pro and Premium licenses. Not all users may need Premium features but you can add them as needed.
To learn more and to find the most up to date pricing from Microsoft click here:
Pricing & Product Comparison | Microsoft Power BI
If you want to dive even further into Power BI Pricing and Licensing check out the Microsoft Power BI Licensing Guide here: Power BI licensing for users in your organization – Power BI | Microsoft Learn