INTRODUCTION Scalefree develops a new data warehouse based on CRM sales data that not only ensures quality, stability and auditability in sales reporting but also allows the management to utilize data history and the integration of multiple operational systems. CASE DESCRIPTION
The client is a Danish company that operates globally in the industrial field. They recent- ly shifted their data architecture towards the modern data stack. The goal of this process is to achieve the best possible usage of data and to optimize the business results. Core components are intended to be the application of Snow- flake’s Data Cloud and Data Vault 2.0. The first project was to improve the sales re- porting. Previously, the report data was loaded directly from the CRM system Dynamics 365 into Power BI. There, the data was transformed and modeled to be displayed in the dashboards. Though, this approach results in several issues. Since a reporting tool like Power BI is not the best fit to store and transform data, the quality and auditability of the reports was less than suf- ficient. Furthermore, not only was the complex model hardly maintainable, it was also not reus- able for other use cases at all. Besides, the client was especially interested in historical data and in the combination of data from different CRM/ ERP systems. To overcome those challenges, the data needed to be extracted from the source and loaded into
a more cost-efficient Snowflake database. Here, a stable and future-proofed data warehouse us- ing the Data Vault 2.0 methodology had to be developed. To apply the strengths of Data Vault, the automation tool needed to be implemented and adjusted according to the architecture as well. Closing the project, the Scalefree consultants were able to successfully migrate the sales data to the data warehouse on Snowflake. All the necessary loading processes have been im- plemented using Microsoft Azure and dbt. The Data Vault automation with dbt was optimized and adjusted to the client’s requirements. In the end, the quality and auditability of the sales reports have been improved heavily. The further requirements regarding historical data and the possibility to extend the data by more source systems was successfully implemented. Finally, the sales management team is able to analyze the client’s sales activities on the basis of high quality data.
Overview of the data architecture:
● MS Azure as a cloud and service provider ● MS Dynamics 365 as the CRM source system ● Snowflake as a cloud data warehouse
● Data Vault 2.0 as the data methodology ● dbt and dbtvault (modified) as the data engi- neering and automation tool
2/3
Moving Sales Reporting to the Cloud Data Warehouse for a Global Industrial Enterprise © Scalefree International GmbH 202 3
Powered by FlippingBook