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
Post has no taxonomies