Business Solution using SSIS, SSAS, Power BI in sales data

The primary objective of this project is “To Identify The Patterns For Deviation In Sales Data”. First we will go insight the data, then solve the problem.

This dummy dataset is from an E-Commers company duration of 2017 to 2020. The dataset includes all the customers details, product details, market details, transactions details with date. The company works in three zones North, Central, South and works in 13 different markets all over the country like Delhi, Mumbai, Ahmedabad, Kochi, Nagpur, Bhopal, Hyderabad, Chennai, Bhubaneswar, Surat, Kanpur, Lucknow, Patna. The company works with two customer types “ECommerce” and “Brick & Mortar” and deals with a total of 38 big customers. And the company produces 339 different products.

METHODS-

• Building of Data Warehouse in SSIS-

1. We collect the dataset from various source.
2. Then integrate the data in data flow using Extract Transform and Load.
3. Finally Data Warehouse was made in SQL Server Integration Service.

• Make OLAP Cube in SSAS-

1. Then import the data in Data Source, make star skima in Data Source View.
2. Then OLAP Cube was made and deploy, process the cube in SQL Server Analytical Services.

• Analysis Data in Power BI-

1. Then in Power BI, Import the data using analytical service.
2. Generated interactive dashboards using different graphs to analyse patterns in sales trends .

• Upload the Dashboard in Comunity Server-

1. upload the dashboard in Microsoft Power BI Community Server.

From the above dashboard, we can see that the dataset is from Oct,2017 to Jun,2020 which represents slicer. And during this period total revenue is 9849 lakh and profit margin is 247 lakh and companies total sales is 24.29 lakh. We have made a graph “Revenue and profit Margin by Date” where revenue represents a bar plot and profit margin represents a line chart. Another similar graph represents “Revenue and Sales by date” where revenue represents a bar plot and sales represents a line chart.

We have made different graphs of zones, Markets, Customers with respect to “Revenue”, “Sales”, “Profit Margin”,”Profit margin %”, “Revenue Contribution %”, “Profit Margin contribution %” to compare each other.

We can see abovethat South zone’s sales are 399 thousands, revenue is 456 lakh and which gives profit of 10 lakhs with a profit margin of 2% and also contributes 4% of overall Profit Margin and 5% overall Revenue. Here we can see the sales fluctuated below the average line in 2019 then again went down in 2020. Anyway it doesn’t affect that much in business because it contributes only 4% of overall profit.

And in the same way for central zone’s sales 758 thousands, revenue is 2637 lakh and which gives profit of 86 lakhs with a profit margin of 3% and also contributes 35% of overall Profit Margin and 27% overall Revenue. We can see a big deviation in sales in the year 2019. And it will affect business because it contributes 35% profit overall with a highest profit margin rate 3%. So, we should look into this zone why this is happening.

Followed by North zone’s sales are 1272 thousands, revenue is 6756 lakh and which gives profit of 151 lakhs with a profit margin of 2% and also contributes 61% of overall Profit Margin and 69% overall Revenue. Here little deviations are there, so we should look into this zone because it contributes most of our revenue 61% and if we neglect this now, in future it can make you hurt.

We can see that Delhi’s sales are 988 thousands, revenue is 5196 lakh and which gives a profit margin of 120 lakhs with a profit margin of 2% and also contributes 48% of overall Profit Margin and 53% overall Revenue. So, If the company focus on profit margin in delhi, then the company will get most of the benefits from it.

Followed by Mumbai’s sales are 384 thousands, revenue is 1501 lakh and which gives a profit margin of 49 lakhs with a profit margin of 3% and also contributes 20% of overall Profit Margin and 15% overall Revenue. So, Mumbai is in a good position in every aspect.

Followed by Nagpur’s sales are 262 thousands, revenue is 550 lakh and which gives a profit margin of 14 lakhs with a profit margin of 3% and also contributes 6% of overall Profit Margin and 6% overall Revenue. So, nagpur’s profit margin conversion rate is not good compared to sales, they should more focus on profit margin.

Followed by Kochi’s sales are 255 thousands, revenue is 188 lakh and which gives a profit margin of 7 lakhs with a profit margin of 4% and also contributes 3% of overall Profit Margin and 2% overall Revenue. So, Kochi’s profit margin is also not as good as nagpur. We need to focus on this.

Followed by Ahmedabad’s sales are 207 thousands, revenue is 1323 lakh and which gives a profit margin of 28 lakhs with a profit margin of 2% and also contributes 12% of overall Profit Margin and 13% overall Revenue. So, It seems very good in all aspects.

Followed by Surat’s sales are 17 thousands, revenue is 26 lakh and which gives a profit margin of 5% total 1lakhs and also contributes 20% of overall Profit Margin and 15% overall Revenue. So, we can see the profit margin is very good in Surat, but revenue and sales are not good. We should focus on sales and revenue.

We can see that Electrical sara Store’s sales are 654 thousands, revenue is 4133 lakh and which gives a profit margin of 2.25% total 93lakhs and also contributes 37.7% of overall Profit Margin and 42% overall Revenue.

