Advanced Data Transformation Platform for Sophisticated Reporting & Analysis
A full-service, global valuation firm (the Company*) hired dbSeer to transform and deliver custom valuation report data in a digital format to one of their high-profile clients.
The Company needed to move from a complex, spreadsheet reporting model to deliver valuation data that was easily consumable by their client for analysis. The dbSeer team architected a sophisticated and secure cloud-based data ingestion and transformation pipeline on AWS native services with an analytics-ready data warehouse.
Company Seeks Standardized, Digital Data Delivery Solution to Enable More Efficient Client Consumption and Analysis
The Company provides valuation reports for their clients to enable analytics and reporting. Each valuation report contains data related to the valuation of a company at a specific point in time based on a variety of sources of data. The report provides enterprise level inputs, assumptions, calculations, and metrics as well as information used to assess the value of a variety of financial instruments associated with the company being valued. These sophisticated valuation reports are detailed spreadsheets with over 100 worksheets containing complex calculations that are synthesized into approximately 1,000 individual output fields requiring conditional logic for interpretation.
One of the Company’s high-profile clients requested to receive valuation reports in a standardized format that includes a subset of specific data points that is consumable for their own data systems. Previously, the client had to manually review summarized PDF reports, identify specific key data of interest, map datapoints for use in their own databases, manually enter that data into their systems, and perform data validation. With this manual data extraction and entry, the process was time-consuming and potentially error prone.
The Company’s immediate need was to deliver a solution to meet the specific client’s needs. At the same time, the Company wanted to build a data warehouse to consolidate data and enable a repeatable process to provide the same value in the future to other clients. This solution would also enable additional analytics (such as trending analysis, competitive information, and so on) that may be required by clients in the future.
dbSeer Designs Secure, Cost-effective Database Solution to Meet Client Data Requirements
dbSeer designed a secure and cost-effective database solution for the Company that transforms data in the valuation reports to deliver customized data to the Company’s client in a digital format. Based on a spreadsheet template provided by the Company, dbSeer engineers mapped current data fields to the client’s requested data model and output. This involved distilling over 1,000 data fields to the almost 300 fields required by the client.
Cloud-based Analytics Database Based on AWS Cloud
With dbSeer’s solution, data from reports is now extracted to a consolidated cloud-based data repository using Amazon Web Services (AWS) Cloud. The client’s subset of data is output and sent securely on a periodic basis throughout the day. Same-day electronic access to report results is provided with no need for manual transformation.
The dbSeer solution facilitates the transfer of the Company’s valuation report data as follows:
- The Company’s analysts save spreadsheet reports, or input files, on their desktops. The files are transferred and saved securely to an S3 bucket.
- An AWS glue job checks regularly for new report files. The job validates file structure and verifies that data fields match defined data types and are populated. Once validations are complete, the job runs a series of transformations to enrich and process the data. The data is then normalized and stored in the data warehouse and is also written back to S3, which serves as a data lake.
- Another glue job runs every hour to consolidate data from the numerous input files and then generates a consolidated output file.
- The output file is sent to the Company’s client via SFTP.
Configurability
The platform is configurable so that different output formats or different delivery mechanisms can easily be defined for different clients as needed.
Security and Data Protection
Security is of utmost importance for the Company. With AWS features, implementing security measures at various stages of the data was easy. dbSeer’s solution uses encryption to protect all data at rest, including when stored in the Redshift database, and uses Amazon’s VPC to extend the Company’s private network in the cloud. Amazon VPC is a private network with no public internet access.
Traceability
AWS Cloud Trail ensures data is accessed only by authorized users.
Scalability
AWS provides the ability to easily scale as the Company grows. The AWS Glue and Lambda jobs can be scaled as more clients are added to the AWS environment.
Customer Delivers Sophisticated Reporting and Analytics Tailored to Client Needs
As a result of dbSeer’s thoughtfully engineered solution, the Company has moved from delivering complex PDF spreadsheets to delivering sophisticated reporting from a cloud-based analytics database. The solution facilitates comprehensive, focused, and customized analysis for clients and enables the Customer to deliver high-value service tailored to their client’s needs.
Time and Cost Savings, while Ensuring Data Quality
The solution helps to eliminate time spent on manual data entry, which can be error prone and time-consuming.
Client Productivity
Clients can now focus on actual data analysis rather than administrative tasks.
Customer Success and Business Value for Future Client Needs
The solution can be extended to support additional clients’ electronic reporting requirements.
More Extensive Analysis for Valuations and Market Competitiveness
Data can be mined across reports more easily, while achieving market competitiveness with digital delivery capabilities.
Start Here & Contact dbSeer today!
