Columns
The BimlFlex Column concept contains the components of an Column. Columns define the detailed information to transform and map data from source to target.
Columns within BimlFlex are displayed in a hierarchical tree view on the left side navigation menu. The total number of Columns within a Project will be displayed next to the Project name in parentheses. The total number of Columns within an individual Object will similarly be displayed next to the Object name.
Selecting a Project will display a secondary list of all Columns within, organized by Object.
The user may navigate to any Column by selecting it from the displayed list.
Overview
Property |
Description |
|---|---|
| Name | Name of the Column within the Object. For any column other than the source we recommend using CamelCase. For ORACLE we recommend UPPERCASE separated by underscores. |
| Length | This value specifies the length parameter for the Column data type. Set to -1 to indicate MAX length. This property applies only to column types that support a length specification, such as String and Binary types. |
| Precision | This value specifies the precision parameter for the Column data type. Precision is the number of digits stored for a numeric value. This property applies only to column types that support precision, such as Decimal. |
| Scale | This value specifies the scale parameter for the Column data type. Scale is the number of digits to the right of the decimal point in a numeric value. This property applies only to column types that support precision, such as Decimal. |
| Element | This value specifies the scale parameter for the Column data type. Scale is the number of digits to the right of the decimal point in a numeric value. This property applies only to column types that support precision, such as Decimal. |
| Ordinal | This value specifies the length parameter for the column type associated with this Column. Set to -1 to indicate MAX length. This property applies only to column types that support a length specification, such as String and Binary types. |
| Primary Key | When enabled, this Column is considered to be part of the Primary Key of the Object. The Primary Key uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. |
| Integration Key | When enabled, this Column is considered to be the Integration Key of the Object. Note that the BimlFlex framework allows only one Integration Key per Object. If the Object has multiple Columns that makes up the Integration Key a derived concatenated Column should be derived. |
| Source Key | When enabled, this Column is considered to be part of the Primary Key of the Object. This is generally defined if the Object has multiple Columns that define the Integration Key or a derived concatenated Integration Key has been defined. |
| Identity | When enabled, this Column is considered to be an identity Column for the Object. |
| Nullable | When enabled, this Column can contain NULL (empty) values. |
| Not Persistent | Defines an override in the behaviour of the Persistent Staging Area (PSA) that applies to a Column for a Connection. When enabled, the Column will not be stored in the PSA. In other words, enabling thisoverride will exclude the column from being persisted. |
| Not Hashed | When enabled the column will be excluded from the Row Hash Differential comparison, ensuring that the column does not affect the hash difference calculation. |
| Exclude from Model | Enabling Exclude From Model for this Column will prevent this Column to be built along with the rest of the solution when the BimlFlex solution is compiled. |
| Business Name | This column contains the business column name users want the object to appear as after the solution is deployed. This is better described with and example. Let’s say you have a source system with cryptic names like GL002 and the actual business name is GeneralLedgerCode. By defining a value and generating a Data Vault using the BimlFlex Accelerator the resulting column will be [GeneralLedgerCode] |
| Business Subject | This value is used to group Columns into their own Satellites when using a Data Vault target architecture. For example, if you have a Customer table that has a column LastLoginDate that changes frequently you might want to model this in a separate Satellite. All Columns with the same Business Subject will be generated into separate Satellites. It is also used to define a Unit Of Work for Links. |
| Business Reference | The Business Reference is used to relate multiple table references together. For example, consider a SalesOrder table that has a ShippingAddress and BillingAddress reference to the Address table. Using a Data Vault target architecture two Link Objects will be created with the Business Reference forming part of the name. This attribute is autogenerated by our schema import and can be overridden. |
| Default Value | The default value the Column should receive if no data is provided. |
| Sql Source Expression | Defines a SQL expression for the selected column, which can be used to extend source queries. Generally used for (source) type casting and case statements. Example: CONVERT(VARCHAR(27), @@this, 121) |
| Sql Target Expression | Defines a SQL expression for the selected column, which can be used to extend target queries. This will apply to the column specified in the Target Column. Generally used for source type casting and case statements. Example: CONVERT(VARCHAR(27), @@this, 121) |
| Dataflow Expression | This value specifies the expression used for the derived column. The expression syntax for the target Integration Stage needs to be used. For example, for SSIS the corresponding expression syntax is required, the same syntax as applied in the Derived Column Transformations. |
| Dataflow Data Type | This value is used to overwrite the data type for the Dataflow Expression. |
| Derived | When enabled, the Column is considered to be derived during the data logistics process. It is not a Column that exists in the (source) Object, but is evaluated during the process. This requires the Data Flow Expression to be used to specify the logic that defines the Column. |
| Solve Order | This attribute is used when defining Dataflow Expressions that need to be evaluated in a specific order. For example, if you want to split a very complex calculation up into multiple expressions that are derived in a specific sequence. The value in the solve order itself determines the order of processing. A higher number will be processed later than Columns that have a lower number for the Solve Order. If the number is the same, the expressions will be evaluated at the same time - usually in the same transformation. |
| Column Alias | Adds an alias for the Column. Generally used in conjunction with Sql Source Expression. |
| Friendly Name | An alias for the Column to be used to display the Column Name in a user-facing way. |
| Description | Free-format additional documentation about the Column. |
| Comments | Generic comments for use by the Analyst and Modelers to keep notes related to the column. |
References
Property |
Description |
|---|---|
| Object | Relationship to the Object for this Column. |
| Customer | Reference to the Customer that this Column belongs to. |
| Version | Reference to the Version that this Column belongs to. |
| Data Type | This value specifies the type of the data stored in this Column using the unified type system. Additional logic is required to cater for data types with a CustomType like hierarchyid. Below is a list of the current possible Data Types. |
| Change Type | This value specifies the slowly changing dimension type for the Column. |
| Business Attribute | The Business Attribute that is assigned for this Column. |
| Data Type Mapping | The Data Type Mapping that applies to this Column. |
| Reference Column | Relationship between this Column and the Reference Object. Reference Columns are used to define logical relationships between Objects. This is used to relate Integration Keys in Data Vault sources and Foreign Keys in the Data Warehouse targets. |
| Target Column | The target Column specified for the selected Column. I.e the target Column in a source-to-target column mapping. |