This project is a Business Intelligence Project that creates dashboard to give monitorization to Marketing and Sales Department upon product sales, region performance, and target achievement periodically. By finishing this project, we hope we can replace project consolidation process that done manually along the time and provide a dashboard that accessible by all of the region manager to monitor and evaluate performance of their region.
Objective of creation of this dashboard is to give end-to-end visualization to Marketing and Sales Department upon product sales in all region and also to give monitorization privilege about sales in certain period.
MongoDB and PostgreSQL are activated with this docker-compose file.
Data Source for this project is located on company's data lake. Company utilize MongoDB, a NoSQL Database, as Data Lake. It can be accessed in company's server (a Virtual Machine), exposed at port 27017. There is 3 databases from the data lake that used in this project, which are db_customers (contains identity of customers), db_products (contains detail of products), and db_orders (contains record of orders).
To maintain data quality, data source will be copied to data staging. From this data staging, we will do transformation and stored it in Data Warehouse. After that, we will also do further transformation and split data into Data Marts. We will utilize PostgreSQL, a Relational Database Management System (RDBMS), as Data Staging, Data Warehouse, and Data Mart. Data Staging and Data Warehouse will be located in a same PostgreSQL database named as superstore_dwh (but they will be put on different schema), while Data Mart will be located in separate PostgreSQL database named as superstore_dmart. PostgreSQL can be accessed in the same server as MongoDB. Superstore_dwh is exposed at port 5432, while superstore_dmart is exposed at port 5433.
No attachment or link need to be embedded.
Server used in this project is utilized Virtual Machine instance provided by Google Compute Engine, with specifications as follow:
- Image of OS: Debian GNU/Linux 11
- Machine Type: E2
- Memory: 4GB
- CPU: 2 Core
- Disk: 10 GB
- Software: MongoDB and PostgreSQL
Data from database used as data source for this project. Grant access as viewer to Business Intelligence Engineers is enough to fulfill principle of least privilege.
Data Transformation in this project done with Pentaho Data Integration software. We can define all pipeline from extract data from data lake until load data to data marts in a single execution. First, we need to define connection with MongoDB and PostgreSQL. Then, we create step for each process in a Pentaho transformation diagram. Last, we schedule a job to run this transformation pipeline every certain time. We can use Pentaho Server if we want to run Pentaho job from the app UI or we can utilized common scheduler, such as cron or PM2.
| Name | Step Title | Detail | Notes |
|---|---|---|---|
| Extract_Orders | MongoDB input | Extract orders data from MongoDB | Connect to db_orders in superstore_db |
| Staging_Orders | Table output | Load orders data to PostgreSQL | Connect to staging.orders in superstore_dwh |
| Sort_cust_id_from_orders | Sort rows | Sort orders table based on customer_id | Preparation to join with customers table |
| Extract_Customers | MongoDB input | Extract customers data from MongoDB | Connect to db_customers in superstore_db |
| Staging_Customers | Table output | Load orders data to PostgreSQL | Connect to staging.customers in superstore_dwh |
| Sort_cust_id_from_customers | Sort rows | Sort customers table based on customer_id | Preparation to join with orders table |
| First_Join | Merge join | Inner join orders table with customers table based on customer_id | Redundant columns deleted |
| Sort_prod_id_from_first_join | Sort rows | Sort table from join process based on product_id | Preparation to join with products table |
| Extract_Products | MongoDB input | Extract products data from MongoDB | Connect to db_products in superstore_db |
| Staging_Products | Table output | Load products data to PostgreSQL | Connect to staging.products in superstore_dwh |
| Sort_prod_id_from_products | Sort rows | Sort products table based on product_id | Preparation to join with table from previous join process |
| Last_Join | Merge join | Inner join the table from previous join process with products table | Redundant columns deleted |
| Sort_Rows | Sort rows | Sort result table based on order_date, row_id, order_id, customer_id, and product_id | So fact table looks good visually |
| Load_Fact | Table output | Load fact table to PostgreSQL | Connect to production.fact_table in superstore_dwh |
| Extract_for_daily_report | Table input | Extract data for daily report from fact_table with SQL query | SELECT order_date AS record_date, COUNT(sales) AS total_sales, SUM(sales - discount * sales) AS net_sales FROM superstore_dwh.production.fact_table GROUP BY record_date ORDER BY record_date; |
| Load_daily_report | Table output | Load daily report data to PostgreSQL | Connect to mart.daily_report in superstore_dmart |
| Extract_for_monthly_report | Table input | Extract data for monthly report from fact_table with SQL query | SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, CAST(EXTRACT(MONTH FROM order_date) AS integer) AS record_month, COUNT(sales) AS total_sales, SUM(sales - discount * sales) AS net_sales FROM superstore_dwh.production.fact_table GROUP BY record_year, record_month ORDER BY record_year, record_month; |
| Load_monthly_report | Table output | Load montly report data to PostgreSQL | Connect to mart.monthly_report in superstore_dmart |
| Extract_for_quarterly_report | Table input | Extract data for quarterly report from fact_table with SQL query | SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, CAST(EXTRACT(QUARTER FROM order_date) AS integer) AS record_quarter, COUNT(sales) AS total_sales, SUM(sales - discount * sales) AS net_sales FROM superstore_dwh.production.fact_table GROUP BY record_year, record_quarter ORDER BY record_year, record_quarter; |
| Load_quarterly_report | Table output | Load quarterly report data to PostgreSQL | Connect to mart.quarterly_report in superstore_dmart |
| Extract_for_yearly_report | Table input | Extract data for yearly report from fact_table with SQL query | SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, COUNT(sales) AS total_sales, SUM(sales - discount * sales) AS net_sales FROM superstore_dwh.production.fact_table GROUP BY record_year ORDER BY record_year; |
| Load_yearly_report | Table output | Load yearly report data to PostgreSQL | Connect to mart.yearly_report in superstore_dmart |
| Extract_for_actual_vs_budget | Table input | Extract data for actual and budget comparison | SELECT SUM(actual_value) AS total_actual_value, SUM(budget_value) AS total_budget_value, ((SUM(actual_value)/SUM(budget_value)) * 100) AS percentage FROM ( SELECT record_year, net_sales AS actual_value, (LAG(net_sales) OVER (ORDER BY record_year) * 1.1) AS budget_value FROM ( SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, COUNT(sales) AS total_sales, SUM(sales - discount * sales) AS net_sales FROM superstore_dwh.production.fact_table GROUP BY record_year ORDER BY record_year ) AS subsubquery ) AS subquery; |
| Load_actual_vs_budget | Table output | Load actual and budget comparison report to PostgreSQL | Connect to mart.actual_vs_budget in superstore_dmart |
| Extract_for_monthly_growth | Table input | Extract data for monthly growth from fact_table with SQL query | SELECT record_year, record_month, gross_sales, ((gross_sales - LAG(gross_sales) OVER (ORDER BY record_year, record_month))/LAG(gross_sales) OVER (ORDER BY record_year, record_month)) * 100 AS growth_percentage FROM ( SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, CAST(EXTRACT(MONTH FROM order_date) AS integer) AS record_month, SUM(sales) AS gross_sales FROM superstore_dwh.production.fact_table GROUP BY record_year, record_month ) AS subquery ORDER BY record_year, record_month; |
| Load_monthly_growth | Table output | Load monthly growth data to PostgreSQL | Connect to mart.monthly_growth in superstore_dmart |
| Extract_for_quarterly_growth | Table input | Extract data for quarterly growth from fact_table with SQL query | SELECT record_year, record_quarter, gross_sales, ((gross_sales - LAG(gross_sales) OVER (ORDER BY record_year, record_quarter))/LAG(gross_sales) OVER (ORDER BY record_year, record_quarter)) * 100 AS growth_percentage FROM ( SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, CAST(EXTRACT(QUARTER FROM order_date) AS integer) AS record_quarter, SUM(sales) AS gross_sales FROM superstore_dwh.production.fact_table GROUP BY record_year, record_quarter ) AS subquery ORDER BY record_year, record_quarter; |
| Load_quarterly_growth | Table output | Load quarterly growth data to PostgreSQL | Connect to mart.quarterly_growth in superstore_dmart |
| Extract_for_yearly_growth | Table input | Extract data for yearly growth from fact_table with SQL query | SELECT record_year, gross_sales, ((gross_sales - LAG(gross_sales) OVER (ORDER BY record_year))/LAG(gross_sales) OVER (ORDER BY record_year)) * 100 AS growth_percentage FROM ( SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, SUM(sales) AS gross_sales FROM superstore_dwh.production.fact_table GROUP BY record_year ) AS subquery ORDER BY record_year; |
| Load_yearly_growth | Table output | Load yearly growth data to PostgreSQL | Connect to mart.yearly_growth in superstore_dmart |
| Extract_for_loss_report | Table input | Extract data for loss report from fact_table with SQL query | SELECT order_date AS record_date, city, region, product_id, product_category, product_subcategory, product_name, SUM(profit) AS loss FROM superstore_dwh.production.fact_table WHERE profit < 0 GROUP BY record_date, city, region, product_id, product_category, product_subcategory, product_name ORDER BY record_date; |
| Load_loss_report | Table output | Load loss report data to PostgreSQL | Connect to mart.loss_report in superstore_dmart |
| Extract_for_cust_segmentation | Table input | Extract data for customer segmentation from fact_table with SQL query | SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, CAST(EXTRACT(QUARTER FROM order_date) AS integer) AS record_quarter, customer_id, customer_name, customer_segment, (CASE WHEN SUM(sales) < 200 THEN 'Bronze' WHEN (SUM(sales) > 200) AND (SUM(sales) < 500) THEN 'Silver' ELSE 'Gold' END) AS shopping_segment FROM superstore_dwh.production.fact_table GROUP BY record_year, record_quarter, customer_id, customer_name, customer_segment ORDER BY record_year, record_quarter; |
| Load_cust_segmentation | Table output | Load customer segmentation data to PostgreSQL | Connect to mart.customer_segmentation in superstore_dmart |
| Extract_for_prod_segmentation | Table input | Extract data for product segmentation from fact_table with SQL query | SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, CAST(EXTRACT(MONTH FROM order_date) AS integer) AS record_month, city, region, product_id, product_category, product_subcategory, product_name, (CASE WHEN SUM(quantity) < 5 THEN '3rd Product' WHEN (SUM(quantity) > 5) AND (SUM(quantity) < 10) THEN '2nd Product' ELSE '1st Product' END) AS product_segment FROM superstore_dwh.production.fact_table GROUP BY record_year, record_month, city, region, product_id, product_category, product_subcategory, product_name ORDER BY record_year, record_month; |
| Load_prod_segmentation | Table output | Load product segmentation data to PostgreSQL | Connect to mart.product_segmentation in superstore_dmart |
| Extract_for_reg_segmentation | Table input | Extract data for region segmentation from fact_table with SQL query | SELECT CAST(EXTRACT(YEAR FROM order_date) AS integer) AS record_year, CAST(EXTRACT(MONTH FROM order_date) AS integer) AS record_month, city, region, (CASE WHEN SUM(sales) < 1000 THEN 'Kategori I' WHEN (SUM(sales) > 1000) AND (SUM(sales) < 2000) THEN 'Kategori II' ELSE 'Kategori III' END) AS region_segment FROM superstore_dwh.production.fact_table GROUP BY record_year, record_month, city, region ORDER BY record_year, record_month; |
| Load_reg_segmentation | Table output | Load region segmentation data to PostgreSQL | Connect to mart.region_segmentation in superstore_dmart |
Data Warehouse in this project is modelled in Star Schema. Star Schema is a modelling for data warehouse which consists of a fact table built from two dimension tables or more. This schema often used for analysis that specific on a subject only.
There are few roles that should be given access to the visualization:
- Head of Marketing and Sales Department -> access to all city and region
- All of Branch Manager -> access to specific city only
- All of Region Manager -> access to specific region only
People that involved in building this project:
- Head of Marketing and Sales Department
- Business Intelligence Engineer
- Business Intelligence Analyst
- Pentaho job to process data from Data Lake to Data Marts will start to be executed at 8 AM GMT+7 in each day.
- Customers' identity are all valid and complete.
- Products' detail are complete, but there are 32 invalid data. These data said to be invalid because they have same product_id with 32 other items (a product_id refers to two different products). After further confirmation to Head of Marketing and Sales Department, for each product_id, the valid one is the newer product. So, we can delete old product for each product_id. This elimination stage is not included in Pentaho job.
Anomaly on data has being communicated with user, which is Head of Marketing and Sales Department).
All data marts are visualized in 4 different dashboards (based on timeframe) using Tableau Desktop. It can be shared to other stakeholders if we deployed the dashboard to Tableau Server. File for workbook and dashboard layout is here.
On November 24th, 2014, there are 19 sales that produce net sales equal to 4206.97 US dollars.
On November 24th, 2014, there is a region in Columbus city which located in East Columbus. This region has 2 products that produce loss.
On September 2015, there are 293 sales that produce net sales equal to 56915.14 US dollars. Compared to August 2015 (the previous month), this month has growth percentage equal to 75.06%.
On September 2015, there are 2 region (67%) in Columbus city that belong to "Kategori I" and 1 region (33%) belongs to "Kategori II".
On September 2015, East Columbus region which belongs to "Kategori I" has 5 different products with their categories. 1 product belongs to "1st Product" and "2nd Product", respectively and the other 3 products belong to "3rd Product" (20%:20%:60%).
On 3rd Quarter of 2015, there are 592 sales that produce net sales equal to 113725.88 US dollars. Compared to 2nd Quarter of 2015 (the previous quarter), this quarter has growth percentage equal to 46.16%.
On 3rd Quarter of 2015, there are many customers that belong to "Gold" category. We should consider to give them bonus to maintain their loyalty to the company.
In total (not based on the year), this company has growth percentage from 2014 to 2017 equal to 133.77%. Its actual sales value equal to 1.972 million US dollars with budget (target) sales value equal to 1.474 million US dollars.
On 2015, there are 2102 sales that produce net sales equal to 407671.32 US dollars. Compared to 2014 (the previous year), this year has growth percentage equal to 53.88%.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent