Data Warehouse for a Beauty & Cosmetics Business

The problem: when business data is scattered across multiple systems

The team built a Data Warehouse for a hypothetical cosmetics and beauty services business in the US. The business concept was self-built, with data generated by AI and then edited to fit realistic analytical scenarios. The problem: the business managed sales, services, customer, and marketing data across multiple disconnected systems, making it hard to consolidate and analyze across dimensions — leadership had no unified view to support decision-making.

The goal was to build a unified data warehouse supporting revenue analysis, marketing campaign effectiveness, branch performance, and customer behavior across multiple dimensions — following the standard Microsoft BI Stack: SSIS for ETL, SSAS for OLAP cubes, MDX for querying, and Power BI for visualization.

Role: researching the architecture and supporting the team’s implementation

Given the heavy workload across courses that semester, the team divided focus areas among members. On this project, the role centered mainly on:

• Researching Data Warehouse architecture and the Constellation Schema model
• Tracking the team’s implementation progress and helping finalize the project
• Learning the Microsoft BI Stack workflow (SSIS, SSAS, MDX, Power BI)

While not directly responsible for building the core pipeline, this was the first time going through a complete Data Warehouse process — from data model design, ETL, OLAP, through to final visualization.

The process: understanding the difference between a transactional database and an analytical warehouse

The team’s workflow followed these steps:

1. Define the business scenario and identify the analytical needs
2. Design the Data Warehouse following a Constellation Schema, with Facts and Dimensions supporting sales, service, and marketing analysis
3. Prepare and organize the source data
4. Run the ETL process using SQL Server Integration Services (SSIS)
5. Build the OLAP cube using SQL Server Analysis Services (SSAS)
6. Set up hierarchies for the dimensions
7. Write MDX queries to support multi-dimensional analysis
8. Build the dashboard in Power BI Desktop
9. Finalize the report and presentation

Results

• Completed the Data Warehouse system following a Constellation Schema
• Built three sets of MDX queries to support different analytical needs
• Deployed a visual dashboard in Power BI
• Gained an understanding of how to build a complete Business Intelligence system following the Microsoft BI Stack standard

The biggest takeaway

The project clarified how organizing data in a Data Warehouse differs from a transactional database (OLTP) — a warehouse is optimized for reading and multi-dimensional analysis, while a transactional database is optimized for writing and handling day-to-day business operations. The ETL process and SSIS’s role in extracting, transforming, and loading data became much clearer, along with the concept of Cubes and OLAP for multi-dimensional analysis, and the role of MDX in querying across those dimensions.

Limitations

The level of hands-on implementation in this project was limited since the team split the workload across multiple courses running concurrently. There wasn’t an opportunity to directly build the full ETL pipeline or develop the cube from scratch, and it wasn’t deployed on a Cloud environment or more modern data platforms.

If I did it again

• Directly build the entire Data Warehouse instead of just contributing research
• Design the ETL process with more automation
• Apply Slowly Changing Dimensions (SCD) appropriately for dimensions that change over time
• Add Incremental Load instead of reloading the full dataset on every ETL run
• Experiment with deploying on Microsoft Fabric or Azure Data Factory
• Build a dashboard with KPIs more directly tied to real decision-making needs