Problem solving experience by creating an OLAP cube using C #

Prehistory

I would like to share my little experience that I gained at work in one public institution. How I got there is not important, but it is important to know, because This imposes its own specifics on the conditions in which the task was to be solved. It should also be noted that my main knowledge and poor programming experience are related to .Net technologies.

Description of the task: there is to this day an information system created 10 years ago that collects reporting information on different, periodically changing statistical indicators from different branches of the organization in the region. The infrastructure of information interaction of the system is shown in the figure below. Indicators in DW are described implicitly.



A short explanation of the picture:
')
0. DW. Data storage on Oracle with very not simple model.
1. SI. Statistical information - indicators of accounting, financial, etc. reporting.
2. CERI. Complexes of electronic statistical data processing, created locally.
3. IM. Interface array - a model describing which lists of SIs are in DW.
4. SP IM SI. The system for preparing interface statistical information arrays is intended for collecting data from the E-CIO, converting it into an AM format and transmitting this array for loading into DW.

Task Description

Problem: The end user using Java software, through direct requests to the DW receives information. No source code of course not. The storage model cannot be changed. In the database for 10 years, there was a lot of data, I worked with a 25 GB backup, but software requests were disgustingly long.

Task: It is necessary to make it so that you can view the necessary information but not waiting for an hour, so what would be convenient, and "if possible, in Excel"

Decision

The quick-witted reader remarked from the figure that the decision was to use OLAP cubes as data stalls, but the path to this solution was not very obvious ...

After a period of studying the problem in the subject of information systems, I came across the concept of "Analytical Pyramid" and its associated OLAP .



I came to the following conclusion:

Since the user needed data that successfully fit into the structure of the standard star model cube, the cube had to contain aggregated information distributed over time, territory and the accountable organization, depending on the branch of the government corporation, it was in itself to somehow automate the creation process cube, for individual branches, organizations and the necessary lists of indicators, which will reduce the amount of unnecessary data.

My .Net profile meant using Microsoft Analysis Services as an OLAP server, which, as seen in the model comparison , supports the Local OLAP cube model, which works offline without a server and can be easily viewed in Excel if you specify them as a source of multidimensional data.
To automate the process of creating cubes, we need to do it programmatically. In .Net there is an AMO object model for OLAP .

The initial example of software creation of a server OLAP-cube was this article .

In my case, I specified certain table names from my DW for a specific cube. Although, I agree that the logic turned out a bit perverted. It should be noted that for software work with data stored in a cube I used ADO MD. This is well described in the series of these Habrovsk articles.
Here it is very clearly shown how the entities of the OLAP server core, such as AMO and ADO MD, relate.

So, after we created a cube on the server and successfully displayed its contents in the console window, how to make such a coveted opening of it in Excel?
- You can connect to the standard server version via Menu \ Data \ From other sources;
- You can immediately generate a local cube, i.e. The report file with the .cub extension is well described in this article . Actually, an interesting feature is that the server first turns the cube data into XML, which opens up another space for creativity.

Conclusion

The bureaucratic red tape with the reports of one official became a little easier, since by creating a cube once, it was convenient to work with the data until the beginning of the next reporting period. Although from my point of view, this is a very budget solution to the problem.

In addition, there are still interesting opportunities for software work with cubes, I myself do not know about all, but I will be glad to learn something new, I suggest sharing in the comments, for example: use LINQ or conduct unit testing in C # using ADO MD and MDX, as described in this article .