Sales Data Analytics – a Case Study Implementation using Microsoft Power BI
By Gokuldas Chandgadkar
Background
Sales Data Analytics:The importance of data is growing day by day. With technological advancements, more and more visualization tools have been entering the market for organizations to gain insights into their businesses by analyzing data. Popular visualization tools are Microsoft Power BI, Infor BI / Birst, ClickView and Tableau to name a few.
Key stakeholders of any business would like to get real-time insights into their business. But getting these insights is not a trivial thing for most of these people as their data is scattered across multiple places in the organization, such as ERP or finance systems. It is also available in numerous formats or even embedded inside a few paper documents or a plethora of Excel templates. There are several resources available on the internet and even in textbooks on BI and Data warehousing technologies, and it’s always hard to find all details in one place.
Building an enterprise-level BI solution is more of an art than a science. It takes a team of experienced consultants to develop sound and scalable solutions to handle ever-changing complex business requirements.
In this article, we provide an example that represents typical real-life practical scenarios, and build an end-to-end BI solution right from inception to elaboration, construction and deployment of custom dashboards, using Microsoft Power BI. This article introduces an article series by giving brief overviews of all the modules covered to implement the complete BI solution to satisfy the business requirements in the example case study.
Sales Data Analytics – a Case Study
The case study considered here is a medium-sized automobile business. ABC Automotives is a fictitious company specializing in the sales and marketing of Motor Bikes, Classic Cars, Vintage Cars, Vans, and Heavy Vehicles. We take a look at the company’s sales data for the last 3 years, including the current year.
The company’s Sales / Marketing Vice President and CFO would like to get key insights into the business using KPIs to make informed business decisions.
Let us understand their Key Business requirements.
Key Business Requirements
Following are their key requirements:
- Year on Year Sales Revenue by Product Category.
- Top 5 Products by Sales volume over a specified period.
- Best performing products by Region / Territory.
- Sales revenue variance analysis by year, by product, by geography.
- Sales order volume by product category over a specified period.
- Order history by status, by quarter or over a specified period.
So what are their key Pain Points?
The Sales / Marketing and Finance teams need to prepare monthly reporting for their business heads. The last week of every month is very hectic and stressful for the entire teams. This is explained well in the below figure.
Fig 1: Team brainstorming on Month-end reporting
You can see from figure 1 the team is brainstorming to determine how to do their month-end reporting to provide consolidated insights to their business heads. The key problem we can see is their data is scattered. Also, it’s coming from their transactional system, papers and Excel. It’s a very ad hoc and manual process to consolidate the data and to do reporting. Hence it is very stressful. They are looking for clues.
Are there better alternatives?
Yes, we can streamline their business processes and simplify their monthly reporting so that the stakeholders can get real-time insights into their business processes by referring custom dashboards and focusing on their respective key performance indicators (KPIs). This will help them make informed decisions. We will build an end-to-end BI solution for this case study and explain all the steps right from inception to elaboration, construction and deployment of custom dashboards, in this article series. This article overviews all the modules used to implement this solution.
Proposed solution
Fig 2 Overview of all the modules to implement BI solution.
Fig 2 shows the modules we will design and implement to satisfy all business requirements discussed earlier. An overview of each module is given below.
Module 1 Data Capture
In module 1, we discuss the data capture process, which is the first step in building a BI solution. We have Sales data for the last 3 years received from their transactional system (ERP). We need to identify key data fields that are important to business requirements. Identifying and selecting these fields will be explained in a separate article as part of module 1 implementation. I will explain dimensions and facts and cover basic concepts to understand measurements and facts and how to identify key dimensions based on business requirements.
Module 2 Extract/Transform/Load (ETL)
In module 2, you will learn essential ETL techniques. This will cover cleaning and transforming techniques using Microsoft Power BI. This will be detailed in the article for module 2.
Module 3 Data Modeling Techniques
In module 3, we will cover basic data modeling techniques. This module will cover the Kimball methodology, which is popular and commonly used across all industry verticals when building a data warehouse solution. Also, in this module, we will make a date (time) dimension from scratch using Microsoft Power BI. Date dimension is a crucial dimension in any BI application.
Module 4 Building Dimension Hierarchies
In module 4, we will build a hierarchy for the Geography and Product dimensions. Finally, we will conclude this module by defining the relationship between the Sales cube and all sizes referred by it.
Module 5 Building Custom Dashboards
Module 5 will be the last module of this series, where we will cover the visualization in detail. Here we will explore different UI widgets to build dashboards to satisfy all the business requirements.
I hope you will enjoy this article series. If you have any comments or suggestions, then they are welcome. I have published a video tutorial series for this case study. So if you would like to watch the video tutorial, you can watch the video playlist here.
You want any help with your BI solution then you can visit our website at
About Author
I am Solutions Architect with more than 20+ years of IT experience. I have been working in the field of Business Intelligence for last 16 years. I am currently working as a Principal Consultant for Infor Global. You can visit my linked in profile using link below
