Most of the basic CRE tools have a deep suite of reports and analytics baked in with standard reporting, but everyone’s business is unique, and everyone’s reporting and integration needs are slightly different. Data from a single property ERP usually needs to be combined with other data points from other systems such as Salesforce, Accounting Software, Construction Software and even static excel files. This is why most CRE organizations must use tools such as Power BI to conduct analytics outside of the core tools. With Yardi, the data is usually harder to get too and there is no ODBC connection, it is doable just with a few extra steps. Let’s review how to Connect Power BI to Yardi Real Estate Data.
If the contents of this post are a outside of your wheelhouse, don’t worry, this is why I started CREXchange.io! We have free Commercial Real Estate Power BI Reports to help clients get up and off the ground with Yardi Data Integration and Advanced Power BI Custom Reports to help run your CRE business. Contact us there for more details or a demo!
1. Export and Restore your Yardi Database backup locally
The first step to be able to analyze your data is going to involve getting access to your data. Most SaaS platforms including Yardi do not allow direct access to the database or data model but most do allow for you to export this data to your local instance to do as you please. You will need to build a process that imports and restores the back up database copy to a local SQL Server. This process can be tricky, but it can be done. See our whitepapers below to get more informational on this step. You will require a SQL server license and a virtual machine to host the back up.
2. Transform the Yardi data into a usable data model
There are thousands of tables in the database, most which you will never need. Extract the handful of tables you need for your data that pertain to occupancy, revenue, properties, tenants, units and accounting and others you may need and organize this data into a data model that can be easily sliced and diced. The model can be complex so be sure to use the right ID and ensure you have similar rules and logic tied to the type id’s and param to be sure you are getting the right data for the right timeline. If you need help with this piece, reach out for more help to get access to our proprietary real estate data model.
3. Connect Power BI do your Data Model
Once you have your data model created and a process to refresh and reload this data nightly, you are ready to connect your visualization tools to being building cool new reports, analytics, and visualizations. Using Power BI’s default SQL server connector, you can connect to your database weather it is on the cloud or on-prem and import the data into Power BI. Your data model should have the foreign keys establish so that it is inherited and be able to create reports and dashboards. Here is an example of one of our Reports that enables tenant and leasing data.
4. Connect Power BI to other data sources and combine to your Yardi real estate data
Having Yardi or Real Estate data is never enough. Now with Power BI, you can connect to numerous data sources in your business to begin to combine data too create analytics across all your business units and lifecycle of the real estate business.
Step 1 and 2 are the more technical heavy steps of the process. In previous post I have written white papers about getting data from Yardi nightly or even hourly using transaction log shipping, both available for download.
If the contents of this post are a outside of your wheelhouse, don’t worry, this is why I started CREXchange.io to help clients get up and off the ground with Yardi Data Integration and Advanced Power BI Custom Reports to help run your CRE business. Our reports are free with integration! Contact us there for more details or a demo!