OLAP Cube

Intro

We got contacted by a company from the US. The main aspect of their business was secure and sustainable reuse and recycling of electronic materials (E-Waste). The company had a multi-million turnover as well as other subsidiary business endeavours in various countries. The owner had an inclination to sell the company and enjoy an unstressed retirement. The problem we were going to face off against was their customers demanded total transparency in terms of financial reporting. Customers wanted to see the profitability and marginality of certain activities the company takes before buying. The reporting system built up on Microsoft Dynamics did not allow for such a possibility.

 

Challenge

To be precise, the system was not able to carry out the orders in this much detail and was no near sufficient. This role was taken by a couple of accountants who would on a regular basis be spending loads of hours to generate the report. The guys were excel gurus and used it up to produce a mere-acceptable result. Usually, a report would take a day away from their regular duties. However, in case of any change being requested, they would’ve needed at least another day to adjust the formulae. Changes are ever-present and are likely to occur more than once a month.

Requirements

  • Create an interactive browser for the OLAP cube
  • Create a universal engine which accesses and uses cube’s data
  • Make report generation time convenient for users
  • Make the size of generated data acceptable

 

Solution

An excellent solution to this problem would be implementation of OLAP cubes. Step 1 was defining together with our client which dimensions we have and which we need for the reports. Examples of these dimensions would be – time, business accounts, branches, product types, etc. Basically, everything you would want to see in the report.

“An OLAP cube is a multidimensional database that is optimized for data warehouse and online analytical processing (OLAP) applications. An OLAP cube is a method of storing data in a multidimensional form, generally for reporting purposes. In OLAP cubes, data (measures) is categorized by dimensions.”
olap 

Next up we needed to create a database structure, as well as stored procedures which would be calculating and putting together all of the data for the reports. The data itself was imported via Navision. Some data was absent but required. Other people in the client’s company pursued the goal of developing a business flow for this data to pile up, but for us the work with the database implied we had to come up with lots of optimisation techniques. Because the brute forcing approach would inevitably fail as it would’ve needed to pull data of the size of around 300 million records, which will definitely use up server resources to the ground. By conducting a number of tests to measure what would be the ideal load on the server we came up with an optimal speed and load on the server that satisfied our client.

 

Second issue we had was to create website and a universal engine that would allow everyone in the company to generate the report they needed interactively and export it in a desirable format to then send it across to their directors or anybody else. What was important here was ensuring it is more than comfortable for customers to use and hiding universal engine’s capabilities that did not have anything to do with anything but what people needed in the context of goals the company set. With the help of the software we developed, the user is able to travel between dimensions seamlessly and quickly sort what exactly they wanted the report to reflect.
 

Conclusion

After several months of development, it became clear that the aforementioned engine that does not require any adjustments is a pretty tall order. Nevertheless, we solved the problem and every requirement was met.
What really did speed up the process was our developers visiting client’s main office to conduct brainstorm sessions with specialists from the client’s side. It took us two weeks to understand what the goal for the client was and what limitations we would be put up against. I consider this result a resounding success. If we had gone with a standard approach of Waterfall, for instance, and asked the client to come up with requirements and put those for us together, we would have ended as far from success as one can only be.