Problem Statement:
To build a data warehouse for PowerBI reporting purposes having source as Business Central and destination as Azure SQL Database.
GitHub
Tools and Technologies:
- Azure Data Factory, Microsoft Azure SQL Database, SQL Server Management Studio, Logic App, Azure Key Vault, Business Central, OData
Summary:
- Designed and Implemented the Data warehouse from scratch using pipelines from Azure Data Factory.
- Implemented the dynamic structure to handle data from different Companies of Business Central.
- Implemented the strategy to load Incremental and Reloading Data.
- This DW helped to create a robust and expandable reporting platform to facilitate ongoing report changes and creation by designated end users.
- Created Data Sets (SQL views) to boost BI reporting.
- Implemented the Star Schema.
- Handled the SCDs and implemented SCD Type 2.
Explanation:
- Created a data warehouse in Azure SQL using Azure Data Factory (ADF) Pipelines.
- Replicated all the tables from Business Central (BC) in Azure SQL using Copy Data Activity of ADF. Used OData Web Services (Queries) of BC for this integration.
- After creating the table schemas wrote Stored Procedures to create and fetch OData Filter Expressions.
- Categorized the data in two Forms: Reloading (Tables having Less Data) and Incremental (Tables having Huge Data).
- Created OData Filter Expressions for Incremental data reload.
Triggered the pipeline to run this process twice a day.
