Data Integration: The key to the success of any BI project
When we make the decision to carry out a BI project, on many occasions we begin to think about what indicators we are going to analyze, that is, what we want to obtain.
It is not a bad start, it is surely what we want and need, but soon 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 point of view of presenting KPIs. These systems show us how to upload some data in Excel and immediately get graphics and other elements on the screen.
The truth is that it is impressive and at first glance it seems simple. However, all professionals who have been involved in projects of this type know that the presentation of the KPIs is only the final packaging of the project.
Below we are going to see the components, in a summarized way, that we must take into account if we want to carry out a scalable and sustained BI project over time.
In this article we want to describe, in an agile way, the components related to Data Integration; analyzing its level of importance.
The first thing we have to do before starting the Data Integration process is to collect all the data sources that are going to be necessary for the calculation of indicators and information that we finally want to show on the dashboards.
It is important that the data sources have the greatest possible granularity, since if we start from data sources with information already aggregated 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 responsible for extracting information from data sources, transforming it through rules and also cleaning and loading it 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, cleaning and harmonization of these.
Detailing what makes up Data Warehouse would be very extensive, so we are going to describe this component in a simple way without going into many of its features and functionalities.
As its name indicates, this data warehouse is the place where the data is stored in an organized way in business data structures, grouping them in smaller data sets (datamarts) in an aggregated way and generally in multidimensional data structures. and with hierarchies, this is what we know as OLAP cubes; they were invented to give analysts better data query performance versus the OLTP normalized transactional model.
Hence, it is a widely used model both for data consultation by dashboard generation tools and for the presentation used for business decision making.
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 take 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 constantly changing and with constant reorganizations.
If we do not have the ability to quickly update this master data, we will be continuously showing analyzes that do not reflect reality, and therefore, our BI system will not serve the ultimate purpose for which it was built, which was none other than to help decision making. .
As we have seen during the description of the layers most focused on the integration and data processing of a BI system, the work of Data Integration is perhaps the most tedious and long part of a BI project. Both at the beginning and implementation of the system and throughout its life.
We must always keep in mind that a BI system is not static and is constantly changing, with the aim of reflecting the company’s business strategies.
For all the explanation up to this point, we can conclude 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 we serve as a decision-making tool, reflecting the desirable and correct indicators.