Microsoft Power BI: What Do You Need to Know?

July 14th 2020

You’ve heard about Power BI, but maybe you’re not as familiar with it as you need to be, so what do you need to know?

It’s been almost five years since Microsoft launched their flagship analytics platform and I understand that as a leader, consultant, or solution developer, it can be hard to keep up with the latest trends in the market, especially if you don’t work with the technology directly. The Power BI platform has been a heavy focus of investment for Microsoft since its release and that has led Gartner to recognize it as a clear leader in the Data Analytics space. So why has the platform gained such praise in the last several years?

I’m going to answer four basic questions to give you what you need to know about Power BI. We’ll talk about the recent updates, why you should care, why companies are happy with it, what the architecture looks like, and how much it costs.

What’s new with Power BI?

Within the last two years, Microsoft has implemented some really neat concepts and features. 

  • Apps – the Power BI App is a collection of data assets that can be made available to your organization’s end user in a nice clean package. An app can include multiple reports, dashboards, and paginated reports. The app is an essential part of the next process which is built into the framework.
  • Development Cycle – the platform enables a really straightforward development cycle (if followed properly of course). The free Power BI Desktop tool is your development environment, the App Workspace is your QA environment for power users, and the introduction of the App allowed for separation of a “Production” deployment of the assets in the workspace ready for consumption by the end users. 
  • Paginated Reports – before Power BI came along, SQL Server Reporting Services was the main reporting tool used by Microsoft. It’s definitely more of a reporting tool than an analytics tool, but even in the modern analytics space, there are plenty of use cases for pixel perfect, formatted reports. Paginated Reports in Power BI gives organizations the ability to centralize their reporting efforts. You no longer need to keep your formatted reports separate from your analytics platform. Organizations with Power BI Premium can take advantage of this feature.
  • Dataflows – this newer feature in the Power BI platform has recently received a lot of enhancement. There are still features to be added, but this provides business users a simple cloud ETL capability to create and transform individuals entities (tables) that can be re-used throughout your Power BI environment. This feature, while it doesn’t replace an enterprise ETL tool, even provides the ability for incremental refresh of entities. 
  • Read/Write XMLA Endpoints – Power BI was built on the same Vertipaq engine that is used in SQL Server Analysis Services and until recently, there were some distinct differences in the code for a model created in Power BI Desktop vs. SSAS. Microsoft has bridged that gap, and in doing so has released a preview of it’s Read/Write XMLA endpoint for Power BI Premium workspaces. This allows organization’s managing Power BI environment’s to take advantage of tools that many developers are already familiar with to manage their assets in Power BI. Things like SQL Server Management Studio, SQL Server Developer Tools, as well as various third party tools that are being developed using this new feature. 
Why should you care/Why do organization’s like it?

Power BI can help remove barriers to organizational change and adoption of an analytics platform. It does this in a way that doesn’t require a large investment in technology or people, but you’ll want to make sure that your processes are clearly defined. 

Power BI can help remove barriers to organizational change because it’s familiar to most data savvy business users. Before joining Waypoint, I was part of a mid-sized organization that adopted Power BI as it’s analytics platform. Several previous platforms had failed to catch on, but Power BI was widely adopted and used across the business for several reasons. Many of our business users were very comfortable and familiar with Excel and used it quite often for reporting and data analysis. Similarities between Power BI and Excel (including Power Query), accessibility of the tool (Power BI Desktop is free and doesn’t require a license), and a platform (Power BI Service; powerbi.com) integrated with the rest of the organization’s productivity tools (Microsoft O365) were just some of the reason that the business users really liked and adopted Power BI.

Another reason why we really Power BI was because a hybrid analytics approach worked really well with it. This hybrid approach was a combination of bottom-up self-service analytics as well as top-down implementation of analytics solutions. The approach worked well because Power BI’s accessibility and adoption by business users allowed for creation of solutions at the lowest level, but once these were published, the Business Intelligence team had knowledge of the solution and could utilize these business users’ knowledge in the development of enterprise solutions for various areas of the business.

Completeness of the platform was also something that business users really liked. They didn’t have to use separate tools to bring data in, then create reports, then publish and share. As an example, users of Alteryx and Tableau will be familiar with the value of using those tools together, but they are separate platforms and therefore require separate access, licensing, etc. Although Tableau has created Tableau Prep Builder, Alteryx is often a preferred platform. In addition, the licensing for Tableau Prep Builder is an additional cost to the Tableau platform. All of these functions can be performed in a single unified platform with Power BI. 

The lack of infrastructure expertise required to implement Power BI is also a very attractive feature. Yes, there is a self-hosted option available for Power BI called Power BI Report Server. However, I have not seen it used very often, typically because the benefits outweigh the cost of maintaining the environment internally. Bug fixes and feature updates in Power BI Desktop are released on a monthly basis, but in the Power BI Service (powerbi.com), bug fixes and feature updates are released weekly and sometimes even daily. The speed at which technology moves is hard to keep up with and a lot of time and money can be saved by using the Power BI Service.

The last reason is value. Power BI delivers a lot of value for its cost. We’ll talk more about licensing below, but it is fairly inexpensive when compared to other analytics platforms (Tableau, Qlik, etc.).

What does the architecture look like?

A picture is worth a thousand words.

Source

This diagram created by Dustin Ryan of SQLDusty.com provides a really good overview of the architecture of Power BI. This accompanying document also helps explain what each element in the diagram means.

