Centralizing Analysis & Forecasting in Retail

Casestudy

Business Case:

Our customer AeRecylingCorps is a rapidly growing company that provides carrier grade Wireless device trade-in solutions to Wireless Carriers and Retailers around the world. eRecyclingCorps goal is to transform the wireless ecosystem by providing an easy way for Wireless Carriers and Retailers to reward customers through a comprehensive wireless device trade-in program.

The firm has till then not implemented any traditional Dataware-housing system (Used post gresdb), that had limitations on historical data that can be stored.Furthermore, these were stand-alone systems that did not connect centrally.The store relied heavily on its IT consultants to extract the desired data. The entire process of getting the right reports took around 80hours every week.

This kind of a system did not give much scope to analyze historical trends, forecast future trends and perform ad-hoc queries. The management relied on raw reports from individual warehouses to gain insights into the overall business trends.

Solution:

Yasmesoft consultants took an initial look into the systems and processes utilized by the company and recommended a solution that would match their current needs as well as can be scaled for future expansion. Considering the firm’s requirements, Yasmesoft suggested the implementation of Microsoft BI solution for data warehousing. A data warehouse can store all the data at a centralized location and will be a single source of truth for the entire organization. The data warehouse will be stored in a MS Sql server data bases and the data will be pulled using Microsoft BI rtl tools like SSIS.

For Reporting we suggested and implemented Qlikview which provided the right amount of querying and dashboarding features that were required for the firm. We have implemented various dashboards & scorecards showing important KPI’s/metrics for the C-Level.

The project implementation took a total of 4 months and constituted the following phases.

Requirements & analysis: The team gathered all the data and analyzed the present scenario and all the source systems in the company.

Designing: The Enterprise Data Warehouse (EDW) was designed to make sure all the data from various systems can be stored at one location. Data mapping and data modeling was done here. EDW was designed and divided in 3 layers –Stage, ODS and DW/Presentation layer.

Stage is where all the data is pulled from source systems. ODS is where all the data modeling is done and DW is the fully normalized data model with facts and dimensions.

Implementation: Once the design was done. All the data was pulled using SSIS ETL tools to the 3 layers.

Reporting: Qlikview was used to design reports/dashboard and scorecards and the data is pulled from DW layer of EDW

Results

Yasmesoft’s BI implementation led to the following direct improvements that could be realized as soon as the implementation of our solution was complete

Reducing time spent on reporting: The entire reporting that took 80 hours every week of the IT consultant, could now be done in less than 3 hours. Also, the enhanced self-service BI analytics features available with Microsoft BI and Qlikview eliminated the need for IT support for generating the reports. The C-level executives and managers could log on to the system and choose the necessary fields and generate their own reports.

Centralized analysis and forecasting infrastructure: With our solution, the top management did not have to wait for reports from individual teams. They could immediately connect to the systems themselves and analyse the necessary data in the format desired. The team was able to perform better forecasting and critical analysis such as market basket analysis and the real impact of certain promotional campaigns.

Reduced inventory costs: With a real time and centralized tracking of inventory used, this BI solution was able to directly reduce the inventory costs of the organization by 10% within an year of its implementation

Reduced IT manpower costs: The process automation and simplified solution led to the reductions in the IT skills and manpower required. This lead to $40000 a month savings in IT man-power costs.