The one who carries the lantern stumbles more often
than the one who follows
Jean paul

Foreword

In this article I want to talk about what I have been doing for the past 5 years. Information has accumulated a lot and I tried to present it in a simple and accessible form. System layout:
General scheme (vsd)

Start

“ We are taking you ,” said my future leader, “ but we must visit another room.”
I was standing in a stuffy corridor, a printer was rustling alongside, and there was a closed door in front of me. Outside the door, someone was talking loudly on the phone. That voice stopped, we knocked. A man sat behind the door in the chair, he looked at me, then at the head and asked:
- Suitable?
- Yes, completely. - My future boss replied.
- OK, we take.
So my career in the Bank began without a hitch.
')
The first task sounded to me is Automation of data collection and provision, based on them, of managerial reporting . I started working with Microsoft SharePoint 2010. Version 2007 has already been deployed to me. And my first feat was an attempt to transfer users from the old version of SharePoint to the new one, during which I accidentally deleted all these users. In this regard, acquaintance with the server administrator for the portal with the reports took place in the process of data recovery: nervously, sadly, quietly. I trembling hands and wet forehead, restored deleted, and he watched, so that I did not throw something out.

First swallow

After some time, Microsoft SharePoint with the integrated Microsoft Reporting Services was deployed. After XSLT transformations and css layout, the portal acquired a corporate look. And the process of creating the “Warehouse” began.

“Storage” was called the Database on Oracle, in which it was supposed to build “storefronts”. And under the "shop windows" was understood data for analytics. I created the first report alone, as the staff expanded slowly, but it was necessary to do it as usual - “yesterday”. At first, there were not even servers for the database and for the SharePoint portal. Used the standard user computer, having got to it the administrator password. The operation of this administrator password itself was a severe disciplinary violation. So from the first days of working at the Bank I had to accept the idea that success can be achieved here only by breaking the rules. The second violation was a decommissioned computer, which for some reason was forgotten to take from us. Since he was not set up for use in the corporate network of the bank, I stuck Yota into it and used it as an access point to the Internet. In general, it was possible to build the workplace of the programmer from improvised means.

It is worth noting that the department in which I was hired was not related to programming and was engaged exclusively in economic tasks. And me and my colleagues were taken, as it were, illegally. The Bank is not taken to be interested in how a person will solve the problem, it just comes down from above and if you solved it, then well done, if not, then there will be trouble (as with real illegal immigrants). From here my direct management, understanding that only programmers can automate management reporting, took a risk and began to recruit us under the guise of economists.

Since there were no resources at first, the first database was Microsoft SQL Server, which was also used by the SharePoint portal and Reporting Services. I built the data according to the “Star” scheme, in the reports I used the widely used control - Matrix, with its help I grouped it in rows and columns. The public liked my reports and in a couple of days we were invited: me, the administrator, two superiors and two analysts, to the most important room of the Bank. The room was furnished with antique furniture, the Big Man was sitting at the table. In a solemn atmosphere, he told us about the importance of our work and inspired us to further exploits.

What does not suit in Microsoft Reporting Services.

But what the management liked was not entirely satisfied with the Bank’s employees, who had to work daily with our reports. Working with Microsoft Reporting Services, users noted the following inconveniences:

1. Matrix reports look like pivot tables, grouped. Groupings can be expanded by clicking on + for each member of the group. On a web form, this works fine, but when uploading to Microsoft Excel, with a large amount of data, everything is very slow.
   
2. In some reports, at a certain set of values, a query that retrieves data from the Database in Reporting took a very long time, and as a result, an error exceeding the timeout interval popped up. Attempts to increase the interval did not solve the problem, because There is a limit on the time to return data from the server. In the case of Internet Explorer, this is ReceiveTimeout. It can be changed, but only on the client’s machine, and we had no such opportunity.
3. The amount of data in the report for the client could exceed all possible values. In the first week of work, I ran into this. The user wanted to unload 1 million lines from the database, so that, as he put it: “I just want to check them, suddenly, where is the error!”. Unloading this amount of information in Excel, Reporting could not complete the upload, because he ran out of memory on the server. The servers, at first, were rather weak and such errors brought the situation to a standstill.
4. The queries used in reports are stored by MS Reporting on their server. But I wanted them to be stored in the Oracle database in which the Warehouse was located. The same problem was with checking access to reports, which is stored in Microsoft SharePoint. I had to write utilities that, daily, copied this data from the source systems to the Oracle database repository.
   

