Case Study: Building a Pharma Sales Star Schema with SQL Server and Power BI

Sep 30, 2026 · @kale

Case study overview: from flat pharma file to analytics-ready model

This project turns a single, flat pharma sales file into a star schema in SQL Server, then loads it into Power BI for fast, reliable sales analytics.

The business problem. Pharma sales data usually arrives as one wide table, with customers, products, sales reps and dates repeated on every row. Reports built straight on that table are slow to maintain, easy to get wrong, and hard to extend when a new question arrives, such as sales by product class or by sales team.

The solution. Separate descriptive attributes from measurable events. A central fact table stores sales quantities and amounts, and surrounding dimension tables describe who, what, where and when. Power BI then relates the tables and calculates measures once, for every report to reuse.

Who it is for. BI developers, data analysts and finance or commercial teams in pharmaceutical distribution, plus Power BI learners who want a realistic data modelling project.

The raw data and its problems

The source is one denormalized table of pharma sales transactions. A typical layout carries columns such as distributor, customer name, city, country, sales channel, product name, product class, quantity, price, sales amount, month, year, sales rep, manager and sales team. [Adjust this list to match the columns in your source file.]

Why a flat table falls short

Repeated text. Customer, city and product names repeat on thousands of rows, which wastes storage and invites spelling inconsistencies.
Mixed levels of detail. Customer, product and rep attributes sit beside transaction amounts, so it is unclear what one row represents.
Weak time analysis. Month and year stored as text or separate columns make year-to-date and prior-year comparisons awkward.
Fragile reports. Every new filter or grouping means reworking the same wide table instead of reusing a clean model.

Star schema design

The design starts with the grain: one row in the fact table equals one sales line for one customer, product, sales rep and month. Every other decision follows from that sentence.

Table	Type	Key	Typical columns
FactSales	Fact	Surrogate keys to each dimension	Quantity, Price, SalesAmount
DimCustomer	Dimension	CustomerKey	Customer name, city, country, channel, sub-channel
DimProduct	Dimension	ProductKey	Product name, product class
DimSalesRep	Dimension	SalesRepKey	Rep name, manager, sales team
DimDistributor	Dimension	DistributorKey	Distributor name
DimDate	Dimension	DateKey	Date, month, quarter, year

Design rules applied

Each dimension has an integer surrogate key, so the model does not depend on text names.
Fact tables hold keys and numeric measures only.
Dimensions connect to the fact table with one-to-many relationships, so filters flow from dimension to fact.
A dedicated date table supports time intelligence in DAX.

The resulting shape looks like a star, with FactSales at the centre and one dimension on each point. [Insert your star schema diagram from the video here.]

Building the schema in SQL Server

The raw file is first loaded into a staging table. Dimensions are then built from its distinct values, and the fact table is loaded by joining staging back to those dimensions. Column names below are examples.

1. Create a dimension from distinct staging values

CREATE TABLE dbo.DimProduct (
    ProductKey   INT IDENTITY(1,1) PRIMARY KEY,
    ProductName  NVARCHAR(200) NOT NULL,
    ProductClass NVARCHAR(100) NULL
);

INSERT INTO dbo.DimProduct (ProductName, ProductClass)
SELECT DISTINCT LTRIM(RTRIM(ProductName)), LTRIM(RTRIM(ProductClass))
FROM dbo.Stg_PharmaSales;

2. Create the fact table with foreign keys

CREATE TABLE dbo.FactSales (
    SalesKey     BIGINT IDENTITY(1,1) PRIMARY KEY,
    DateKey      INT NOT NULL,
    CustomerKey  INT NOT NULL REFERENCES dbo.DimCustomer(CustomerKey),
    ProductKey   INT NOT NULL REFERENCES dbo.DimProduct(ProductKey),
    SalesRepKey  INT NOT NULL REFERENCES dbo.DimSalesRep(SalesRepKey),
    Quantity     DECIMAL(18,2) NOT NULL,
    Price        DECIMAL(18,2) NULL,
    SalesAmount  DECIMAL(18,2) NOT NULL
);

3. Load the fact table by looking up each key

INSERT INTO dbo.FactSales (DateKey, CustomerKey, ProductKey, SalesRepKey, Quantity, Price, SalesAmount)
SELECT d.DateKey, c.CustomerKey, p.ProductKey, r.SalesRepKey,
       s.Quantity, s.Price, s.Sales
FROM dbo.Stg_PharmaSales AS s
JOIN dbo.DimCustomer AS c ON c.CustomerName = s.CustomerName AND c.City = s.City
JOIN dbo.DimProduct  AS p ON p.ProductName  = s.ProductName
JOIN dbo.DimSalesRep AS r ON r.RepName      = s.SalesRepName
JOIN dbo.DimDate     AS d ON d.[Year] = s.[Year] AND d.[MonthNumber] = s.[MonthNumber];

4. Validate before loading Power BI. Compare row counts and total sales between staging and the fact table, and check for unmatched keys. If the totals differ, a join is dropping or duplicating rows.

Loading into Power BI and writing measures

Connect. Use Get Data, SQL Server, and import the fact and dimension tables. Import mode suits most pharma sales volumes and keeps reports fast.
Check relationships. Confirm each dimension key relates to the matching fact key, one-to-many, with single-direction filtering.
Mark the date table. Set DimDate as the date table so time intelligence works.
Hide keys. Hide surrogate keys and the raw fact columns, and expose measures instead.
Create measures. Build them once on the model, then reuse them in every visual.

Total Sales = SUM ( FactSales[SalesAmount] )

Total Quantity = SUM ( FactSales[Quantity] )

Sales PY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

Sales YoY % =
DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )

Sales YTD =
TOTALYTD ( [Total Sales], DimDate[Date] )

With the model in place, one slicer on product class or sales team filters every visual consistently, because all filtering flows through the dimensions.

Results, business value and lessons learned

What the star schema delivers

One trusted definition of sales, quantity and growth, reused across every report.
Faster refreshes and smaller models, since text attributes are stored once in dimensions.
Easy extension: a new dimension such as region or payer adds a table and a key, without rebuilding existing reports.
Clear answers to commercial questions: top products by class, sales by channel, rep and team performance, and year-over-year trends.

[Add 2 or 3 findings from your dashboard, for example the top product class by sales, the strongest sales team, and the year with the highest growth.]

Lessons learned

Define the grain first and write it as a sentence.
Clean and trim text in the staging layer, so dimensions do not hold near-duplicate values.
Reconcile totals between source, fact table and Power BI before building visuals.
Keep business logic in measures rather than calculated columns where possible.

Frequently asked questions

What is a star schema in Power BI? A star schema is a data model with one central fact table of measurable events, surrounded by dimension tables that describe them. It is the recommended structure for Power BI because it keeps models fast and easy to understand.

Why use SQL Server before Power BI? SQL Server lets you clean, validate and reshape data once in a governed database, so Power BI receives analytics-ready tables instead of a raw file.

What is the grain of a fact table? The grain defines what one row represents, such as one sales line per customer, product, rep and month.

What is the difference between a fact table and a dimension table? Fact tables store numbers you measure, like quantity and sales. Dimension tables store the descriptive attributes you filter and group by, like product, customer and date.

Do I need a date table? Yes. A dedicated, marked date table is required for reliable time intelligence functions such as year-to-date and prior-year comparisons.

Try it yourself

Watch the full tutorial, then download the complete source files and build the star schema yourself. Need a SQL Server and Power BI data model for your own sales data? Contact datascientist.ca.

From Raw Pharma Data to Star Schema: SQL Server + Power BI Explained!