This will not be in BI (Purchasing a BI tool, you do not get this)

Introduction

From my point of view, one of the main issues between business analysts and database developers is the interaction with each other. A lot of modern Business Intelligence solutions offer to transfer work of analysts to developers or rise technical level of business analysts, make them closer to developers. Instead of it, I advise to consider a solution, which takes information from Database and gives it to business analysts. This solution will allow everyone to focus on their own work, instead of becoming the programmer and the business analyst in one face.

Business Intelligence for Enterprise

What is BI? ― That is the first thought that whenever a occurs in my head when I hear about it. This term, which is trying to identify a specific product, but itself that is an abstract concept. As soon as someone says that he needs to analyze the activity of the enterprise, he almost certainly will be offered BI.

Consider the notion of BI:

Business intelligence (BI) ― that are tools and techniques for transferring raw information into meaningful, easy-to-use form.

This definition of BI from Wikipedia does not answer the question "What is a BI", because it is unclear what is raw information.

Here's its definition:

Raw information is background information, in other words ― primary one.

Based on these definitions, it's not obvious, why you need to buy BI products, if almost at any enterprise process of reflection on primary information in exists any case. Within it are used:

1. Automating the collection of primary information: in today's world such automation introduced almost everywhere, starting with payment terminals and ending with the staff that every day manually enter this information into the computer.
2.  Data warehouse: the information is trying to somehow analyze, for which often created a database holding information for the current day (OLTP), and a separate database, which stores information for all the previous days (Data Warehouse); data from one database to another usually moved at night.
3. Analysis of the data in the warehouse: the accumulated information trying to analyze, organizing of unloading from it, which bring in an Excel file using Excel features for understanding this information.
4. Microsoft Excel as an analytics tool: Microsoft offers the Power of BI, which converts Excel in a very serious and a modern BI-tool to help analysts. Working with it, business analysts make up a complete picture of the current financial processes of the organization.

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

• Microsoft Office;
• OS MS Windows;
•  Database.

Hence for question "Do I need to buy a separate BI-tools?" the answer is rather No than Yes! Why do we need account reporting system, various business analytic portals and similar products? Excel and so perfectly copes with all this. Theoretically, this really is quite sufficient even for large businesses. But there are reasons that make you look for something more suitable. In my opinion, in business analysis there are two bottlenecks, which deliver some inconvenience and at worst case can cause the information will cease to understand.

"Bottleneck" No. 1 ― information gathering

Gathering information is the process of primary accumulation of information and bring it to the store. To successfully accomplish this task more often:

1) Expand your organization database;

2) Hire people who will work with the database;

3) Create or acquire the means to input information into the database-user interfaces, automatic transformers; 

4) Trained staff work with these systems;

5) Provide support for the health of all systems.

 In the market you can find a lot of solutions that dramatically simplify the task of gathering information. It's a different system for the transfer of data between data sources, such as Microsoft Integration Services, Informatic and others. In addition, databases are becoming faster and more flexible, appears   Big Data system. As a consequence, the problems with the collection of information are quite soluble as opposed to the next problem.

"Bottleneck" No. 2 ― Data transfer of from the warehouse to users

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

1. Excel templates-created documents that had already been made to connect to a database and configured the sample from it.
2. Users are given the opportunity to write queries to the database and choose what they want.
3.  The account reporting system, such as Microsoft Reporting Services, SAP BO, Oracle BI and others acquired.
4.  Local databases for users, e.g. MS Access organized.

 Each of these methods sooner or later faces the challenge that it cannot solve. Optimal use of these methods is doubtful too. I propose to consider these methods in more detail.

Excel-documents templates

• With such templates it is not very convenient to work to those who creates them. They must open each of them, see select in it; remember passwords for access to the select in order the end user does not change anything.
•  Storage and access to these patterns also need to somehow provide. You can store them in a shared folder, you can develop or acquire a Web application that will provide access to all of this. Sooner or later there comes a situation where reports are getting a lot more, and we should remember what they do.
• Data becomes very much, and the report could not be opened due to lack of memory.
•  The query parameters enter is difficult. You can write in Excel VB-script, which prompts them to enter, but for each Excel you have to write for this script either improvement or correction of this script must be broadcast to all the other templates.

Users write requests to the base

The disadvantage of this variant lays in the level of skill of the staff. For business analyst the ability to write SQL queries is not the main activity, hence the combination of several activities in the hands of one specialist requires a high level of proficiency.

Purchase reporting system

Significant plus of this solution compared to previous ones is a compact report storage, easy-to-use administration and the availability of user interface for setting input parameters.

 Reporting system solves the problem partly, but not completely.

Business analyst work in Excel – this is very convenient for them. But reports are built into the Web,    from where they need to unload. In the same way they build various summary tables and graphs, which Excel can do too. The result is that the reports serves as superfluous link between Excel and database.

 They have also other disadvantages:

•  Information when unloading in Excel can disrupt, formatting can spoil as well as column types can change, etc.
• The report, which runs on the Web, in Excel runs very slowly or breaks.
•  Reports the implementation of which requires a lot of time (over an hour), may not be completed due to achieve maximum wait time.
•  Trying to unload large amounts of information can lead to a lack of memory on the server, which potentially can lead to its stop, either report formed in the Web, but cannot be unloaded into Excel. There are different ways to work around these problems, such as unloading in CSV, partial transfer of information, etc., but they are inconvenient from the user point of view and can require increased attentiveness, carefulness and additional knowledge from user.

In my opinion, described systems more suited to display data, but not to distribute them to users. They can be compared to a beautiful showcase data where you can watch them, but it is difficult to "touch" them.

Local users database

In my opinion such a decision is bulky and not optimal, though perhaps in the future it may become relevant. The slogan "Own Database for every business analyst!" came into my mind.

 What user wants 

The main problem for users is the lack of a convenient and reliable system for the transfer of data to users from the store. Business analysts often enjoy Excel for its calculations, so it is necessary to transmit the data in Excel format. Unfortunately, a user-friendly solution I have not seen and had to do something combined ― partly transfer data in reports, something pass in the Excel templates, implement OLAP cubes and similar to these server solutions. CSV files and user databases to Microsoft Access also used. Caching reports and deferred execution actively used. Decisions that have appeared on the market parallelly had to consider with a view to their implementation.  In the end a little "Zoo" of different approaches and solutions turned out. In order to escape from the Zoo, it is necessary to look at the question from user: "Ensure the transfer of information from data store to the user in Excel format!" and solve this task specifically, not trying to expand and improve the tools used by business analysts. Hence, having studied the intricacies 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: (https://www.youtube.com/watch?v=y-PX9W4qsd4). Create it is an easy case, as well as brought it to the level of commercial decision. It can unload an arbitrarily large amounts of information and also to wait indefinitely time until queries are executed in the database. SAX document model and streaming data used for this.

Conclusion

After many years of having work with BI, I never saw a solution which could pass the users information from the data warehouse without problems. First and foremost, BI tools help analyze information, show the whole picture, they are not done with a view to ensuring stable data transmission. This is often not enough for stable analytical system. Analysis of information is convenient for doing on the client side in Excel and Power BI and data storage is a work for database programmers. The bridge between them does not built so far.