Data Type Mappings Editor
BimlFlex Data Type Mappings provide the ability to map Data Types from a source system to another more standardized data type. This can be either a conversion of the Data Type entirely, such as a int to a bigint, the expansion of an existing Data Type, such as nvarchar(13) to nvarchar(20), or the combination of both, such as char(1) to nvarchar(10).
Data Type Mappings is a BimlFlex feature that expands the Data Types of the source to a larger data type that is more accommodating. This is done to accommodate changes in the source system without the need to update the data solution, or the load process. The most common expansions are for short string representations that might be updated in the source.
A 'Name' field of 20 characters might be updated to 250 to accommodate longer customer names, or it might be changed from varchar to nvarchar to accommodate international characters. An integer might be updated to big int when the source counter nears its maximum allowed value.
By expanding incoming data it is possible to cater for these future changes before they become a load issue.
Note
Note that larger data types might require more database resources.
BimlFlex provides a rules engine for applying the expansions based on the Business Requirements. Based on the configuration, BimlFlex can apply the expansions across all columns for a source and all tables and load patterns will take the new expanded type into consideration.
The expanded data types will be used in the source to staging load and the new data types will be used in both Staging and Persistent Staging. When the Data Vault accelerator is run on the source metadata the Satellite attributes will inherit the expanded data types from the source definition.
Overview
The following sections describe the UI elements of the Data Type Mappings Editor and how they are used to author and manage BimlFlex Data Type Mappings.
Action Buttons
| Icon | Action | Description |
|---|---|---|
 |
Save | This will save the current set of staged changes. The Save button is only enabled if the Data Type Mapping has changes staged and there are no major validation issues with the current Data Type Mapping properties. |
 |
Discard | This will Discard any unsaved changes and revert to last saved form. |
 |
Apply Data Type Mappings | Brings up the Apply Data Type Mappings Dialog to apply all Data Type Mappings to a specified Record Source. Note that this is the complete set of Data Type Mappings and not just the record that was previously selected. |
 |
