BI Solution for Multinational Pharmaceutical

A multinational pharmaceutical firm wanted to improve performance through efficient and multi-perspective analysis of their sales and performance data. We developed a Business Intelligence (BI) solution that integrates analysis and KPIs that help monitor trends, targets, and performance. The reports generated help in budgeting and forecasting, providing delivery and sales statistics that can be accessed using mobile devices. The solution we developed provides the client with insights into the growth of the company along with reports and analysis on competitor performance.

Leveraging SQL Server tools, we created a new BI environment by integrating the client’s systems and processes, building a data warehouse, and mobilizing data. We used SSIS to Extract, Transform and Load (ETL) data to tables of ‘Facts’ and ‘Dimensions’ of the data warehouse, applying business logic and transformation rules. Facts comprise numerical values such as sales and budget, whereas Dimensions refer to products, geographic locations, and time period.

We built an SSAS Master Cube using the data warehouse for faster data retrieval of reports. Our reporting team used this Cube to generate the SSRS reports and XLCubed reports as per client specifications. The SSRS reports are deployed in a RoamBI server, and accessible via RoamBI on an iPad. We also developed a web-based administration tool to access the XLCubed reports deployed in a separate server.

• User-specific reports for sales representatives and management
• Insights on performance, sales, targets, and market trends
• Competitor analysis
• Architecture scaling to accommodate additional specifications and servers
• Faster report loading using MDX (Multidimensional Expressions) queries to retrieve data
• Automated report generation on demand for a wide range of users
• Drill-down and drill-through features to better analyze reports on web and mobile

• Microsoft SQL Server 2012
• SSIS
• SSAS
• SSRS
• XLCubed
• RoamBI