With the landscape of Real Estate companies growing and turning to Yardi’s expansive Application Stack and infrastructure to manage their business there is a growing need to get your data out of Yardi and into the hands of an analyst or power user via extracts or a data warehouse for analytical purposes.
Getting data back from any software as a service platform can be tricky especially due to proprietary rules. Unfortunately, out of the box reporting is never robust enough to meet your specific needs and the data you need is stuck behind their servers. Fortunately, there are two efficient ways to get your data from Yardi based on if you are using their service as their primary software as a service or if you choose their hosted private cloud option. To see which one is right for you, please reference this post.
How to get your data from Yardi
There are two primary options you can use. First, Yardi provides a mechanism to download your database backups from an SFTP they will configure for you. You can script a job using Python or Command line to do this for you and restore the SQL database locally. Second, if you hit a high enough property threshold and pay extra, they can grant you direct VPN access to your data.
Getting your data from Yardi Software as a Service (SaaS)
In Yardi’s default set up, the database and application are hosted on Yardi shared servers.
The databases are core SQL server databases with expected functionality from a SQL 2014 database or above.
In a nightly process, Yardi will back up your entire database and land it in a shared SFTP folder for your organization. This folder can only be accessed via an FTP tool which can be downloaded free of cost. This process should only be made available to a technical admin or a DBA within your organization. Even though no write backs can be committed and the files are backups, security and other concerns must be considered. The STFP can only be accessed with credentials set up on the Yardi side which must be requested for security reasons. Once setup you can move backups over to a local file location. Below are the general consideration for automaton of this process:
- You will need to create “.bat” file once you have configured a SFTP set up and schedule. Credentials will encrypted.
- Automate the “.bat” file to run the SFTP command line tool to pull the database back up file over to a local environment or where the data warehouse may live. This step can be time intensive based on the database size.
- You will need to install and set up LiteSpeed backup, a Dell product. This tool will be needed to decompress the backup file pulled over in the previous step. This may break the back up into a series of back up files.
- You will need an auto script restoring the database back up to a SQL server via SQL scripts. Dynamic naming will be needed based on decompressed back up file names.
- You will need to auto script setting up database properties, configurations and credentials for access as well as installing the encryption certificates.
This process can be set up and scheduled to run every morning usually after Yardi’s backups are completed.
Still need help with this process? Come reach out at www.crexsoftware.com for help!
Update!
There is a new method available which allows you to accomplish this same task more efficiently as well as more frequently. Please see the respective post on Getting Data from Yardi via Logshipping!
Get your data via SQL Logshipping!