How Ganit Helped Drive Financial Control Using a Unified Data Platform on AWS
By Gaurav H Kankaria, Head of Strategic Partnerships – Ganit
By Karan Vichare, Lead Solution Architect – Ganit
By Vaishnavi B, Apprentice Leader – Ganit
By Rajdip Chaudhari, Sr. Partner Solution Architect, Data & Analytics – AWS
One of the critical responsibilities of the financial control team in any organization is to ensure the efficient functioning of the company’s cash flow. To accomplish this, they must source multiple data from various functions/divisions, analyze this, and then identify cost-efficiency avenues and past leakages.
Ganit is an AWS Advanced Tier Services Partner that provides intelligent solutions at the intersection of hypothesis-based analytics, discovery-driven artificial intelligence (AI), and new-data insights.
Over the years, Ganit has successfully deployed business intelligence (BI) systems on Amazon Web Services (AWS) with a data lake and data warehouse being core to those solutions. This system has helped many of Ganit’s clients reduce more than 25 million hours in the creation of reports and spend more time driving action to improve top- and bottom-line numbers.
In this post, we will explore how one of Ganit’s client in the financial control division of an Indian subsidiary of a large apparel manufacturer was tasked to optimize expenses across divisions and ensure functions operate most efficiently.
Prior to achieving the client’s goals, the financial control team was facing a few key challenges:
- Data resided in disparate sources; for example, functions-maintained data was available in either a global enterprise resource planning (ERP) system (MySQL-based), locally maintained ERP system (MSSQL-based), or Excel documents in SharePoint.
- Since the organization was large, the data size was huge (>10 GB overall) and required a lot of processing time using Excel Macros (pre-built scripts).
- Most of the time spent (~2 weeks) would go into collating data across various departments; hence, they would create reports monthly. To monitor operational efficiency and drive efficient capital flows, the process of creating and circulating reports required a shorter time to reach the truth.
- The client would manually create the reports which were prone to:
- Human error (while collating data, processing, and circulating).
- Ability to deep dive (used pivot tables to identify deviations) on the concerns raised.
The above circumstances would often lead to more extended discussions on the sanctity of the data during the debate, rather than working towards resolving the issue.
Some key constraints the client shared with Ganit to consider before building the solutions included:
- Since some source systems were maintained by a global team, the Indian subsidiary did not have permission to connect to those source systems directly.
- While the IT team was able to provide access to source systems maintained in India, due to the on-premises system’s capability, data movement was allowed only for a few hours during the day.
- Excel documents maintained by departments were prone to changes in formats (data inputted manually).
- While the client wanted a robust and scalable solution, they also wanted to ensure the costs to this unified platform were efficiently handled.
- Since these files had sensitive information, the client (both financial and IT teams) wanted a solution which had high security provisions with limited (permission-enabled) access to select users.
- Business filters and other adjustments varied based on the divisions and had to be incorporated in the reports to be developed.
Given these challenges, Ganit suggested the client to create a data lake on a cloud environment which would act as a single source of truth (both structured and unstructured data) for all of its business intelligence reporting needs. Amazon QuickSight was chosen to be the reporting user interface (UI) layer .
AWS was finalized as the cloud provider as its ecosystem allowed:
- Ease of integration with other disparate source systems.
- Quick to deploy in production.
- Enables scale within the organization.
- Its suite of artificial intelligence (AI) and machine learning (ML) products improve predictive capability (enable proactive vs. reactive correction).
- Cost efficient (pay-per-use model).
Ganit configured the transactional systems to push incremental data to a remote SFTP server during this process.
The client’s finance team also manually creates reports like budgets and revenue targets; now, they can place these files directly in an Amazon Simple Storage Service (Amazon S3) bucket location. Amazon S3 supports both data encryption in transition and at rest.
Figure 1 – Production system technical architecture diagram overview.
Ganit used AWS Glue for data orchestration and as an extract, transform, load (ETL) tool, as it’s a one-stop solution for the following reasons:
- Supports all types of Java Database Connectivity (JDBC) connections.
- Has crawlers and catalogs to crawl the files in S3 and generate metadata.
- AWS Glue workflows help efficiently orchestrate the entire workflow.
- Glue job bookmarks help process only newly-added data without the need to track old files.
- Supports encryption in transit to ensure secured data transfer.
Using Amazon Athena, a serverless analytics service built on open-source frameworks, Ganit created tables and views which could directly connect with Amazon QuickSight. Athena was used to query encrypted data in Amazon S3 and connect to QuickSight.
Key features of QuickSight were utilized during the report development journey:
- Row-level and column-level security: Ganit provided role-based access to all users, enabling only relevant information to be accessed by respective users.
- Threshold monitoring and user alerts: For critical metrics such as Contribution Margin and Net Sales, appropriate thresholds were set based on business inputs. Upon breach, these would send “threshold breach” alerts to all relevant departments along with the finance team to act.
- Reports subscription: Users can subscribe to paginated reports in their inboxes on a scheduled basis.
- SPICE memory: The super fast, parallel, in-memory calculation engine of QuickSight enables faster data retrieval and rapid calculation of complex calculations, giving a seamless experience to the users.
Setup to Ensure Maximum Security of Data
Ganit used native AWS security provisions to ensure data encryption is easily integrated at rest and in transit. Amazon S3 uses SSE-S3 to encrypt data at rest, while AWS Glue enables encrypting data in transit using AWS Key Management Service (AWS KMS).
Encryption for data at rest in QuickSight’s SPICE memory used AWS-managed key services.
Production System Governance
For smooth functioning and governance of the infrastructure built, Ganit enabled Amazon CloudWatch to capture and check all the processes’ logs. AWS CloudTrail was set up to track all API calls across the AWS account.
AWS Identity Access Management (IAM) was used to manage access to different AWS services for various users, groups, or roles across the account. Alerts are sent to the user during job failures using the Amazon Simple Notification Service (SNS).
Figure 2 – Snapshots of BI board built on Amazon QuickSight.
A scalable, cost-efficient, and robust system addressing all challenges and constraints laid out by the client was built using an AWS data lake. This system led to a potential savings of more than 1,000 hours every month, enabling Ganit’s client to proactively track and monitor cost leakages and take appropriate actions immediately.
Ganit has successfully automated the entire financial control monitoring system for its client by empowering their decision making and leveraging the plethora of services offered by AWS.
To learn more about Ganit and its solutions, reach out at firstname.lastname@example.org.
Ganit – AWS Partner Spotlight
Ganit is an AWS Partner that provides intelligent solutions at the intersection of hypothesis-based analytics, discovery-driven AI, and new-data insights.