Welcome to part one of our three-part series on how to do more with your Yardi data. Written by Vimal Vachhani, a senior data architect at Saxony Partners, this series focuses on how data can be extracted from Yardi and used to create actionable reports and give you a competitive advantage.
In this edition, Vimal provides step-by-step instructions on how you can get your data from Yardi by utilizing software as a service (SAAS) and/or the Private Cloud environment.
Real estate companies are increasingly turning to Yardi’s expansive Application Stack and infrastructure to manage their business. This creates a need to get 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, due to proprietary rules. Usually, the out-of-the-box reporting they provide is never robust enough to meet your specific needs. The data you really need is stuck behind their servers.
Fortunately, there are three efficient ways to get your data from Yardi -- based on whether you are using their primary software as a service or if you choose their hosted private cloud option.
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 2008 database or above. Each night, Yardi backs up your entire database and places it in a shared FTP folder your organization can access. This folder can only be accessed via their FTP tool, yFx, which is provided free of cost. (Note: This tool should only be made available to a technical administrator or a DBA within your organization.)
Though the files are backups and no write-backs can be committed, security and other concerns must be considered. The yFx tool can only be used with credentials set up on Yardi, which must be requested for security reasons. With yFx, you can connect to Yardi’s FTP and move backups over to a local file location. (See addendum at the bottom of this article.)
Getting your data from Yardi Private Cloud
In the Yardi Private Cloud (PC) environment you will realize a few benefits that you do not have in the SaaS select environment. This includes:
- Increased speed due to dedicated hardware
- Increased customization of the Yardi Voyager application
- A VPN tunnel for ease of integration
The VPN tunnel is a key reason why Yardi customers, especially those who are forward-thinking and interested in analytics, may choose to pay for the Yardi PC environment. The VPN tunnel affords the customer the ability to access both the live and test databases through Extract Transform and Load (ETL) tools (including Informatica Power Center, SQL Server Integration Services, Data Stage, Talend Open Studio, etc.) Any of these tools and others will allow you to have direct access to the database tables on the Yardi PC server.
Once the switch from SaaS select to Private Cloud has been made, take the following steps to have direct access to the data in your Yardi application:
- Confirm your Yardi Private Cloud contract allows for a VPN tunnel. (If you have multiple database landing environments you may need multiple VPN tunnels – so, ensure you have contract terms that allow for this as well.)
- Request a networking survey for each VPN tunnel from Yardi PC team. Complete and return it to Yardi.
- Coordinate a time to set up each VPN tunnel with Yardi support.
- Once the tunnels have been properly configured, your database team should be able to ping the Yardi database servers and directly connect to them through SQL Server Management Studio or other relational database management system software applications.
- The database team should be able to create an automated ETL pull from the Yardi database through one of the ETL tools mentioned above.
There are a few things to keep in mind once this level of access has been opened.
First, you may pull data from Yardi as frequently or infrequently as you like – however, it is recommended that data is only accessed when needed, as more frequent pulls can result in performance degradation on the Yardi application. Aim for one to five extracts a day for slower changing real estate data; do not exceed this limit.
Second, reporting needed at a higher frequency should be built as operational reporting, leveraging Yardi’s built in reporting capabilities.
Third, note that VPN tunnels are not 100 percent reliable. Monitoring should be set up to view the status of the VPN tunnel (See Fig. 1).
Fourth, Yardi data can be directly accessed from any server inside of the sub-net that has the VPN tunnel. This allows for ease of use by developers, but also means that writes to the live database can be made. Be careful!
Finally, gathering data from Yardi and its many modules can be very challenging. Once the data has been acquired, piecing it together in a meaningful way can be even more challenging. Consider partnering with a firm with the proper expertise to help acquire and interpret Yardi data.
Yardi integration will never fully solve all operational reporting requirements, but is a vital piece to building insights, accumulating actionable analytics, and driving your business away from reactionary.
Addendum: Connecting to Yardi FTP through yFx
Configure FTP set-up and schedule, then create a “.bat” file in the yFx tool. yFx will encrypt and stave the credentials, as well selected settings.
Automate the “.bat” file to run the yFx tool, pulling the database backup file over to a local environment or to where the data warehouse may live. This step can be time intensive.
Install and set up LiteSpeed backup to decompress the backup file pulled over in previous step. (May break the backup into a series of backup files.)
Create auto script restoring the database backup to a SQL server via SQL script. Dynamic naming will be needed based on decompressed backup file names.
Create auto script setting up database properties, configurations and credentials.
This process can be set up and scheduled to run every morning (usually after 1 a.m., once Yardi’s backups are completed). See diagram to the left.