Applying Business Intelligence Technology And Automation Processes To Make More Accurate Decisions For Sales Operations
student_id | class | full_name | role |
---|---|---|---|
K204061440 | K20406T | Tran Nhat Nguyen | Leader |
K204061446 | K20406C | Man Dac Sang | Member |
K204061411 | K20406T | Nguyen Thai Ngoc Suong | Member |
K204060310 | K20406C | Thai Thien Truc | Member |
- ๐ ๏ธ 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
> 1. Report (both Word and pdf file) - Link
> 2. Slide - Link
> 3. SSIS and SSAS package - Link
> 4. SQL file query - Link
> 5. Power BI file (include 4 Dashboard) - Link
- 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