Architecture

1. Overview

This document describes the architecture of the Centrify 360 Business Intelligence solution. The system is designed to extract, transform, and load (ETL) data from Microsoft Dynamics 365 Business Central into a reporting-ready star schema model in a PostgreSQL database. The solution supports incremental data processing to ensure efficiency by handling only new or changed data. Power BI is used for visualization and reporting on top of the stored data.
Support will be added later on for another target data warehouse (Google BigQuery)

2. Components

The BI solution consists of the following key components:

2.1. Source System

The source system is Microsoft Dynamics 365 Business Central, a cloud-based ERP solution. An extension is integrated with Business Central to push data to the orchestrator. This extension identifies and extracts relevant data increments (new or modified records) and forwards them downstream.

2.2. Orchestrator

The orchestrator acts as the ETL (Extract, Transform, Load) component. It receives data from the source system’s extension, performs necessary transformations (e.g., data cleansing, aggregation, and formatting), and loads the processed data into the target database. The orchestrator ensures the data conforms to the star schema model, which is optimized for analytical queries and reporting.

2.3. Target Database

Currently, PostgreSQL serves as the target database, storing data in a star schema model. This model includes fact tables (containing measures) and dimension tables (containing descriptive attributes) to facilitate efficient querying. In the future, this may be migrated to BigQuery for enhanced scalability and cloud-native features.

2.4. Reporting Tool

Power BI connects to the PostgreSQL database to load data. It includes predefined views, measures, and visualizations built on top of the star schema. Users can create interactive reports and dashboards for business insights.

3. Data Flow

The data flow is incremental, meaning only new or updated data is processed to minimize resource usage and latency:

  • Data changes occur in Microsoft Dynamics 365 Business Central.

  • The extension detects these increments and pushes them to the orchestrator.

  • The orchestrator transforms the data and loads it into the PostgreSQL star schema.

  • Power BI refreshes its datasets from PostgreSQL to reflect the latest data in reports.

This process ensures data freshness without full reloads.
The extension uses Business Central system fields System Modified At and System Created At and Deletion Changelogs to detect changes and push incremental data.
The process can be run at any interval (Daily, Hourly, ..) which will achieve near real-time behaviour.

4. Architecture Diagram

This diagram shows the directional flow from the source to the reporting tool, highlighting the incremental nature of the process.

Architecture Diagram

5. Incremental Logic

The entire pipeline operates on an incremental basis:

  • Detection: The extension in Business Central monitors for changes (e.g., via change logs and delta queries).

  • Processing: The orchestrator applies transformations only to the incremental data, merging it with existing records in the target database.

  • Loading: Upserts or inserts are performed in PostgreSQL to update the star schema without duplicating data.

  • Reporting: Power BI datasets are refreshed incrementally where possible, ensuring reports reflect the latest state efficiently.

This approach reduces data transfer volumes, processing time, and costs, especially as the system scales.