Power BI Data Model

Business context

The pharmaceutical distribution chain includes several stages: manufacturer → distributor → pharmacy → consumer.

Manufacturers typically track product movement from their warehouses to distributors or directly to pharmacies. In some cases, pharmacy chains also provide inventory and sell-out reports.

These reports can be part of marketing agreements - for example, in exchange for branded shelf placement or video ads near checkout areas.

Data challenges

• A single distributor report could exceed 1 million rows per week.
• Reports had to be processed incrementally due to file size and performance constraints.
• Each pharmacy chain delivered data in its own format.
• Consolidation and reconciliation were done manually by several managers.

Solution

I designed and implemented a Power BI data model that combined all sources into a single analytics structure.

• Normalized heterogeneous distributor and pharmacy reports into a common schema.
• Built a scalable data model capable of processing millions of records per period.
• Unified SKU identifiers across sources.
• Enabled comparison of planned vs actual metrics by SKU.
• Implemented monitoring of P&L metrics based on consolidated data.

Results

• All distributor and pharmacy reports were consolidated into a single system.
• Multi-step manual Excel processing was removed.
• Management gained transparent analytics at the SKU level.
• P&L monitoring became data driven.