BTB, is a real estate investment trust that understands the importance of data and its powerful impact on strategic decision-making. That's why BTB implemented a comprehensive reporting system several years ago.

However, this system had its limitations, since it required a lot of manual action, which increased the margin of error and wasted a lot of the company's time and energy.

BI assets with limited scalability

BTB was using on-premise technologies to manage its BI solution based on a SQL Server relational database.

This database was updated once a day from the operational database of its ERP and through manually generated SQL scripts. In addition, Power BI reports then exploited this same database.

BTB's business intelligence needs

BTB chose Azure technology that would allow its IT staff to focus on its business without having to worry about in-house capacity or maintaining overtaxed or under-utilized equipment. The platform would enable BTB's existing applications to be deployed quickly and easily with little or no downtime.

BTB needed to resolve the time and cost disadvantages of their legacy system, so they turned to Momentum, a Microsoft Gold Partner, to help them resolve the disadvantages presented and benefit from a new cloud BI solution that:

• Enables processing of data from on-premise databases, Excel files, SharePoint lists and any other data source;
• Stores processing results in a multi-dimensional data warehouse optimized for analysis;
• Keeps history of ingested sources;
• Ensures historization of loaded data;
• Offers a dynamic, high-performance and easy-to-use reporting and dashboarding solution;
• Offers a robust, secure and scalable solution.

A new BI solution from Momentum Technologies

The solution implemented by Momentum Technologies enables data to be processed, whatever its source. Raw and refined data are stored in a data lake, and all ETL processing results are stored in a multidimensional data warehouse optimized for Azure Synapse-based analysis.

Several technologies were used to implement the solution. Microsoft cloud infrastructures were leveraged with Software as a Service (SAAS) products to meet the various needs of data integration, storage and exploitation.

Here is a list of the technological components proposed by Momentum Technologies:

• Azure Data Lake Gen2: data storage in HDFS-compatible Azure (Hadoop Distributed File System). Data is compressed to optimize storage and minimize costs. The data lake contains two storage zones:
• Bronze zone: raw data from different systems and files without any alteration.
• Silver Zone: cleansed, harmonized and compliant data.
• Azure Data Factory: Data management and processing.
• Azure Synapse Analytics: Data warehouse.
• Power BI Service: Analysis, visualization and restitution of information in the form of reports and dashboards.
• Azure Active Directory: Enterprise directory.
• Azure Key Vault: Store security information (service accounts, core services, encryption keys).

Azure: robust, secure governance

To ensure governance, Azure offers mechanisms and processes for maintaining control over different applications and resources. Azure provides a way to effectively manage access, policies and compliance for subscriptions.

Azure administration groups provide a level of scope beyond subscriptions. Subscriptions are organized into containers called “Administration Groups”, to which governance conditions can be applied. All subscriptions within an administration group automatically inherit the conditions applied to that administration group.

In the case of BTB, and in order to minimize costs, the choice of back-end architecture was made to use only one administration group with one subscription. The various environments are managed by resource groups.

The administration group is used to manage access, policies and compliance (avoids granular management if there are several subscriptions). The rules defined apply to the resource groups included in the administration groups.

A maintainable, scalable solution

The first stage of the solution was to ingest data from all sources. Azure Synapse Analytics ingestion pipelines are designed using several linked services, one for each data source. This raw data was then stored in a datalake container (Zone Bronze) to preserve the history and be able to reproduce the constitution of the data model in the event of an incident.

Based on the business rules supplied by BTB, a star decision model was set up: the physical tables created are loaded into the dedicated SQL pool via Synapse Analytics pipelines. The pipelines for ingesting, loading data, starting and stopping the SQL pool are optimized to minimize execution time and thus reduce processing overheads, an area of expertise that Momentum Technologie has mastered in its own right.

A Git was implemented to manage the different versions of the code by development branches. This enabled developers to parallelize their work while ensuring code evolution. Finally, Devops pipelines were created to migrate code between different environments.

A secure solution

For Reporting, the decision was made to use Power BI Service with PRO licenses, while integrating user and workspace management strategies. To achieve this, three types of user have been categorized. Each user can have up to 4 different roles in the workspaces:

.

In addition, to reinforce security, conditional access has been enabled on the BI service and Azure synapse.

User-friendly dashboards and reports pointing to the BI data warehouse have been implemented to make BTB's relevant data accessible whether on the web, mobile, or even on Teams. This enables BTB managers to get a clearer picture of the assets under management, and make the best decisions when and where they are needed.

Conclusion: Cost and time savings

Thanks to its sound strategy, BTB has succeeded in having its own 100% automated decision-making system. This approach enabled the company to reduce costs and save processing time, while adopting state-of-the-art technology that is efficient, secure and easy to maintain.

Following the success of this experiment with its real estate assets, BTB has chosen to rely once again on the expertise of Momentum Technologies to enrich its new BI system with other business areas, notably finance.

Written by Mohamed Amine Ben Romdhane BI Expert