Adventure Works Cycle - Sales Reporting Solution

Project Scope

The project scope was to build a complete sales reporting solution for Adventure Works Cycle, starting from the ETL process and ending with a weekly refreshed report in PBI Service. The raw data was based on a csv files. The final output consisted in three pages report:

• Flash Report
• Executive Summary
• Exploratory Report

My solution was to build an ETL process to transform and load the data into a MySQL DB and then connect it to PBI to build the final report.

ETL Process

The source dataset was based on csv files. My purpose was to clean them and load into a database. I made an SSIS project where I've picked the data, made some basic transformation and finally load into a MySQL database.

For most of the source (eg. customers, territories) the transformation consisted in converting data types and create some calculated columns.

The sales and product required a different kind of transformation.

For the product source there was the need to normalize them and split into three different column:

• Product
• Product Subcategory
• Product Category

For the sales source, I had three different source for 2015, 2016 and 2017. I had to merge into one single source before the final loading.

I've also configured an error output for each source to avoid the process to break and analyze the skipped rows (mainly duplicate values)

Once in MySQL I performed some spot check and create indexes, foreign keys and some views with calculated tables to use for the report building in PBI.

Report Building

Time to build the report in PBI. My solution for this project was based on three pages report. As I made the main transformations in the ETL process I only had to build the start schema and use Power Query just to made minor adjustment like rename some columns

The Calendar Issue

Analyzing the calendar consistency in MySQL I spotted a lack of days over 2017. For this reason I left the Calendar behind and created a new auto calendar in the model using DAX.

Flash Report

Is the first page of the report. I've focused on the following:

• Give a breakdown of the orders by category
• Top customer by revenues
• Total revenues and orders
• Top 10 of best and least selling products

The aim was to provide a snapshot of the overall sales contest give some quick insights to the management.

Executive Summary

This page is presented with:

• Target by Order, Profit and Returns
• Category order breakdown with a drill down by subcategory
• Monthly profit trend
• Scatter plot comparing total orders and annual income

The aim of this page was to give the management the possibility to monitor the target and focus on the different customer clusters per product to take better business decision. This report has the possibility to slice by date.

Exploratory Report

This is a more analytical page, presented with two matrix charts that allow the final user to perform a deep dive within the orders and the top ten customers.

There's the possibility to select a specific period and slice the charts with different parameters.

Final Deployment and Maintenance

The report was deployed on PBI Service provided with a smartphone layout and KPI dashboard with data alert configured. To guarantee the data accuracy I made:

• SSIS job to assure a weekly ETL process
• Weekly schedule refresh over PBI Service (two hours delayed than SSIS job)

Note: the dataset and the logo used for the project was part of the PBI Desktop Course by Maven Analytics.