Services

Standard information systems are usually designed to handle “transactions”, i.e., they register individual events of a certain process. For instance, a bank registers each deposit and draft, among others, for individual accounts. For this reason, searching through each of these transactions is particularly quick since current database engines allow optimized searches based on several parameters and ancillary structures, such as indexes.

But what happens if we want to find out the accumulated outcomes in a dimension and compare them with others? Let’s assume we want to compare all the deposits made in the bank during November, grouped by region, and then with a national total, with those of last year. In the database engine queries this would mean selecting all the registers corresponding to the chosen periods, grouped by region and date, to then add the field that stores the deposit in each register for every category.

While the database engine can be particularly efficient and the hardware it runs on very fast, for a particularly large amount of data this query can still fail to meet the speed requirements of an analyst or decision-maker in an interactive experience. Not to mention the fact that each query is working on the organization’s production systems.

To solve this issue we can begin by considering that the data we want to analyze, like in the example, are often historical data; this means that since they belong to past transactions, they will remain unchanged. Hence, we can copy such data from the production relational database to an optimized database specifically designed for the analysis (unstandardized). This special database is often known as a Data Warehouse or DataMart, depending on whether it stores all the organization’s data or only part of its process.

On this DataMart we build an additional structure which is finally queried for the analysis, known as an OLAP (Online Analytical Processing) cube. The name “cube” derives from the multi-dimensional structure that shapes the context under analysis, which can often be time, geographical range, products, etc. Each dimension can have multiple levels, like in the case of the geographical range, which can encompass country-region-commune. This cube stores historical data with accumulated calculations for the aggregations according to the type of analysis involved. Therefore, not all registers need to be reviewed and added when making the query since the addition has already been made. Every dimension crossing can have several optimally calculated members, as described, or with more complex formulas that take into account their position in the cube, the dimensions involved and others.

Queries over the OLAP cube can use the same interface on which it was built or be integrated with Excel to make them look like dynamic tables. OLAP engine APIs can also be used to build web or desk applications that generate queries, occasionally in a simpler manner by establishing a clear and graphic means of dragging dimensions and alternatively reducing freedom of use based on the user.

At Miró, we have vast experience in OLAP cube building and maintenance for the financial industry and supermarkets, among others. The challenges we have faced usually involve handling large amounts of data (such as in the supermarket industry where all transactions are stored) and the construction of OLAP cubes derived from others. For instance in the financial area, the exchange rate is built in an ancillary cube, which is then matched with the cube with data in local currency to create a third cube with data in US dollars. All these cubes are built using Microsoft Analysis Services, part of the SQL Server Suite, in multiple versions.