Business Requirements

Pipey has several outlets located in various locations worldwide and every outlet stores their customer data in their respective database and it’s not mandatory that every outlet is using the same database. Some outlets use Oracle as a database, some use SAP, some stores just using flat files to store the sales data and so on.

A kind of system was anticipated which can seamlessly integrate all the massive amount of diversified data from all the various outlets, stored in various regional formats in one centralized place uniformly. Business Intelligence implementation to analyze all the data and scrutinize the sales, volume, which products are in major demand and which are not in demand, frequent customers, etc. Analytical reporting for executive decision makers to predict the revenue growth, transaction reporting etc.

At the same time, they also need various kind of dashboards to display different categories of data based on the executive level. For instance, CEO will be looking at high level summary report, Sales President would like to see sales figures data, Quality person would like to see product based data to identify which products are most selling and which are not.

In the nutshell, a complete Business Intelligence system with the data integration from various places, analyzing data and presenting in interactive dashboards and reports to make the decision process faster and accurate.

Key Functional Areas:

1. Providing ETL functionality
2. Moving and transforming data from various data stores to central repository
3. Managing and unifying the various formants of data, like data format, time format based on the country
4. Ensuring data integrity and data cleansing in a remarkable format
5. Integrating Heterogeneous data sources
6. Providing advanced analytical features such as complete data mining and key performance indicatory frameworks
7. Providing a complete reporting platform for people at different level in the organization
8. Dashboard management with filtering of data based on analyzed data

The Solution

Data residing in different sources are extracted which are in various forms (flat file, excel sheets, SAO, Oracle etc.) using ETL technique. The data then transformed into uniform format and at the end all data merged into single integrated data to be stored in data warehouse which is a centralized repository of organization.

Data stored in centralized repository is divided into Data Marts on which the analytical processing is followed. OLAP (Online analytical Processing) based analytical services are developed with data mining capabilities which as a result provides multi-dimensional structures called cubes to pre-calculate and store complex aggregations, and also to build mining models to perform data analysis which helps in identifying valuable information like what are the recent trends, patterns, dislikes of customers. This processed data can then be used by analysts to look data from different perspectives.

The collected analyzed data is then represented in the form of various kind of dashboards with multiple charts, graphs implying sales figures, volumes, sales by area, sales by brands and many more formats. These interactive dashboard combines data and graphical indicators to deliver at-a-glance summaries of information for users to view the state of the business and quickly respond.

Several reports were created to provide best analytical results, few of the majorly highlighted reports are :

• Performance Reports includes Labor Cost Efficiency Graph, Customer Satisfaction Survey Results Graph, Processing Time Graph, OTC Sales Efficiency Graph, Budget Estimator Report
• Performance Diagnostics includes Hourly Volume and Median Processing Time, Daily Volume and Median Processing Time, Weekly CRP Usage and OTC Sales Efficiency
• Sales- Actual vs Projected by product class
• Inventory value by product class

Performance metrics are presented graphically in web user interface, which makes the presentation easy to update, easy to reuse and produces excellent visualization. Application security facilities that authorized access to all tables, input forms and reports. The user interface is database schema-driven for most table maintenance dialogs. An application data dictionary and input form layout editor enable database schema updates to be deployed in minutes.

Key Highlights of the Solution Implemented

1. Real-time data synchronization for operational & strategic decisions
2. Flexible platform connects to any source
3. Ability to analyze data across multiple attributes and time periods
4. Addition of calculations at runtime or within the relational OLAP model
5. Interactive user interface - Drill down, drill across, drill up, filter, pivot, and sort
6. Granular security access down to the cell and column level
7. Sophisticated caching of data and dimensions reduces query latency
8. Aggregate-aware architecture simplifies setup and improves user response times
9. Relational database storage architecture
10. Scheduling and report distribution
11. Ability to display dashboards, tables, crosstabs, charts and widgets
12. Sub-reports for highly complex layouts
13. Use of Report virtualizers to optimize memory consumption
14. Report output to PDF, HTML, CSV, XLS, TXT, RTF, and more
15. Internationalization and localization for global reporting management

Benefit Impact On Business

At the end of its first quarter, results showed that the overall labor cost efficiency improved by 4.2%, the number of POs filled on time increased by 3%, OTC sales efficiency improved by 13.4% and central processing usage increased by 7.7% for the 300+ stores that started using this system.

Before the presence of this system there was no way to measure the performance consistently across the organization. Now, they can easily measure the performance of various stores and predict the consolidated result.

CEO of Pipey said “reports created by this system tells everything we need to know about the performance on a single page”.

• Cost Efficiency improved by 4.2%
• Performance increased by 20%

Prime Focus Points

• Extracting and Transforming large amount of data into central data warehouse
• Analyzing the data of million records and making the data retrieval performance oriented
• Dashboard management for people working in different hierarchical level in an organization
• More than 150 Interactive Reports with several filters to provide various view of the data
• 99.9% accuracy in data analysis with high performance reports rendering within milliseconds to 5 seconds at maximum depending on the report size
• Performance targets for peer groups are maintained with effective dates. Bonus amounts are tied to multi-level performance targets. This makes it easy set and raise the standards of excellence.