What is your choice?
Often the use of expensive and complex BI systems can be replaced by simple and relatively inexpensive, but quite effective analytical tools. After reading this article, you will be able to assess your needs in the field of business intelligence and understand which option is best for your business.
Of course, all BI systems have an extremely complex architecture and their implementation in a company is not an easy task, requiring a large amount of money for a solution and highly qualified integrators. You will have to repeatedly resort to their services, since everything will not end with the implementation and commissioning - in the future, it will be necessary to refine the functionality, develop new reports and indicators. It should be noted that if the system is successful, you will want more and more employees to work on it, which means purchasing additional user licenses.
Another integral feature of advanced business intelligence systems is an extremely large set of features, many of which you will never use, but will continue to pay for them every time you renew your licenses.
The above features of BI-systems make you think about the selection of an alternative. Next, I propose to compare the solution of a standard set of tasks when preparing reports using Power BI and Excel.
Power BI or Excel?
As a rule, to build a quarterly sales report, an analyst uploads data from accounting systems, compares them with his directories and collects them using the VLOOKUP function into one table, on the basis of which the report is built.
And how is this task solved using Power BI?
Data from sources is loaded into the system and prepared for analysis: it is divided into tables, cleaned and compared. After that, a business model is constructed: tables are linked to each other, indicators are defined, custom hierarchies are created. The next step is visualization. Here, by simply dragging and dropping controls and widgets, an interactive dashboard is formed. All elements are connected through the data model. When analyzing, this allows you to focus on the necessary information by filtering it in all views with a single click on any dashboard element.
What advantages of using Power BI compared to the traditional approach can be seen in the example?
1 - Automation of the procedure for obtaining data and preparing them for analysis.
2 - Building a business model.
3 - Incredible visualization.
4 - Differentiated access to reports.
Now let's look at each item individually.
1 - To prepare data for building a report, you need to define a procedure once that connects to the data and processes it, and each time you need to get a report for a different period, Power BI will pass the data through the created procedure. This automates most of the work of preparing data for analysis. But the fact is that Power BI performs the data preparation procedure using a tool that is available in the desktop version of Excel, and it is called . It allows you to complete the task in Excel in exactly the same way.
2 - Here the situation is the same. The Power BI tool for building a business model is also available in Excel - this is .
3 - As you probably already guessed, the situation is similar with visualization: Excel extension - copes with this task with a bang.
4 - It remains to deal with access to reports. Everything is not so rosy here. The fact is that Power BI is a cloud service that is accessed through a personal account. The service administrator distributes users into groups and sets different levels of access to reports for these groups. This achieves differentiation of access rights between employees of the company. Thus, analysts, managers and directors accessing the same page see the report in a view that is accessible to them. Access can be limited to a specific set of data, or to the entire report. However, if the report is in an Excel format file, then the system administrator can try to solve the problem with access, but it will not be the same. I will return to this task when I describe the features of the corporate portal.
It is worth noting that, as a rule, a company's need for complex and beautiful dashboards is not great and often, for data analysis in Excel, after building a business model, they do not resort to Power View capabilities, but use pivot tables. They provide OLAP functionality, which is enough to solve most business analytical tasks.
Thus, the option of doing business analysis in Excel may well meet the needs of an average company with a small number of employees who need reports. However, if your company's requests are more ambitious, take your time to resort to tools that will solve everything at once.
I bring to your attention a more professional approach, using which you will receive your own, fully managed, automated system for building business intelligence reports with limited access to them.
ETL and DWH
In the previously considered approaches to building business reports, loading and preparing data for analysis was carried out using Power Query technology. This method remains fully justified and effective as long as there are not many data sources: one accounting system and directories from Excel tables. However, with the increase in the number of accounting systems, the solution of this problem through Power Query becomes very cumbersome, difficult to maintain and develop. In such cases, ETL tools come to the rescue.
With their help, data is unloaded from sources (Extract), their transformation (Transform), which implies cleaning and comparison, and loading into the data warehouse (Load). A data warehouse (DWH - Data Warehouse) is usually a relational database located on a server. This database contains data suitable for analysis. According to the schedule, an ETL process is launched that updates the storage data to the latest. By the way, this whole kitchen is perfectly served by Integration Services, which are part of MS SQL Server.
Further, as before, you can use Excel, Power BI, or other analytical tools such as Tableau or Qlik Sense to build a business data model and visualization. But first, I would like to draw your attention to one more opportunity that you might not know about, despite the fact that it has been available to you for a long time. We are talking about building business models using the analytical services of MS SQL Server, namely Analysis Services.
Data Models in MS Analysis Services
This section of the article will be of more interest to those who already use MS SQL Server in their company.
Analysis Services currently provides two kinds of data models, multidimensional and tabular models. In addition to the fact that the data in these models are related, the values ββof the model indicators are pre-aggregated and stored in the cells of the OLAP cubes, which are accessed by MDX or DAX queries. Due to this data storage architecture, a query that covers millions of records is returned in seconds. This way of accessing data is necessary for companies whose transaction tables contain more than a million records (the upper limit is not limited).
Excel, Power BI and many other "solid" tools can connect to such models and visualize the data of their structures.
If you have gone the "advanced" way: automated the ETL process and built business models using MS SQL Server services, then you deserve to have your own corporate portal.
Corporate portal
Through it, administrators will monitor and manage the reporting process. The presence of the portal will make it possible to unify the directories of the company: information about clients, products, managers, suppliers will be available for comparison, editing and downloading in one place for everyone who uses it. On the portal, you can implement various functions to change the data of accounting systems, for example, manage data replication. And most importantly, with the help of the portal, the problem of organizing differentiated access to reports is successfully solved - employees will see only those reports that were prepared personally for their departments in the form intended for them.
However, it is not yet clear how the display of reports on the portal page will be organized. To answer this question, you first need to decide on the technology on the basis of which the portal will be built. I suggest taking one of the frameworks as a basis: ASP.NET MVC / Web Forms / Core, or Microsoft SharePoint. If your company has at least one .NET developer, then the choice will not be difficult. You can now select an OLAP client that is embedded in your application and can connect to multidimensional or tabular Analysis Services models.
Choosing an OLAP client for visualization
Let's compare several tools in terms of complexity of embedding, functionality and price: Power BI, Telerik UI for ASP.NET MVC components and RadarCube ASP.NET MVC components.
Power BI
To organize access of company employees to Power BI reports on the page of your portal, you need to use the function .
I will say right away that you will need a Power BI Premium license and additional dedicated capacity. Having dedicated capacity allows you to publish dashboards and reports to users in your organization without having to purchase licenses for them.
First, a report generated in Power BI Desktop is published to the Power BI portal and then, with the help of not a simple setup, it is embedded in a web application page.
An analyst can easily handle the procedure for generating a simple report and publishing it, but serious problems can arise with embedding. It is also very difficult to understand the mechanism of this tool: a large number of cloud service settings, many subscriptions, licenses, capacities greatly increase the requirement for the level of specialist training. So it is better to entrust this task to an IT specialist.
Telerik and RadarCube components
To embed Telerik and RadarCube components, it is enough to have basic knowledge of software technologies. Therefore, the professional skills of one programmer from the IT department will be quite enough. All you need is to place the component on the web page and customize them to your needs.
Component from the Telerik UI for ASP.NET MVC suite embeds itself into the page in a nifty Razor manner and provides essential OLAP functionality. However, if more flexible interface settings and advanced functionality are required, then it is better to use the components . A large number of settings, rich functionality with the possibility of redefining and expanding it, will allow you to create an OLAP report of any complexity.
Below is a table comparing the characteristics of the instruments under consideration on a scale of Low-Medium-High.
Power BI
Telerik UI for ASP.NET MVC
RadarCube ASP.NET MVC
Visualization
Tall
Low
Average
Set of OLAP functions
Tall
Low
Tall
Customization flexibility
Tall
Tall
Tall
Ability to redefine functions
β
β
+
Software customization
β
β
+
Difficulty level of embedding and customization
Tall
Low
Average
The minimum cost
Power BI Premium EM3
190 000 rub / month
Single developer license
90 000 rub.
Single developer license
25 000 rub.
Now you can move on to defining criteria for choosing an analytical tool.
Power BI selection criteria
- You are interested in reports rich in various indicators and data-related elements.
- You want reporting employees to be able to quickly and easily get answers to their business tasks in an intuitive way.
- The company has an IT specialist with BI development skills.
- The company has a large monthly budget for a business intelligence cloud service.
Conditions for selecting Telerik components
- Need a simple OLAP client for Ad hock analysis.
- The company has an entry-level .NET developer on staff.
- A small budget for a one-time purchase of a license and its further renewal at a discount of less than 20%.
Conditions for selecting RadarCube components
- You need a multifunctional OLAP client with the ability to customize the interface, as well as support for embedding your own functions.
- The company has a mid-level .NET developer on staff. If this is not the case, then the developers of the component will kindly provide their services, but for an additional fee that does not exceed the salary level of a full-time programmer.
- A small budget for a one-time purchase of a license and its further renewal with a 60% discount.
Conclusion
The right choice of a business intelligence tool will allow you to completely abandon reporting in Excel. Your company will be able to gradually and painlessly move to the use of advanced BI technologies and automate the work of analysts in all departments.
Source: habr.com