Deleted | This will soft delete the currently selected Data Type Mapping. This will remove the Data Type Mapping from all processing and it will be excluded from all validation. |
[//]: # (TODO: Find a switch SVG to use for Deleted)
Additional Dialogs
Apply Data Type Mappings Dialog
From here you can apply your Data Type Mappings to an individual Record Source. Select your Record Source from the dropdown, enable the options you want and click Apply. Defaults should be acceptable for most cases but options are provided to support user cases where you may only be concerned with a particular property.
Apply Data Type Mapping Options
| Option | Description |
|---|---|
| Override Existing Mappings | When enabled, any existing settings will be overridden with current settings. When disabled only previously unmapped columns will have logic applied. |
| Apply Data Type Mappings | When enabled, applies the Mapped To Data Type from the Data Type Mappings to the Data Type Mapping property of the Columns with the defined data types. |
| Apply SQL Source Expression | When enabled, applies the SQL Source Expression from the Data Type Mappings to the SQL Source Expression property of the Columns with the defined data types. |
| Apply SSIS Data Flow Expression | When enabled, applies the SSIS Data Flow Expression from the Data Type Mappings to the SSIS Data Flow Expression property of the Columns with the defined data types. |
| Apply Column Alias | When enabled, applies the Column Alias from the Data Type Mappings to the Column Alias property of the Columns with the defined data types. |
| Apply Convert Source Type | When enabled, applies the DATA TYPE, LENGTH, PRECISION and SCALE from the Data Type Mappings to the DATA TYPE, LENGTH, PRECISION and SCALE property of the Columns with the defined data types if the Data Type Mapping has the CONVERT DATA TYPE MAPPING enabled. |
Fields
| Field | Description |
|---|---|
| Data Type Mapping | Name of the Data Type Mapping. |
| Mapped To Data Type | The Data Type Mapping this type is mapped to. If this value is populated then this Data Type Mapping will use the values specified by the Mapped To Data Type. |
| Data Type | The type of data stored in this column using the Unified System Types. Additional logic is required to cater for data types with a 'CustomType' like hierarchyid. |
| Default | The default value that should be used when a value is not received. Be sure to verify that the value listed her is valid for the specified Length, Precision or Scale. |
| Length | Only applicable for a Data Type that requires this specification. If the Data Type Mapping does not have a Mapped To Data Type and Is Master then this value represents the value to be used when a column is created using this Data Type Mapping. If the Data Type Mapping has a Mapped To Data Type then it is the maximum value that will be included when mapping to the Mapped To Data Type. Note that when there are multiple mappings for the same Data Type the lowest values are checked first. |
| Precision | Only applicable for a Data Type that requires this specification. If the Data Type Mapping does not have a Mapped To Data Type and Is Master then this value represents the value to be used when a column is created using this Data Type Mapping. If the Data Type Mapping has a Mapped To Data Type then it is the maximum value that will included when mapping to the Mapped To Data Type. Note that when there are multiple mappings for the same Data Type the lowest values are checked first. |
| Scale | Only applicable for a Data Type that requires this specification. If the Data Type Mapping does not have a Mapped To Data Type and Is Master then this value represents the value to be used when a column is created using this Data Type Mapping. If the Data Type Mapping has a Mapped To Data Type then it is the maximum value that will included when mapping to the Mapped To Data Type. Note that when there are multiple mappings for the same Data Type the lowest values are checked first. |
| Ordinal | This value is used to determine the default sort order when populating the navigation/value pane on the right side of the screen. |
| System Type | If unspecified the Data Type Mapping will be applied to all system types. When specified, defines the System Type that is required in order to apply the Data Type Mapping. This is commonly used to control the mapping of more complex data types such as Date and DateTime variants as syntax and functions differ from system to system. Must be a valid System Type. |
| SQL Source Expression | SQL expression for this column is used to extend source queries. Generally used for source type casting and case statements. Example, CONVERT(VARCHAR(27), @@this, 121) |
| SSIS Dataflow Expression | The expression used to calculate the value of the derived column via SSIS. SSIS expression syntax is used. Example, (DT_DBTIMESTAMP2, 7)([DTM_@@this]) |
| ADF Dataflow Expression | The expression used to calculate the value of the derived column via ADF. ADF expression syntax is used. |
| SQL Target Expression | The SQL expression used to extend target queries. Generally used for source type casting and case statements. Example, CONVERT(VARCHAR(27), @@this, 121) |
| SQL Target To String | This SQL expression is used when a datatype needs to be converted to a string. |
| SQL Target To Data Type | This SQL expression is used when a string datatype needs to be converted to another datatype. |
| Description | Optional metadata to provide description. |
| Column Alias | Maps to a Columns Column Alias field. Example, DTM_@@this |
| Is Master | If true this Data Type Mapping may be used as a Mapped To Data Type. |
| Convert Source Type | When enabled, applies the DATA TYPE, LENGTH, PRECISION and SCALE from the Data Type Mappings to the DATA TYPE, LENGTH, PRECISION and SCALE property of the Columns with the defined data types if the APPLY CONVERT DATA TYPE MAPPING is selected when using the [Apply Data Type Mappings] button. |
[//]: # (TODO: Create or link to a Unified System Types guide. [unified-system-types.md]) [//]: # (TODO: Outline or link to configuration of a Data Type = 'CustomType'.)
[//]: # (TODO: Circle back and outline some examples of the follow: Proper use of[Mapped To Data Type], Examples of using proper and improper [DEFAULT], Example for each 'Expression', examples of each 'SQL' column, [Column Alias] usage, Ranged values and qualifying mapping i.e. [Length], [Precision], [Scale]. Once completed set an Anchor Link in the table above.)
[//]: # (### Detailed Examples)
[//]: # (Examples that can better communicate usage of more complex concepts are included below. )
[//]: # (#### Ranged Values Detailed Examples)
[//]: # (TODO: Create a more detail example of [Length] usage and qualification.)
Allowed Values
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.
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 |