CRM-DWH

CRM - Datawarehouse

On the CRM DWH project, I had the task of building the ETL process from the staging layer to the core layer. The MS SERVER database was chosen for the backend, since it was the products from Microsoft that were supposed to cover the entire project from etl design, implementation via SSIS and SSAS to display in Power BI or Excel and at the end of the day in CRM. At the beginning, I unified the data sources that were scattered in several csv or excel files and on other MS server databases. Subsequently, I created tables in the Staging layer and data marts on the DWH layer, which contained dimensional and fact tables into which the data was to be poured and which were the display layer for BI. The transformation process itself was covered in SSIS and then the data was modeled in SSAS. The modeling of transformation mechanisms often contained complex T-SQL commands, which were supposed to capture the internal logic of the management settings in the company so that the data could then be traced back, or changed in the input tables, and thus show the best possible picture for the users, who were only and exclusively the company's employees. Datamart's contained a hierarchical division of workers, their hours worked and wages, the projects they work on and their knowledge.

Benefit for the company

The project lasted about 1 year and at the end of the day it greatly simplified the work for the finance and project department, which received an online application that responded to the current demand for efficient and simple setup of the company's processes.