PowerPivot and Real Estate

During these challenging economic times our ability to understand operational trends can quickly translate into improved performance through proactive vs. reactive management. Yet our property systems are too often buried under layers of transaction level details to provide useful information.

Despite our enormous investment in enterprise level solutions Excel is still the predominant business analytical tool used in the real estate industry today. Furthermore, Excel spreadsheets that drive key investment level decisions are often created from scratch re-entering system data.  Over a decade ago the Data Consortium’s, our industry’s original data standards board, put forth a motto of “write once, use often”.  It seems we’ve learned little from the wisdom espoused by Mike Young which was a cornerstone in the formation of OSCRE and now ten years later the ongoing push for global standards.

With the release of Power Pivot, available with Excel 2010 or SharePoint 2010, we finally have an arrow in our quiver worthy of William Tell’s marksmanship. Power Pivot is Microsoft’s first foray into self-service Business Intelligence (BI) for the masses removing dependency on report writers and developers. Without over simplifying it we have three elements to consider; a data source, a data set and how we want to slice our data.

In PowerPivot once connected to your data source, in this case a SQL 2005 database, you will need to decide what tables and fields you want to analyze.  Depending on your level of knowledge Power Pivot will provide the option of using an Import Wizard or specifying a SQL Query.

• Once your data elements have been selected PowerPivot will return the respective data in an Excel format as shown below. 
•  Then from the Pivot Table menu select an appropriate display format.

 

• In this example we’ll select the chart and table option which also will display the raw data. 
• To the right of the screen Excel’s Pivot Table control window will be displayed. 
• Place a check box near each field you’ll want to include in your chart and drag them into the Values pane.
• Then place a check near each field you’ll want to slice your data with and drag them into the Slicer pane. 

• You will then see the results displayed in your Excel worksheet as shown below.
• By clicking on slicer tabs in the left column your can apply ad-hoc filters searching for trends.
• You can continue to apply multiple slicers providing a more granular portfolio insight.

• Another ad-hoc capability is redefining your chart’s axis.
• In this case, to analyze your results by Asset Manager we simply dragged that data element into the Axis pane.
• Suddenly we are now comparing the performance of each Asset Manager by a variety of measures.
• This could also be used to compare properties in a portfolio by a specific expense category or market, the possibilities are endless.

• This Power Pivot table can also be published to a SharePoint site from the File menu.

PowerPivot and SharePoint in tandem provide and improved user experience worthy of  “C” level dashboards, but we’ll save that for another day.

Tagged: Excel 2010, PowerPivot, Real Estate

Posted in: Business Intelligence, Microsoft Office, PowerPivot, SharePoint