When we make the decision to carry out a BI project, on many occasions we start to think about what indicators we are going to analyze, this means; what we want to obtain
It is not a bad start, it is surely what we want and need, but immediately we will have doubts about how to obtain and relate all the information necessary to achieve these indicators. This is the key point for us to really have a serious and scalable BI system.
They continually appear and offer us impressive Business Intelligence and Analytics solutions from the KPIs presentation point of view. These systems show us how to upload some data in Excel and immediately obtain graphics and other elements on the screen.
The truth is that it is shocking and at first glance it seems simple. However, all professionals who have been involved in this kind of projects know that the presentation of KPIs is only the final wrapping of the project.
Next, we will see the components, in a summarized way, that we must take into account if we want to carry out a scalable BI project that is maintained over time.
In this article we want to describe, in an agile way, the components related to data integration; analyzing its level of importance. Let’s get started:
The first thing we have to do is collect all the data sources that are going to be necessary for the calculation of indicators and information that we want to finally show in the dashboards.
It is important that the data sources have the greatest possible granularity, as if we start from data sources with information already added or calculated, it will limit us when presenting the information, because we will not be able to obtain certain details or levels.
ETL (Extract, Transform and Load)
This component is the first critical layer for our BI solution, it is in charge of extracting the information from the data sources, transforming them through rules and also cleaning them and loading them to the next layer: the Data Warehouse.
Depending on the data source, these operations will be more or less complex, although the most critical part of this layer is usually the transformation of the data based on rules, the cleaning and harmonization of these.
Detailing what makes up the Data Warehouse would be very extensive, so we are going to describe this component in a simple way without going in detail into many of its characteristics and functionalities.
As its name suggests, this data warehouse is the place where data is stored in an organized way in business data structures, grouping them in smaller data sets (datamarts). In an aggregate way and generally in multidimensional data structures and with hierarchies. This is what we know as OLAP cubes; that were invented to offer analysts better performance in data queries compared to the normalized transactional OLTP model.
Therefore, it is a widely used model both for querying data by dashboard generation tools and so for the presentation that is used for making business decisions.
On many occasions, Data Warehouse systems also include the ETL layer for data extraction, transformation and loading.
Master Data Management
In a simple way, MDM is understood as the tools and methodology dedicated to managing and storing master data. But what is this master data? Actually, they are the entity tables, the basis of a data model, for example, the product master data, which would include the code, the description of the product and its properties
Let’s put an example, in a BI system for commercial effectiveness of the pharmaceutical industry we need to manage the data of the territorial structure of the sales network, which as everyone knows, this territorial structure is in constant change and with constant reorganizations.
If we do not have the ability to quickly update this master data, we will continually show analyzes that do not reflect reality, and therefore, our BI system will not serve the ultimate purpose that it was built for, which was none other than to help make decisions.
As we have seen during the description of the layers more focused on the integration and data treatment of a BI system, the data integration work is perhaps the most tedious and longest part of a BI project. Both at the beginning and implementation of the system and throughout its life.
We must always bear in mind that a BI system is not static and is constantly changing, in order to reflect the company’s business strategies.
Through all the explanation up to this point, we can come to the conclusion that the integration of data in a well-structured way is the key for our BI system to grow, be scalable and constantly adapt to business strategies and to be served as a decision-making tool, reflecting the desirable and correct indicators.