Connection Editor
BimlFlex Connections provide the information required to connect to data sources. They also acts as containers for Objects, which are the definitions in metadata for files and tables. Objects can be imported using the Import Metadata feature. This is especially useful for connections that will act as sources for loading data into the data solution.
Connections are used in Projects to direct BimlFlex in generating the data logistics processes for the intended flow of data.
A connection definition includes the configuration of the Integration Stages which will classify the connection for its role in the architecture. For example, to define a connection as a data source, Data Vault, or Data Mart.
For connections that are configured for Cloud Computing, additional configuration options will be made available to support generating the connection as a Linked Service in Azure Data Factory (ADF).
A new BimlFlex solution always starts with defining connections to your data sources and targets depending on your intended data solution architecture. The design work commences by importing the objects for the source connection (configured with a Source System integration stage).
Connections are created and modified using the Connection Editor in the BimlFlex Application.
BimlFlex supports platforms such as SQL Server, Azure Synapse and Snowflake for the data solution, but can connect to many different data sources.
Overview
The following sections describe the User Interface elements of the Connection Editor, and how they are used to author and manage BimlFlex Connections. Detailed descriptions of each component is also available in the reference documentation covering connections.
Note
Detailed descriptions of all Connection Editor fields and options are available in the Reference Documentation.
The Connection Editor contains four main tabs that can be used to modify connection details, as well as various properties and settings related to the Objects that are associated with the selected connection.
Connection Tab
The Connection Tab is the first tab in the Connection Editor, and it is selected by default. The connection tab focuses on general connection information and configuration. This tab is used to define and create the connection itself.
Action Buttons
| Icon | Action | Description |
|---|---|---|
 |
Save | This will persist changes made to the Connection modified in the designer. Ctrl+S can also be used as a shortcut. |
 |
Discard | This will Discard any unsaved changes, and revert to last saved form. |
 |
Archive | Archive will remove the Connection from the active metadata repository, and move it to the metadata archive. Clicking Archive displays the Archive Connection Dialog. |
 |
Duplicate | This will create a duplicate of the selected Connection. A Duplicate Connection Dialog will appear asking for a Connection Name. A new Connection will be created using most of the selected Connection's current properties (except Attributes and Parameters). |
 |
Import Metadata | Import Metadata using the configured connection details. For more information please refer to the importing metadata section. |
 |
Cloud | When enabled, this allows the configuration of Linked Services. This only applies to Azure Data Factory (ADF) deployments. |
 |
Excluded | This will determine if the Connection and its associated entities will be excluded along with the rest of the solution. This is designed to be paired with the Use My Exclusions (Locally) global setting to allow for multiple developers to work on different functional areas without deleting or globally excluding entities. |
|
Deleted | This will soft-delete the selected Connection. This will remove the Connection and all associated entities from processing and validation. |
Linked Services
Connections can be configured to be cloud enabled by modifying the Cloud Selection button on the top-right of the connection screen. This will show (or hide) the Linked Services details, which are required to deploy cloud-based solutions. For information on Linked Services and their configuration, please refer to Configuring a Linked Service Connection.
Additional Dialogs
Archive Connection Dialog
When a connection is archived, BimlFlex will display a confirmation box warning against the dangers of archiving. Users are required to confirm by ticking the checkbox and then pressing the Ok button.
Warning
Archiving is a permanent removal of the selected entity from its associated table in the BimlFlex Database. The best practice is to first use the Deleted flag (soft delete) as an indication that the connection may need to be removed.
Duplicate Connection Dialog
This will create a copy of the selected Connection and properties.
Important
Attributes and Parameters are not copied over.
Allowed Values
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. |
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. |
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 |
Objects Tab
The Objects Tab provides quick access to all Objects associated with the Connection.
Action Buttons
The following options are available:
| Icon | Action | Description |
|---|---|---|
 |
