Improving Data Infrastructure with a data warehouse
Revolution Beauty is a leading cosmetics company that faced a challenge with its data infrastructure.
The core of the business reporting was being served directly from their SAAS platform
As a result it was clunky and slow to update. It required specialist skills to service the reporting requirements. Quite simply Revolution Beauty has out grown this approach
The existing data structures lacked the flexibility required to help to drive the business forward. The company was looking for a more targeted approach to gain insights and make data-driven decisions.
Revolution Beauty commissioned Select Distinct to help to build out their data warehouse.
Data Health Check and Discovery Phase
To start the project, Select Distinct conducted a Management Information Health check.
The existing infrastructure was based on data available in the Oracle NetSuite ERP system, with reports distributed through ad-hoc analysis, often in Microsoft Excel.
The health check reviewed the processes and technology involved, and provided recommendations and a roadmap for moving forward.
The main recommendation of the road map was to build a data warehouse. The data warehouse would hold internal and external data. With reports and visualizations built from the data that could be securely distributed to key users.
During the discovery phase, Select Distinct worked with Revolution Beauty to identify the initial key subject areas. And defined the short and long-term requirements.
Decisions were also made regarding the infrastructure. We recommended that the client continue to use the Microsoft stack. And host the data warehouse on an Azure SQL Server instance, with visualizations in Power BI.
One of the primary reasons for this choice of platform was the familiarity of the Microsoft environment for the existing team. And some prior knowledge and skills that would add more value
Data Warehouse and ETL Processes
The main data sources for the data warehouse were the internal NetSuite ERP data and external Point of Sale data.
Select Distinct developed ETL processes to extract and regularly update the data. This included writing scripts to clean and transform the data and designing an efficient data loading process.
The structure of the data in the warehouse was optimized to meet the business’s reporting and analytical requirements.
Visualizations in Power BI
A dashboard was built in Power BI for each subject area to meet the business stakeholders’ requirements.
The dashboards allowed users to quickly gain insights and take action. With self service data analysis options for the more capable users
The visualizations were published and automatically refreshed included allowing for analysis.
Further Optimization of the ETL Process
To further optimize the ETL process and improve the performance of the data warehouse.
Select Distinct moved away from replicating the NetSuite saved searches. And moved towards replicating the key back-end tables in the system.
This change removed the constraint of NetSuite running the queries. This gave improved performance. It also allowed Select Distinct to design the ideal structures and indexes to support the key areas.
This case study demonstrates how Select Distinct helped Revolution Beauty.
To improve its data infrastructure by building a data warehouse. and visualizations in Power BI.
The data health check and discovery phase provided a roadmap for moving forward. And the data warehouse and ETL processes ensured that the data was organized and regularly updated.
All to meet the business’s reporting and analytical requirements.
The visualizations in Power BI allowed users to quickly gain insights and take action. And the optimization of the ETL process improved the performance of the data warehouse. And at the same time reduced workloads on the transactional systems
Paul Reed – Director of Enterprise Architecture
“Having up to date, readily available data that we can
explore in Power BI has enabled us to see and react to business performance
much more readily. The flexibility afforded by the data warehouse means that
reporting workload can shift from our ERP system to the right tool for the job”