Business Data Analysis Report: Exercise “Commande” with SQL Server, SSIS, Tableau and Power BI

7 min readApr 15, 2019

Thi Xuan Hieu NGO
Date 04/03/2019
Professor: Sarah Bouraga
Technofutur Tic

After receiving raw data from six files for the imaginative company in the exercise “commande” (see Annex 1), I created a denormalized schema for dimensional database and used the SQL Server Management Studio to create data warehouse structure (see Annex 2). Then I used SSIS to transform, extract and load the data into the structure (see Annex 3). The data transformation allowed read-intensive improvement and allowed more efficient analysis. Then, I extracted the denormalized data from the data warehouse into Excel and loaded them onto Tableau and Power BI for making an analysis report (see Annex 4).

The purpose of the report is to give insights for the decisional purpose about the company’s performance in terms of sales and delivery by country and product over the period of 2014–2016. Overall, while the company’s delivery performance significantly increased over the period, its sales dramatically declined in all geographical markets and its products. Its biggest markets were Belgium, the US, and the UK, but its sales to Belgium experienced the sharpest decline. Its highest sold products were furniture, carpet and table, yet sales in furniture dropped the most significantly. The company even lost its Italy buyer and discontinued sales in glass product in 2016.

Tableau Dashboard. Graph 1. Sales Timeline 2014–2016

Graph 2. Delivery Performance 2014–2016

Graph 1 shows the sales timeline in the period of 2014–2016. Overall, the total sales declined sharply from approximately 80,000$ to approximately 17,000$ from 2014 to 2016. In particular, the sales plummeted by almost 15.5 percent in 2015 and 74 percent in 2016.

Graph 2 shows the delivery performance in the same period, measured by the difference between the expected and real delivery in days. Unlike the sales performance, the delivery performance improved significantly, even by the same annual percentage in 2015 and 2016. In 2014, the real delivery was made a total of 103 days later than what customers expected. But the delay reduced to -4 days in 2015 and -9 days in 2016, showing that customers received their orders a total of 4 days and 9 days respectively earlier than expected.

PowerBI. Graph 3. Expected and Real Delivery in Average Days 2014–2016

Graph 3 shows that the average real delivery generally improved from 2014 to 2016. It was consistently higher than the average expected delivery before April 2015, meaning that the company did not meet the customers delivery expectation most likely due to high sales. The biggest gap was 2 days late on average in April 2014. But the average real delivery significantly dropped lower than the average expected delivery since April 2015, indicating that the company exceeded the customers delivery expectation most likely due to major decline in sales.

Power BI. Graph 4. Total Quantity and Sales per Product 2014–2016

Graph 4 shows that the company’s sales values were most dominated by furniture, carpets and tables reaching almost 65,000$, 53,000$ and 33,000$ respectively. Meanwhile, the highest sales volumes were mainly in glass and cups at 1900 units and 2000 units respectively.

Dashboard 2.

Tableau Dashboard. Graph 5. Total Sales by Country 2014–2016

Graph 6. Sales Timeline by Country 2014–2016

Graph 5 shows the total sales 2014–2016 by country. Overall, Europe which comprised of Belgium, the UK, France and Italy, was the biggest market for the company with the total sales of 110,000$. Belgium, the US, and the UK were the biggest buyers with the total volume of approximately 64,000$, 25,000$, and 23,000$ while Italy was the smallest buyer with just over 7,000$.

Graph 6 shows the sales timeline by country in the period of 2014–2016. Overall, total sales to all countries sharply plummeted. The steepest decline was with Belgium, the company’s biggest buyer, from approximately 39,000$ in 2014 to 6,600$ in 2016. Belgium, significantly reduced their purchases by approximately 54 percent in 2015 and continued declined by 63 percent in 2016. Sales to Russia also dramatically dropped by 82 percent in 2015 but slightly increased by almost 35 percent in 2016. All sales to other countries experienced significant increase in 2015 with the highest jump by just over 300 percent but then plummeted again in 2016 with the total loss of Italy buyer.

Tableau dashboard. Graph 7. Total Sales by Product 2014–2016

Graph 8. Sales Timeline by Product 2014–2016

Graph 9. Total Sales by Country by Product 2014–2016

Graph 7 shows total sales by product between 2014 and 2016. Furniture, carpet and table were the main sales income for the company with approximately 64,000$, 53,000$ and 33,000$ respectively. Glass, cup, chair had the lowest sales of just approximately 3,000$, 3,800$, and 9,000$.

Graph 8 shows the sales timeline by product in the same period. Overall, total sales in all products declined sharply. Sales in furniture experienced the steepest decline from approximately 38,000$ in 2014 to 5,400$ in 2016, by 47 percent in 2015 and continued downwards by 73 percent in 2016. Although sales in all other products slightly picked up in 2015, they all plummeted sharply in 2016 with the total disappearance of glass in 2016.

Graph 9 shows total sales by country by product in the same years. Belgium was the biggest buyer in all products. Thus the sharp decline in purchase from Belgium as shown in Table 3 significantly affected the sales of all products.

In conclusion, although the delivery performance improved dramatically, sales plummeted sharply in all products and geological markets. Europe was the biggest market even though the US was the second largest buyer just after Belgium. The company experienced the sharpest decline in sales in its most sold furniture product and with its biggest buyer Belgium. The company even lost its Italy buyer and discontinued sales in glass product in 2016.

Annex 1:

Original data in six files (excel and csv)