Add | Add will create a new Object. Clicking Add will open an Add Object Dialog. |
|
Save | Save will save the currently set of staged changes. The Save button is only enabled if any Object has changes staged and when there are no validation issues. |
|
Discard | This will Discard any unsaved changes and revert to last saved details. |
When checking a single Object in the overview two additional options will become visible. When selecting two or more objects only the Archive option will be visible.
| Icon | Action | Description |
|---|---|---|
|
Archive | Archive will remove the Object from the active metadata repository, and move it to the metadata archive. Clicking Archive will create an Archive Object Dialog. |
 |
Edit | Edit will open an Edit Object Dialog to edit the currently selected Object. After edits are complete, be sure to click the Save button to confirm the changes. |
Additional Dialogs
Add Object Dialog
Creates a new Object. Once entry is finished ensure that the Save button is clicked.
Archive Object Dialog
Archiving means that metadata will be physically deleted. A confirmation box warning against the dangers of archiving. You are required to confirm by both the check box and the Ok button.
Warning
Archiving is a permanent removal of the selected entity from its associated table in the BimlFlex Database. The best practice is to first use the Deleted flag (soft delete) as an indication that the connection may need to be removed.
Objects Tab Views
The Objects Tab Views provide different ways to review the properties of Objects associated with the selected entity. The general purpose and overview of each view will be outlined below.
Note
See the Objects Editor for further details on creating or editing an Object along with details on individual property values.
Overview
The Overview is available to give quick access to the more physical properties of the Objects relating to the selected entity. Additional properties are available for bulk management such as controlling exclusions, disabling persistence, or flagging one or more Object(s) for deletion.
Various navigational transitions are available on view form and outlined below.
The following details are shown in this view:
| Component | Type | Description |
|---|---|---|
| Project | Value | The Project that the Object belongs to. See Projects Documentation. |
| Object | Value | The name of the Objects as associated with the current Connection. See the Object Editor documentation for more details. |
| Object Type | Value | The type of Object that is being extracted or created. See Valid Object Types. |
| Excluded | Checkbox (Tri-state) | Setting to determine whether the selected entity will be built out with the rest of the solution when the BimlFlex solution is being compiled. The state of being "partially checked" is based on the selection of its child elements (below). |
| Exclude Build | Checkbox (Y/N) | Toggle to determine whether the Object is excluded from the build when the BimlFlex solution is compiled. |
| Not Persistent | Checkbox (Y/N) | Toggle to determine whether the Object is excluded from Persistent Staging Area (PSA). When enables, the Object will not be stored in the PSA. |
| Deleted | Checkbox (Y/N) | Toggle to determine whether the Object is soft-deleted from the build when the BimlFlex solution is being compiled. |
Navigational Transitions
From the Objects Tab, it is possible to navigate directly to the following areas of BimlFlex:
| Item | Action |
|---|---|
| Project Value | Navigate to the Project Editor by clicking <img class="icon-inline" src="../../static/svg/navigate.svg style="width: 18px"/> |
| Object Value | Navigate to the Object Editor, selecting the clicked Object |
Note
The Object navigational transition is the only one provided on the additional views. As such, no further callouts will be made to this. Functionality on the below forms however is consistent: click the Object name to navigate to the selected Object in the Object Editor.
Model Overrides
The Model Overrides view allows for quick management of metadata configured through the Data Vault Accelerator, Business Modeling or the Schema Diagram. This view is intended as an easy way to review, reference, and maintain previously entered metadata.
The following details are shown in this view:
| Component | Type | Description |
|---|---|---|
| Object | Navigational Value | The name of the Objects as associated with the current Connection. See the Object Editor documentation for more details. |
| Accelerator Type | Value | The type of entity the target Object will be defined as for the Data Vault Accelerator. This information will determine to what Data Vault entity type this object will be mapped. See Data Vault Accelerator Documentation for more details. |
| Business Entity | Value | Displays the Business Entity that is associated with this Object. Use Business Modeling to define the Business Entities. See Business Modeling Documentation. |
| Business Name | Value | Displays the Business Name that is defined for this Object. This value will be used to drive the naming of the target object when using the Accelerator. For a source object with Accelerator Type Hub, the Business Name will be used for the Hub name, as well as the default Satellite name. |
| Business Subject | Value | Displays the Business Subject to which this Objects is added. A Business Subject is an additional way to classify Business Entities. A Business Subject may contain multiple Business Entities. Each Business Entity can be associated with a single Business Subject. See Business Modeling Documentation. |
Important
Although it is possible to manage high level metadata from this screen, it is recommended to you use the Data Vault Accelerator for implementing changes in the design. The Data Vault Accelerator is specifically designed to simplify and improve the experience of modeling metadata via a graphical designer as opposed to the table entry that this view provides.
Query Overrides
The Query Overrides view exposes any SQL overrides that may be defined for the Object. Any SQL that is commonly used to perform most ETL/ELT tasks are dynamically generated by the BimlFlex framework. In many cases providing SQL query overrides is not required. However, when necessary this view can be used to review these overrides across all objects.
The following details are shown in this view:
| Component | Type | Description |
|---|---|---|
| Object | Navigational Value | The name of the Objects as associated with the current Connection. See the Objects Editor documentation for more details. |
| From SQL | Field | Override the FROM clause of the generated SQL statement that is used to query the Object. |
| Join SQL | Field | If the source select requires a JOIN, this is where it can be entered. Note: This field is limited to 1,000 characters. |
| Where SQL | Field | Utilize if the source select requires a WHERE clause. Note: Defining a Parameter on the Object will be appended to the statement. |
| Group By SQL | Field | Utilize if the source select requires a DISTINCT or TOP N clause. Note: This field is limited to 1000 characters. |
| Override SQL | Field | Overrides the complete SQL statement used to query the Object. This must be a complete SQL statement. Adding an override here disregards any other SQL overrides, such as FROM SQL or WHERE SQL. |
See the Objects Editor documentation for additional information on the above fields and more.
Important
This is designed for developers/architects who have a strong understanding of SQL. Intellisense and code auto completing and validation are not provided in the BimlFlex Applicationbecause a direct connection to the data is not supported for security reasons.
Attributes Tab
The Attributes Tab provides a view of any Configuration or Setting overrides that have been applied to the selected Connection.
Action Buttons
All Attributes assigned to the current entity show here. New items can be entered via the Add action button. Existing items can be edited by clicking the Value field to select the Attribute and using the Edit action button. Various navigational transitions are available on this form and outlined below.
The following options are available:
| Icon | Action | Description |
|---|---|---|
|
Add | Add will create a new Attribute and assign it to the current entity. When adding an attribute via this button, Attribute Type and the respective linked fields, will be pre-filled on the created Add Attribute Dialog. |
|
Save | Save will save the currently set of staged changes. The Save button is only enabled if any Attribute has changes staged and there are no major validation issues with the current list of Attribute properties. |
|
Discard | This will Discard any unsaved changes and revert to last saved form. |
When checking a single Attribute in the overview two additional options will become visible. When selecting two or more attributes only the Archive option will be visible.
| Icon | Action | Description |
|---|---|---|
|
Archive | Archive will hard delete the selected Attribute. This will result in the physical removal of the selected record from the BimlFlex database. The data will no longer be accessible by the BimlFlex Applicationand will require a Database Administrator to restore. Clicking Archive will create an Archive Attribute Dialog. |
|
Edit | Edit will open an Edit Attribute Dialog to edit the currently selected Attribute. After edits are complete, be sure to click the Save button to confirm the changes. |
Additional Dialogs
Add Attribute Dialog
Creates an Attribute and associates it with the current entity. Once entry is finished ensure that the Save button is clicked.
Edit Attribute Dialog
Edit the selected Attribute. Once entry is finished ensure that the Save button is clicked.
Archive Attribute Dialog
Confirmation warning against the dangers of archiving. You are required to confirm by both the check box and the Ok button.
Warning
Archiving is a permanent removal of the selected entity from its associated table in the BimlFlex Database. The best practice is to first use the Deleted flag (soft delete) as an indication that the connection may need to be removed.
Attribute Tab View
The Attribute Tab provides a different way to review the properties of Attributes associated with the selected entity.
Overview
The following details are shown in this view:
| Component | Type | Description |
|---|---|---|
| Object | Value | The name of the Objects as associated with the current Connection. See the Object Editor documentation for more details. |
| Column | Navigational Value | Define the scope of the Attribute. See the Attribute Editor for more details. |
| Attribute | Navigational Value | Define the scope of the BimlFlex process that the Attribute is applied to. Attribute Type is required and must be a valid Attribute Type. |
| Value | Value | The value to be returned when the custom attribute is accessed by the framework. See the Attribute Editor for more details. |
| Property | Field | Define the value for a custom attribute if it is not specified in the Attribute Value field. See the Attribute Editor for more details. |
Navigational Transitions
From the Attribute Tab, it is possible to navigate directly to the following areas of BimlFlex:
| Item | Action |
|---|---|
| Object Value | Navigate to the Object Editor, by clicking <img class="icon-inline" src="../../static/svg/navigate.svg style="width: 18px"/> |
| Column Value | Navigate to Column Editor, selecting clicked Column |
| Attribute Value | Navigate to Attribute Editor, selecting clicked Attribute |
Parameters Tab
The Parameters Tab provides a view of any Parameters overrides that are associated with the selected Connection and its associated Objects.
Action Buttons
All Parameters assigned to the current entity show here. New items can be entered entered via the Edit action button. Various navigational transitions are available on this form and outlined below.
| Icon | Action | Description |
|---|---|---|
|
Add | Add will create a new Parameter and assign it to the current entity. When adding an attribute via this button Attribute Type, along with the respective linked fields, will be pre-filled on the Add Parameter Dialog. |
|
Save | Save will save the currently set of staged changes. The Save button is only enabled if any Object has changes staged and there are no major validation issues with the current list of Object properties. |
|
Discard | This will Discard any unsaved changes and revert to last saved form. |
When checking a single Parameter in the overview two additional options will become visible. When selecting two or more parameters only the Archive option will be visible.
| Icon | Action | Description |
|---|---|---|
|
Archive | Archive will hard delete the selected Parameter. This will result in the physical removal of the selected record from the BimlFlex database. The data will no longer be accessible by the BimlFlex Applicationand will require a Database Administrator to restore. Clicking Archive will create an Archive Parameter Dialog. |
|
Edit | Edit will open an Edit Parameter Dialog to edit the currently selected Parameter. After edits are complete, be sure to click the Save button to confirm the changes. |
Additional Dialogs
Add Parameter Dialog
Creates a Parameter and associates it with the current entity. Once entry is finished ensure that the Save button is clicked.
Edit Parameter Dialog
Edit the selected Parameter. Once entry is finished ensure that the Save button is clicked.
Archive Parameter Dialog
Confirmation warning against the dangers of archiving. You are required to confirm by both the check box and the Ok button.
Warning
Archiving is a permanent removal of the selected entity from its associated table in the BimlFlex Database. The best practice is to first use the Deleted flag (soft delete) as an indication that the connection may need to be removed.
Parameters Tab View
The Parameters Tab View provides a different way to review the properties of the Parameters associated with the selected entity.
Overview
Parameters Tab Table Components
| Component | Type | Description |
|---|---|---|
| Object | Value | The name of the Object within the current Source Connection. See Objects Documentation. |
| Column | Value | The name of the Column within the current entity. See Columns Documentation. |
| Parameter | Navigational Value | The name of the Parameter that is being applied to the selected entity. See Parameters Documentation. |
| Operator | Value | Operator to use in the Parameter when applied to the source Column. |
| Default | Date | The parameter load value to use for the first load. |
| Data Type | Value | The data type to use for the Parameter. Must be a valid data type. See Valid Data Types. |
Navigational Transitions
From the Parameters Tab, it is possible to navigate directly to the following areas of BimlFlex:
| Item | Action |
|---|---|
| Object | Navigate to Object Editor, by clicking <img class="icon-inline" src="../../static/svg/navigate.svg style="width: 18px"/> |
| Column | Navigate to Column Editor, by clicking <img class="icon-inline" src="../../static/svg/navigate.svg style="width: 18px"/> |
| Name | Navigate to Parameter Editor, selecting the clicked Parameter |