Pronos always focus on quickly accessible and easily scalable solutions for our clients. For over four years we work for the largest retailers in Poland and not only. Our goal is to provide them not only with the best possible solutions that will meet their requirements, but also allow them to grow with our business partners.
Therefore, we successfully implement our solutions in new companies, where we use an agile approach not only in project management but also in our software. Thanks to this, we find the best balance between the technical requirements needed for proper operation and the costs that the customer always pays attention to.
Since the beginning of our work, we rely on Microsoft cloud tools. That’s why we use a variety of solutions from Azure every day to develop our software. We use them, among other things, as a storage space, an environment to build microservices for our applications, space to run VMs, data analytics environments, and more.
As the availability and capabilities of Azure services grow, we’re not lagging behind. This is where projects like the one we implemented for the leader of the retail in Poland — optimization of Data Warehouses and Power BI reports come from.
Problem
The customer has been developing his data warehouse (DWH) for several years. The whole thing was previously embedded by us on Azure VMs, which provided us with stability, ease of scalability, and access to the latest versions of the software. As the business grew, the amount of data available for analysis increased. It was no longer just sales data, but also full information about a product, stock, inventory level, customers (and all about them), suppliers, etc.
This amount of data increased every day and new reports came along with it. The use of virtual machines in Microsoft cloud services made it easy to upgrade available resources. This, however, caused the machines to be loaded during heavy tasks (e.g. running recurring reports, feeding data, …) but almost unused for the rest of the day.
Besides, it turned out that many of the business assumptions created at the beginning of DWH are no longer valid. The business has developed, new sales channels (e-commerce) have emerged. This has resulted in the redundancy of data and measures in reports. This again caused a long waiting time for access to key information.
Request
After several internal and external audits of the entire BI ecosystem, which included a review not only of DWH (data warehouses) but also of reports, the customer decided to entrust Pronos with the optimization of the entire solution.
Our task included:
- reviewing and updating the data model in MS SQL database and Analysis Services
- optimizing the data loading process (ETL)
- acceleration of tabular models
- improving the functioning of Power BI reports
Also, the customer wanted to optimize costs.
Solution
Our choice was to introduce Azure Analysis Services (AAS), which was the transfer of tabular models to typically cloud-based services. Besides, make the required changes to the data warehouse itself and also in reports.
We began with reviewing the indicators required by the business, the data available in the DWH, and in the source systems. From the analysis, it turned out that many conversions should have been done earlier — at the data mart building level within SQL. This allowed us to stop importing raw data into the “cubes” themselves. Many KPIs have also been transferred from Power BI reports directly to AAS models.
The analysis services that were used in the “on-premise” version have choked on the amount of data and users. The transition to Azure allowed us to use tools to manage them. Now we can scale them “on the fly” or make the right amount of replicas for users. This allows the customer to decide when more resources should be made available to operators (recurring report generation, daily analytics). The client can also decide at what time the power is available for data load or refresh.
The result of this project is to expand structures with new resources, increase data availability for analysts and business users while keeping costs at a similar level.
End users noticed the biggest difference after implementing new Azure solutions. All reports they used before have accelerated significantly. In some cases, the time to access all data decreased by 70%. Analysis of average times showed us that we accelerated business by 30%.
However, this is not the end of our challenges. We have more changes ahead of us and a full migration to the cloud — Azure Synaptics, Data Lake, …. At the next opportunity, we will talk about these changes.
________________________________________________
Wojciech Piątkowski, Head of Project Management, Pronos
