A leading real estate firm specializing in the multi-family residential sector needed a platform to consolidate data across multiple source systems. The firm is currently leveraging the Yardi Property Management software to manage the aspect of all real estate operations, as well as Salesforce CRM, Argus Enterprise for valuation and asset management, and data from an internally built HR application.
The firm was facing several challenges associated with data not matching between multiple reports, questions on how certain metrics were being calculated, and consolidating data for reporting and analytics.
An opportunity to introduce a consolidated data warehouse was identified by the firm in order to streamline data processes and automate the firm’s reporting process.
Nivis Solutions identified an architecture that would eliminate these challenges and allow the firm’s resources to focus on the more important duties in their role. The solution consisted of leveraging the Microsoft Azure Platform to connect and harvest data from each of the key source systems, transform and load into a Snowflake Data Model, and allow for the presentation of the data in the Microsoft Power BI reporting tool. The illustration below depicts the data from the source systems through to the Power BI outputs utilized to visualize the data.
The Data Sources consisted of a cloud-based Yardi Property Management System, Argus Enterprise, Salesforce, and an on-prem custom built application consisting of the firm’s HR Data.
Each source presented a different method of connecting and extracting the data needed for the ultimate data warehouse. Yardi was a secure direct connection to a cloud database, Argus connectivity utilized API’s, Salesforce consisted of a cloud connection to the firm’s Salesforce instance, and the HR data consisted of On-Prem .csv files.
The Microsoft Azure Data Factory service was utilized for the purpose of extracting raw data from each of the source systems and stored into a single Microsoft Azure Blob Storage account. A series of Delta processes needed to be developed for a number of result sets due to the volume of data.
Data Storage / Data Model
The Snowflake database technology was selected due to a number of reasons including
Nivis Solutions designed a fact dimension data model that supported multiple departments within the firms organization. Finance, Operations, Marketing, and HR groups were all identified as the consumers for the Snowflake database.
As the organization continues to grow and introduce additional sources, Snowflake will scale to support the volume and compute resources required to provide data for analytics. The security features within Snowflake allowed for securing the data at multiple levels (schema level, table level, and column level) to ensure users were only provided data access to results sets needed for their role.
The data is stored in an Azure Blob Storage account and keeps Snowflake updated leveraging the Snowpipe service. Multiple data models were created to support reporting across each of the key departments within the organization.
The Microsoft Power BI visualization tool is being to provide dashboards to the individual analytics teams across the firm.
Ready to make the switch to Snowflake? Contact us to get started.