Analytics

Title Corrective and Preventive Actions (CAPA) enables enterprises to manage their issues, corrective actions with optimal efforts and resolutions. It provides deep analysis into customer issues/corrective actions to help enterprises optimize their processes and costs. Objectives Provide a role based consistent view of CAPA across enterprise business units for management with a deep insight and learning dashboards on issues and actions across enterprise. Implements a comprehensive CAPA systems to better manage usage of resources and solutions covering business units to ensure production cost savings. Solution Hybrid and modular REST APIs for managing the business workflow, notifications and audits. Integrated dashboard with deep analytics on the issues and actions and multiple visualizations to enable process and cost optimizations. Integrated machine learning to forecast volume of issues, specific inputs on processes in business units. Benefits Single repository for all customer complaints and solutions accessible across business units. Mitigation of risks and improvement of processes. Analytics on types of issues, actions and opportunities for process optimizations and cost savings. Predictive analytics on volume and stress areas. Solution Stack Apache solr for search and filter. Spring Boot REST API's. AngularJS for web and mobile UI. Django framework for Admin management. Ngx-Charts for visualization, D/W for analytics.

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

A niche pharmacy company in USA wanted to build a data warehouse system, generate reports using Business Intelligence to gain insight on the company’s market share, product volumes and sales. Objectives Automate the data load from excel files in-to data warehouse Maintain the history tables for updated and deleted values Create interactive reports for sales and budget with drill-down and filters Solution Load the excel data using SSIS package Control flow and data flow Automate the SSIS packages by scheduled Jobs in SQL Agent Maintain history data using SQL query Interactive dashboard, which gives complete view of sales and budget details using Power BI Implemented the filters such as year-wise, brand--wise, quarter-wise, sales vs budget Created measures using DAX to achieve complex calculations for the required reports Technology Stack Server: MS SQL Server 2017 Languages: SQL and Data analysis expressions(DAX) Tools: SSIS for ETL process and Power BI