A Step-by-Step Guide to the Data Analysis Process

In the daily work of a data analyst, several essential steps should be followed:

Now, let’s delve deeper into each of these steps. We’ll illustrate them using the Adventure Works sample database, which is freely available.


Step 1: Setting the Analytical Objective

Here is an example of a business request from the sales manager of a bike shop:

Hi Sergey!
I hope you doing well.
Our goal is to analyse online sales of bicycles and accessories.
Thank you,
Alex Wheeler
General Sales Manager
Adventure Works

We need to focus on a business request and determine the key data metrics for analysis. We need to concentrate on the most profitable product categories sold to customers in different age groups, segmented by gender. Additionally, we require the capability to analyze the data on a monthly and yearly basis. Let’s refine the request by including additional technical details.

I am a sales manager of a bike shop, and I need a comprehensive view of the business’s performance, including:


- vertical bar charts showing total sales by product categories grouped by age groups among males across different countries;


- vertical bar charts showing total sales by product categories grouped by age groups among females across different countries;


- compare males sales vs females sales;


- visual representation of sales data on a map.


Furthermore, I require the flexibility to filter these reports by year and month basis.

We know that data needed to be extracted from MS SQL Server.


Step 2: Collecting the Data

Based on the provided requirements, it is essential to understand the database structure. We utilise Microsoft SQL Server Management Studioto to assess which tables are necessary for our operations and report generation. Upon thorough data analysis, we have determined that we must work with and extract data from the following tables: DimProduct, DimGeography, DimSalesTerritory, DimCustomer, DimProductCategory, DimDate, FactInternetSales, DimProductSubcategory, DimCurrency.

Next, in Power BI Desktop, we navigate to the Home tab and select Get Data. From there, we choose SQL Server and input our server credentials.

Subsequently, a window prompts us to select the required tables from our MS SQL Server. In this popup window, to avoid the lengthy process of loading all the data we click on Transform Data (not Load).

This action leads us to the Power Query Editor, where we can perform data cleaning and manipulation.


Step 3: Exploration and Cleaning the Data

To gain deeper insights into our data, it is valuable to examine the distribution and profile of each column within the datasets.

In this interface, we have the capability to perform data refinement tasks, such as column pruning (e.g., eliminating columns related to product name translations in French and Spanish languages) and column merging (e.g., generating a calculated field like “Full Name” for the DimCustomer table). Once the data cleaning process is completed, we proceed to the “Home” tab and select “Close & Apply” to finalize and implement these data transformations.


Step 4: Data Analysis

By selecting the Model view tab in left-side panel, we can access a visual representation of all the tables and their relationships in our data model. This perspective becomes particularly valuable when dealing with complex relationships among numerous tables. Our objective is to establish a star schema, wherein we have a single fact table (FactInternetSales) linked to multiple dimensional tables through one-to-many relationships. This design facilitates efficient data analysis and reporting.


Step 5: Data Reporting and Visualisation

Let’s reference back to our Sales Manager requests from “Defining the Question”. For our visualisations we would need 6 visual elements:

  • Clustered Column Chart (I want to view vertical bar charts showing total sales by product categories grouped by age groups among males across different countries)
  • Clustered Column Chart (I want to view vertical bar charts showing total sales by product categories grouped by age groups among females across different countries)
  • Pie Chart (I want a comparison chart that displays sales for males versus females)
  • Map (I want a visual representation of sales data on a map)
  • Two Slicers (I want to have the flexibility to filter all the reports by year and month)

Besides what we planned, we have included sections on the dashboard that shows the profit we made from female customers, male customers, and the total sales and profit.

The dashboard looks now as in screenshot below:


Step 6: Feedback and Iteration

We are gathering user feedback and continuously refining the dashboard based on that input. This iterative process ensures that the dashboard remains user-centric and meets evolving requirements.


Step 7: Collaboration

Effective collaboration with other cross-functional team members among data analysts, designers, and stakeholders ensures that the dashboard aligns with business goals.


Step 8: Data Security and Compliance

At this stage, we put strong security measures in place to keep sensitive data safe and make sure we follow the rules about data protection.


Step 9: Professional Development

It’s essential to continuously learn and enhance skills among team members. Staying updated with the latest Power BI features and data visualisation best practices ensures the dashboard remains effective and competitive.

I am delighted to offer my assistance with your data analysis requirements. Please feel free to reach out via my personal website .






Posted in News, Data Analysis
•
7 Sep 2023

Categories

Archives