How much does it cost (licensing)?

Shared Capacity vs. Dedicated Capacity

In the Power BI Service (powerbi.com) you can elect to either use shared capacity or dedicated capacity. There are some feature differences which are outlined in this comparison table. The architectural difference between these two options is simply that you either share cloud resources with other users in your own secure tenant, or you pay for dedicated capacity (i.e. dedicated servers) in Microsoft’s data center. 

With either of these methods, you will use the Pro license for your developers. Any user needing to create, edit, update, and publish content in the Power BI Service (powerbi.com) will need to have a Power BI Pro license. However, if you are using a shared capacity, all of your users (developer and viewer) will need to be granted a Pro license. One of the main selling features for larger organizations to purchase dedicated capacity is that it comes with viewer privileges for your entire organization, allowing you to reserve the additional cost of a Pro license for those who are creating content.

Power BI Pro

Licensed per user with full developer privileges 

Retail: $9.99 per user per month

Power BI Premium

Licensed per capacity based on projected usage and size of the organization; grants all users in the organization viewer privileges, but still requires purchase of individual Pro licenses for developers

Retail: $4,995 per node per month (P1 node)

Power BI Pricing Calculator

This pricing calculator is helpful, but I believe it trends upward on its recommendation for more capacity. With that in mind…if you’re thinking about the capacity that your organization requires, remember that with most Microsoft agreements, you can always scale up if you need more capacity or licenses, but you cannot typically scale down during your agreement period. 

I mentioned above that Power BI delivers a lot of value for it’s cost. For just $9.99 per user per month, you can license your organization with a full cloud analytics platform using Power BI Pro (Shared Capacity). While extremely cost effective, there are some limitations. Everyone in the environment must have a Pro license to interact with content, you cannot deploy Paginated Reports, you cannot apply incremental refresh policies to your datasets, storage is limited (1GB per dataset, 10GB per user personal storage), and refreshes are limited (eight times per day per dataset). Every organization has different data needs and there are a number of ways to implement analytic solutions using Power BI and some methodologies utilize more resources on the BI platform than the data platform. 

For organizations needing some of the features or resources that are limited with Power BI Pro, Power BI Premium offers a Dedicated Capacity based solution. There are no feature limitations in this offering, storage limits are much higher (10GB per dataset, 100TB organizational storage), and it supports refreshes up to 48 times per day per dataset. This solution also includes use of Shared Capacity and provides the option of self-hosting through Power Bi Report Server. However, with all these features comes a larger price tag. Power BI Premium is currently $4,995 per P1 node (there are also other node types; multiple nodes may be needed depending on the size of your deployment). You will still need to license each developer in this environment, but with Power BI Premium, all users in your organization have a viewer license by default. This type of deployment can deliver a lot of value for it’s cost especially in larger organizations since each viewer does not need an individual license.  It also reduces the administrative need for managing multiple license types or those licenses for each individual user. It also increases adoption of analytics with every single user in the organization having the ability to consume and interact with content.

There are plenty of other questions that can be asked, but I think that covers the basics.

That was a lot of information, so let’s review.

We discussed some recent updates in Power BI and gave some reasons why you should care and why other organizations like the platform. The architecture is a great selling feature due to its ease of use and implementation as well as the completeness of the platform. Finally we talked about some considerations for cost and licensing. If you have additional questions or would like to discuss utilizing Power BI in your organization, please contact us.

Written By: Scotty O’Leary
Consultant
Waypoint Consulting

Resources:  

Gartner February 2020 Magic Quadrant – https://info.microsoft.com/rs/157-GQE-382/images/MQ.jpg
Apps – https://youtu.be/lcGoWfXLRpc 
Power BI Desktop – https://powerbi.microsoft.com/en-us/desktop/
Paginated Reports – https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-report-builder-power-bi
SQL Server Reporting Services – https://docs.microsoft.com/en-us/sql/reporting-services/create-deploy-and-manage-mobile-and-paginated-reports
Dataflows – https://docs.microsoft.com/en-us/power-bi/transform-model/service-dataflows-overview
Read/Write XMLA Endpoints – https://powerbi.microsoft.com/en-us/blog/announcing-read-write-xmla-endpoints-in-power-bi-premium-public-preview/
SQL Server Analysis Services – https://docs.microsoft.com/en-us/analysis-services/analysis-services-overview
Alteryx – https://www.alteryx.com/
Tableau – https://www.tableau.com/
Tableau Prep Builder – https://www.tableau.com/products/prep
Power BI Report Server – https://powerbi.microsoft.com/en-us/report-server/
Power BI Architecture Diagram – https://i1.wp.com/sqldusty.com/wp-content/uploads/2019/02/Power-BI-architecture-v4.png?ssl=1Power BI Architecture Diagram Legend – https://onedrive.live.com/view.aspx?resid=CBDD37C1AF373511!310729&ithint=file%2cdocx&authkey=!AKCqsWZfBSjgGWU
Power BI Pro (Shared Capacity) vs. Power BI Premium (Dedicated Capacity) Comparison Table – https://powerbi.microsoft.com/en-us/pricing/#powerbi-comparison-table
Power BI Pro – https://powerbi.microsoft.com/en-us/power-bi-pro/
Power BI Pricing Calculator – https://powerbi.microsoft.com/en-us/calculator/