{"id":96442,"date":"2020-10-11T19:42:46","date_gmt":"2020-10-11T17:42:46","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/kak-vybrat-instrument-dlya-biznes-analiza"},"modified":"2020-10-11T19:42:46","modified_gmt":"2020-10-11T17:42:46","slug":"kak-vybrat-instrument-dlya-biznes-analiza","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kak-vybrat-instrument-dlya-biznes-analiza","title":{"rendered":"How to Choose a Business Analysis Tool","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

What is Your Choice?<\/h2>\n

\nOften, the use of expensive and complex BI systems can be replaced by simple and relatively inexpensive yet efficient analytical tools. By reading this article, you will be able to assess your business analytics needs and understand which option is best suited for your business.
\n<\/a><\/noindex>
\nOf course, all BI systems have an extremely complex architecture, and their implementation in a company is not a simple task, requiring a significant budget and highly qualified integrators. You will need to repeatedly seek their services since the implementation and commissioning will not be the end\u2014you will need to customize features, develop new reports and metrics in the future. Keep in mind that if the system works successfully, you will want more employees to use it, which means purchasing additional user licenses.<\/p>\n

Another integral feature of advanced business intelligence systems is the extremely large set of functions, many of which you will never use but will continue to pay for each time you renew your licenses.<\/p>\n

These characteristics of BI systems make you think about finding alternatives. Next, I suggest comparing the standard set of tasks for report preparation using Power BI and Excel.<\/p>\n

Power BI or Excel?<\/h2>\n

\nTypically, to create a quarterly sales report, an analyst extracts data from accounting systems, matches it with their directories, and compiles it into one table using the VLOOKUP function, which is then used to generate the report.<\/p>\n

How is this task solved using Power BI? <\/p>\n

Data from sources is loaded into the system and prepared for analysis: it is broken down into tables, cleaned, and matched. After this, a business model is constructed: tables are linked to each other, metrics are determined, and 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. During analysis, this allows users to focus on the necessary information, filtering it across all views with a single click on any dashboard element.<\/p>\n

What advantages of using Power BI over traditional approaches can be seen in the example provided?<\/p>\n

1 \u2013 Automation of the data retrieval process and preparation for analysis.
\n2 \u2013 Construction of a business model.
\n3 \u2013 Incredible visualization.
\n4 \u2013 Restricted access to reports.<\/p>\n

Now, let's break down each point individually.<\/p>\n

1 \u2013 To prepare data for report creation, you only need to define the procedure once that connects to the data and processes it; each time you need a report for a different period, Power BI will run the data through the created procedure. Thus, a significant portion of the work involved in preparing data for analysis is automated. The thing is, Power BI carries out the data preparation procedure using a tool that is available in the classic version of Excel, called Power Query<\/a><\/noindex>. It allows you to accomplish the task in Excel in exactly the same way.<\/p>\n

2 \u2013 The same situation applies here. The Power BI tool for building a business model is also available in Excel \u2013 it\u2019s called Power Pivot<\/a><\/noindex>.<\/p>\n

3 \u2013 As you may have already guessed, visualization is handled similarly: the Excel extension - Power View<\/a><\/noindex> handles this task exceptionally well.<\/p>\n

4 \u2013 The next step is to understand access to reports. It's not all that straightforward. The fact is that Power BI is a cloud service accessed through a personal account. The service administrator assigns users to groups and sets different access levels for those groups regarding the reports. This creates a distinction of access rights among the company's employees. Thus, analysts, managers, and directors accessing the same page see the report in a view available to them. Access to a specific dataset or even to the report itself can be restricted. However, if the report is in an Excel file, the system administrator may attempt to resolve the access issue, but it won't be the same. I will revisit this issue when discussing the features of the corporate portal.<\/p>\n

It\u2019s worth noting that, as a rule, a company's need for complex and attractive dashboards is not very high and often, for data analysis in Excel, after constructing a business model, they do not utilize the capabilities of Power View but rely on pivot tables. These provide OLAP functionality, which is sufficient to address most business analytical tasks.<\/p>\n

Thus, the approach of conducting business analysis in Excel can adequately meet the needs of an average company with a small number of employees requiring reports. However, if your company\u2019s requests are more ambitious, do not rush to use tools that promise to solve everything at once.<\/p>\n

I propose a more professional approach, which will enable you to have your own fully managed and automated system for generating business analytical reports with restricted access to them.<\/p>\n

ETL and DWH<\/h2>\n

\nIn the previously discussed approaches to building business reports, data loading and preparation for analysis were 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 materials from Excel spreadsheets. However, as the number of accounting systems increases, solving this task through Power Query becomes very cumbersome, difficult to maintain, and develop. In such cases, ETL tools come to the rescue. <\/p>\n

These tools enable data extraction from sources (Extract), transformation (Transform), which includes cleansing and mapping, and loading into a data warehouse (Load). A data warehouse (DWH) is typically a relational database located on a server. This database contains data suitable for analysis. An ETL process is scheduled to run, updating the warehouse data to current information. By the way, all this background is beautifully handled by Integration Services, which are part of MS SQL Server.<\/p>\n

Moreover, just like before, for building data business models and visualizations, you can use Excel, Power BI, or other analytical tools such as Tableau or Qlik Sense. But first, I would like to draw your attention to another opportunity that you may not be aware of, even though it has been available to you for a long time. I'm talking about building business models using the analytical services of MS SQL Server, specifically Analysis Services.<\/p>\n

Data Models in MS Analysis Services<\/h2>\n

\nThis section of the article will be more interesting for those who already use MS SQL Server in their company.<\/p>\n

Currently, Analysis Services provides two types of data models \u2013 multidimensional and tabular models. Besides the fact that data in these models is interconnected, the values of the model metrics are pre-aggregated and stored in the cells of OLAP cubes, which can be accessed via MDX or DAX queries. Thanks to this data storage architecture, queries covering millions of records return in seconds. This method of data access is necessary for companies whose transaction tables contain over a million records (with no upper limit).<\/p>\n

Excel, Power BI, and many other robust tools can connect to such models and visualize their structured data.<\/p>\n

If you have taken the 'advanced' route: automated the ETL process and built business models using MS SQL Server services, you deserve your own corporate portal.<\/p>\n

Corporate Portal<\/h2>\n

\nThrough it, administrators will monitor and manage the report building process. The portal will standardize the company's directories: information about clients, products, managers, and suppliers will be accessible for matching, editing, and downloading in one place for all who use it. The portal can implement various functions for changing data in accounting systems, such as managing data replication. Most importantly, the portal effectively resolves the issue of organizing restricted access to reports\u2014employees will see only those reports that have been prepared specifically for their departments in the intended format.<\/p>\n

However, it is still unclear how the reports will be displayed on the portal page. To answer this question, one must first decide on the technology that will be used to build the portal. I suggest using one of the frameworks: ASP.NET MVC\/Web Forms\/Core, or Microsoft SharePoint. If your company has at least one .NET developer, the choice will be straightforward. Now you can select an embedded OLAP client that can connect to multidimensional or tabular models from Analysis Services.<\/p>\n

Choosing an OLAP Client for Visualization<\/h2>\n

\nLet's compare several tools based on the complexity of integration, functionality, and price: Power BI, Telerik UI for ASP.NET MVC components, and RadarCube ASP.NET MVC components.<\/p>\n

Power BI<\/h3>\n

\nTo provide company employees access to Power BI reports on the page of your portal, you need to use the Power BI Embedded<\/a><\/noindex>.<\/p>\n

I will say right away that a Power BI Premium license and additional dedicated capacity will be required. Having dedicated capacity allows you to publish dashboards and reports for users within the organization without needing to purchase licenses for them.<\/p>\n

First, the report generated in Power BI Desktop is published on the Power BI portal and then, with some complex configuration, embedded into the web application's page. <\/p>\n

An analyst can easily handle the procedure of creating a simple report and publishing it, but there may be serious problems with embedding. It is also quite challenging to understand how this tool works: a large number of settings in the cloud service, various subscriptions, licenses, and capacities significantly increase the requirements for the specialist's skill level. Therefore, it is better to entrust this task to an IT specialist.<\/p>\n

Telerik and RadarCube components<\/h3>\n

\nTo embed Telerik and RadarCube components, basic programming skills are sufficient. Thus, the professional skills of one programmer from the IT department will be quite adequate. All that is needed is to place the component on the web page and configure it according to requirements.<\/p>\n

Component PivotGrid<\/a><\/noindex> from the Telerik UI for ASP.NET MVC set is elegantly embedded on the page using Razor and provides the essential OLAP functions. However, if more flexible interface settings and advanced functionality are required, it is better to use the components RadarCube ASP.NET MVC<\/a><\/noindex>. A large number of settings, rich functionality with options for overriding and extending will allow creating OLAP reports of any complexity.<\/p>\n

Below, I will provide a comparison table of the features of the tools considered on a Low-Medium-High scale.<\/p>\n

 
\nPower BI
\nTelerik UI for ASP.NET MVC
\nRadarCube ASP.NET MVC<\/p>\n

Visualization
\nHigh
\nLow
\nMedium<\/p>\n

Set of OLAP functions
\nHigh
\nLow
\nHigh<\/p>\n

Configuration flexibility
\nHigh
\nHigh
\nHigh<\/p>\n

Ability to override functions
\n\u2014
\n\u2014
\n+<\/p>\n

Software customization
\n\u2014
\n \u2014
\n+<\/p>\n

Level of complexity of embedding and configuration
\nHigh
\nLow
\nMedium<\/p>\n

Minimum cost
\nPower BI Premium EM3<\/p>\n

190,000 rub.\/month
\nLicense for one developer<\/p>\n

90,000 rub.<\/p>\n

License for one developer<\/p>\n

25,000 rub.<\/p>\n

\nNow we can proceed to define the criteria for selecting an analytical tool.<\/p>\n

Conditions for choosing Power BI<\/h3>\n

<\/p>\n