It should also be noted that in the Bank, users work with data most often in Microsoft Excel. Most of them do not need reports on the Web form. Making a report or presentation, the user wants to somehow “play” with the numbers. Add something of your own, conduct some analytics or compare with previous reports. Only accounting and statistical data were formed in standard forms. But such reports were a minority, the majority provided data to the user, in order to further work with them.

Microsoft Reporting Services can not, Analysis Services will help

If the mountain does not go to Mahomet,
Mahomet goes to grief
the story of Hodja Nasreddin

The above problems have caused a lot of trouble. While I was looking for their solution, the guys from QlikView came to us and began to offer their product. The people were colorful, who wore berets with a suit, who wore a jacket over a t-shirt. In general, they saved on salespeople, they wanted to do everything on their own. But the product itself made an impression and made us think about how to improve what we are doing. The downside of this product, in the case of our repository, was that it used memory to build its reports. And in our case, huge amounts of data were assumed, and the limited memory could potentially be a serious problem. The second point was that the direct management wanted to have a team of their own personal developers, not outsiders. In general, despite their chic presentations, they were refused. But QlikView was similar to OLAP, and at one time I was on Microsoft Analysis Services courses. This led to the fact that I began to build cubes on top of our storage. And using ASPxPivotGrid from the company DexExpress, I showed them on a Web form. It also turned out that Microsoft Excel could be an Analysis Services client, which turned out to be very convenient. Since the user could build and analyze what he needed.

Difficulties with Microsoft Analysis Services

But not without a spoon of tar. Microsoft Analysis Services is not very convenient for administration and it has a number of problems:

1. Mass distribution of access - for example, users need to give access to 30 different cubes. When solving this problem, standard means must be given access in each cube separately.
2. Autopartitioning - there is no regular way to make new partitions added automatically.
3. Update cubes - when there are a lot of cubes, with many partitions in each, the complexity of administration increases many times. Microsoft Integration Services is useful in the case of a single cube, but with the growth in the number of cubes, convenience is lost.
4. When a user is deleted from Active Directory, it becomes impossible to deploy a cube project to a server if the remote user has access to this cube. You have to find out which account was deleted and also delete it from the cube.
5. The difficulty with the deployment of the modified project.
   To solve these problems, Microsoft has provided an API, through which you can develop utilities that facilitate work. Also, projects that describe the cube being created store all information in xml files. This greatly facilitates the development of software for convenient administration of MS Analysis Services.
   

In general, in his own style, Uncle Bill, instead of giving the finished house, handed the board and hammer the nails.

Taming Microsoft Analysis Services

Experience is a school where lessons are expensive,
but this is the only school in which to learn.
Franklin B.

Using the tools from Microsoft, I have developed and actively use the following utilities:

1. The utility of mass distribution of access, which allows you to distribute access simultaneously to many cubes, to certain roles in each. It also automatically removes users that are no longer in Active Directory.
2. An auto-update utility that determines when and which cube needs to be updated, and what needs to be updated in it (which partition, etc.) and starts the update. It has the ability to save errors that occurred during the update process, and also, there is the possibility of adding new cubes, their administration, suspension of updates.
   

Unfortunately, the current workload does not allow us to develop utilities that can significantly facilitate the work with MS Analysis Services. Although the thoughts and ideas on this subject set.

It is also worth noting the mistake I made when I first started building them. It consisted in the fact that I built the facts and measurements with the help of complex selects. When it came time to cut the cube into partitions, they moved into each partition. What led to their repeated duplication, which is very bad. On the other hand, some selects were performed for quite a long time and in the case when it was required to rebuild the whole cube again, the time for its rebuilding increased greatly. After some time, it came to an understanding that before creating a cube it is necessary to create tables in the database that will contain all the data for it. And add new data to them, daily, with the help of jobs. On the basis of these tables, as a result, build a cube. It is also worth developing rules for naming entities and deciding in which cases it is necessary to create separate schemas in the Database.

Always want more.

