The Challenge
The credit union relied on FICS’s Loan Producer and Mortgage Servicer products for their mortgage services, which included access to several pre-built reports delivered through SAP Crystal Reports. However, this setup posed several challenges. Any modification to the reports required custom paid services and a release cycle, causing delays and additional costs. Moreover, the use of SAP Crystal Reports was redundant, given that the credit union had adopted ThoughtSpot as its organization-wide business intelligence (BI) tool. The task at hand was to migrate the existing reports from SAP Crystal Reports to ThoughtSpot, despite having only report queries and lacking a logical or physical data model.
The Solution
To address this challenge and provide the credit union with flexibility and control over their mortgage reports, a comprehensive solution was devised:
Data Model Reverse Engineering: The solution involved reverse engineering and deducing the data model from the existing report queries. This critical step aimed to understand the underlying data structure and relationships.
Data Model Migration: The physical data model was migrated from SQL Server to Snowflake, a cloud-based data warehousing platform.
Data Validation and Comparison: All 14 report queries were meticulously analyzed, and a data model was constructed accordingly. The data was temporarily loaded into Snowflake, and the same report queries were executed in Snowflake. The results were compared to ensure data accuracy and consistency.
Automated Data Loading: Alteryx workflows were developed to enable the recurring loading of data into the data model within Snowflake. This automation ensured that the data remained up-to-date and readily available for reporting purposes.
ThoughtSpot Integration: The same data model was declared within ThoughtSpot, the organization’s chosen BI tool. A ThoughtSpot LiveBoard was created to host all 14 legacy reports, providing business users with a consolidated view.
The Result
The implementation of this solution delivered significant benefits to the credit union:
Business users gained a unified view of all 14 legacy reports within ThoughtSpot.
The newfound flexibility allowed users to independently customize views, apply filters, and create reports without requiring developer intervention, a stark contrast to the previous dependence on SAP Crystal Reports.
The migration to ThoughtSpot reduced the reliance on custom paid services and release cycles, streamlining the reporting process and minimizing costs.
This client success story exemplifies how a credit union harnessed data model reverse engineering and ThoughtSpot integration to transition from SAP Crystal Reports, providing business users with self-service capabilities, enhanced flexibility, and control over their mortgage reports.
Tools and Platforms Used
