Common Securitization Solutions uses QuickSight to create a business intelligence data engine

This is a guest post authored by Rishi Ranjan from Common Securitization Solutions.

A joint venture of Fannie Mae and Freddie Mac, Common Securitization Solutions (CSS) administers a portfolio of $6.4 trillion worth of mortgage-backed securities. They use huge quantities of data to facilitate investment decisions. In this post, CSS shares how Amazon QuickSight helped them reduce report generation time by 90% and empower users with interactive dashboards for business intelligence (BI) data.

Data is the lifeline of the organization, and our data team uses BI tools, machine learning (ML), and AI to manage it.

We received a critical request for the organization: a 360-degree view of issuers’ portfolio of mortgage-backed securities and loans, from single class to more complex multiclass securities.

Requests like this from the business were common, and we knew the need for data analysis would only grow. We needed better tools for our users—and to spend less time maintaining and more time building.

At the time of this request, we were using multiple tools and methods to build analytics solutions for different use cases. With no standard architecture guidance, we had redundant architecture patterns that were cumbersome to maintain. In some cases, we were managing BI environments that required skilled staffing and administrative work managing the associated licenses. This often made scaling difficult and time-consuming. In other use cases, we were using scripts to generate visuals from the data.

Our capabilities depended too much on people, which impeded us from supporting new use cases or making fast changes to existing ones.

We wanted a solution that would alleviate capacity issues while providing their users with more functionality.

Building capacity to use business intelligence data

We felt most BI solutions offered limited interactivity and required too much staff time to set up and maintain. We chose QuickSight for a few reasons:

• As a managed service, QuickSight allowed us to build products without having to set up a new environment. It gave us extreme elasticity and met our need to scale.
• The dashboards were user-friendly and had a lot of helpful features, like drill-down exploration, filters and parameters, the ability to add derived fields with custom code, ML insights, and natural language processing capabilities.
• We use AWS Cloud services, which meant integrating QuickSight would be seamless and we could use a common AWS-based framework for orchestration and monitoring.

In addition, we would achieve savings by using in-memory SPICE (Super-fast, Parallel, In-memory Calculation Engine) storage—and save a lot of staff time in development and managing BI licenses.

A speedy timeline to launch

We started exploring QuickSight in Q4 of 2021. By the end of Q1 2022, we’d released our first product. We added more functionalities and visuals by the end of the next quarter, and now push updates to this product every 2 months.

We also kicked off another project to build an analytics dashboard for our corporate data. We established data acquisition pipelines to ingest financial data from one application, contract details from a software as a service (SaaS) platform, and contractor details from SAP Fieldglass to the target Snowflake platform. QuickSight gets data from Snowflake for all these platforms to build a comprehensive dashboard to support our corporate functions.

Solution architecture

We use different data acquisition patterns to pull data from diverse data platforms to Snowflake. Data is further transformed into a data mart to make it analytics ready. QuickSight is integrated with Azure AD to provide single sign-on. Azure AD groups are integrated with QuickSight user groups. There are a few active directory groups for internal and external users. Internal user groups are from Data, Single Family Business, and readers across the companies. FHFA users are the only external groups.

We use SharePoint for our intranet and content management, and we are embedding QuickSight dashboards there to make it straightforward for our workers to share dashboards with our customers.

The following diagram illustrates this architecture.

The following screenshot shows an example of our UCRA (User Centric Reporting & Analytics) dashboard covering security-level new issuance activity. This sample dashboard shows Unpaid Balance (UPB) of the new securities issues by Freddie Mac (FRE) and Fannie Mae (FNM). This also shows the count of securities issued by each issuer (FRE and FNM) on a monthly basis. Issuer, Reporting Period, and Trend Month filters allow users to update dashboard visuals as needed to see different historical periods or breakdowns.

Monthly ongoing administration data and visuals allow users to see the breakdown of total portfolio and changes in portfolio over time. Loan Count and UPB trend graphs help us understand the liquidity provided for all of the approximately 30 million loans for different periods and unpaid balances.

Multiple trending visuals capture the breakdown of loan volume and unpaid balances into different category ranges like FICO range, interest rate, and more. For example, the FICO range-based trend report helps us understand the underwriting standards and borrowing qualifications (credit scores) for different periods.

Outcomes

QuickSight has saved us time and money while enhancing our capabilities:

• 90% reduction in time spent generating reports and supporting inquiries from customers and senior leadership
• 100% reduction in time spent preparing data for analytics usage
• 70% decrease in time to address ad hoc requests
• A single pane of glass to view issuance and performance of all mortgage-backed securities products

What’s next

QuickSight lets us keep building new applications for different business domains, even as we continuously expand data acquisition from new internal and external sources. We’re also excited to expand our use of Amazon Q in QuickSight, an AI-based BI assistant that makes queries and other data harvesting methods more straightforward and intuitive for our users. Finally, we’re exploring the benefits of adding a generative AI extension to QuickSight.

About the author

Rishi Ranjan is the Senior Director of Data Management at Common Securitization Solutions. He directs and oversees the design and development of data systems used for managing data from the Common Securitization Platform (CSP), an advanced cloud-based mortgage securitization platform. An expert in big data, real-time technologies, machine learning, and modern cloud data platforms, Rishi is an innovative leader with a talent for building cutting-edge enterprise-scale data products and services.

Tejas Thaker is a Customer Solutions Manager for Amazon Web Services. He has been serving Worldwide Public Sector (WWPS) Federal Financial Services organizations since 2017. His primary focus is to expedite customers’ cloud transformation journeys and maximize business value derived from cloud investments. Tejas is driven by a passion for assisting customers in solving complex problems and exploring novel use-cases leveraging data intelligence and the latest generative AI capabilities.

Neelima Veligeti is Data and ML Lead Architect at CSS.  She is an expert in using AWS services to build data and ML solutions. She has been working in the FinTech area for the past 15 years as a developer and solution architect for cloud and FinTech technologies. Neelima is passionate about how cloud, data and AI/ML can bring significant business transformation.

Madhan Merugu is lead Data & ML engineer at CSS, based in North Carolina. Madhan has a strong software development experience for the last 23+ years with focus on Data and ML technologies. His role at CSS is focused on design and development of complex data solutions. Outside work, he is a cricket enthusiastic and participates in local league matches.

Steve Doerrer is the Director of Securities Administration within the Business Operations group at CSS.  He has nearly 20 years of experience in the mortgage securitization industry in both operations and technology roles.  Steve and his team have benefitted from using data, process automation, and business intelligence products to improve operational efficiency and make more informed business decisions.