First Project Walkthrough
Summary
The Getting Started with BimlFlex is an end to end, step by step guide to building a Data Warehouse using BimlFlex.
The getting started documentation implements an on-premises installation and uses a local installation of BimlFlex and BimlStudio for development.
BimlFlex supports SQL Server 2008-2019 as well as Azure Synapse and Snowflake and both SQL Server Integration Services and Azure Data Factory. The examples here use SQL Server 2017 for hosting databases, uses SSIS for the load process and uses the Microsoft AdventureWorksLT sample database as a source.
The getting started process demonstrates a 3 layer approach with staging, Data Vault, and Data Mart layer.
This uses a staging layer with both a transient staging database and a persistent staging (archive) database. The Data Vault layer illustrates how BimlFlex allows agile acceleration of an integration layer for modern data warehousing. For easy reporting and analytics, the architecture is completed with an analyst-facing dimensional model.
The following is needed:
- Windows-based development machine for local installation of BimlStudio and BimlFlex
- SQL Server 2017/2019 with SQL Server engine and SSIS/Integration services
- Visual Studio 2017/2019 with SSDT and SSIS components
- SQL Server Management Studio or similar for managing databases and running SQL Scripts
- Excel 2013 or later, if the Excel Metadata management add-in is used
Setting up the AdventureWorksLT source database
Once the installation is completed and the databases are available, it is time to create the metadata customer/project that will be used in the getting started process. Once the project is available it is time to load some sample metadata to get started.
More information: Setting up the AdventureWorksLT source database
Load Sample Metadata
The BimlFlex App includes ready-made sample metadata that can be loaded into the project.
There are several sample metadata sets available for different architectures.
This walkthrough uses 01 - MSSQL Starting Point
More information: Load Sample Metadata
Import of source metadata
Source metadata management and modeling is done through the BimlFlex App. It makes it easy to import the AdventureWorksLT source metadata into the metadata repository.
More information: Importing Source Metadata
Data Vault Acceleration
Graphical, agile data vault modeling and acceleration is done through the BimlFlex App. This allows the modeler to create the target Data Vault model with ease.
More information: Accelerating the Data Vault
Build the Data Vault Project
Once the metadata meets the data warehouse requirements it is time to use BimlStudio to build the databases, tables, scripts and load packages for the Data Warehouse process. This includes creating the table and load scripts and building SSIS packages.
More information: Building Databases, Tables and SSIS packages for source to staging
Applying Load Parameters
To load only new data every time the load process is run, add load parameters where needed. The Parameters function in BimlFlex allows easy adding of high watermark load parameters to the sourcing process.
More information: Applying Load Parameters
Business Data Vault Model
The Point in Time and Bridge table structures are used in Data Vault to make the Data Vault easier to query and to improve query performance.
More information: Adding the Business Data Vault Model
Dimensional Model
The Dimensional model is built on top of the Raw and Business Data Vault model. By using a view-based abstraction layer between the tables and the Data Mart load it is possible to more easily accommodate future changes and optimize the sources for the Dimensions and Facts.
More information: Data Mart Dimensional Model