OLAP cubes greatly facilitated the work of users, but did not solve all the problems. There were still reports that could not be loaded into Analysis Services for various reasons. Certain difficulties were associated with the fact that the update time of a cube or its partition can be from several minutes to several hours. When this time is available, there are no problems, but there are situations when the data must be provided “the sooner the better” and here the cubes are not an assistant. The solution was found in the process of working with users. In the subdivision adjacent to me, one very intelligent employee worked. Seeing her high professional level, it was decided to give her access to the database through a Web form. The task did not seem to me too complicated, and I wrote an application that allowed me to write database requests and return the result as a file in csv format. It was a web form with a text entry field and a button that, when clicked, the query was executed and the data was returned.

Thanks to this, the employee wrote many templates on MS Excel, using OLAP cubes and uploads. She analyzed the latter using formulas as well as using Power Pivot. As a result, she began to do well and too much and ... she quit. A little later, another employee quit in the same way. I shared my thoughts about this with an experienced colleague of retirement age, to which I received an intricate answer:

- The train from Romashkovo must correspond to Romashkov, otherwise the rails will not stand. (well, something like, the staff has professionally outgrown her department)

As a result, I reworked the program that unloaded the data from the database. Now it can upload to Microsoft Excel file format. When creating it, I used the OpenXML SDK. The system consisted of three web forms. One for easy administration, the second for the user to see a list of existing reports and the third for generating a report. Compared to Microsof Reporting Sevices, this system required a minimum of RAM in its work, since she used the SAX model to generate an Excel file. Also, she could wait for the query to execute indefinitely. I did this by sending verification requests from the client to the server. Another advantage was a significantly larger amount of information that she could upload. I did this by forming a certain number of lines per tab (Sheet) in the Excel file. As soon as this number was reached, the following tab was created. This application, at first, was hidden behind 7 locks, and access was given only to those who specifically requested it. But, gradually, it gained popularity, and he was taken out of the shadows.

There are many tasks, few people

The development team slowly expanded. They took one more, 5 people of Oracle DB programmers help me. My assistant was immediately loaded with the SharePoint design and bonus system, so the only help was that from that moment I was not doing it.

The task of the programmers working with the database was filling it with data. I built the reports alone. In parallel, I administered the OLAP Cubes and all the programs I wrote. I also tested all of this. I also had to, constantly, by request, distribute access to all our resources to everyone. It was also necessary to maintain a number of reports that were sent to the center. The principle worked in the department - the one who does is on that one. Attempts to transfer part of their functionality to someone else were met with misunderstanding by the management. To throw off at least the reports had to go on vacation for a month. I was lucky, because after this incident, for such a period, only the head went on vacation. While I was resting, the management was beside himself with anger, since there was no one to make reports. As a result, DB programmers have sorted out Microsoft Reporting Services and learned how to work with it. Returning from vacation, I felt some relief. Now you could not go home at 8 pm, but a little earlier.

What used

The reporting system has grown rapidly and gained popularity. In addition, she had to do other useful applications. A system was developed for evaluating customer profitability, employee bonuses, comparison of customer commissions, and insurance premiums. Programs were developed on the .Net Framework, Elmah was used to control errors. As an ORM system, Dapper best suited. Logfornet was also used, jQuery was used on the client side. Since development time was set according to the principle “must be done yesterday”, then the DevExpress and ASP.NET controls were widely used. JSON was used to transfer information between the client and the server. Control over the execution of tasks was carried out with the help of Redmine.

Oh, those files!

Files are widely used to transfer information between systems. And most often it is an Excel file format. I don’t know where this tradition comes from, but they need to be loaded, and there are a lot of files. The download was done, who could. Someone used Oracle tools, I wrote utilities on C #, one of my colleagues used Microsoft Integration Service, another Informatica.

And so, as when I came from vacation, I saw a long list of request-wishes that my colleagues wrote in my absence. It was a universal loader from files to the database. We agreed that it will be able to disassemble Excel files of the old and new formats, as well as any text files. For Excel, it will convert everything to string and write column by column in the staging table, for plain text files, write everything to another table, on the principle - one line - one entry in the column. The input interface should be a web form. And the application itself must be unified and work on the Web server. What complicated its development, since users, on the one hand, should be able to download data in real time through a browser, on the other hand, downloads should be launched offline, according to a schedule.

