Warehouse system for a US Pharma company to generate Business Intelligence reports

A prescriber call data warehouse system for a US Pharma company to generate Business Intelligence reports. This is to gain insight on the company products volume in the pharma industry by distinct call data constraints.


  • Automated, Interactive and dynamic reports for complete view of products volume at region level and territory level of call data with drill downs.
  • Compute statistical performance like Net-sales, contribution margin, variable margin, gross margin with extension data of physician call.
  • Auto load the physician call data into data warehouse for real-time data analysis


  • Lack of integration between data sets to group varied data set values for the fine tune of reports.
  • Mismatch of data attributes for comparison from multiple sources, to combine/aggregate columns of summarized report.


  • Auto load the excel data using SSIS package control flow and data flow
  • Transform and aggregate varied data sources into one data set by considering the common attributes.
  • Create interactive reports, which gives complete view of call data details using Power BI visuals with drill down options.
  • Create measures using DAX to achieve statistical calculations in building the required reports.


  • Anytime access of analytics report across devices for latest data charts.
  • Removal of manual data load and report generation process to minimize costs.

Technology Stack

  • Server: MS SQL Server 2017.
  • Languages: SQL and Data analysis expressions(DAX)
  • Tools: SSIS for ETL process and Power BI

Post has no taxonomies