Introduction
The client develops a web-based real estate management platform with specialized modules tailored to different industry roles. Property owners and asset managers gain insights to maximize profitability, while property managers, trustees, and marketers use the tool to enhance efficiency and drive business growth. Users import their data into this proprietary PropTech software, where it is stored across MongoDB and SQL databases, while the Abacus ERP system supplies users’ accounting data.
To further strengthen their analytics capabilities, icCube was brought in to help develop a data model that consolidates data from these three heterogeneous sources.
The goal was to merge these sources into a single, cohesive data model, enabling powerful analytics for a range of dashboards such as:
- occupancy
- device management (e.g., building washing machines)
- building/property insurance
- marketing (properties listed online for leasing or selling)
- budget reports
and more.
Technical Architecture and ETL Process
The project centered on consolidating data from MongoDB, SQL, and Abacus ERP into a single, cohesive multidimensional - OLAP - schema in icCube. A sophisticated ETL (Extract, Transform, Load) process was employed to manage the inherent complexity of merging these disparate sources, particularly addressing the challenging Abacus data model.
The complexity of Abacus is not only limited to tables and columns, it is also related that each customer of this PropTech company had a different database (different URL, user and password).
- Data Extraction
- With existing connectors managing MongoDB and SQL, a new icCube connector for Abacus ERP was developed to navigate its intricate data model, ensuring that even its non-standard structures were accurately captured and integrated.
- Data Transformation
- The transformation phase relied on advanced ETL joins and custom Java code to harmonize the data. Key tasks included:
- Reconciling differences between the flexible schemas of MongoDB and the rigid schemas of SQL and Abacus ERP. This required deep alignment of keys and data types across systems.
- Standardizing data formats and addressing inconsistencies, including managing missing or anomalous values, i.e. data cleansing.
- The transformation phase relied on advanced ETL joins and custom Java code to harmonize the data. Key tasks included:
- Semantic Layer
- The semantic layer translates technical field names and relations into comprehensive terms that are easy for a business user to understand. Carefully setting up this layer provides end-users with intuitive, context-rich labels on dashboards and reports, ensuring that data insights are easily interpretable by business stakeholders, removing the need for any technical data knowledge.
- Aggregation and Metric Derivation
- Pre-calculating metrics and performing aggregations to support rapid queries, enabling robust slicing and dicing across multiple dimensions.
The model was meticulously designed to support complex analytical queries and ensure high performance. Correctness is ensured by the model and a layer of checks when loading data. The integration of these diverse data sources resulted in a comprehensive model that offers a single source of truth for operational and financial insights.
Integration
The visual integration is completely seamless—embedded directly within the client’s application. Users interact with intuitive dashboards that blend naturally with the PropTech software’s overall look and feel, unaware of the complex analytics processing happening behind the scenes.
User-Specific Security
In the typical embedded analytics setup, authentication is handled by the PropTech software, while authorization - of the analytics and data features - is managed by icCube through parameters sent by the host application. The system takes into account source-specific security measures from all three raw data sources, ensuring that every user only accesses data pertinent to their role. This layered security approach not only integrates seamlessly with the client’s existing platform but also maintains stringent control over sensitive data at both the application and data layers.
For example, each user can see a different list of properties and has access to a given set of dashboards (ranging from occupancy, marketing, insurance, finance, etc) with its own data.
Dashboard Diversity
Each dashboard aggregates a combination of data from MongoDB, SQL, and Abacus ERP model, providing a holistic view of the client’s operations. The PropTech application now offers a variety of standard dashboards—covering insurance, devices, marketing, occupancy, and more. Users can slice and dice data across dimensions like time, geography, device type, insurance type, etc.
Additionally, the client has the ability to create new dashboards leveraging the advanced analytics model with their own metrics—giving them the freedom to customize and differentiate their business.
Conclusion
The integration of MongoDB, SQL, and the complex Abacus ERP data into a single data model in icCube demonstrates a robust solution for addressing the analytical needs of a PropTech client. Through a rigorously engineered ETL process and a specialized connector for Abacus, the solution achieves powerful and flexible data analytics. This model not only simplifies data access and analysis but also upholds critical security standards, providing a scalable foundation for future enhancements in the PropTech domain.
Why doing all this? The Doggy bag
The benefits of creating such a model :
- Data connection: Directly integrate data from MongoDb, Abacus and MySQL into a single schema
- ETL: Make complex data transformations and checks
- On-the-fly security: ensuring each user could see its data
- Advanced analytics: build metrics by mixing the data together
- Dashboards: build custom dashboards users can interact with, slice and dice the data
- Integration: seamlessly embedding the dashboards into their application