Data Warehouse Helps Client Make Better Business Decisions

Massive, time-consuming spreadsheets were falling short of data management and reporting needs

Complex data warehouse built with data organized and staged to make queries and reporting fast and accessible

Our leasing and financing client serves customers in construction, healthcare, refrigeration, and other industries requiring large equipment. In part one of our success story series, we discussed how Chateaux’s Digital Transformation team created the UI behind their new Broker Portal web app. In part two of our series, we explored how Chateaux used microservice architecture to support the Broker Portal. In the third and final installment of our three-part success story series, we look at how Chateaux’s Data Management Team solved data accessibility and reporting issues using a data warehouse solution.

Our equipment leasing and financing client’s data was not easily accessible. The client relied on multiple enormous spreadsheets, fed monthly from various source systems, to report and submit to banks for month-end reporting and funding requirements. Each spreadsheet took dozens of hours to do, including ETL within the spreadsheet that was time consuming, difficult to manage, and hard to enhance. Requests often required a technologist to pull data into spreadsheets to fit the needs of individual business users.

The client needed a flexible solution that provided up-to-date information and covered a wider range of reporting needs.

Thanks to our experience working with the client on other IT endeavors, as well as our competency in data management, the client selected Chateaux to provide a data warehouse subject matter expert to generate detailed requirements and data mappings, configure their business intelligence (BI) infrastructure, design and develop data warehouse data structures, perform ETL automation, and support testing and training activities.

Chateaux’s top priority was to replace the existing spreadsheets with a more comprehensive reporting solution.

Our Data Management Team created a data warehouse to enable strategic reporting. It incorporates source data from the client’s current application management system, legacy application management system, and contract management system, plus ancillary files for further historical reference. Information from a recently acquired company was also incorporated by tying into the applications and contracts used by that entity.

Chateaux used a conceptual-to-source spreadsheet to capture the critical business terms used by the client and mapped the entities to the source systems. In addition, the team defined each item, tracked current methodology or logic needed to capture data for that term, implemented each term into the new data warehouse, and tracked the testing status of each term, which was managed by the client to ensure each term was properly implemented.

The data model design focuses on the reporting model, not the sources, to reflect the business as a whole. Chateaux built this solution using a Microsoft stack that included Azure Cloud, Azure Data Factory, Azure SQL server, and Power BI.

After evaluating the breadth of data available and recognizing the value it could provide, the client tasked the Chateaux team with expanding the number of calculations to support a wider array of reporting needs.

Our client’s existing CRM systems were great at collecting, managing, and storing data but could not provide extensive strategic reporting or reporting that incorporated data across sources, resulting in the reliance on massive, time-consuming spreadsheets.

Because of the new data warehouse solution, the client’s data can now refresh daily, allowing unified information to be shared across all business teams for more accurate, actionable insights. The client can evaluate data internally and present it to associated vendors and partners, such as financial institutions, with greater confidence in the information. Plus, Power BI allows the story of the data to be communicated more effectively using stunning visualizations, such as maps and graphs. Customization of fields also improved, adding exponential value. Plus, internal staff previously tasked with creating spreadsheets can now focus their time and expertise on writing reports and creating content.

An additional benefit includes how the data warehouse streamlines the acquisition process. As a growing company acquiring other businesses, the legacy system required layering in more spreadsheets with disparate data. With the new data warehouse, the client can integrate new acquisitions into the business with greater care, providing better data insight across the board.

The client is a large/heavy equipment financing company providing small businesses and sole proprietorships with the financing they need to purchase essential use operating assets. The client works with third-party referral (TPR) broker sources across the USA to finance commercial leases and loans ranging from $15k to $300k in value. The company works with A to C credits, finances many types of assets, and is an “application only” lender.