For the PoC, 100 GB of data was generated which comprised 12 months of data for 530 million credit card accounts. 10 node cluster in AWS stack with 16 vCPU and 30 GB memory per node was used to process the data.
It took 11 minutes to process end–to-end data on IIP based on Hadoop and Apache Spark.
The processed output data was graphically depicted by different charts based in Tableau; with needful integration of in memory schema RDDs in IIP/ Apache Spark with Tableau.
Analytics like standard deviation of outstanding balance, trend analysis of various balances, credit utilization ratio etc. was performed on a huge amount of retail data relatively much faster and in a cost effective manner, based on in-memory processing paradigm of IIP.
The ability to store historical data of 100 TB in Hadoop and move 20 TB of data in Spark for query and analysis gives the right mix of technology in this case.
The ability of Tableau to read data directly from Spark enables users to visualize data quickly without having another semantic or data storage layer.
The data set included master data and mapping info along with monthly sales inventory and costs. Lookups and aggregation of data was done during data load.
The ingestion and report processing and generation was done on a 2 core, 16 GB RAM, 2 node on premise cluster. Data volume of 19 million records was ingested into IIP in 6 minutes.
Reports for sales performance, variance between actuals and budgets, sales distribution, cost and sales comparison by category/period/geography/region was generated after the data load. Graphical features, geo maps and drill down functionalities were available in the reports. Report generation took less than 20 seconds for 19 million records.
Reports were visualized in Tableau with real-time analytics on the data.
Large month-end data feeds processed in near real-time and data loading and reporting was handled by IIP. Data load performance improved by 600 times vis-a-vis the current system using IIP. Reports were generated 60 times faster than the current system with millions of records in the backend.
Overall solution offered has substantially improved client's price performance ratio.
IIP analyzed and created ingestion models to ingest data from the source systems, including porting of 24 months of historical trend data for analysis. A staging and design model was created to transform and store data for payments, credits, debits, adjustments and other transaction tables. In addition,a master data for customers and collectors was also brought out from the source systems. The data model was optimized, harmonized and summarized tables in Hive were created.
IIP leveraged Spark SQL to visualize data as tableau reports. Provided ad hoc search and download interface to power users, allowing them to download large amounts of data for troubleshooting and auditing purposes. This simplified the complex reporting navigation structure, improved the report performance as well as provided export options for offline analysis.
IIP improved reporting accuracy with reduced reporting variance between old DWH system and AR balance from $30 million to just $84K. In addition, many discrepancies in discount and payment transactions were identified and fixed.
Over 400 different views for reports were reduced to approximately 50 views enabling a flexible and simplified the reporting framework. Reporting capabilities improved for liquidation of invoices over 24 months from the earlier 13-month period.
User adoption capabilities was increased with the ability to create additional reports and dashboards. The data can be combined with other sources and the marginal cost to build newer applications is significantly lesser.
Selective SAP ERP data across AP, AR and inventory processes (PO date, invoice received date, GRN date, payment terms, ASN), were extracted and transformed.
Correlations were established and calculations were done across millions of records to uncover patterns and trends impacting working capital which helped analyze smart vendors who raised early invoices of high amount as a regular practice.
A detailed drill-down analysis was done for the lowest level of granularity for individual suppliers who were raising early invoices.
IIP analyzed top 10 smart vendors and the financial impact they caused on working capital of the company and its subsidiaries. For the year the total impact of early invoicing amounted to approximatelyUSD 27 million.
Inventory data for uncovering working capital improvement opportunities was analyzed.
IIP also provided interactive analysis dashboards for the executives for efficient working capital management.