OLAP systems

Currently, the constant generation of data means that companies have more and more information to process, clean and analyze.

One of the most important points is the speed and optimization of relational databases where all this information is stored.

In order to carry out business intelligence operations, it is necessary to optimize data recording and analysis to detect new opportunities and improve business-level metrics.

OLAP systems stand for online analytical processing and are designed to increase the speed of querying complex SQL tables. An OLAP system is designed for analysis so it is optimized for SELECT type queries as opposed to OLTP (online transaction processing) systems which are designed for data recording through SQL statements of the type: INSERT, UPDATE OR DELETE.

OLAP cubes are the basic unit of OLAP systems and are visualized as multidimensional cubes or hypercubes where each dimension belongs to a value of interest to the business.

For example, imagine a company that is dedicated to selling products online. In one dimension you could have the date in different hierarchies (years, months, days), in another dimension there would be the country of purchase and in the last one the type of product sold.

Each of the dimensions can have different hierarchies. The most detailed hierarchy level gives us the level of granularity of that dimension.

For some time, companies adopted the OLAP system through non-relational databases. Some time later, they realized that by applying certain relationship schemes between tables such as the star schema or the snowflake schema, together with indexing and grouping processes, OLAP systems could be built on databases. relational data.

The OLAP systems created in relational databases adopted the name ROLAP (Relational OLAP) and the others adopted the name MOLAP (Multidimensional OLAP).

ROLAP systems can have a longer response time than MOLAP systems. However, their great scalability has made them the preferred choice in most companies and businesses when designing the entire business intelligence architecture.

OLAP cubes are very interesting because they allow us to aggregate information so that it is quickly accessible, but they also allow us to perform different operations on them. Let's look at some examples:

Drill down : This operation consists of disaggregating the hierarchy level to view the information at a more detailed lower level. For example, going from years to months would be a drill-down operation.

Roll-up : The roll-up operation is the inverse of the drill-down. It consists of aggregating the data at a higher, broader hierarchy level. An example would be adding months to years.

Slicing : The slicing function is used to select a subset of specific cells from the multidimensional cube and create a cube with fewer dimensions since we eliminate the one that is not of interest.

Saying : telling consists of rotating the hypercube and selecting a subcube of the general cube according to the information that is of interest to us.