Followed by Premium Store’s sales are 279 thousands, revenue is 450 lakh and which gives a profit margin of 2.5% total 11lakhs and also contributes 4.3% of overall Profit Margin and 4.6% overall Revenue.

Followed by Nixon’s sales are 123 thousands, revenue is 439 lakh and which gives a profit margin of 4% total 18lakhs and also contributes 7.2% of overall Profit Margin and 4.46% overall Revenue. So, Nixon is very good in profit margin and it should focus on more sales.

Followed by Leader’s sales are 10 thousands, revenue is 64 lakh and which gives a profit margin of 11% total 7lakhs and also contributes 6.7% of overall Profit Margin and 1.89% overall Revenue. So, It returns the most profit margin percentage and we should focus on more sales for more revenue.

Followed by Surge Store’s sales are 176 thousands, revenue is 286 lakh and which gives a profit margin of 2.3% total 7lakhs and also contributes 2.7% of overall Profit Margin and 2.91% overall Revenue. So, Surat’s sales is quite good but it should improve in revenue conversion rate.

Followed by Excel Store’s sales are 139 thousands, revenue is 491 lakh and which gives a profit margin of 1.5% total 7lakhs and also contributes 2.8% of overall Profit Margin and 5% overall Revenue. Excel store’s sales lso good but have to focus on more revenue.

Followed by Surat’s sales are 17 thousands, revenue is 26 lakh and which gives a profit margin of 5% total 1lakhs and also contributes 20% of overall Profit Margin and 15% overall Revenue. So, Surat is very good in profit margin % but should focus on sales and revenue.

We can see in the “Total Sales by Date” graph the sales rate drops down in 2019 and 2020. So, find out the reasons behind it.

• To find out the reasons of deviation of sales, I try to find out few question’s answer-

Those are-

• First check, Is there any zone present where the sales get down during that period…? If yes, then which one and how much?
• Then check market wise, Is there any market present where the sales get down…?
• who are the users, are not buying during this period and try to find out any pattern ( like location, age, sex, old or new customers )
• What type of product that was bought previously.
• What products are not getting sold
• Compare the products with it’s Previous record with diff parameters (sales amount, Products quality review check, packaging, Promotion, advertising, discounts)

Now, we have compared the 2019 data with 2018 data and saw a fews facts. In the North, Central zone the sales get down but in the south zone sales does not effect. In Delhi, Mumbai, Nagpur, Ahmedabad, Bhupal, Hyderabad the sales get down but in Kochi the sales have increased. Same for the Surge store’s demand is also increased.

We can see that the North region’s sales gets down from 524k to 427k and this region contributes most of the sales. So, we need to identify the reasons for inclination. Same for the Central region’s sales goes down from 321k to 214k. So, we need to identify the reasons for inclination. The South region’s sales do not change by yearly but sales fluctuate monthly.

We can see that Delhi’s sales gets down from 407k to 336k and this region contributes most of the sales. So, we need to identify the reasons for inclination in sales. Same for Mumbai’s sales goes down from 171k to 128k. Except Jan, Sep and Oct sales are goes down So, we need to identify the reasons for inclination. Followed by Nagpur Kochi, Ahmedabad, Bhupal, Hyderabad and others.

We can see that Electricalsara Store’s sales goes down from 268k to 212k and this customer contributes most of the sales. So, we should talk with this customer for increase the sales. Same for Premium Stores, sales goes down from 122k to 99k. Except Jan,Sep and Oct, for all months the sales goes down. Followed by Excel Stores, Nixon, Surface Stores and other.

Revenue Comparisons

When we compare 2019 and 2020 revenue, we can see all the revenue of 2020 are below then 2019 except in the month july and october.

When we break into location wise, we can see that the monthly revenue goes down in the year 2020. Delhi contributes almost 53.4% in the year 2019 but in the year 2020 the revenue gets down 2.4% i.e. 51% And it will affect the company surely in future. In the location mumbai, revenue gets high by 0.33% in 2020 but we can improve it more, in the month february, march, october, november, december the revenue is down and followed by Ahmedabad, Bhupal, Nagpur.

Electricalsara Store contributes 41% i.e. most of the revenue among all customers. And it’s revenue goes down in the year 2020. So we need to look into it.

Premium Store contributes almost 5% of the revenue among all customers. And it’s revenue goes down in the year 2020. Though it contributes 5% of total revenue, So it doesn’t affect that much in overall profit margin. But we have to look into those customers, why they don’t buy products. Followed by Nixon, Control, Surge Store and more.

We can see in red that customers and locations even didn’t contribute 1–2% revenue, We should make different plans and strategies to make profit.

Here we can see thebelow customers, who are stay behind in the year 2019.

Here we can see the below products, which doesnot sales or stay behind in the year 2019.

Conclusion-

• We have identified the zones, Markets in which sales are going down. Then we identified the customers who don’t buy products and the products which don’t buy by customers.
• We need healthy communication between management and suppliers to fill the gap between customers and products.
• We already identify the products not selling, Now, we need to identify what is wrong with those products. May be some packaging issue, Product quality.
• We need to talk with higher authority how we will sell those non selling products by giving discounts, promotions or advertising.