About the Client
The client is a largest NBFC in Indian Financial master. They offer a wide range of secured and unsecured loans to our customers. have more than 1300 Branches spread across 24 States & 3 Union Territories. They provide around 20+ product to the customers in financial domain.
The project revolves around providing analytical insight to the clients where they take actionable
Step to bridge the same. We also provide the YTD, MTD, Branch Profitability report, MIS Expenses,
The team consisted a team of Delivery head, Project manager, SQL developers, Data Analysts and Data scientists.
- Overall understanding of Banks growth & Defects.
- Branch profitability analysis presenting top and bottom branches.
- YTD sales with head count.
- Most expensive branches & most profitable branches.
About the Client
- One of India’s largest road developers as measured by lane kilometres under Concession Agreements signed with the Central and State Government authorities.
- The Trust’s initial portfolio of assets comprised of five toll road projects in the Indian states of Rajasthan, Karnataka, Tamil Nadu and Telangana. Subsequently, the Trust added another eight road assets in the states of Rajasthan, Karnataka, Telangana and Maharashtra. Out of these eight projects, six are NHAI projects and two are state projects. These toll roads are operated and maintained pursuant to the concessions granted by the NHAI / State authorities.
- The project is to facilitate development of an Analytical system which consists of extracting Toll transaction data from 11 Toll Plazas & Acquiring bank feeds, processing and transforming to a common reporting structure, developing reports and dashboards, maintaining, and stabilizing the system after development.
- These data to be created as different logical Data marts for relevant reports and analysis models.
- Around 30 set of standard dashboards, and 5 Dashboards & Reports for Big Data Analysis covering Traffic-Plaza cohort analysis.
- Availability of Adhoc report generation facility for the supervisors and business owners
- 90% of the reports shall load in less than 5 seconds, heavy reports fetching several thousand / lakhs of records shall also be processed in reasonable time. Most Dashboards shall load within 5 seconds
Approach was taken towards addressing client side challenge
Client Side Challenges
- Client hosted more than 3 different data vendors. (Geographic based)
- Organizing different data structures and data quality into a single uniform data (Core Challenge)
- Maintaining of too large datasets at monthly, yearly level.
- Traffic & Revenue reports was the predominant one for client, but too much delays on daily basis.
- Capturing and analyzing the revenue gaps was a challenge
- Revenue loss due to class discrepancy was un identified and difficult to analyse and predict.
- Microsoft SQL server.
- Oracle Server.
- AWS S3 ( Raw data storage), Glue ( Data Transformation) , Redshift ( Datamart Creation)
- Tableau server (BI Web interface), Tableau desktop. (BI preparation tool)
- Automatic dashboards updation on periodic intervals.
- APIs using Python
- Project Manager, Business Analysts
- Senior Solution Architect
- Senior Data Warehouse Architect cum Designer
- ETL Developers and Data Ops Engineers
- Dashboard & Report Developers
- Data Scientist
- Data / Report Testers & Data Quality Analysts
- Data stored in Cloud after transformation to a common DataMart.
- Bypassing manual SQL process by AWS S3 storage and providing all time access to view raw data.
- Risk of data losing is prevented by secure data storage in S3 and layers of data transformation into various functional RDBMS.
- Simple & seamless DataMart creation in AWS Redshift of assorted data helped the client to view at one go.
- Live dashboards facilitated client to foresee the real business & take actions
- Models build to predict Traffic according to the weekends, holidays etc.
- Models build to predict possible revenue loss, prediction with related to road wear and tear to plan for laying roads.
- Dashboards to understand the class discrepancies and plan for solutions were implemented.
About the Client
Client caters to businesses of Personal Care as well as Food/Snacks, Beverages and Dairy segments. Client’s goal is to remain the preferred choice for customers at home and in every market, besides operating with conviction and leading certain chosen sectors for innovation. The company operates throughout India through a wide network of 25 lakh outlets and 1300 stockists.
To redesign their manual accounting & R&D system to automated Power BI ecosystem
- Deliverables : Accounts Receivables Dashboard, Cash collection Dashboard, Inventory holdings dashboards Models build to predict the growth of the branches .
- Converting traditional excel sheet to automation.
- Solving of crucial accounting formulas in DAX.