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 last 5 years. A lot of information has accumulated and I tried to present it in a simple and accessible form. System Diagram:
General Diagram (vsd)

Start

“ We're taking you ,” said my future leader, “ but you need to visit another office.”
I stood in a stuffy corridor, a printer rustled nearby, and in front of me was a closed door. Outside the door, someone was talking loudly on the phone. Here the voice fell silent, we knocked. A man was sitting in a chair outside the door, he looked at me, then at the head and asked:
“ Will it fit?”
- Yes, quite. - My future boss answered.
- Ok, take it.
So plainly began my career at the Bank.

The first task voiced to me is Automation of data collection and the provision, on their basis, of management reporting. I started with Microsoft SharePoint 2010. Version 2007 was already deployed before 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 reports took place in the process of data recovery: nervously, sadly, quietly. With trembling hands and a wet forehead, I restored the deleted ones, and he looked so that I would not throw anything else.

First swallow

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

The “Repository" was called the Oracle Database, in which it was supposed to build "windows". And by "storefronts" was meant data for analytics. I created the first report alone, as the staff was expanding slowly, and I had to do it as usual - “yesterday”. At first, there were not even servers for the database and for the SharePoint portal. We used a standard user computer, having obtained an administrator password for it. The operation of this same administrator password was a severe violation of discipline. So from the first days of work at the Bank, I had to come to terms with 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 be taken from us. Since it was not configured for use in the bank’s corporate network, I stuck Yota in it and used it as an Internet access point. In general, out of the means at hand, we managed to build a workplace for the programmer.

It is worth noting that the department in which I was hired was not involved in programming and was engaged exclusively in economic tasks. And they took me and my colleagues illegally, as it were. It is not customary for the Bank to take an interest in how a person will solve a problem, it just goes down from above and if you solved it, then well done, if not, then there will be troubles (as with real illegal immigrants). Hence my direct leadership, realizing that only programmers can automate management reporting, took a risk and began to recruit us under the guise of economists.

Since there were initially no resources, 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 widely used the control - Matrix, with its help I grouped by rows and columns. The audience liked my reports and after a couple of days we: I, the administrator, two bosses and two analysts, were invited to the main room of the Bank. The room was furnished with antique furniture, a Big man was sitting at the table. In a festive atmosphere, he told us about the importance of our work and inspired us to further exploits.

What does not suit Microsoft Reporting Services.

But what management liked was not entirely satisfying for the Bank's employees, who were supposed to work daily with our reports. When working with Microsoft Reporting Services, users noted the following inconveniences:

1. Matrix reports look like pivot tables, with groupings. 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, with a certain set of values, the query that received data from the Database in Reporting took a very long time, and as a result, an error occurred when exceeding the wait interval. Attempts to increase the interval did not solve the problem, because There is a time limit for returning 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 didn’t have such an opportunity.
3. The amount of data in the report for the client could exceed all conceivable values. In the first week of work, I came across this. The user wanted to unload 1 million lines from the database so that, as he put it: “I just want to compare them, suddenly, where is the error!”. When uploading such an amount of information to Excel, Reporting could not complete the upload, because he was running out of memory on the server. The servers, at first, were rather weak and such errors led the situation to a standstill.
4. MS Reporting saves the queries used in reports on its server. But I wanted them to be stored in the Oracle database in which the Storage 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 a Web form. Making a report or presentation, the user wants to somehow “play” with numbers. Add something of your own, conduct some kind of analytics or compare with previous reports. Only accounting and statistical data were formed in standard forms. But there were a minority of such reports; the majority provided data to the user in order to further work with them.

Microsoft Reporting Services cannot; Analysis Services will help

If the mountain does not go to Mohammed,
Mohammed goes to the mountain the
story of Khoja Nasreddin

The above problems brought 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 sales people, they wanted to do everything on their own. But the product itself impressed and made me think about how to improve what we do. 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 supposed, and 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, and not outsiders. In general, despite their chic presentations, they were refused. But QlikView was like OLAP, and I used to take Microsoft Analysis Services courses. This led to the fact that I began to build cubes on top of our storage. And with ASPxPivotGrid from DexExpress, I showed them on a Web form. It also turned out that Microsoft Excel can act as an Analysis Services client, which turned out to be very convenient. Because the user could build and analyze what he needed.

