Excel BI or Power BI?

I recently conducted a poll with the question: #Excel BI (#PowerPivot, #PowerQuery, #PowerMap) or #PowerBI? What would you choose to create your #BI reports and why? You can see the voting results in the chart below (Psst, "pie" is a terrible choice for visualizing such data).

Very few people voted for Excel BI. It seems to me that a lot of users have heard and accepted Power BI without even knowing that this whole self-service #businessintelligence story started in Excel. Hence my need to write a text in which I will describe the similarities and differences between these two tools.

How did it all start?

SQL Server 2008 R2 was released in July 2010 and brought a significant novelty within its BI platform: in addition to the OLAP tool for multidimensional data analysis, #Microsoft also offered the Tabular Model. It allows tables from one or more sources to be imported into the Data Model, a pseudo-database run by the xVelocity/Vertipaq engine. On that occasion, they are compressed and the space savings, depending on the data structure, can be up to ten times. Tables are linked in the Model in a similar way as when you create a database schema. The result is a unique source for creating reports. The tabular model is a bit slower than traditional OLAP cubes, but it also offers excellent performance because the data is unpacked "on the fly", when it needs to be displayed in a report. Its "trigger", the DAX expression language, is easy to use and allows you to easily create reporting dimensions and measures that can be used to create complex reports.

Very soon after the appearance of the Tabular model, an add-on for Excel: Power Pivot was released. It allowed you to deploy a local instance of the SQL Server Analysis Services business intelligence platform in a "friendly environment" and create Power Pivot reports, which are actually pivot tables that have a Data Model in the background. From version to version Power Pivot is better and better integrated into Excel, and today it can be used in any version of this application.

Shortly after we met Power Pivot, Power Query was created, a tool for importing and transforming data, as well as Power Map, which, based on data from the Model, is used to create charts on a 3D model of our planet. The third tool used to visualize the data was called Power View. It is built on Microsoft Silverlight technology and has enabled the creation of rich reports, much like Power BI does today. These add-ons were initially called "Power BI for Office 365". In the middle of the previous decade, new standards for displaying web pages within Internet browsers were adopted, which give priority to HTML 5.0 technology instead of external graphics libraries (Silverlight, Flash). As a result, Power View was "flushed down" and Microsoft set out to develop a new business reporting solution - that's how Power BI came about. In the beginning, it was characterized by quite modest possibilities compared to Excel, while today it is immeasurably better.

Why use Excel BI?

Power Pivot is a practical choice because you work in a familiar environment, and an average PC is enough to make reports. The Power BI development team has expanded it considerably when it comes to resources. Once the i5/8GB RAM/SSD 256GB configuration was more than enough and today it just “does the job”. As things stand, hardware requirements will grow day by day, while Excel also works great on computers 10 years old or older. Then, if you have the local infrastructure on which your business (ERP?) Solution and/or other data sources are located, you can access and refresh the data as often as you want. Data availability, security and safety are "on your playground" - you have complete control! Excel works like lightning, reports are created faster and can be combined with existing tables. The price comes down to investing in a #Microsoft365 ( #Office365) license, which is a minimum of € 100 per user ...

What about Power BI?

Power BI is a "powerful" platform for developing business intelligence reports. It exists as a "cloud" service that allows you to connect to many different sources: files, on-premises and cloud databases, Internet services, applications, etc. At the same time, analysts have at their disposal a Power BI Desktop development platform that allows you to create reports on your computer and then post them on the service. Reports can be specifically designed for computers and/or mobile devices. Power BI Mobile is a native application for mobile phones and/or tablets that allows you to view reports on your "pet device". Power BI has a built-in Data Model, supports the writing of DAX statements, as well as Power Query, which allows the transformation of data from the initial form to one that is suitable for reporting. It is licensed in three ways, as: Free, Professional and Premium licenses.

• Free licenses are a great choice to learn to use this service and/or platform. They offer full functionality when it comes to importing data from multiple sources, linking tables, writing DAX statements, and creating reports. You may not share reports or use functionality reserved for members of the organization. I would not advise you to use free licenses for business purposes because your reports are public: anyone who owns the link to reports can see them!
• Professional licenses offer functionalities that meet the needs of the average company. They allow you to create and share reports, and they can be combined into applications, making them easier to distribute. You can manage user rights to access reports or view them (row level security). The data model size limit is 1GB, and a total of 10GB per user is supported. These licenses support connecting to the local infrastructure through a Gateway application that is installed on a local server or computer. The number of refreshments is a maximum of 8 times a day at minimum intervals of 30 minutes. Each user, whether creating or viewing reports, must have a license via PC, phone or tablet (one user can view the report on multiple devices), and the price of the license corresponds to the price of the more expensive Microsoft 365 subscription.
• Premium licenses offer maximum functionality. Companies have at their disposal a Power BI Reporting Server and the ability to create reports adapted for printing: Paginated Reports. It is possible to create pre-prepared data flows (Dataflows), use advanced AI analysis tools, as well as advanced functions for service administration. Users have 100 GB for the Model, 100 TB in total, the ability to refresh a maximum of 48 times a day, and the minimum refresh interval is 1 second (if you access the data via a DirectQuery connection). Impressive, isn't it? Such is the price. These licenses are too expensive for the average company. Since last year, Microsoft has also introduced a new, Premium Per User subscription that is slightly cheaper. However, it often happens that a small company, such as a retail chain, really needs Premium licenses and their purchase is simply not profitable.

How about we meet halfway?

Given the size and purchasing power of the average Serbian company, it may be best to opt for a hybrid solution. Some users, primarily those from operational and middle management, would use reports created with the help of the Power Pivot platform. Senior management reports would be generated using the Power BI platform, and users can select a device to review them. In this way, the optimal number of licenses can be chosen, which helps to bring the budget within reasonable limits.

Both of these BI platforms have their own audience. Don't be enslaved to trends, master both. Start with Power Pivot, and let Power BI be an upgrade of knowledge. Later, depending on the ambitions and budget of the user, choose the path you need to take in making functional and beautiful reports.