Power BI Data Model

Business Context

The pharmaceutical distribution chain consists of multiple stages: manufacturer → distributor → pharmacy → consumer.

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

These reports are often part of marketing agreements — for example, in exchange for branded displays, shelf placement, or in-store video advertising 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 proprietary format.
• Consolidation and reconciliation were handled manually by several managers.
• No unified view of planned vs actual performance across SKUs existed.

Solution

I designed and implemented a Power BI data model that unified all incoming data sources into a single analytical structure.

• Normalized heterogeneous distributor and pharmacy reports into a common schema.
• Built a scalable data model capable of handling millions of records per reporting period.
• Unified SKU identifiers across sources to enable consistent analysis.
• Enabled comparison of planned vs actual performance per SKU.
• Implemented monitoring of P&L indicators based on consolidated data.

Results

• All distributor and pharmacy reports were consolidated into a single system.
• Manual multi-step Excel processing was eliminated.
• Management gained transparent visibility into SKU-level performance.
• P&L monitoring became data-driven and near real-time.
• Operational workload for report consolidation was significantly reduced.

Key Competencies Demonstrated

• Power BI data modeling and schema design
• Large-scale data consolidation
• Normalization of heterogeneous data sources
• SKU-level analytics
• P&L monitoring and performance analysis
• Replacement of manual Excel-based workflows