One morning, the boss calls me to himself and sets before the fact: “It is necessary to do this in a week, without ceasing to administer everything that you wrote before.” In an attempt to increase the deadlines for the task, the manager, with the appearance of a professional, listed the points I have to do and how much time to spend on it. In general, it was possible to agree on 3 weeks. But the work was actually completed in 3 months. This is probably a managerial tradition - to set short terms.

In order not to anger the management, the employees, within the time allotted for the development, quickly make a prototype that somehow works. Demonstrate his superiors, it puts a tick - “done” and runs all to show and tell what a miracle thing their subordinates wrote and how it all works. Moreover, it is casually mentioned that the project is at the testing stage and there are errors in it. After the management has reported everything, everyone forgets about the project and you can slowly finish it. There were cases when no one needed it by the time the application was actually ready.

Three months later, the loader was ready. During its development jQWidgets controls were used. The application turned out to be very useful, because users saw all downloads in one place and could easily and conveniently manage downloads from a Web browser. The problem disappeared - “What and from where we ship, why it does not load and how to deal with it”.

Do any of the developers like SharePoint?

... then thoroughly
file a file
From Anecdote

Many of the difficulties in developing delivered applications for SharePoint. By itself, the development, as it were, fundamentally, does not differ from the creation of ordinary Web-sites. But the server is all deployed for a very long time.

As a result, we decided: serious applications that require a large amount of coding should be done separately, with links to SharePoint. They had a similar design, using the same access system as the portal.

In the process of administering the portal, there is a need for more flexible work with user accounts. The problem was that some of the information was stored in Active Directory, some in Oracle. A search engine was developed that worked with both systems simultaneously. It turned out to be so successful that so many employees at the Bank began to use it. Especially after I screwed pictures of the staff to him.

In search of a magic button

No skill without magic.
Valentin Borisov

If we were somehow able to satisfy ordinary Bank employees, the management wanted something more from us. They saw a Web-form with a large traffic light and a button that says “Press me and everything will be better”. This traffic light tried to do since the start of the project. But serious work began only after 3 years.

All planned indicators were taken and compared with the facts. From all this, we made a tree, bypassing which graphs with signs for each indicator opened. The project provided the ability to download and reflect actions to improve the situation with the implementation of the plan. The solution turned out to be very interesting and useful, but it required a lot of human resources, because for each indicator, it was necessary to load the predictions on how it will be carried out in the event of non-compliance, load explanations and changes to improve the situation. This was his big plus. With this application, the position of our department at the Bank has been greatly strengthened. The management obliged to work with this program employees of various departments.
In parallel with it, I, on my own initiative, developed a program that combined all analytical cubes. In addition to the pivot tables, she also displayed information graphically. The result of this work turned out to be indirectly useful. The application was used more for informational purposes, because it clearly showed the usability of working with cubes. As a result, the number of employees actively working with them has increased. Another advantage of this solution was the ability to quickly detect errors in the system, since On the graphs constructed using analytical cubes, anomalous bursts are visible, or dips in those days that contain erroneous data. And the search and correction of errors was the second part of the work.

Trust but check

Data could change in hindsight, partially load, or double. The credibility of the products was very dependent on the quality of the data. No wonder they say - “Quality commands respect!”. Over time, it came to an understanding of the need to develop a unified quality control system. As a result, we developed a Web application in which selects used in reports were established and checks for them. The system ran these checks daily and reported errors. At the moment I am trying to connect to it test cubes. The check of calculated data was also connected to it. As a result, trust in products has grown, although there is still something to work on. No wonder they joke:

“If the programmer were a doctor:
- Doctor, my leg hurts.
- Strange. I have the same and does not hurt! “

Place to step forward (Viktor Tsoi)

At the moment, the development of management reporting automation systems has almost stopped. Some of the developers, deep down, got bogged down in administration, and another was hired for various projects of related departments. Also, some of the forces were taken away by the draft manual on the automated control of our activities. Although, despite this, there are many things that can be done much better and more convenient. And if to gain strength and be able to not only realize them, but also give a presentation, then I think they could bring good dividends. As Onore Balzac said: “To reach the goal, you must first go.”

Although the automated reporting system has not turned into a large company competing with Microsoft, but in my opinion, it is quite capable of that.