How to choose a business analysis tool

What choice do you have?

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 can assess your business intelligence needs 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 that requires a large sum for a solution and highly qualified integrators. You will have to repeatedly resort to their services, since everything will not end with implementation and commissioning - in the future, you will need 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 in it, which means the purchase of additional user licenses.



Another inherent feature of advanced business intelligence systems is an extremely large set of functions, 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 us think about choosing 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 unloads data from accounting systems, compares them with his reference books and collects using the VLOOKUP function into one table on the basis of which the report is built.



How does Power BI solve this problem?



Data from sources is loaded into the system and prepared for analysis: it is split into tables, cleaned and compared. After that, the business model is constructed: tables are linked to each other, key figures are defined, and custom hierarchies are created. The next stage is visualization. Here, by simply dragging and dropping controls and widgets, an interactive dashboard is formed. All elements are linked through the data model. When analyzing, this allows you to focus on the information you need, filtering it out in all views with one click on any dashboard element.



What are the benefits of using Power BI over the traditional approach in this example?



1 - Automation of the procedure for obtaining data and preparing them for analysis.

2 - Building a business model.

3 - Incredible visualization.

4 - Restricted access to reports.



Now, let's take a look at each item separately.



1 - To prepare the data for building a report, you need to define a procedure that connects to the data and processes it once, and every time you need to get a report for a different period, Power BI will pass the data through the created procedure. Thus, most of the work on preparing data for analysis is automated. But the fact is that Power BI carries out the procedure for preparing data using a tool that is available in the classic version of Excel, and it is called Power Query . It allows you to complete the task in Excel in exactly the same way.



2 - Here is the same situation. The Power BI tool for building a business model is also available in Excel - it's Power Pivot .



3 - As you probably already guessed, the situation is similar with visualization: the Excel extension - Power View copes with this task with a bang.



4 - It remains to deal with access to reports. It's not all 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 assigns different levels of access to reports for these groups. This achieves the differentiation of access rights between company employees. Thus, analysts, managers and directors visiting the same page see the report in a view accessible to them. Access to a specific set of data, or to the entire report can be restricted. However, if the report is in an Excel file, then the efforts of the system administrator can try to solve the problem with access, but this will not be the same. I will return to this problem later when I describe the features of the corporate portal.



It is worth noting that, as a rule, the 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 that is sufficient to solve most business intelligence problems.



Thus, the option of doing business analysis in Excel may well satisfy the needs of an average company with a small number of employees who need reports. Nevertheless, if your company's requests are more ambitious, do not rush to resort to tools that will solve everything at once.



I would like to bring to your attention a more professional approach, using which you will get your own, fully controlled, automated system for building business analytical reports with delimited access to them.

ETL and DWH

In the previously discussed approaches to building business reports, loading and preparing data for analysis was carried out using Power Query technology. This method remains quite justified and effective as long as there are not many data sources: one accounting system and reference books from Excel tables. However, with the increase in the number of accounting systems, solving this problem through Power Query becomes very cumbersome, difficult to maintain and develop. In such cases, ETL tools come to the rescue.



They are used to unload data from sources (Extract), transform them (Transform), which implies cleaning and matching, and loading into the data warehouse (Load). A Data Warehouse (DWH) is usually a relational database located on a server. This database contains data suitable for analysis. An ETL process is launched according to the schedule, which updates the warehouse data to the current one. By the way, this whole kitchen is perfectly served by Integration Services included in 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 data business model and visualization. But first, I would like to draw your attention to one more opportunity, which 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 analytical services 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 types of data models — multidimensional and tabular. In addition to the fact that the data in these models is linked, the values ​​of the model measures are pre-aggregated and stored in the cells of OLAP cubes, which are accessed by MDX or DAX queries. With this storage architecture, a query that spans millions of records returns in seconds. This way of accessing data is necessary for companies whose transaction tables contain at least a million records (the upper limit is not limited).



Excel, Power BI and many other "solid" tools are able to connect to such models and visualize the data of their structures.



If you went 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 control the reporting process. The presence of the portal will allow to unify the company's directories: information about customers, 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 limited 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 look for a plug-in OLAP client that can connect to Analysis Services multidimensional or tabular models.

Choosing an OLAP client for rendering

Let's compare several tools in terms of embedding complexity, functionality and price: Power BI, Telerik UI for ASP.NET MVC components and RadarCube ASP.NET MVC components.

Power BI

To enable company employees to access Power BI reports on your portal page, you need to use the Power BI Embedded feature .



Right off the bat, you'll need a Power BI Premium license and additional dedicated capacity. 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, using a tricky customization, is embedded into a web application page.



An analyst may well cope with the procedure for generating a simple report and its publication, but serious problems may arise with embedding. It is also very difficult to understand how this tool works: 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 integrate Telerik and RadarCube components, it is enough to have a basic knowledge of software technologies. Therefore, the professional skills of one programmer from the IT department will be quite enough. All you need to do is place the component on a web page and customize it to fit your needs.



The PivotGrid component from the Telerik UI for ASP.NET MVC suite embeds on a page in a nifty Razor fashion and provides essential OLAP functionality. However, if you need more flexible interface settings and advanced functionality, then it is better to use the RadarCube ASP.NET MVC components . A large number of settings, rich functionality with the ability to redefine and extend it, will allow you to create an OLAP report of any complexity.



Below is a table comparing the characteristics of the considered instruments on the Low-Medium-High scale.





Now you can proceed to the definition of criteria for the selection of an analytical tool.

Power BI selection criteria

• You are interested in reports rich in various metrics and data-related elements.
• , , , - .
• IT-, BI-.
• -.

Telerik

• OLAP- Ad hock .
• .NET .
• 20%.

RadarCube

• OLAP- , .
• The company employs a mid-level .NET developer. If this is not the case, then the developers of the component will kindly provide their services, but for an additional fee not exceeding the salary of a full-time programmer.
• A small budget for a one-time license purchase and its further renewal with a 60% discount.

Conclusion

Choosing the right tool for business intelligence will allow you to completely abandon the formation of reports in Excel. Your company will be able to gradually and painlessly switch to the use of advanced technologies in the field of BI and automate the work of analysts in all departments.