Difficulties with Microsoft Analysis Services

But not without a fly in the ointment. 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 be given access to 30 different cubes. When solving this problem by standard means, you must give access to each cube individually.
2. Auto-partitioning - there is no regular way to make new partitions automatically add.
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 convenient in the case of a single cube, but with an increase in the number of cubes, convenience is lost.
4. When a user is deleted from Active Directory, it becomes impossible to deploy the cube project to the server if the remote user had access to this cube. You have to find out which account has been deleted and also delete it from the cube.
5. Difficulty deploying a modified project.
   To solve these problems, Microsoft has provided an API that can be used to develop utilities that make work easier. Also, projects that describe the created cube store all the 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 a finished house, handed a hammer to a board and nails.

Taming Microsoft Analysis Services

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

Using Microsoft tools, I 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 specific roles in each. It also automatically deletes 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. She 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 prevents us from developing utilities that can greatly facilitate the work with MS Analysis Services. Although there are many thoughts and ideas on this subject.

It is also worth noting the mistake I made when I just started building them. It consisted in the fact that I built facts and measurements with the help of complex selects. When it was time to cut the cube into partitions, they migrated to each partition. Which led to their repeated duplication, which is very bad. On the other hand, some selects were performed for a rather long time, and in the case when it was required to rebuild the entire cube anew, the time of its rebuilding greatly increased. After some time, the understanding came that before creating a cube, you need to create tables in the database that will contain all the data for it. And to add new data in them, daily, with the help of Jobs. Based on these tables, in the end, build a cube.

I always want more.

OLAP cubes greatly facilitated the work of users, but did not solve all the problems. There were still reports that, for various reasons, could not be loaded into Analysis Services. 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. One very sensible employee worked in the unit adjacent to me. Seeing her high professional level, it was decided to give her access to the database through a Web form. The task didn’t seem too difficult for me, and I wrote an application, which allowed you to write queries to the database and return the result as a file in csv format. It was a Web form with a field for entering text and a button, by clicking on which a query was executed and data was returned.

Thanks to this, an employee wrote many templates in MS Excel using OLAP cubes and uploads. She analyzed the latter using both formulas and Power Pivot. As a result, she began to do well and too much and ... 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 should correspond to Romashkovo, otherwise the rails will not stand. (well, sort of, the employees professionally outgrew their department)

As a result, I reworked a program that unloaded data from the database. Now it can upload to a Microsoft Excel file format. When creating it, I used the OpenXML SDK. The system consisted of three Web forms. One for convenient administration, the second so that the user can 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 during its operation, as She used the SAX model to generate an Excel file. She could also wait for the request to execute as long as she wanted. I did this by sending test requests from the client to the server. Another advantage was the significantly larger amount of information that it could upload. I did this by forming a certain number of rows per bookmark (Sheet) in an Excel file. As soon as this amount was reached, the next bookmark was created. This application, at first, was hidden behind 7 locks, and access was given only to those especially asking. But, gradually, it gained popularity, and it was taken out of the shadows.

Many tasks, few people

The development team was slowly expanding. They took one more person, 5 Oracle DB programmers to help me. My assistant was immediately loaded with the design of SharePoint and the bonus system, so the help was only that from that moment it wasn’t me who did it.

The task of programmers working with the database was to fill it with data. I built reports alone. In parallel, I administered OLAP Cuba and all the programs I wrote. I was also engaged in testing all this. I also had to constantly distribute to everyone, upon request, access to all our resources. 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 it is the one who drives it. Attempts to transfer part of its functionality to someone else met with misunderstanding on the part of management. To throw off at least reports I had to go on vacation for a month. I was lucky, because after this incident, only the head went on vacation for such a period. While I was resting, the leadership was beside myself with anger, since there was no one to report. Eventually, database programmers figured out Microsoft Reporting Services and learned how to work with it. When I returned from vacation, I felt some relief. Now it was possible to leave home not at 8 in the evening, but a little earlier.

What did you use

