Transition of companies from MS Excel to MS Power BI

Power BI makes it easy to connect to data sources, visualize and discover what’s important, and share the results with anyone you want.

Disclaimer: This article was translated from the Czech language by AI.

Thanks to the intensive development of Power BI, the investments that Microsoft is making in it technically and in marketing, and its integration into Office 365, many companies are considering replacing their manual reporting in MS Excel with automated reporting in this tool. If this is the case for you, this article should help you evaluate the benefits of such a move and advise you on what to consider to make the transition as successful as possible.

The first step in the process should be to evaluate your existing reports, in particular:

where the source data is stored, how accessible it is and its size;
who creates the reports in MS Excel, how the data is transformed and what visualisations are used;
to whom and how reports are shared, how often users use them;
how and how often updates are made;
what difficulties source data managers, report creators and report users have with existing reports.

Particularly problematic may be reports based on ad-hoc exported data shared via email, reports without an explanation of their exact meaning or, for example, reports that are very slow due to their size and complexity.

It is then possible to assess the current state of corporate reporting in terms of the biggest advantages of Power BI over MS Excel, which can take reporting to a whole new level. It is therefore useful to determine to what extent they will be used in the new solution and what benefits they will provide.

Some of the benefits of using Power BI may include:

The ability to connect directly to many cloud and on-premise data sources, combining them and updating them automatically instead of manually entering data;
the ability to quickly work with large data sets that MS Excel can no longer handle, and transform them into complex data models directly within the Power BI environment, including sensitivity and security settings;
reports can be created without writing formulas, however, very advanced calculations can also be created with the basic functionality being identical to MS Excel, yet working on a relational basis;
visualizations on report pages can be interactive, setting their appearance is very intuitive and hundreds of ready-made visualization types are available;
reports can be easily shared in the cloud with users and to Office 365 applications for effective team collaboration – reports can also be used on tablets and mobile devices;
dashboards can be created with a few clicks, set up data-based alerts, automatic email subscriptions and data updates;
thanks to integrated artificial intelligence, automatic quick reports can be obtained from the data model, data can be queried in natural language, abnormality analysis can be used, etc.

Based on an analysis of the needs and potential benefits, the company should set migration goals, design the specific solution architecture and processes that will govern the use of the new reporting environment.

Power BI is a flexible platform and reports can be created and used (for free) by individual users individually in the Power BI Desktop application, but it is also possible to create a complex reporting system for thousands of users, over huge data. A typical deployment of the Power BI platform involves viewing and sharing reports in a secure cloud, for special cases reports can be published to the on-premise Power BI Report Server.

For this, appropriate licenses must be secured – Power BI Pro to enable sharing of reports in the cloud, or Premium licenses for a dedicated cloud environment to enable sophisticated administration, performance monitoring and reliability of the entire solution.

From an organizational perspective, it is necessary to consider whether the Power BI solution will be deployed in the company as:

a self-service platform where everything around reporting is handled by business people and IT provides only minimal support (e.g. license assignment, installation of data gateways, etc.);
a reporting and analytics environment made up of BI specialists, where business users participate only as report submitters and readers;
a combination of both approaches.

While the current clear trend is a self-service approach, it is also important to be aware of its risks and limitations:

High level of redundancy of activities (for example, if each department has to invent and implement client unification between different information systems by itself);
possible different results presented by different departments over the same data due to the use of different algorithms;
the possibility of misinterpreting data and obtaining erroneous results due to ignorance of the logic of the underlying data structures;
lower technological knowledge of people for whom data analysis and development in Power BI is not their daily bread.

Even with a self-service approach, it is therefore advisable to take into account the need for intensive user support by data specialists.

From the architectural perspective of the infrastructure, it is necessary to consider whether data flows and data models will be connected directly to the original data sources (information systems) or whether it will be appropriate to build a consolidated data intermediate layer for their access (data lake, data warehouse or reporting data mart). A data middleware layer is particularly suitable if the data needs to be historicized or, when combined, to perform complex data operations. Of course, it is necessary to determine who will develop and maintain this middleware layer.

It is also necessary not to forget about the human factor, i.e. the users of the reports themselves, but we will discuss this in the article Key success factors in the transition of companies to new technology.

Autor: Petr Kolář (Czech version)