Objects
Objects in BimlFlex are the data stores that can be used as source, or target, for data logistics. Objects are managed in the Object Editor.
An Object can be a database table, a flat file, Excel documents, and, via use of Extensions Points, APIs calls.
Instead of using multiple entity types to handle these scenarios BimlFlex uses managed metadata and the Object Type field to annotate what type of Object is being referenced.
Overview
Property |
Description |
|---|---|
| Schema | The database Schema the Object belongs to. |
| Name | Name of the Object (e.g. table or view). The BimlFlex recommended practice is to use CamelCase as a naming convention. During the import of source metadata BimlFlex provides options to convert the name with underscores to CamelCase, or using various other conventions. |
| Not Persistent | When enabled, this will prevent a thread Staging Area (PSA) table or file from being created for this Object. This features defines an override that applies to an Object for a Connection that impacts the behaviour in the Persistent Staging Area (PSA). When enabled, the Object will not be stored in the PSA. In other words, setting this override to Y will exclude the column from being persisted. |
| Sequential | |
| Exclude Build | Enabling will determine that this Object will be built along with the rest of the solution when the BimlFlex solution is being compiled. Disabling this will not generate an output artifact for the Object, as it will be removed from the build process. |
| Exclude from Model | Enabling will determine if this object’s metadata will be excluded along with the rest of the BimlFlex solution. |
| Source Create Sql | Specify a SQL statement that can be used to override the creation, or definition, of an Object when it is a source - an Object that is selected from. For example, this can be used to deploy a view onto the source database to represent the Object or to add SQL to a CI\CD pipeline. This must be a complete SQL statement. |
| Create Sql | Specify a SQL statement that can be used to override the creation, or definition, of a target Object. By default, target Objects are generated by BimlFlex but the way these are created can be overridden here for each Object individually. This must be a complete SQL statement. |
| Override Sql | 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. |
| From Sql | Override the FROM clause of the generated SQL statement that is used to query the Object. This snippet must start with the FROM keyword. This can be used to customize the query behaviour. If using an Object Alias this must be included in the SQL statement. For example FROM @@this source WITH (NOLOCK). |
| Join Sql | If the source select requires a JOIN this is where it can be entered. Example: INNER JOIN [SalesLT].[CustomerAddress] ca ON src.[AddressID] = ca.[AddressID] AND ca.[AddressType] = ’Main Office’ The query is limited to 1000 characters. |
| Where Sql | If the source select requires a WHERE clause this is where it can be entered. Note that defining a Parameter on the object will be appended to the statement. Example: WHERE [Culture] = ’en’. |
| Object Alias | Defines an alias (short name) for the Object. This is relevant in conjunction with the JoinSql and/or WhereSql attributes. Generally src is used as a default. |
| Select By Sql | If the source select requires a DISTINCT or TOP N clause this is where it can be entered. The field is limited to 1000 characters. |
| Group By Sql | If the source select requires a GROUP BY clause this is where it can be entered. The field is limited to 1000 characters. |
| Order By Sql | If the source select requires an ORDER BY clause this is where it can be entered. The field is limited to 1000 characters. |
| Business Name | The name of the Business Concept or Target Object you are modeling. This contains the actual 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 GeneralLedger. By defining a value and generating a Data Vault using the BimlFlex Accelerator the result would be [HUB_GeneralLedger], [SAT_GeneralLedger]. Suffix or prefix values are all configured via the use of Settings and are generated dynamically depending on entered metadata. |
| Business Short Name | This column contains the short name users want the Object to appear as after the solution is deployed. If populated, this value takes precedence when a ’Short Name’ is applicable. This is generally used in the generation and naming of Link Tables (LNKs), and other entities whose names are dynamically generated from a concatenation of entity names. This works as per the BusinessName, but is used in naming Links and Link Satellites. As an example if there is a link between the GeneralLedger and ChartOfAccounts the name will be [LNK_GeneralLedger_ChartOfAccounts]. Defining values here the name could be [LNK_GL_COA], providing flexibility when modelling. |
| Business Subject | Categorizes Objects into a subject area grouping. This grouping can be used in the Data Vault Accelerator for constrained acceleration. |
| Same As Inherited | Defines if the Inherit From Object is the same Object. Enabling this will instruct BimlFLex to reuse the inherited tables for loading. |
| Use Inherited Name | Defines if the Inherit From Object has the same object. Entering Y into this column will instruct BimlFLex to reuse the inherited tables ObjectName for loading. |
| Source File Path | The path where the source file are located. Not applicable to SQL Server Integration Services (SSIS) projects. |
| Source File Pattern | The pattern of the source file. Not applicable to SQL Server Integration Services (SSIS) projects. |
| Source File Filter | The filter of the source file to be applied to the Filter Activity after the Metadata Activity. |
| Landing File Path | The path where the target files should placed. Not applicable to SQL Server Integration Services (SSIS) projects. |
| Landing File Pattern | The pattern of the target file. Not applicable to SQL Server Integration Services (SSIS) projects. |
| Persistent Landing File Path | The file path where the raw landing files will be stored persistently. This is the initial location where data files land before processing. |
| Persistent Landing File Pattern | The naming pattern used for files that are stored in the Persistent Landing File Path. This pattern may include date, time, or other variable elements. |
| Partitioned By Clause | Databricks CREATE TABLE an optional clause to partition the table by a subset of columns. |
| Location Clause | Databricks CREATE TABLE an optional path to the directory where table data is stored, which could be a path on distributed storage. |
| Cluster By Clause | Databricks CREATE TABLE optionally cluster the table or each partition into a fixed number of hash buckets using a subset of the columns. |
| Table Properties | Databricks CREATE TABLE optionally sets one or more user defined properties. Recommended to add TBLPROPERTIES (delta.autoOptimize.optimizeWrite = true, delta.autoOptimize.autoCompact = true) |
| Option Clause | Databricks CREATE TABLE sets or resets one or more user defined table options. |
| Column Names In First Row | This value specifies whether more descriptive column names are provided in the first data row to replace the column names specified in the Flat File Format Columns collection. |
| Flat File Type | The Flat File Type provides options for the type of flat file row format that will be used. |
| Data Rows To Skip | This value specifies how many data rows to skip before beginning to product data. |
| Text Qualifier | This value specifies which character is used to identify contiguous blocks of text in a flat file. Column delimiters within blocks of contiguous text will be ignored and treated as text data. |
| Row Delimiter | This value specifies the delimiter to use to segregate rows. |
| Column Delimiter | This value specifies the delimiter string that will be used to mark the end of this column. |
| Last Column Delimiter | This value specifies the delimiter to use for column header rows. |
| Code Page | This value specifies the code page to use for character and string manipulation of non-Unicode text. |
| Unicode | This value specifies whether Unicode character and string manipulation will be used. |
| Compression Level | The compression applied to a flat file Object. |
| Encoding | The encoding applied to a flat file Object. |
| Escape character | This value specifies the Dataset Escape character to use. |
| Null value | This value specifies the Dataset Null value to use. |
| Friendly Name | Business-friendly name of the Object. |
| Description | The Object described in business context. Often referred to as business metadata. |
| Comments | Generic comments for use by the Analyst and Modelers to keep notes related to the Object. |
| Own Thread | Objects will be executed within the Batch based on Topological Sort Order and then alphabetical. If you would like the object to be executed in its own thread at the start of the respective execution container set this attribute to true |
| Threads | At the object level, this is where we can define the amount of threads that we want the load to be distributed across in the SSIS package. |
References
Property |
Description |
|---|---|
| Project | The Project that the Object belongs to. A Project must exist for an Object to be created. If you would like the Object to be referenced by additional Projects, you can use the InheritFromObject, SameAsInherited and UseInheritedName attributes. This is useful when an Object is used in an incremental load during the day and should be part of a full load during the end of day (month) process. |
| Connection | Every Object is referenced through a Connection, which is set in this field. |
| Customer | Reference to the Customer that this Object belongs to. |
| Version | Reference to the Version that this Object belongs to. |
| Object Type | An Object Type is a classification of the Object, to identify the role to play in the data processing. The Object Type is used to select the code generation to apply at Object level. The most common Object Types are Table or Flat File, but various types exist. Please refer to the Object Editor for the full list of allowed values and descriptions. |
| Compression Type | The compression type applied to a flat file Object. |
| Business Entity | The Business Entity that is associated with the Object. |
| Accelerator Type | The classification of the type of target Object you are designing to process from the current (selected Object). This is used to define which (target) model Object the select (source) Object is defined to be. For instance if this object is defining a HUB LINK or SAT this is where we can make this distinction. The framework will interpret the type when importing metadata from source and can be overridden after additional analysis. Please note that this attribute is only relevant when using BimlFlex to generate a Raw Data Vault model. |
| Inherit from Object | Contains a reference to the Object that column metadata should inherited from unless it has its own column metadata. This object will use the inferred from object name in the source query. |
| Depends on Object | Contains a dependency reference to the Object that defines the precedence for execution solve order. |