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

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.

Objectives

• 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

Challenges

• 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.

Solution

• 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.

Benefits

• 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