Metadata Types
BimlFlex has a number of internal static types that are used to classify metadata. They can also be referred to as an enumeration type (or 'enum'), and represent the constant values that can be used in the App.
Attribute Types
Attribute Types are applied using the Attribute Editor and govern the scope for which the Attribute is applied. Selecting the attribute type will then show available options for the selected type.
The following options are available:
| Attribute Type | Code | Description |
|---|---|---|
| Batch | BAT | A Batch in the BimlFlex App. |
| Column | COL | A Column in the BimlFlex App. |
| Connection | CON | A Connection in the BimlFlex App. |
| Customer | CUS | A Customer in the BimlFlex App. |
| Object | OBJ | An Object in the BimlFlex App. |
| Project | PRJ | A Project in the BimlFlex App. |
Change Types
Change Types are applied using the Column Editor and drive the behavior and purpose of the Column for the design and code generation process. The available change types are dependent on the way the Connection is used in a BimlFlex Project. Depending on the connection's Integration Stage some options may, or may not be, available.
The following options are available:
| Change Type | Code | Description | Applies To |
|---|---|---|---|
| Update | CHG | Assigns the column to track the latest value assigned to the column. Also used as default for attribute columns. In Dimensional Modeling, this corresponds to a 'Type 1' column. | All integration stages, any column. |
| Key | KEY | Indicates that the column is treated as a key value in downstream processing. It is part of the object's Primary Key definition for all intents and purposes. | All integration stages, any column. |
| Ignore | IGN | Assigns the column to be ignored from all processing. This means that the column will not appear in the object and any data logistics associated with it. | All integration stages, any column. |
| Track History | HST | Assigns the column to track historical changes for the column. In Dimensional Modeling, this corresponds to a 'Type 2' column. | Columns for an objects belonging to a connection with a Data Mart integration stage. |
| Change Data Capture | CDC | Indicates this column is used for historization in Data Marts. | Columns for an objects belonging to a connection with a Data Mart integration stage. |
| Hash Distribution | HDK | Assigns the column to be used for hash distribution when using Synapse objects. | All integration stages, any column, but only when used for Synapse connections. |
| Transient | TRS | Assigns the column to be used in relevant data flow tasks, but not to persist the information. Used when a column value is needed for an expression but the value of the column itself is not needed to be persisted. | Any object used in a project with the SQL Server Integration Services (SSIS) integration template without pushdown processing enabled. |
| Exclude DV | EXC | Processes the column into the Staging Area and Persistent Staging Area (if configured), but does not process/accelerate the column into the Data Vault. | Columns for an objects belonging to a connection with a Source System integration stage, any object. |
| Link Reference | REF | Defines what columns will be used to define Surrogate Keys for the associated Link. These values will be pre-calculated (pre-hashed, if hashing is used) and saved in the Staging Area, but not in the Persistent Staging Area (if configured). Generated by BimlFlex during acceleration to map source columns to individual Link Surrogate Keys. | Columns for an objects belonging to a connection with a Source System integration stage, any object. |
| Link Degenerate | DGA | Assigns the column as part of the Unit of Work for a Link. The column will be included in the hashing of the Link's Surrogate Key, and will be added to the Data Vault Link object. This is also known as a dependent child key. | Columns for an objects belonging to a connection with a Source System integration stage, any object. |
| Link-Satellite Reference | LSR | Defines what columns will be used to define the Surrogate Keys for the target Link Satellite. These values will be pre-calculated (pre-hashed, if hashing is used) and saved in the Staging Area, but not in the Persistent Staging Area (if configured). Generated by BimlFlex during acceleration to map source columns to individual Link Surrogate Keys. | Columns for an objects belonging to a connection with a Source System integration stage, any object. |
| Stub Hub | STH | Designates an object to be created as its own Hub using the Data Vault Accelerator, with a Link to the main Hub that will be created from the source object. The column on which Stub Hub is applied will be used as Business Key / Surrogate Key definition. | Columns for an objects belonging to a connection with Source System integration stage, any object. |
| Driving Key | LDK | A Driving Key is a special column that is used in Data Vault to drive custom historization in Link Satellites. This occurs when changes in Link key values must trigger a change record in the Link Satellite. | Columns for an objects belonging to a connection with a Data Vault integration stage. |
| Multi Active Row | MAK | Assigns the column to be a multi-active value for the associated object. Used to determine the grain of a Multi-Active Satellite. This column must also be part of the Primary Key, together with the Integration Key. | Columns for an objects belonging to a connection with Source System integration stage, any object. |
Configuration Attribute Types
Configuration Attribute Types are the allowed values to configure the attribution of a Configuration to a specific area of the data solution architecture. This is defined using the Configuration Editor and governs the location in the data logistics process for which the Configuration is applied.
Each configuration property will allow a list of values to be selected, which impacts how the configuration is applied to the in-scope objects.
| Attribute Type | Code | Description |
|---|---|---|
| Ignore | IGN | Set the Configuration Attribute to Ignore to make sure the configuration does not apply to the assigned area. |
| Derived | DER | Derive the configuration using the value specified in the appropriate Expression column. Which expression column will be used depends on the Integration Template used. |
| Source | SRC | Use the value as is available from the source object. This column needs to exist in the source and have the same name as specified by the Configuration Value column for the configuration in the source object. |
| Default | DEF | Apply the value specified in the Configuration Default field of the Configuration as a default value / constraint for the Objects the configuration has been defined for. Note that this only applies to SQL Server, Managed Instances and Azure SQL because these database technologies support having default constraints with expression (example: GETDATE()). |
| Target | TGT | Defined by the Target. |
| Hash | HSH | Apply hashing logic when loading the value. |
Connection Types
Connection Types are configured using the Connection Editor to define what kind of data source or target is connected to.
| Connection Type | Code | Description | Applies To |
|---|---|---|---|
| ADONET | ADONET | An ADO.NET connection is used to connect to the data. | Any Connection. |
| Azure Blob Storage | AZB | A connection to an Azure Blob Storage Account. | Connections with a Source System, Landing Area, or Staging Area integration stage. |
| Azure Data Lake Store | AZDLS | A connection to Azure Data Lake Storage Account. | Any Connection except if defined with integration stage Data Warehouse. |
| Custom Component | CUSTOM | A connection to a custom component data source (COZYROC). This is limited to connections that are used in Projects that have a SQL Server Integration Services (SSIS) integration template. | Connections with a Source System integration stage. |
| FILE | FILE | A connection to a file. | Connections with a Source System integration stage. |
| FTP | FTP | An FTP connection is used to connect to the data source. | Connections with a Source System integration stage. |
| Microsoft MDS | MDS | A connection to Microsoft's Master Data Services (MDS). | Connections with a Master Data Services integration stage. |
| ODBC | ODBC | An ODBC connection is used to connect to the data. | Any Connections except ones with a Landing Area integration stage. Allowable System Types will be limited depending on the integration stage applied. |
| OLEDB | OLEDB | An Object Linking and Embedding (OLE DB) connection is used to connect to the data. | Any Connection. For connections with a Source System there will be additional System Types available. |
| ORACLE | ORA | A connection to a Teradata data source. | Connections with a Source System integration stage. |
| REST | REST | A connection to a Rest API endpoint. | Connections with a Source System integration stage. |
| Script Source | SCSRC | A connection to a script task data source. This is limited to connections that are used in Projects that have a SQL Server Integration Services (SSIS) integration template. | Connections with a Source System integration stage. |
| SFTP | SFTP | An secure FTP (SFTP) connection is used to connect to the data. | Connections with a Source System integration stage. |
| TERADATA | TD | A connection to a Teradata data source. | Connections with a Source System integration stage. |
Data Types
Data Types are the allowed values that can be used to specify Columns in BimlFlex.
| Data Type | Description |
|---|---|
| AnsiString | ANSI string. |
| AnsiStringFixedLength | Fixed length ANSI string. |
| Binary | Binary data column. |
| Boolean | Boolean, Bit or True/False. |
| Byte | Unsigned tiny integer. |
| Currency | Money or currency. |
| Date | Date only. |
| DateTime | Date and time. |
| DateTime2 | Variable precision date and time. Increased precision over DateTime. |
| DateTimeOffset | Data and time with time zone offset. |
| Decimal | Fixed precision and scale numbers. |
| Double | Float or Double. |
| Guid | Unique identifier or GUID. |
| Int16 | Small integer. |
| Int32 | Integer. |
| Int64 | Big integer. |
| Object | Object. Used for complex data types. |
| SByte | Tiny integer. |
| Single | Real or Single. |
| String | Unicode String. |
| StringFixedLength | Fixed Length Unicode String. |
| Time | Time only. |
| UInt16 | Unsigned small integer. |
| UInt32 | Unsigned integer. |
| UInt64 | Unsigned big integer. |
| VarNumeric | Variable numeric |
| Xml | XML formatted data. |
| HierarchyId | SQL Server HierarchyId data type. |
| Geometry | SQL Server geometry data type. |
| Geography | SQL Server geography data type. |
| SmallMoney | Small money. |
| Variant | Variant. Non explicit data type. |
note
For information on how these map to SQL Server, Synapse and Snowflake data types see associated link below.
BimlFlex Documentation: BimlFlex Data Type Conversions.
Integration Stages
The Integration Stage is a Connection property that defines and drives how a connection is used in the architecture. The integration stage is an important classification for a connection, because it is used by BimlFlex to direct what code is generated, and what further options are available - including Connection Types and corresponding System Types and even down to the Change Types at Column level.
The following integration stages are available:
| Integration Stage | Code | Description | Applies To |
|---|---|---|---|
| Business Data Vault | BDV | This layer represents the data following the application of the soft business rules that may be required. | Any connection. Selecting this integration stage will limit the available connection types and system types to BimlFlex supported target platforms. |
| Data Mart | DM | A subset of data stored within the data warehouse, for the needs of a specific team, section or department within the enterprise. | Any connection. Selecting this integration stage will limit the available connection types and system types to BimlFlex supported target platforms. The allowed change types at column level will also be scope to support Dimensional Modeling. |
| Data Warehouse | DWH | The connection type dedicated for the BimlCatalog. A connection with the Data Warehouse integration stage can not be used in project definitions. | BimlCatalog connections only. |
| File Export | EXP | A file or series of files that contain a subset of the data in a Data Warehouse. | Connections used in projects that have been configured with an Integration Template of SQL Server Integration Services (SSIS) only. |
| Landing Area | LND | A Landing Area connection is only applicable to Cloud Enabled Connections, to identify the location where data needs to be 'landed' before being loaded into the cloud database. | Any connection. Selecting this integration stage will limit the available connection types and system types to BimlFlex supported target platforms. |
| Master Data Services | MDS | Microsoft's solution for handling "master" data within an enterprise using SQL Server. | Connections used in projects that have been configured with an Integration Template of SQL Server Integration Services (SSIS) only. |
| Persistent Staging Area | PSA | A staging area where history is kept and changed data capture (CDC) is managed for all source attributes. | Any connection. Selecting this integration stage will limit the available connection types and system types to BimlFlex supported target platforms. |
| Data Vault | DV | A uniquely linked set of normalized tables that support one or more functional areas of business and provides historical tracking. | Any connection. Selecting this integration stage will limit the available connection types and system types to BimlFlex supported target platforms. |
| Source System | SRC | A data source that is being extracted, with the intent to be processed into the BimlFlex data integration environment. | Any connection. |
| Staging Area | STG | A temporary storage area between the source systems and a data warehouse. | Any connection. Selecting this integration stage will limit the available connection types and system types to BimlFlex supported target platforms. |
| Target Staging Area | TSA | Only used in SSIS DataWarehouse and Data Mart templates. | Connections used in projects that have been configured with an Integration Template of SQL Server Integration Services (SSIS) only. |
Integration Templates
An Integration Template in BimlFlex drives which code generation patterns are applied to the design metadata. In other words, the Integration Template defines how the output will be generated. An integration template is set for a Project, and all data logistics defined in this project will be generated for the selected template.
BimlFlex supports SQL Server Integration Services (SSIS), Azure Data Factory (ADF) Execute Pipelines and Mapping Data Flows as runtime integration engines, and depending on specific Connection properties also generates SQL-based Stored Procedures.
The following integration templates are supported in BimlFlex:
| Integration Template | Code | Description |
|---|---|---|
| SQL Server Integration Services (SSIS) | SSIS | Deliver the data logistics for the project in SQL Server Integration Services (SSIS). If Pushdown Processing is enabled, SSIS will orchestrate the data logistics, but the processing will mostly occur on the data platform using Stored Procedures. If pushdown processing is disabled, most the processing will be done in SSIS Data Flows. |
| Azure Data Factory (ADF) | ADF | Deliver the data logistics for the project using Execute Pipelines. This will generate Execute Pipelines that use a Copy Activity to ingest the data from the source, and Stored Procedures to process the data further. |
| Azure Mapping Data Flows (ADF) | DFL | Deliver the data logistics using Azure Data Factory Mapping Data Flows ('data flows'). Processes will be run in data flows using inline processing, not Stored Procedures. |
| SSIS: File Extract | S2FIL | Use SSIS to extract data to file. |
| SSIS: Zip File Extract | S2ZIP | Use SSIS to extract data to a zip file. |
Object Types
Object Types are configured using the Object Editor and drive the behavior and purpose of the Object for the design and code generation process. The available object types are dependent on the way the Connection is used in a BimlFlex Project. Depending on the connection's Integration Stage and System Type some options may, or may not be, available.
| Change Type | Code | Description | Allowed for Integration Stage | Allowed for System Type |
|---|---|---|---|---|
| Bridge | BRG | Defines the object as a Data Vault Bridge entity. | Business Data Vault, Data Vault | All |
| Dimension | DIM | Defines the object as a Dimension Table in a Dimensional Model / Data Mart. | Data Mart | All |
| Exclude in Accelerator | EXC | Flags an object to not be included in the Data Vault Accelerator. | Source System | All |
| Fact | FCT | Defines the object as a Fact Table in a Dimensional Model / Data Mart. | Data Mart | All |
| Flat File | FIL | Determines that the object is a flat file. | File Export, Source System | Avro Format, Binary Format, Delimited Format, JSON Format, ORC Format, Parquet Format, Ragged Right Format , XML Format |
| Hierarchy Link | HAL | |||
| Hub | HUB | Defines the object as a Data Vault Hub entity. | Business Data Vault, Data Vault | All |
| Ignore | IGN | Setting an object to Ignore will remove it from any processing and/or visualisation in BimlFlex. No Staging Area or Persistent Staging Area objects will be derived. | All | All |
| Json | JSON | Determines that the object is a flat file in JSON format. Only applies when used for a REST API connection. | Source System | Rest Service |
| Link | LNK | Defines the object as a Data Vault Link entity. | Business Data Vault, Data Vault | All |
| Link Satellite | LSAT | Defines the object as a Data Vault Link-Satellite entity. | Business Data Vault, Data Vault | All |
| Point In Time | PIT | Defines the object as a Data Vault Point-In-Time (PIT) entity. | Business Data Vault, Data Vault | All |
| Reference | REF | Determines that this object will be generated as a stand-alone historized table in a Data Vault context. | Business Data Vault, Data Vault | All |
| Reference Satellite | RSAT | Determines that this object will be generated as a stand-alone (unconnected) Satellite in a Data Vault context. | Business Data Vault, aw Data Vault | All |
| Satellite | SAT | Defines the object as a Data Vault Satellite entity. | Business Data Vault, Data Vault | All |
| Stored Procedure | SP | For source objects only, determines the the object is a stored procedure whose output must be ingested into the data solution by BimlFlex. | Source System | Azure SQL Database, Azure Synapse, Microsoft SQL Server |
| Staged Query | STQ | Defines the object as an object that is derived from other existing objects. This will be treated as a data source, but no Staging Area and/or Persistent Staging Area components will be generated. For more details, please refer to the Staged Query Concept. | Source System | Azure SQL Database, Azure Synapse, Microsoft SQL Server, Snowflake Data Warehouse |
| Table | TBL | This is the default object type in BimlFlex. A Table property for the object means that no specific behaviour is applied by the BimlFlex code generation processes. | Business Data Vault, Landing Area, Master Data Services, Persistent Staging Area, Data Vault, Source System, Staging Area | Azure Synapse, COZYROC Excel File, COZYROC Salesforce, DB2, Dynamics CRM, Microsoft SQL Server, MySql, Oracle, Oracle RDB, PostgreSQL, Salesforce, Salesforce Marketing Cloud, Salesforce Service Cloud, Snowflake Data Warehouse, Sybase, Teradata |
| CDC All | TCA | Determines that the object is configured for Change Data Capture, querying the full history of available changes. BimlFlex will generate specific code to query the object. | Source System | Microsoft SQL Server |
| CDC Last | TCL | Determines that the object is configured for Change Data Capture, but only retrieves the last value every time data is loaded. BimlFlex will generate specific code to query the object. | Source System | Microsoft SQL Server |
| Change Tracking | TCT | Determines that the object is configured for Change Tracking. BimlFlex will generate specific code to query the object. | Source System | Microsoft SQL Server |
System Types
System Types are configured using the Connection Editor to define what kind of data source or target is connected to as part of the Connection Type configuration.
| System Type | Code | Description | Supported by Connection Type |
|---|---|---|---|
| Avro Format | AVRO | Avro is a binary, compressed, file format. Only supported for Azure Data Factory. | Azure Blob Storage, Azure Data Lake Store, FILE, FTP, SFTP |
| Azure Synapse | SQLDW | Azure Synapse Analytics database (Formerly Azure SQL Data Warehouse). | ADONET, OLEDB |
| Binary Format | BIN | Binary storage file. Only supported for Azure Data Factory. | Azure Blob Storage, Azure Data Lake Store, FILE, FTP, SFTP |
| COZYROC Excel File | CREXCEL | Excel File using COZYROC SSIS Components. | Custom Component |
| COZYROC Salesforce | CRSFDC | Salesforce API using COZYROC SSIS Components. | Custom Component |
| DB2 | DB2 | IBM DB2 database. | ADONET, ODBC, OLEDB |
| Delta Lake | DELTA | Delta Lake Storage Layer. | Azure Data Lake Store |
| Dynamics CRM | DYNCRM | API that can connect to Microsoft Dynamics data sources. | Rest API |
| Delimited Format | FF_DEL | Flat File with Delimiters. | Azure Blob Storage, Azure Data Lake Store, FILE, FTP, SFTP |
| Ragged Right Format | FF_RAG | Flat File with Ragged Right formatting. | FILE |
| JSON Format | JSON | JSON file format. Only supported for Azure Data Factory. | Azure Blob Storage, Azure Data Lake Store, FILE, FTP, SFTP |
| Microsoft SQL Server | MSSQL | Microsoft SQL Server database. Can be configured for on-premise, also accessible via a Self-Hosted Integration Runtime. Also can be configured for cloud computing as a Managed Instance or Azure SQL database. This is done in the Linked Service configuration for the connection. | ADONET, Microsoft MDS, OLEDB |
| MySql | MYSQL | MySQL database. | ADONET, ODBC, OLEDB |
| Oracle | ORA | Oracle database. | ADONET, ODBC, OLEDB, ORACLE |
| Oracle RDB | ORARDB | Oracle RDB Database. Discontinued product line from Oracle. Separate from there current database product. | ADONET, ODBC, OLEDB, ORACLE |
| ORC Format | ORC | ORC file format. Only supported for Azure Data Factory. | Azure Blob Storage, Azure Data Lake Store, FILE, FTP, SFTP |
| Parquet Format | PARQ | Parquet file format. Only supported for Azure Data Factory. | Azure Blob Storage, Azure Data Lake Store, FILE, FTP, SFTP |
| PostgreSQL | PGR | PostgreSQL database. | OLEDB |
| Rest Service | REST | Configurable REST API connector. | Rest API |
| Salesforce | SFDC | API to connect to the generic Salesforce tenant. | Rest API |
| Salesforce Marketing Cloud | SFDCMC | API to connect to the Salesforce Marketing Cloud. | Rest AP |
| Salesforce Service Cloud | SFDCSC | API to connect to the Salesforce Service Cloud. | Rest API |
| Snowflake Data Warehouse | SFLDW | Snowflake database. | ODBC |
| Sybase | SYBASE | Sybase database. | ADONET, ODBC, OLEDB |
| Teradata | TD | Teradata database. | ADONET, ODBC, OLEDB, TERADATA |
| XML Format | XML | XML file format. | Azure Blob Storage, Azure Data Lake Store, FILE, FTP, SFTP |