Low Cost” Corporate BI with Power BI
In this post we want to explain how to set up a corporate BI solution 100% with Power BI tools, so we would only have a cost associated with licensing, hence the title “Low Cost” because Power BI is the lowest cost solution currently on the market.
This architecture will also help us to solve a very common problem: when we are already using Power BI and we have many repeated or, even worse, similar datasets, i.e., we have the absence of the only version of the truth, reducing maintenance and development costs. For this we are going to make use of several functionalities of Power BI Services Dataflows, Shared Dataset, Power BI Application and Analyze in Excel.
When we talk about a corporate BI solution, we mean that we will have all the elements of this type of solutions such as: ETL, Data Warehouse, where we will have our business data centralized; and an analytical model (cube) that will contain the indicators and KPIs so that business users can generate reports, dashboards and Ad-hoc Analysis in Excel.
This would be a flowchart of a generic architecture for a corporate BI solution:
Solution Overview
Our solution is composed of:
- An ELT (Extract, Load Transform) process with different Dataflows blocks.
- A first Dataflow block to load the raw data per system without transformation to affect the source systems as little as possible.
- A second block of Dataflows to transform the raw data and mixing different sources and add them to a single dimensional model that works as a Data Warehouse.
- An analytical model that centralizes.
- Several reports connected to the single model.
- Excel connected to the single model.
- Power BI application to reorder and find all reports in one place.
The following diagram shows the parts that make up our solution:
The size of the team required to implement a corporate BI solution with Power BI depends mainly on the complexity and size of the company. However, in general, it can be said that this architecture allows to significantly reduce maintenance and development costs because:
- Dataflows enables reuse, which reduces the work required to add new functionality. In addition, it enables real-time collaboration among team members, which facilitates teamwork and reduces development time.
- On the other hand, Shared Dataset allows you to centralize and share data between different reports and dashboards in Power BI, reducing the need to maintain and update several different data sources. In addition, it allows automatic updating of data in reports and dashboards, which facilitates the maintenance of the solution.
In summary, this architecture allows implementing a complete corporate BI solution using only Power BI, which reduces the total cost of the solution by minimizing the need to purchase additional tools. The unification into a single source of truth translates into time and effort savings for the team in charge of implementing and maintaining the solution.
Finally, we will have reports in Power BI and Excel with a consistent view of the data that will drive data-driven decision making across the organization.
Author:
Esteban Londoño Padilla
Data Analytics Consultant
