Client

The client is an integrated developer and operator of retirement villages specialising in affordable, stylish, and secure independent living communities throughout Australia.

Purpose

The client required an interactive dashboard to analyse the resident demographics and identify trends. The project would be an end-to-end service with the dashboard connected to a proposed cloud-based database and accompanying web-based interface.

• Dashboard – feature an operational, interactive BI dashboard to allow for comprehensive analysis and to help users draw actionable insights from the underlying data.

• Analytical Depth – help the client understand resident demographic trends and other socio-economic factors in local catchment areas. Analysis of this would guide pricing models and average tenure expectations.

• Front-End Interface – include a user-friendly web-based front-end design to access the dashboard and database.

• Various Levels of User-Permissions – account for various roles with databases users permitted to varying degrees of data access (read-only, edit, add entries etc.).

• Flexibility – be designed to cater for future dashboarding reporting and enhancements as more resident-specific datasets are collected.

Approach

Microsoft Power BI was chosen to be BI software for the interactive dashboard as the client had previous experience with the software. In-built MySQL connectors linked Power BI to the underlying database and a series of reports were developed. Reports in the dashboard include resident demographic analysis, portfolio summary, financial analysis, price and volume analysis and an overview of future developments. Power BI’s drill-down and drill-through functionality were important features that helped consumers analyse the data at various levels of detail and to gain confidence in their decision-making as a result.

Based on the client’s database requirements, data volume and data type, an AWS RDS instance of MySQL was selected. A database schema and ER diagram were designed, keeping in mind the principles of data normalisation. Parent-child relationships were clearly defined via foreign keys.  Procedures were written for individual actions (insert, update etc.) for each table to ensure adequate data access rules were enforced. Audit logs were created for any edits on the main entities and a periodic snapshot was scheduled for automatic execution.

The front-end was designed in Django and provided a user-friendly way for the datasets to be accessed. Front-end security validation was conducted to ensure any data access was according to a user’s privilege configuration. During data entry, data validation was done at the front-end as well. This provided a robust mechanism to ensure clean data was stored in the database. The front-end provided additional features like file-upload and preview and CSV download.

Outcome

The client received a web portal that was hosted on a sub-domain of their main website. The portal is to be used as an internal tool by the Finance team to track their cash flow and by the Operations team to manage the lifecycle of a resident.