More and more customers are looking to define roadmaps and move some of the traditional reporting to PowerBI.

The visuals are very appealing, there is a broad community extending the functionality and offering controls and visuals.

Most of the time we do not have a green field and companies have to migrate or extend existing data warehouse and reporting solutions to Power BI cloud. Therefore we summarized below the most important aspects which must be considered and potential strategies to achieve modern, flexible online dashboarding capabilities.

We found that the licensing approach is closely linked with the deployment / migration strategy chosen.

There are two different types for Power BI licenses: Power BI Pro licensed per user and Power BI Premium licensed per Azure BI server capacity. Microsoft provides an online calculator that helps potential customers comparing different models but we felt that companies making a choice need concrete examples and decision rationales.

“PowerBI Pro” allows licensed users to design datasets based on connectors to internal DWHs and create dashboards / reports / BI apps.

There are several limitations though:

• 1 compressed dataset model cannot exceed 1GB data.
• The synch of datasets is done 8x/day
• Per workspace storage limit of 100GB.
• Per licensed user, we have 10 GB. Workspaces storage cannot exceed number of users x 10 GB
• To consume reports/dashboards a Power BI Pro license is needed.

The extension to Power BI dashboards is relative straight forward, enable at least 1 Power BI data gateway on-premises (can be an existing server as long as the load is not too high) and funnel the required data out of the on-premises data warehouses towards the Power BI workspaces.

The gateway servers as an https tunneling proxy, initiated from the on-premise server(s) to the Azure cloud. It supports live connections with SQL databases and Analysis Services Tabular and Multidimensional models.

Further readings:

We can connect to the on-premises DWHs, populate datasets in Azure and create / publish dashboards based on the datasets refreshed. The datasets act as a cloud cache making the displays fast and neat. There is a possibility to use direct query but in our experience does not work fast enough even if you have pre-aggregated views on-premises.

We can create multiple workspaces in the cloud, define models , synchronize selected data from on-premises and allow key business users to contribute to the definition of reports and dashboards. The process is fast and with few technical resources we were able to support multiple business lines: sales, customer relationship management, operations, finance.

“Power BI Premium” removes the limitations of Power BI Pro but requires that we provision dedicated processing power in Azure and create there datasets (fresh data , precalculated fields).

The BI Premium uses the concept of frontend nodes (publishing dashboards, reports) and backend nodes (extractors, ETLs processing). There are 3 tiers: P1 , P2 and P3 and a good comparison article is available from: https://www.encorebusiness.com/blog/power-bi-premium-p1-vs-p2-vs-p3/

Important note is that “Power BI Premium” does not replaces the DWHs rather it acts as a pre-processing and caching layer allowing concurrent usage of dashboards, reports and ad-hoc queries against refreshed datasets.

BI Premium refreshes data hourly (planned increase to 48x/day) and it allows larger datasets (P1 – 3GB per dataset, P2 – 6GB, P3 – 50GB).

Our assessment translated into the following strategy:

• Use Power BI Pro licenses, provision 1 data gateway (VM covered by HA) and connect on-premises DWH.
• Define a user threshold when it becomes more efficient using Power BI Premium licensing model. In our case, the threshold was around 1200 reporting users (consumers or editors).

The move to a “Power BI Premium” might happen a later stage when we simplified the reports/dashboards, but it is not cost-efficient (yearly cost 324.000 euro) at this moment:

• Production – 2 P2 nodes (2 x 8 frontend + 2 x 8 backend, 50GB RAM) - ~18k/month
• Dev/UAT – 2 P1 nodes (2 x 4 frontend+ 2 x 4 backend, 25GB RAM) - ~9k/month

Similarly, we must remark that we see Power BI as an extension for the existing reporting environments, allowing "live", "interactive" dashboards, passing some of the reporting responsibilities directly to the line of business key users but it does not replace the need to produce for example scheduled formatted reports. Scheduled jobs are still to be used when there are heavy calculations across large data datasets (30+mil records ).

I hope the post was not too long and it gave you good insights into some of the "not so obvious" considerations to establish a Power BI strategy.