• 🛠️ Requirements
• 🧙‍♂️ Data Source
• 🚀 Solution
• 🧱 Building Data Warehouse
• 📊 Result
• 📂 Files

The process of decision-making involves utilizing data and analytical tools to make informed decisions that align with business goals and objectives. This allows organizations to make decisions based on data-driven insights, leading to improved operational efficiency, increased profitability, and a competitive edge in the market. The aim of this project is to develop Business Intelligence for Sales modules that can provide managers with a deeper understanding of the department's features that enhance decision-making.

AdventureWorks Database is a sample online transaction processing (OLTP) database provided by Microsoft. The AdventureWorks Database supports a multinational manufacturing company called Adventure Works Cycles. This project will utilize the AdventureWorks Database version 2019 and concentrates on module Sales only.

Data Source: Sales Module

BI Solution

• Step 1: Load data from On-Premise and On-Cloud into SQL Server Integration Services (SSIS) tool via Ingest Phase
• Step 2: ETL data into the Staging Area with the schema name "Integration", and set up job scheduler to automatically run the package once every hour
• Step 3: ETL data into Data Warehouse with the schema name "DW", and run the package automatically once a day. At the same time, Truncate all data from the Staging Area every month.
• Step 4: divide 2 branches:

Using Power Automate to automatically refresh data and perform analysis report generation once a day.

Using SQL Server Analysis Services (SSAS) tool to create cube for analysis

Bus Matrix, Master Data, Transaction Data, ETL Mapping, etc. are deployed to support the data warehouse construction process.

The diagram below illustrates the fundamental conceptual diagram of the proposed data warehouse in Star format.

Data Warehouse Star Schema

ETL Master Pipeline

Based on the pipeline shown above, it is divided into 3 phases:

• Phase 1: Load data from Source --> Staging Area
• Phase 2: Load data from Staging --> Dimension Tables
• Phase 3: Load data from Staging --> Fact Table

Load data from Source --> Staging Area

Load data from Staging --> Dimension Tables

Load data from Staging --> Fact Table

Truncate Tables in Staging Area

Schedule jobs configure the step truncate

Data Warehouse Schema (SSAS)

The Flow of automated refresh a dataset

Sales Performance Dashboard

Product Analysis Dashboard

Delivery Performance Dashboard

Customer Segmentation Dashboard

• You must open the empty Power BI file first and then Ctrl + O to open the Dashboard_BoKho.pbix file to display the map chart.
• If you still can't open, please see the instructions at: View solution

© 2023 BoKho