Skip to main content

Column Editor

The Column Editor is used to manage how BimlFlex interacts with all Columns in the BimlFlex design process. The column editor provides a fine-grained means of configuring specific data logistics behavior.

Mappings from source to target are defined at column level in BimlFlex, and the column editor provides various ways to influence how this design metadata is translated into data logistics artifacts.

Overview

Columns within BimlFlex are displayed in the Treeview on the left side-navigation menu. Columns belong to an Object, and can be access by opening the object in the treeview or by navigating to the columns tab in the Object Editor .

note

Detailed descriptions of all Column fields and options are available in the Reference Documentation.

From any editor, any instance of the icon will serve as a Global Navigation Transition. Clicking this icon will navigate the user directly to the referenced Project, Connection or Object.

Accessing the Column Editor from the Treeview

Selecting any column from the treeview will display the column editor.

BimlFlex Column Editor

Accessing the Column Editor via the Object Editor

The column editor can also be accessed via the object editor, from various locations in the App. When an object is selected, the columns tab is visible in the editor. Navigating to the columns tab will show a grid view of all the columns belonging to the object.

The user may navigate to any column by selecting it from the displayed list.

BimlFlex Column Editor - Grid View

Action Buttons

Column Editor Action Buttons

IconActionDescription
SaveSaves any changes made in the form. The Save button will only enabled if there are unsaved changes in the form and no major validation errors.
DiscardThis will Discard any unsaved changes and revert to last saved form.
ArchiveThis will hard delete the selected Column. 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, if possible. Clicking Archive generates an Archive Column Dialog.
DuplicateThis will create a duplicate of the selected Column. Clicking the Duplicate button will cause a Duplicate Column Dialog to appear. The new Column will be created using all of the selected Column's current properties.
ExcludedThis will determine if the Column will be excluded from processing and validation 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.
DeletedThis will soft delete the currently selected Column. This will remove the Column and all associated entities from processing and validation. For information on how to add excluded or deleted items back to the scope, please read our tips and tricks section.

Column Details

Detailed reference documentation on each of the fields in the column editor is found in the Reference Documentation. A brief overview will be provided here.

The column editor is organized in three different groups.

Column Specification

The main column details capture the technical specifications of the column. This includes the way the column is used in the physical model. The (Foreign Key) references to another object and the mapping to the target column (and object) are also administered here.

Business Overrides

The Business Overrides section contains information from the business model. Please refer to the Business Modeling feature for more information about the definition of a business model. This section also specifies the data type conversion that must be applied to a column when propagating it through the data solution architecture. For example, when deriving the Staging Area or Data Vault columns.

SQL Overrides

Lastly, the SQL Overrides section allows for detailed modification of the way the column is used. This is where custom transformation logic at column level can be defined using the various override fields available.

Additional Dialogs

Archive Column Dialog

Confirmation box warning against the dangers of archiving. You are required to confirm by both the check box and the Ok button.

Archive Column Dialog - mtb-20-image

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 Column Dialog

This will create a copy of the selected Column and properties. A unique name is required.

Duplicate Column Dialog -mtb-20-image

Allowed Values

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 TypeCodeDescriptionApplies To
UpdateCHGAssigns 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.
KeyKEYIndicates 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.
IgnoreIGNAssigns 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 HistoryHSTAssigns 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 CaptureCDCIndicates this column is used for historization in Data Marts.Columns for an objects belonging to a connection with a Data Mart integration stage.
Hash DistributionHDKAssigns the column to be used for hash distribution when using Synapse objects.All integration stages, any column, but only when used for Synapse connections.
TransientTRSAssigns 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 DVEXCProcesses 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 ReferenceREFDefines 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 DegenerateDGAAssigns 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 ReferenceLSRDefines 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 HubSTHDesignates 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 KeyLDKA 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 RowMAKAssigns 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.

Data Types

Data Types are the allowed values that can be used to specify Columns in BimlFlex.

Data TypeDescription
AnsiStringANSI string.
AnsiStringFixedLengthFixed length ANSI string.
BinaryBinary data column.
BooleanBoolean, Bit or True/False.
ByteUnsigned tiny integer.
CurrencyMoney or currency.
DateDate only.
DateTimeDate and time.
DateTime2Variable precision date and time. Increased precision over DateTime.
DateTimeOffsetData and time with time zone offset.
DecimalFixed precision and scale numbers.
DoubleFloat or Double.
GuidUnique identifier or GUID.
Int16Small integer.
Int32Integer.
Int64Big integer.
ObjectObject. Used for complex data types.
SByteTiny integer.
SingleReal or Single.
StringUnicode String.
StringFixedLengthFixed Length Unicode String.
TimeTime only.
UInt16Unsigned small integer.
UInt32Unsigned integer.
UInt64Unsigned big integer.
VarNumericVariable numeric
XmlXML formatted data.
HierarchyIdSQL Server HierarchyId data type.
GeometrySQL Server geometry data type.
GeographySQL Server geography data type.
SmallMoneySmall money.
VariantVariant. 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.