The reporting system has grown rapidly and gained popularity. In addition to it, I had to make other useful applications. A system was developed to evaluate customer profitability, employee bonuses, a comparison of customer commissions, and insurance premiums. Programs were developed on the .Net Framework, and Elmah was used to control errors. Dapper is best suited as an ORM system. Logfornet was also used, on the client side they used jQuery. Because development terms were set on the basis of “must be done yesterday”, then the DevExpress and ASP.NET controls were widely used. To transfer information between the client and server, JSON was used. Monitoring of the implementation of tasks was carried out using Redmine.

Oh, these files!

Files are widely used in the Bank to transfer information between systems. And most often these are Excel format files. I don’t know where this tradition comes from, but they need to be loaded, and there are a lot of files. They did the download as best they could. Someone used Oracle tools, I wrote utilities in C #, one of my colleagues used Microsoft Integration Service, another Informatica.

And now, like that, coming from vacation, I saw a long list of requirements, wishes that my colleagues wrote in my absence. It was a universal downloader from files to the database. We agreed that he will parse 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 into a staging table, for ordinary text files, write everything to another table, in principle - one line - one record in a column. The input interface should be a Web form. And the application itself must be single and run on a Web server. This made it difficult to develop, since users, on the one hand, should be able to download data in real time through a browser, on the other hand, the download should be launched offline, according to a schedule.

One morning, the boss calls me to his place and confronts the fact: “You have to do it in a week without stopping administering everything you wrote before.” In an attempt to increase the time frame for completing the task, the manager, with a look of a professional, listed what I have to do and how much time to spend on it. In general, we managed to agree on 3 weeks. But really, the work was completed after 3 months. This is probably a managerial tradition - to set short deadlines.

In order not to anger the management, the employees, in the time allotted for the development of time, hastily make a prototype that somehow works. They demonstrate it to the authorities, it ticks off “done” and runs to show and tell everyone what a miracle the thing was written by their subordinates and how it all works. Moreover, it is mentioned in passing 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, slowly, can finish it. There were cases when by the time the application was really ready, nobody needed it.

Three months later, the bootloader was ready. During its development, the 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 where are we loading from, why it does not load and how to deal with it”.

Do any of the developers like SharePoint?

... Then carefully
file with a file!
From the joke

Many development difficulties brought applications for SharePoint. By itself, the development, as it were, is fundamentally no different from the creation of ordinary Web sites. But all this takes a very long time to deploy to the server.

As a result, they decided: serious applications requiring a large amount of coding should be done separately, with links to SharePoint. They had a similar design, used 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 part of the information was stored in Active Directory, part in Oracle. A search engine was developed that worked with both systems simultaneously. It turned out to be so successful that many employees at the Bank began to use it. Especially after I screwed pictures of employees to him.

Looking for the magic button

No mastery without magic.
Valentin Borisov

If we were somehow able to satisfy ordinary employees of the Bank, the management wanted something more from us. They saw a Web form with a large traffic light and a button that says "Click me and everything will get better." They tried to make this traffic light 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 this, we made a tree, bypassing which graphs with tablets for each indicator were opened. The project provided for the ability to upload 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 forecasts on how it will be carried out and in case of non-fulfillment, to load explanations and changes in order to improve the situation. This was also a big plus. With this application, the positions of our department in the Bank have greatly strengthened. The management obliged employees of various departments to work with this program.
In parallel with her, on my own initiative, I developed a program that combined all the analytical cubes. In addition to pivot tables, she also displayed information in graphical form. The result of this work was indirectly useful. The application was used more for educational purposes, because it clearly showed the convenience 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, as on graphs constructed using analytical cubes, abnormal surges or dips in those days that contain erroneous data are visible. Finding and fixing bugs was the second part of the job.

Trust but check

Data could change retroactively, partially load, or double. Product credibility depended heavily on data quality. No wonder they say - “Quality inspires respect!” Over time, it became clear that it was necessary to develop a unified quality control system. As a result, we developed a Web application in which the selections used in the reports and checks were made for them. The system ran these checks daily and reported errors. I'm currently trying to connect a cubes check to it. Also, a check of calculated data was connected to it. As a result, product confidence has grown, although there is still work to do. No wonder they joke:

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

A place to step forward (Viktor Tsoi)

At the moment, the development of management reporting automation systems has practically stopped. Some of the developers, by their ears, got bogged down in administration, and the other was tied to various projects of related departments. A part of the forces was 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 you gain strength and manage to not only realize them, but also give them 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 this.