How to display 350 million rows from a database on a web form
The title of this article is a brief summary of what the customer requested. At that time I was on vacation, and my head, without going into details, gave the go-ahead for implementation. Having had a good rest and going to work, I felt myself in the shoes of a hedgehog, which fell into the fog in one famous cartoon. And the situation demanded the Wizard of the Emerald City.
So, everything is in order:
The customer was the department for dealing with problem debts. To successfully solve their problems, they needed
The customers certainly saw the solution in the form of a Web-form, this was explained by the fact that the department was heading, and its subdivisions were located in various cities of our vast country and all employees had to see the same picture, demarcated by access rights.
The task was made easier by the fact that the daily review of the loan portfolio was needed only for the last three months. Later information should have been on the first day of each month.
In addition to this condition, there was another problem: a loan was understood as all types of lending, including credit cards, which were stored separately and differed by measurement.
At the time of implementation, there was a repository on the Oracle database, in which all information on cards and credits was stored, in the form in which it came there from operational sources. Information was received with a delay of 1 day. On average, every day there were 3 million new entries, some of which were loans, part of the card.
To solve this problem, the following algorithm was chosen:
In addition to the implementation of the algorithm, it was necessary to minimize or eliminate all the risks and obstacles that could nullify the operation of this system.
')
The implementation itself was based on the knowledge and technology that I owned at that time and the company's software policy. Therefore, if suddenly it seems to you in the implementation that I was blowing a ball in the shape of a kitten, then please be indulgent, not everything depended on me.
The system was deployed on Windows Server 2008 64-bit, Web-form - on IIS, ASP.NET technology.
It is a little about technical subtleties and cunnings that were used in the development of this system.
In this article, I wanted to show how you can solve problems associated with large amounts of data using available and long-existing technologies. Before the introduction of OLAP, my leadership, in search of the “golden bullet”, considered a lot of boxed solutions, both from large manufacturers and from small companies. It often turned out for checking that decision support out of the boxes required a lot of effort and financial costs. In some cases, the boxed product simply could not do what they wanted from it because of its architecture. Perhaps the company where I work was looking for the wrong place and there is no such “golden” or at least “silver” bullets. But that was, that was. On the other hand, this article may stop someone from inventing “bicycles” in the form of building additional aggregated tables, some complicated architectures, and bulky sites.
But how many people have so many opinions and on this account I like the fictional story of the invention of a ballpoint pen:
NASA has spent millions of dollars on the invention of the pen, which you can write in zero gravity.
Russians simply wrote in pencil.
But where is the pencil now and where is the pen. Therefore, any solution, even very complex and cumbersome, can find its user, just like a simple one.
Thanks to everyone who could read everything I wrote about above!
So, everything is in order:
Staging
The customer was the department for dealing with problem debts. To successfully solve their problems, they needed
- Track the history of problem loans: watch when he went out on delay and how it extinguished. Also check whether the borrower, released for the delay, other loans.
- Receive information on the loan portfolio, monitor trends and portfolio changes for any arbitrary period.
- Opportunity to “fall through” on aggregated indicators, such as “Date of issue of a loan”, “Region of issue”, “Overdue loans”, etc. directly to the loan agreements themselves.
- Track the work of credit managers: check how successful they give loans.
The customers certainly saw the solution in the form of a Web-form, this was explained by the fact that the department was heading, and its subdivisions were located in various cities of our vast country and all employees had to see the same picture, demarcated by access rights.
The task was made easier by the fact that the daily review of the loan portfolio was needed only for the last three months. Later information should have been on the first day of each month.
In addition to this condition, there was another problem: a loan was understood as all types of lending, including credit cards, which were stored separately and differed by measurement.
Decision
At the time of implementation, there was a repository on the Oracle database, in which all information on cards and credits was stored, in the form in which it came there from operational sources. Information was received with a delay of 1 day. On average, every day there were 3 million new entries, some of which were loans, part of the card.
To solve this problem, the following algorithm was chosen:
- A data mart was built that combined loans and cards. The showcase was built according to the “Star” scheme.
- Every day, with the help of the task scheduler, “yesterday” was added to it.
- Ball created OLAP cube, for the calculation of aggregates.
- At the end of the storefront update, the OLAP cube update was launched.
- OLAP delivered data to a web form via the OLAP Viewer.
In addition to the implementation of the algorithm, it was necessary to minimize or eliminate all the risks and obstacles that could nullify the operation of this system.
')
Preparatory work:
- In the showcase for combining loans and cards, a common key was created that uniquely identified both the loan and the card. The key was stored in a table, each row of which uniquely identified either a credit or a card.
- A mechanism for tracking changes in the operational tables not only of yesterday's date, but also of all data for an arbitrary interval in the past, starting from yesterday, was added to the daily fill. This was done in case of any changes or errors in the database of “Yesterday”. The situation when the information on credits and cards appears in the database with some delay, took place, and it was necessary to fight with it. We checked the last 15 days from yesterday.
- The cube was created under the capabilities of the Web application. There were about 3 million credits and cards in total and it was either very difficult or impossible to display them in the browser at once. To solve this problem, in addition to the standard division of credits and cards by opening date, the region of binding, the issuing manager, etc. extra dimensions were invented. In the case described, grades of credits were added by the Amount, by the Amount of delay, by the number of days of delay, by the sign - was there at least once in the delay. Also, by the date of opening and closing of the loan, hierarchies of the year-quarter-month-day type were built. In the end result, all loans were combined into groups, the largest of which was no more than 10,000 lines. The customer was only interested in the delay, so for the loans that were repaid on time, no additional measurements were invented. Access to them was limited to the Viewer, and could be opened at any time. In the current version, the user saw from them only the information aggregated by standard measurements. But some of these loans could still be seen - these were the loans of the debtor, who has only one loan overdue and several taken.
- The cube update was supposed to start immediately after the storefront was updated. For this purpose, a service has been created that checked the status of the Oracle task scheduler. A utility was also written to it, with the help of which it was possible to urgently start / interrupt the update of the cube or monitor the current status.
- It was necessary to find an OLAP Viewer that could provide all the required functionality, normal look and flexibility of settings. This task was not easy: because many of them are not at all friendly to users, and working with them is not very convenient. Even with the one that was found, I had to suffer.
The implementation itself was based on the knowledge and technology that I owned at that time and the company's software policy. Therefore, if suddenly it seems to you in the implementation that I was blowing a ball in the shape of a kitten, then please be indulgent, not everything depended on me.
Implementation:
The system was deployed on Windows Server 2008 64-bit, Web-form - on IIS, ASP.NET technology.
- Data Showcase - Oracle Database
- Task Scheduler - Oracle Database
- OLAP Cube - Microsoft Analysis Services
- Storefront update tracking and update launch for a cube - Windows Service
- OLAP Viewer - DevExpres AspxPivotGrid
It is a little about technical subtleties and cunnings that were used in the development of this system.
Technical Tricks:
- 1. For correct operation of 32-bit and 64-bit Oracle providers for Windows:
- Download 32-bit and 64-bit Oracle drivers from the official site.
- Put in a separate folder Client32 Runtime.
- To restart a computer.
- Put in a separate folder Client64 Runtime.
- In the Windows registry, add the TNS_ADMIN value to the KEY_LOCAL_MACHINE \ SOFTWARE \ Wow6432Node \ ORACLE key and give it the path to the folder for Clinet32 - ... \ network \ admin
- To restart a computer
- When access control in Cuba is selected, Analysis Services on the Cubes tab should be set to Read, then select the measurement values ​​in the cube, select those that the role has access to and, switching to the Advanced tab, enable the Enable Visual Totals checkbox, it is disabled by default. If you do not include it, then the results of this dimension will display the total not only for those dimensions to which the role had access, but also those measurement values ​​to which there was no access.
- For large dimensions in an ASPxPivotGrid, you must disable the default sorting.
About important!
In this article, I wanted to show how you can solve problems associated with large amounts of data using available and long-existing technologies. Before the introduction of OLAP, my leadership, in search of the “golden bullet”, considered a lot of boxed solutions, both from large manufacturers and from small companies. It often turned out for checking that decision support out of the boxes required a lot of effort and financial costs. In some cases, the boxed product simply could not do what they wanted from it because of its architecture. Perhaps the company where I work was looking for the wrong place and there is no such “golden” or at least “silver” bullets. But that was, that was. On the other hand, this article may stop someone from inventing “bicycles” in the form of building additional aggregated tables, some complicated architectures, and bulky sites.
But how many people have so many opinions and on this account I like the fictional story of the invention of a ballpoint pen:
NASA has spent millions of dollars on the invention of the pen, which you can write in zero gravity.
Russians simply wrote in pencil.
But where is the pencil now and where is the pen. Therefore, any solution, even very complex and cumbersome, can find its user, just like a simple one.
Thanks to everyone who could read everything I wrote about above!
Source: https://habr.com/ru/post/234413/
All Articles