Unicon Worldwide provides extensive business intelligence solutions and data warehousing services spanning a wide range of industry verticals, such as healthcare, IT, Life Insurance, Automotive, etc. Unicon's customized reports receive the bulk data provided by the client and turns it into consequential, relevant and easily comprehensible information that. These data warehousing services and intelligence solutions assist the companies to formulate better and timely business decisions following their everyday activities.

Unicon has achieved significant proficiency in developing data warehousing solutions that allow effective assessment of the data warehousing investment in terms of operational competence and enhanced monetary returns. To achieve the augmented returns, warehouse must complement and support the business. To achieve this aim, Unicon employs Informatica, DB2, Oracle Warehouse Builder, or Oracle Data Integrator while executing data warehouse architecture, design and development.

Data Warehouse Case study

Developing an Enterprise Data Warehousing for a Pharmaceutical Company

While building this data warehousing, Unicon Worldwide followed a level-headed approach to target each and every concern of the project. The project involved the following entities:

The Client

The client is a subordinate of a reputed and large U.S. based pharmaceutical company, which deals in production, import and marketing of pharmaceutical drugs in the Indian market spanning range of therapeutic categories. Our client manufactures over 700 products in 3 locations, and put them on the market from 19 locations to 38 regions all over the nation. The client has many field sales teams organized along lines of business covering their respective regions and zones to support sales.

Issues Faced

This pharma warehousing project responded to various specific requirements and issues while developing the same:

• Inflexible and strict reporting requirements of the U.S. based company
• Necessity of regular and synchronized information to make immediate decisions in a complex and dynamic environment
• To resolve the tedious and time taking process of creating consolidated reports when relevant data existed in different systems
• No easy and quick access to market and competitor data

The Solution

The team of Unicon Worldwide including top and mid level business analysts and marketing executives sat around the table to assess analytics and reporting needs of the project, and to conduct an in-depth study of the source systems. Following functional areas were considered pertaining to their reporting needs:

• Finance
• Sales
• Marketing
• MIS
• Distribution

After thoroughly understanding and reporting these business requirements, Unicon designed and developed a competent data warehouse that consolidated data from sales, distribution, budgeting, accounting, and external (IMS) data sources. The system was designed to manage and control several Microsoft Excel Workbook inputs as a significant part of the sales planning and budgeting was completed using Excel Workbooks. Market and competitor data was pulled out of an IMS database in an Excel file and entered into the data warehouse. Few key reports delivered as a part of this solution were as follows:

• Monthly trend analysis of market share and market rank of the client
• Monthly track of market share and market rank of therapeutic category-wise individual products of the client
• Track of top 100 customers by sales value
• Track of top 10 pharmaceuticals by Evolution Index
• Regular analysis of saving net across various dimensions like time, location, and products

As each geographical territory is maintained by Medical Sales Representative (MSR), so, performance monitoring of MSRs was additional functionality of this data warehouse.

Inventory analysis

Apart from the main utility of data consolidation, tracking and analysis, this data warehouse served many other analysis requirements including sales-to-stock ratio, sales-to-expense ratio, stock adjustment vs. stock in transit, credit/debit note analysis, stock ageing analysis, outstanding analysis, etc.

Tools and Technologies Employed

Unicon employed latest as well as time-honored tools and technologies to build this data warehouse. The data warehouse was built on an Extract Transform Load (ETL) layer for staging tables and then filling fact tables. Dimension interplay was controlled with the help of metadata tables that set the rules for authenticating the pool of the source data into the fact tables, where it has been mentioned that Excel Workbooks were taken as sources. The tools used in the data warehouse were as following:

• MS SQL Server
• Data Transformation Services to control extraction, transformation and loading
• BI Portal for presentation
• Reporting Services for creating reports
• MS OLAP to make analysis cubes