Two brands, in two different environments
The more than 2,000 points of sale that bring together the Inkafarma and Mifarma brands daily generate relevant information about sales and, in general, consumption patterns of their customers. Thus, they collect information by region on a daily basis in different databases that were ultimately concentrated on two different servers. The problem for the client was that, although it is true that the data was collected with moderate ease, it was difficult to analyze since these large volumes were barely able to be concentrated (with hours of lag) and it took more than a day to generate insights from of your information.
The fact that the data generated by both chains is hosted on two different servers is explained by the fact that Mifarma had recently been acquired by Intercorp, a Peruvian business holding company that owns Inkafarma. Consequently, the integration of data from both Inkafarma and Mifarma was presented as a challenge. In that sense, Rolando Castro, IT Director of Farmacias Peruanas, says: “There is a vision for InRetail to migrate our information to the cloud. In the case of Farmacias Peruanas the complexity was greater because it was not just one system, but two different systems in the process of integration. We could not coexist with two different information systems. There was the problem of slow loading, which was hindering our objective of democratizing data.”
The data sources for both brands, Inkafarma and Mifarma, were the sales of pharmacies located nationwide. In the case of Mifarma, the data was concentrated in an Oracle database hosted in-house in the company's offices; Inkafarma also did it in an Oracle database located in an IBM data center. In that sense, the need for Farmacias Peruanas was to analyze said data as soon as possible before the 'next day' in order to be able, based on the analysis of sales, to make management decisions the 'previous day'.
Before the client arrived at Xertica, the process in question was slow and the information took around nine hours to process. With the integration of the operations of both brands, the risk of data processing becoming even slower grew.
Starting the migration
The information that was extracted from both servers was essentially sales. The migration included between the two brands around 200 tables, some master and others transactional from the sale of the day. Information was uploaded since 2015, in order to have a solid history.
The insights obtained from these servers totaled daily sales, generated year-to-year comparisons, among others. All that logic was in Oracle PL/SQL code. In order to take advantage of the facilities of the cloud, Xertica used Bigquery from Google Cloud Platform (GCP). Consequently, the PL/SQL code was translated into BigQuery SQL.
The extraction is done directly from the data sources through a VPN (Virtual Private Network) connection. Once the process of extracting the daily information is finished, the ELT (Extract, Load and Transform) process begins, with the use of the Matillion tool in the extraction and loading of data. Once the data is completely loaded, the transformation process begins and the final tables that Farmacias Peruanas requires to obtain insights are reached, which serve to generate impact strategies on the consumer.
Less processing time, more insights
When the implementation was completed, the final result was that all these processes of both brands, which in addition to the sales calculations, also added new data on stocks and sales generated through the online channel, were executed in a maximum of 2 hours and 30 minutes, the process began to run every day from 3 am and ended at 5:30 am, so at dawn they already had the insights from the previous day in time-to-market.
The differential component of the project led by what BigQuery can offer in terms of response time and volumes of processed information. Currently, all these volumes of millions of records are processed in short periods of time.
BigQuery is a serverless, highly scalable and economically profitable product that allows us to have a Data Warehouse with ML functionalities and an integrated BI engine. It is one of the star products of Google Cloud Platform, which has more and more followers around the world.
Finally, forming a committed work team dedicated to the project was essential. In this regard, Lisbeth Atachagua, Data Leader of Farmacias Peruanas, emphasizes: “On the side of Xertica they were very accessible in assigning human resources at the complete disposal of Farmacias Peruanas to resolve on-site the pain points that initially delayed the work schedule. From that moment on, the plan began to run as stipulated and with the results expected by the business.”