In this ‘Data Analysis’ project, we’ll analyze a global Pharmaceutical Manufacturing Company's raw sales data and draw meaningful insights.

⚡PowerBI Desktop
⚡PowerQuery Editor [For data-transformation/data-modeling]
⚡PowerBI Service [For making the report accessible on the web without PowerBI login]
⚡Multipage fully Interactive Report [For drawing insights and analysis]

• Introduction
• Objective
• Dataset
• Solution Approach
• How To Use
• License
• Credits
• Get in touch

• Datamatrix-ml Pharmaceuticals is one of the leading Pharmaceutical Manufacturing companies with a global presence.
• Their Markets are divided into different regions across the world. One of those regions manages the German and Poland Markets.
• Company does not sell directly to customers. Instead, they work with a couple of Distributors in all their regions.
• They have an agreement with each distributor to share their Sales Data. This is to enable them to gain insights up to the retail level. This data is made available to them in CSV format.

The firm has asked us to perform in-depth data analysis to get insight into company sales performance. Specifically, below are the essential requirements to be satisfied…

Table-1 : Requirements

The dataset is sourced from each distributor. It contains Pharmaceutical Manufacturing Company’s, Wholesale-Retail Data. The field description of the raw data is given below. The raw dataset pharma-data.csv can be downloaded from here

Table-3 : Proposed Solution

To understand, be familiar with and check the sanity of the given data, the first step is EDA. This project's initial data exploration has been carried out using the pandas python package. Here, in general, we are checking...

• Presence of any missing values
• Any unusual value (outliers)
• Incorrect values (e.g., sales column, we see -ve numbers)
• Determine categorical and numeric columns
• Determine dimensions of categorical columns and range of numeric columns Note that these steps can be performed using PowerQuery Editor and/or excel; however, pandas makes it much easier and faster; on top of that, pandas can handle massive datasets.

EDA steps can be found in the data-exploration.ipynb notebook.

The provided dataset was relatively clean and well organized; hence only a little work was required in this step; the following steps were carried out...

• Correct column heading provided
• Correct data type is assigned to columns

• The provided data is in a single table format. The exploration revealed that it contains both categorical (dimensions) and numeric (facts) data.
• We build a data model where dimensions and facts are separated, then they are linked together by logical relationship to form a star schema. The resultant data model is shown below...

The tables with the prefix DIM are dimension tables, and FACT is the fact table.

Three interactive reports/dashboards (report pages) will be created to implement the proposed solution. Refer to Table-3: Proposed Solution for detailed requirements and the corresponding proposed solution.

This high-level report shows the overall sales figures and elements at a glance.

This more granular detailed report analyses data from the company distributors' and customers' perspectives. Sales by the distributor can be drilled down to specific product levels.

This is another detailed report that analyses the performance of the company's sales team. Sales by the sales team can be drilled down to product class and specific product levels.

Explore the fully functional report with native PowerBI interactive experience.

If you have PowerBI desktop installed, download the pharma-analysis.pbix from the repo and open it using PowerBI desktop. There is no need to download the raw dataset; the pbix files contain the complete normalized data model, feel free to modify and experiment with it.

• Dataset sourced from Foresight BI

Back To The Top