This will not be in BI (By purchasing the BI tool, you will not get it)

Introduction:

In my opinion, one of the main problems between business analysts and database programmers is their interaction. Many modern BI solutions offer, in fact, to transfer the work of analysts to programmers. Or raise the technical level of analysts, bringing them closer to the programmers. Instead, I propose to consider a solution that takes information from DB programmers and gives it to business analysts. This solution will allow everyone to do their own thing, and not to become a programmer and business analyst in one person.

Business analysis for the enterprise:

What is BI? - this is the first thought that every time arises in my head when I hear about it. This is a term that is trying to designate a particular product, but in itself is an abstract concept. As soon as someone says that they need to analyze the activities of the enterprise, they will almost certainly be offered BI.

Consider the very concept of BI.
Business intelligence (BI) is the methods and tools for translating raw information into a meaningful, convenient form.
Such a definition of BI from Wikipedia does not answer the question “What is BI”, because it is not clear what raw information is .
')
Here is its definition:
Raw information - source information, primary information.

If you rely on these definitions, then it is not obvious why you should buy BI products, if in almost any enterprise a process of understanding primary information already exists. In its framework are used:

1. Automation of the collection of primary information: In the modern world, such automation has been introduced almost everywhere. Starting from the payment terminals and ending with the employees, who manually manually enter this information into a computer every day.
2. Data Warehouse: They try to somehow analyze the information received, for which they most often create a database that stores information for the current day (OLTP) and a separate database for information for all previous days (Data Warehouse); at night, data usually moves from one database to another.
3. Data analysis in the repository: They try to analyze the accumulated information by organizing uploads from it, which are brought into an Excel file, using the capabilities of Excel to interpret the information received.
4. Microsoft Excel as an analytics tool: Microsoft offers Power BI to analysts, which turns Excel into a very serious and modern BI tool. Working with him, business analysts provide a complete picture of the organization’s current financial processes.
   

This practice is widely used in many companies. The process can be organized using publicly available components:

• Microsoft Office
• Os ms windows
• Database.

Hence, to the question “Should I buy separately BI-tools?” The answer is no , rather than Yes ! Why do we need reporting systems, various business intelligence portals and similar products? Excel is doing great with it. Theoretically, this is really quite enough, even for large businesses. But there are reasons that make you look for something more appropriate. In my opinion, these are two bottlenecks in the organization of business analysis, which cause certain inconveniences and in the worst case may cause the information to cease to be comprehended.

“Bottleneck” № 1 - Information Collection

Information collection is the process of primary accumulation of information and its transfer to the repository. For a successful solution of this problem most often:

1. Deploy in the organization database;
2. Hire people who will work with the Database;
3. Create or acquire means of entering information into the database - user interfaces, automatic transferrs;
4. Train personnel to work with these systems;
5. Provide support for the health of all systems.

On the market, you can find many solutions to significantly simplify the task of collecting information. These are various systems that provide data transfer between sources, such as Microsoft Integration Services, Informatic and others. In addition, databases become faster and more flexible, systems like Big Data appear. As a result, problems with the collection of information are completely solvable, unlike the following problem.

“Bottleneck” № 2 - Data transfer from the repository to users

Users need all information for analysis. But it must somehow be structured and displayed in a form convenient for business analyst. In my opinion, it is here that there is the field for creativity, which gave rise to many software tools on the market, but did not solve the main problem, the problem of reliable and convenient data transfer to users. There are several ways to organize this process:

1. Templates of Excel-documents are created, in which the connection to the database is already provided and samples from it are set up.
2. Users are given the opportunity to write queries to the database themselves and choose what they need.
3. Acquire a reporting system, for example, Microsoft Reporting Services, SAP BO, Oracle BI, and others.
4. Organize local databases for users, for example, MS Access.

Each of these methods sooner or later faces a task that he cannot solve. The optimal use of these methods is also questionable. I propose to consider these methods in more detail.

Excel Document Templates

• These templates are not very convenient for those who create them. It is necessary to open everyone, to look in it; remember passwords for access to select, so that the end user does not change anything.
• Storage and access to these templates also need to somehow provide. You can store them in a shared folder, you can develop or purchase a Web application that provides access to all of this. Sooner or later, there is a situation when there are a lot of reports, what they are doing, we must also remember.
• There is a lot of data, and the report cannot be opened due to lack of memory.
• Entering query parameters is difficult. You can write VB-script in Excel, which will offer to enter them, but for every Excel you will have to write this script or improve or correct this script will need to be translated into all other templates.

Users write queries to the database

The disadvantage of this option is in the staff qualification level. For a business analyst, the ability to write SQL queries is not the main activity, hence the combination of several types of activities in the person of one specialist requires him to be highly qualified.

Purchase a reporting system

A significant advantage of this solution in comparison with the previous ones is the possibility of compact storage of reports, convenient administration and the availability of a user interface for setting input parameters.

Reporting systems partly solve this problem, but not completely.

Business analysts work in Excel - it is very convenient. But reports are built on the Web, from where they need to be uploaded. They also build various pivot tables and graphs that Excel can do anyway. The result is that the reports act as an extra link between Excel and the database.

They also have other disadvantages:

• When uploading to Excel, the information may be broken, the formatting “will go away”, the column types will change, etc.
• A report that quickly runs on the Web in Excel runs very slowly or “breaks”.
• Reports that take a long time to complete (more than half an hour) may not be completed due to the maximum waiting time.
• Attempting to upload large amounts of information can lead to a shortage of memory on the server, which could potentially lead to its stopping, or the report will be generated on the Web, but will not be able to be unloaded in Excel. There are various ways to circumvent these problems, such as uploading to CSV, partial transfer of information, etc., but all of them are inconvenient from the user's point of view and require heightened attentiveness, accuracy and additional knowledge.

In my opinion, the described systems are more suitable for displaying data, and not for transferring them to users. They can be compared with a beautiful showcase, the data in which you can look, but it is difficult to “touch”.

Local user databases

Such a decision, in my opinion, is cumbersome and not at all optimal, although it may be relevant in the future. The slogan came to mind: “To each analyst on the database!”

What the user wants

The main problem for users is the lack of a convenient and reliable system for transferring data from storage to users. Business analysts often use Excel for their calculations, so data must be transferred in Excel format. Unfortunately, I did not see a convenient solution, I had to do something combined - to transfer part of the data in reports, transfer something in Excel-templates, implement OLAP-cubes and server-solutions similar to them. CSV files and user databases on Microsoft Access were also used. We actively used report caching and deferred execution. At the same time, we had to consider solutions that appeared on the market in order to implement them. The result was a small "zoo" of various approaches and solutions. In order to get away from this zoo, you need to look at asking the question from the user: “Ensure the transfer of information from the repository to the user in Excel format!” And solve this problem specifically, not trying to expand and improve the tools used by business analysts. From here, having studied the subtleties of Excel and downloading the OpenXML SDK, I wrote an application that generates reports directly in Excel files. How it works can be viewed at the following link: An example of such a system . It is easy to create, as well as bring to the level of a commercial solution. It can unload arbitrarily large amounts of information and also wait indefinitely while requests are executed in the database. For this purpose, the SAX-model of the document and streaming data were used.

Conclusion

After many years of working with BI, I never saw a solution that could easily transfer information from users to the repository. BI tools primarily help analyze information, show a complete picture, they are not made to ensure the transfer of data to users. Namely, this is most often not enough for a stable working analytical system. It is convenient to analyze information on the client side in Excel and Power BI, and database programmers can successfully handle data storage. The bridge between them is still not built.