Configurations

This document outlines the metadata and framework configurations available in BimlFlex.

These configurations drive the behavior of the product.

By changing them the produced artifacts can adapt to support requirements for file locations, naming conventions, data conventions etc.

The Configuration defaults are the Varigence recommended values and there is no need to change or configure unless there is a requirement to change specific behaviors. Align these settings with the organizations best practices and environmental requirements.

The Configurations are available in the Configurations sheet in the BimlFlex Excel Add-in.

Configurations Sheet

Metadata column overview

KeyValue
Configuration Keythe Configuration Key, the internal key BimlFlex refers to, cannot be changed
Configuration Valuethe Configured Value, can be updated to support a different design pattern or behaviour
Configuration Datatypethe data type the configuration Value uses. Needs to be a valid data type definition
Configuration Defaultthe Configuration Key’s Default Value
Configuration GroupingBimlFlex Internal Grouping of configurations
Configuration OrderBimlFlex Internal Ordering of configurations
SSIS Expressionthe SSIS Expression used to derive the value. Needs to be a valid SSIS Expression. Uses the shorthand @@this to define the current entity
Is NullableDefines If the attribute is nullable Valid Enumeration {Empty, Y, N}
Staging AttributeValid Enumeration {Ignore, Derived, Source, Default, Target, Hash}
Persistent Staging AttributeValid Enumeration {Ignore, Derived, Source, Default, Target, Hash}
Hub AttributeValid Enumeration {Ignore, Derived, Source, Default, Target, Hash}
Satellite AttributeValid Enumeration {Ignore, Derived, Source, Default, Target, Hash}
Link AttributeValid Enumeration {Ignore, Derived, Source, Default, Target, Hash}
Dim AttributeValid Enumeration {Ignore, Derived, Source, Default, Target, Hash}
Fact AttributeValid Enumeration {Ignore, Derived, Source, Default, Target, Hash}
DescriptionAn optional description for custom attributes or definitions. The default configurations are described in this document
Is DeletedFlag to set if a custom attribute has been entered but is now no longer needed and should be considered deleted. Valid Enumeration {Empty, Y, N}


Standard configurations

RowEffectiveFromDate

Description

The RowEffectiveFromDate defines the start of time for timelines in the data warehouse. The DateTime2(7) datatype supports a wider range of dates than traditional DateTime meaning starting on 1 Jan 0001 will support most use cases. If a specific start DateTime for timelines is needed, such as to support an existing design template, this can be updated to support it. Unless there is a good reason to change it, it is recommended to keep the default.

The SSIS Expression is used in the Staging and Persistent Staging loads to derive the RowEffectiveFromDate from the @[User::ParentBatchStartTime] variable. This is inserted into the Data Flow to give each row its effectiveness date. The default configuration is to use the start date time of the parent Batch for this column.

An example of implementation usage for the configuration default is in the Default constraint of this table creation script for the Address staging table from the AdventureWorks LT Source:

```

IF NOT EXISTS (SELECT * FROM sys.defaultconstraints WHERE [parentobjectid] = OBJECTID(N'[AWLT].[Address]') AND [name] = 'DFAddressFlexRowEffectiveFromDate')

ALTER TABLE [AWLT].[Address] ADD CONSTRAINT [DFAddressFlexRowEffectiveFromDate] DEFAULT ('0001-01-01 00:00:00.000') FOR [FlexRowEffectiveFromDate] ```

Example

See defaults

Valid Value

Depends on configuration attribute

Default Metadata information

KeyValue
Configuration KeyRowEffectiveFromDate
Configuration ValueFlexRowEffectiveFromDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Configuration Default0001-01-01 00:00:00.000
Ssis Expression@[User::ParentBatchStartTime]
Staging AttributeDerived
Persistent Staging AttributeDerived
Hub AttributeSource
Satellite AttributeSource
Link AttributeSource


RowEffectiveToDate

Description

The RowEffectiveToDate defines the end of time for timelines in the data warehouse. The DateTime2(7) datatype supports a wider range of dates than traditional DateTime meaning ending on 31 Dec 9999 will support most use cases. If a specific start DateTime for timelines is needed, such as to support an existing design template, this can be updated to support it. Unless there is a very good reason to change it, it is recommended to keep the default.

The SSIS Expression is used in the Staging and Persistent Staging loads to derive the RowEffectiveToDate from the (DT_DBTIMESTAMP2, 7)"9999-12-31 00:00:00.000" expression. This is inserted into the Data Flow to give each row its end date. The default configuration is to use the end of timeline definition.

An example of implementation usage for the configuration default is in the Default constraint of this table creation script for the Address staging table from the AdventureWorks LT Source:

``` IF NOT EXISTS (SELECT * FROM sys.defaultconstraints WHERE [parentobjectid] = OBJECTID(N'[AWLT].[Address]') AND [name] = 'DFAddressFlexRowEffectiveToDate')

ALTER TABLE [AWLT].[Address] ADD CONSTRAINT [DFAddressFlexRowEffectiveToDate] DEFAULT ('9999-12-31') FOR [FlexRowEffectiveToDate] ```

Example

See defaults

Valid Value

Depends on configuration attribute

Default Metadata information

KeyValue
Configuration ValueFlexRowEffectiveToDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Configuration Default9999-12-31
Ssis Expression(DT\_DBTIMESTAMP2, 7)"9999-12-31"
Staging AttributeDerived
Persistent Staging AttributeDerived
Satellite AttributeDerived


RowLastSeenDate

Description

The RowLastSeenDate defines the default SSIS Expression used to derive the RowLastSeen attribute

Example

(DT_DBTIMESTAMP2, 7)"1900-01-01"

Valid Value

A valid SSIS expression and data type.

Default Metadata information

KeyValue
Configuration ValueFlexRowLastSeenDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Ssis Expression(DT\_DBTIMESTAMP2, 7)"1900-01-01"


RowStartDate

Description

The RowStartDate defines the start of time definition for a row in the data warehouse. This attribute is used to define the timeline in use, from start of time to end of time.

Example

(DT_DBTIMESTAMP2, 7)GETDATE()

Valid Value

A valid SQL, SSIS Expression and data type that defines a date time

Default Metadata information

KeyValue
Configuration ValueFlexRowStartDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Configuration Default1900-01-01
Ssis Expression(DT\_DBTIMESTAMP2, 7)GETDATE()
Dim AttributeDerived


RowEndDate

Description

The RowEndDate defines the end of time definition for a row in the data warehouse. This attribute is used to define the timeline in use, from start of time to end of time

Example

(DT_DBTIMESTAMP2, 7)"9999-12-31 00:00:00.000"

Valid Value

A valid SQL, SSIS Expression and data type that defines a date time

Default Metadata information

KeyValue
Configuration ValueFlexRowEndDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Ssis Expression(DT\_DBTIMESTAMP2, 7)"9999-12-31 00:00:00.000"
Satellite AttributeDerived


RowAuditId

Description

The RowAuditId defines the derivation pattern for the audit value for a row. The default derives the audit id from the ExecutionID user variable. This value will be added to all rows as the audit id, default column name: [FlexRowAuditId]

Example

@[User::ExecutionID]

Valid Value

A valid SSIS Expression and data type, attributes as per their enumerations

Default Metadata information

KeyValue
Configuration ValueFlexRowAuditId
Configuration DatatypeDataType="Int64"
Ssis Expression@[User::ExecutionID]
Staging AttributeDerived
Persistent Staging AttributeDerived
Hub AttributeDerived
Satellite AttributeDerived
Link AttributeDerived
Dim AttributeDerived
Fact AttributeDerived


RowChangeType

Description

The RowChangeType defines the string representation of the change type when inserting new rows into the data warehouse.

Example

I

Valid Value

A Valid SSIS Expression

Default Metadata information

KeyValue
Configuration ValueFlexRowChangeType
Configuration DatatypeDataType="AnsiString" Length="1"
Configuration DefaultI
Ssis Expression(DT\_STR,10,1252)"I"
Staging AttributeDerived
Persistent Staging AttributeDerived
Satellite AttributeSource


RowRecordSource

Description

The RowRecordSource defines the record source for the data. This is a required attribute for Data Vault sources and normally defined in the connections definition for external sources loaded into the Data Vault

Example

(DT_STR,10,1252)"@@this"

Valid Value

A valid SSIS Expression

Default Metadata information

KeyValue
Configuration ValueFlexRowRecordSource
Configuration DatatypeDataType="AnsiString" Length="10"
Configuration DefaultFLX
Ssis Expression(DT\_STR,10,1252)"@@this"
Staging AttributeDerived
Persistent Staging AttributeDerived
Hub AttributeSource
Satellite AttributeSource
Link AttributeSource


RowSourceId

Description

The RowSourceId defines the sequence number of the data row within the set. This is used to identify all rows in order within a batch.

Example

-1

Valid Value

A valid SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowSourceId
Configuration DatatypeDataType="Int32"
Configuration Default-1
Ssis Expressiontrue
Staging AttributeDerived
Persistent Staging AttributeDerived
Satellite AttributeSource


RowIsCurrent

Description

The RowIsCurrent defines the current row flag for timelined data, such as for satellites. The RowIsCurrent is the definition for how the current row is defined in the data.

Example

true

Valid Value

A valid SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowIsCurrent
Configuration DatatypeDataType="Boolean"
Configuration Default1
Ssis Expressiontrue
Staging AttributeDerived
Persistent Staging AttributeDerived
Satellite AttributeDerived
Dim AttributeDerived


RowIsDeleted

Description

The RowIsDeleted defines the derivation pattern to if a row is deleted or not

Example

false

Valid Value

A valid SSIS expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowIsDeleted
Configuration DatatypeDataType="Boolean"
Configuration Default(DATEPART("ms", GETDATE())%2)==1?TRUE:FALSE
Ssis Expressionfalse
IsNullableY
Staging AttributeDerived
Persistent Staging AttributeDerived
Satellite AttributeSource


RowIsInferred

Description

The RowIsInferred defines if the row is inferred

Example

false

Valid Value

SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowIsInferred
Configuration DatatypeDataType="Boolean"
Configuration Default1
Ssis Expressionfalse
IsNullableY
Dim AttributeDerived


RowHash

Description

The RowHash defines the expression used to derive a full row hash. The default uses the combination of a forward hash and a backwards hash to minimise the risk for hash collisions. The default hashing mechanism uses the Varigence Custom SSIS Components.

Example

[vck@@this1]+[vck@@this2] 000000000000000000000000000000000000000000000000000000000000000000000000000000

Valid Value

A Valid SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowHash
Configuration DatatypeDataType="AnsiString" Length="80"
Configuration Default000000000000000000000000000000000000000 000000000000000000000000000000000000000
Ssis Expression[vck@@this1]+[vck@@this2]
IsNullableY
Staging AttributeHash
Persistent Staging AttributeHash


RowHashKey

Description

The RowHashKey defines the expression used to derive a key hash. The default hashing mechanism uses the Varigence Custom SSIS Components.

Example

[vck@@this1]

Valid Value

A valid SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowHashKey
Configuration DatatypeDataType="AnsiString" Length="40"
Configuration Default000000000000000000000000000000000000000
Ssis Expression[vck@@this1]
IsNullableY
Staging AttributeHash
Persistent Staging AttributeHash


RowHashSat

Description

The RowHashSat defines the expression used to derive a satellite attribute hash. The default hashing mechanism uses the Varigence Custom SSIS Components

Example

[@@this1]

Valid Value

A valid SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowHashSat
Configuration DatatypeDataType="AnsiString" Length="40"
Configuration Default000000000000000000000000000000000000000
Ssis Expression[vck@@this1]
IsNullableY
Dim AttributeHash


RowHashType1

Description

The RowHashType1 defines the expression used to derive a forward hash. The default hashing mechanism uses the Varigence Custom SSIS Components

Example

[vck@@this1]

Valid Value

A valid SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowHashType1
Configuration DatatypeDataType="AnsiString" Length="40"
Configuration Default000000000000000000000000000000000000000
Ssis Expression[vck@@this1]
IsNullableY
Dim AttributeHash


RowHashType2

Description

The RowHashType2 defines the expression used to derive a backward hash. The default hashing mechanism uses the Varigence Custom SSIS Components

Example

[vck@@this1]

Valid Value

A valid SSIS Expression and data type

Default Metadata information

KeyValue
Configuration ValueFlexRowHashType2
Configuration DatatypeDataType="AnsiString" Length="40"
Configuration Default000000000000000000000000000000000000000
Ssis Expression[vck@@this1]
IsNullableY
Dim AttributeHash


RowLoadSequence

Description

The RowLoadSequence defines the data type for the Load Sequence organiser.

Example

DataType="Int32"

Valid Value

A valid sortable integer data type

Default Metadata information

KeyValue
Configuration ValueFlexRowLoadSequence
Configuration DatatypeDataType="Int32"


RootPath

Description

The RootPath defines the default Root Path folder of the BimlFlex solution.

Example

C:\Varigence\BimlFlex

Valid Value

A valid path

Default Metadata information

KeyValue
Configuration ValueC:\Varigence\BimlFlex


ImportPath

Description

The ImportPath defines the default folder path for file import.

Example

C:\Varigence\Import

Valid Value

A valid path

Default Metadata information

KeyValue
Configuration ValueC:\Varigence\Import


ExportPath

Description

The ExportPath defines the default folder path for file exports.

Example

C:\Varigence\Export

Valid Value

A valid path

Default Metadata information

KeyValue
Configuration ValueC:\Varigence\Export


UseBimlCatalog

Description

The UseBimlCatalog defines whether or not to use the BimlFlex Catalog database for SSIS package orchestration and logging.

Example

Y

Valid Value

Enumeration {Y,N}

Default Metadata information

KeyValue
Configuration ValueY


ConfigurationPath

Description

The ConfigurationPath key defines the default path for configurations

Example

C:\Varigence\Configurations

Valid Value

A valid path

Default Metadata information

KeyValue
Configuration ValueC:\Varigence\Configurations


7ZipPath

Description

The 7ZipPath key defines the file path/location of the 7-Zip application that is used for zipping/compression of files. The 7-zip executables are needed for zip-related operations. The 7-Zip application is open source and available to use without license cost.

More information and downloads: http://www.7-zip.org/download.html

Example

C:\Program Files\7-Zip

Valid Value

A valid path to the 7-Zip executable

Default Metadata information

KeyValue
Configuration ValueC:\Program Files\7-Zip


AzCopyPath

Description

The AzCopyPath key defines the file path/location of the AzCopy application used to copy files to Azure storage.

Example

C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy

Valid Value

A valid path to the AzCopy executable

Default Metadata information

KeyValue
Configuration ValueC:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy


KeyEndsWith

Description

The KeyEndsWith key defines the text the metadata import uses to identify key columns that aren’t technically identified as keys in the source. Add any source specific key identifiers to enable automatic identification of key columns.

Example

Id,Code,No,Key,Cd

Valid Value

Any valid comma separated list of SQL or SSIS Strings

Default Metadata information

KeyValue
Configuration ValueId,Code,No,Key


SuffixOrPrefixColumn

Description

The SuffixOrPrefixColumn key defines the behaviour when defining column names. Use Suffix or Prefix to define if the column identifiers are added after or before the column names in the solution.

Example

S for Suffix will generate Entity_SK P for Prefix Will generate SK_Entity

Valid Value

Enumeration {P, S}

Default Metadata information

KeyValue
Configuration ValueS


SuffixOrPrefixObject

Description

The SuffixOrPrefixObject key defines the behaviour when defining object names. Use Suffix or Prefix to define if the object identifiers are added after or before the object names in the solution.

Example

S for Suffix Will generate Entity_HUB P for Prefix Will generate HUB_Entity

Valid Value

Enumeration {P, S}

Default Metadata information

KeyValue
Configuration ValueP


UseRecordSourceAsAppend

Description

The UseRecordSourceAsAppend Key specifies if the record source should be appended to the object name

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


UseRecordSourceAsSchema

Description

The UseRecordSourceAsSchema Key specifies if the record source should be used as the schema for objects. As an example, the default behaviour means a source table called Product from the record source AWLT will be created as AWLT.Product in the Staging Area

Example

Y

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueY


UseColumnModelOverride

Description

The UseColumnModelOverride Key specifies if the model override for column should be used instead of the source names for columns in the Staging and Persisted Staging areas. The recommended, and default behaviour, is to use source names for Staging and only use override names in the Data Vault/later layers.

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


UseObjectModelOverride

Description

The UseObjectModelOverride Key specifies if the model override for objects should be used instead of the source names for objects in the Staging and Persisted Staging areas. The recommended, and default behaviour, is to use source names for Staging and only use override names in the Data Vault/later layers.

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


HashBusinessKey

Description

The HashBusinessKey Key specifies if the Business Key should be hashed. This is implemented by default for Data Vault regardless of setting but can be specified for other modelling approaches.

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


ConcatenatorBusinessKey

Description

The ConcatenatorBusinessKey Key specifies the value to use as filler between business keys when concatenating them. Single business keys are created from multiple source columns through concatenation to maintain a single business key. When concatenating it is important to be able to distinguish between similarly formed inputs. E.g. concatenating ABC and DEF without the concatenator will make it the same as AB + CDEF (ABCDEF). The concatenator will maintain the two as different and distinct entities (ABC~DEF vs. AB~CDEF). Using a concatenator is required to maintain data integrity but the value can be configured to support an existing process, design pattern or specific requirement.

Example

~

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration Value~


BusinessKeyNullValue

Description

The BusinessKeyNullValue Key specifies the defined value to use for null values in the business key.

Example

~NULL~

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration Value


AppendBusinessKey

Description

The AppendBusinessKey Key specifies the string to append to the Business Key Columns. Prefixing or suffixing is specified by the SuffixOrPrefixColumn configuration

Example

BK = BusinessKeyColumnName_BK

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueBK
Configuration DatatypeDataType="AnsiString" Length="40"


AppendSurrogateKey

Description

The AppendSurrogateKey Key specifies the string to append to the Surrogate Key Columns. Prefixing or suffixing is specified by the SuffixOrPrefixColumn configuration

Example

SK = KeyColumnName_SK

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueSK
Configuration DatatypeDataType="AnsiString" Length="40"


AppendRecordSource

Description

The AppendRecordSource Key specifies if the Record source should be appended to object names

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


AppendSchemaDm

Description

The AppendSchemaDm Key specifies if the Schema should be appended in the Data Mart layer

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


AppendSchemaRdv

Description

The AppendSchemaRdv Key specifies if the Schema should be appended in the Data Vault layer

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


AppendSchemaPsa

Description

The AppendSchemaPsa Key specifies if the Schema should be appended for the Persistent Staging layer when colocated in the Staging database

Example

ods

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration Valueods


AppendSchemaStg

Description

The AppendSchemaStg Key specifies if the source schema should be appended to the object name. this is useful for when a source has multiple schemas with the same object name repeated across these schemas. To be able to distinguish between them in the Staging Area the schema name needs to be added. The default process disregards the schema for simplicity in the naming. An example where this might be needed is when loading all tables from the WideWorldImporters demo database where the same table name is repeated across multiple schemas.

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


AppendDomain

Description

Should Domain be addedf

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


DisplayDatabaseNameStg

Description

Should the Database name be added to the Staging Layer

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


DisplaySchemaNameStg

Description

Should the source schema name be added to the Staging Layer

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


LookupCachePath

Description

The LookupCachePath Key specifies the path for cache files for the lookup process when it is using external persistence of cache data.

Example

C:\Varigence\Cache

Valid Value

Any valid and safe path

Default Metadata information

KeyValue
Configuration ValueC:\Varigence\Cache


DisplayDatabaseNameRdv

Description

The DisplayDatabaseNameRdv Key specifies if the database name should be added to the Rdv layer

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


DisplaySchemaNameRdv

Description

The DisplaySchemaNameRdv Key specifies if the source schema name should be added to the Rdv Layer

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


LookupAddFilterTable

Description

The LookupAddFilterTable Key specifies if table filter should be added to the lookup

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


DisplayDatabaseNameDm

Description

The DisplayDatabaseNameDm Key specifies if the objects database name should be displayed in the Data Mart.

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


LookupTablePattern

Description

The LookupTablePattern Key specifies the lookup naming convention used for SSIS table lookup.

Example

lkp.ReferenceColumnName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration Value"lkp." + column.Name.MakeSsisSafe


DisplaySchemaNameDm

Description

The DisplaySchemaNameDm Key specifies if the objects schema name should be displayed in the Data Mart.

Example

N

Valid Value

Enumeration {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


DmAppendDim

Description

The DvAppendDim Key specifies the string to append to Dimension objects in the Data Mart. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

DIM = DIM_DimensionEntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueDIM


DmAppendFact

Description

The DvAppendFact Key specifies the string to append to Fact objects in the Data Mart. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

FACT = FACT_FactEntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueFACT


DmAppendExternal

Description

The DvAppendExternal Key specifies the string to append to External objects in the Data Mart. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

EXT = EXT_ExternalEntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueEXT


DmAppendStaging

Description

The DvAppendStaging Key specifies the string to append to Staging objects in the Data Mart. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

STAGE = STAGE_DimensionEntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueSTAGE


DvAppendBridge

Description

The DvAppendBridge Key specifies the string to append to Bridge objects. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

BRD = BRD_BridgeEntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueBRD


DvAppendHub

Description

The DvAppendHub Key specifies the string to append to Hub objects. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

HUB = HUB_EntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueHUB


DvAppendLink

Description

The DvAppendLink Key specifies the string to append to Link objects. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

LNK = LNK_LinkName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueLNK


DvAppendLinkSatellite

Description

The DvAppendLinkSatellite Key specifies the string to append to Link Satellite objects. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

LSAT = LSAT_LinkName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueLSAT


DvAppendReference

Description

The DvAppendReference Key specifies the string to append to Reference objects. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

REF = REF_ReferenceEntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueREF


DvAppendSatellite

Description

The DvAppendSatellite Key specifies the string to append to Satellite objects. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

SAT = SAT_EntityName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValueSAT


DvPreviewSchema

Description

The DvPreviewSchema Key specifies the default schema to use for Data Vault Accelerator generated preview objects

Example

pdv

Valid Value

Any valid and safe SQL schema name

Default Metadata information

KeyValue
Configuration Valuepdv


DvDefaultSchema

Description

The DvDefaultSchema Key specifies the default schema to use for Data Vault objects

Example

rdv

Valid Value

Any valid and safe SQL schema name

Default Metadata information

KeyValue
Configuration Valuerdv


DvAppendPointInTime

Description

The DvAppendPointInTime Key specifies the string to append to Point in time objects. Prefixing or suffixing is specified by the SuffixOrPrefixObject configuration

Example

PIT = PIT_EntityEventName

Valid Value

Any valid and safe SQL and SSIS String

Default Metadata information

KeyValue
Configuration ValuePIT


DvSnapshotFromDate

Description

The DvSnapshotFromDate Key specifies the Data Vault Snapshot from/start date

Example

0001-01-01 00:00:00.000

Valid Value

Any valid and safe SQL and SSIS date datatype and date expression

Default Metadata information

KeyValue
Configuration ValueSnapshotFromDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Configuration Default0001-01-01 00:00:00.000


DvSnapshotToDate

Description

The DvSnapshotToDate Key specifies the Data Vault Snapshot to/end date

Example

9999-12-31 00:00:00.000

Valid Value

Any valid and safe SQL and SSIS date datatype and date expression

Default Metadata information

KeyValue
Configuration ValueSnapshotToDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Configuration Default9999-12-31 00:00:00.000


DvSnapshotIncremental

Description

The DvSnapshotIncremental Key specifies if the Data Vault Snapshot feature should provide incremental snapshots.

Example

Y

Valid Value

Enumerator {Y, N}

Default Metadata information

KeyValue
Configuration ValueY


DvSnapshotLastModifiedDate

Description

The DvSnapshotLastModifiedDate Key specifies the Data Vault Snapshot last modified date

Example

0001-01-01 00:00:00.000

Valid Value

Any valid and safe SQL and SSIS datatype and expression

Default Metadata information

KeyValue
Configuration ValueLastModifiedDate
Configuration DatatypeDataType="DateTime2" Scale="7"
Configuration Default0001-01-01 00:00:00.000


EnableRollbackStg

Description

The EnableRollbackStg Key specifies if the Staging Area should accommodate the orchestration rollback feature. This will roll back a failed previous load when identified by the orchestration engine. Note that the Staging Area is truncated on load using the normal load pattern making rollback here irrelevant.

Example

N

Valid Value

Enumerator {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


EnableRollbackPsa

Description

The EnableRollbackPsa Key specifies if the Persistent Staging Area should accommodate the orchestration rollback feature. This will roll back a failed previous load when identified by the orchestration engine.

Example

N

Valid Value

Enumerator {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


EnableRollbackRdv

Description

The EnableRollbackRdv Key specifies if the Raw Data Vault should accommodate the orchestration rollback feature. This will roll back a failed previous load when identified by the orchestration engine.

Example

N

Valid Value

Enumerator {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


EnableInitialRecordRdv

Description

The EnableInitialRecordRdv Key specifies if the Raw Data Vault should produce initial records for entities. This is useful when an unbroken timeline is needed to support equijoins, inner joins on Hubs/Links to Sats regardless of the effectiveness dates used. With this configuration set to No, a satellite load of a new business key will only add a single row to the Raw Data Vault table. The effectiveness will be from the batch load date time to end of time. With the configuration set to Yes the Satellite load process will add 2 rows, the additional one will be a zero or ghost row with an effectiveness from start of time to the batch load date time.

Example

N

Valid Value

Enumerator {Y, N}

Default Metadata information

KeyValue
Configuration ValueN


EnableEndDateRdv

Description

The EnableEndDateRdv Key specifies if the Raw Data Vault should end date loaded information. This is useful to simplify reads out of the Data Vault. The end dating will change the end data of the previous row to the load date of the new row and the new row will have an end date equal to the end of time specification. The process will also maintain a isCurrent flag for rows meaning it is trivial to derive the current valid set of data. The end dating process adds time and effort to the load processing time and can therefore be configured if needed. Setting this to No can potentially increase performance when loading in to the Data Vault.

Example

N

Valid Value

Enumerator {Y, N}

Default Metadata information

KeyValue
Configuration ValueY


SsisMaxConcurrentExecutables

Description

The SsisMaxConcurrentExecutables Key specifies the number of SSIS control flow executables that can run in parallel. The default value of -1 translates to the number of logical processors plus 2 concurrent tasks. This value can be tweaked to optimise performance in certain scenarios.

Example

10

Valid Value

A valid Integer value

Default Metadata information

KeyValue
Configuration Value-1


SsisEngineThreads

Description

The SsisEngineThreads Key specifies the SSIS property with the same name. this defines the number of engine threads SSIS will use. This value can be tweaked to optimise performance in certain scenarios.

Example

10

Valid Value

A valid Integer value

Default Metadata information

KeyValue
Configuration Value10


SsisMaximumInsertCommitSize

Description

The SsisMaximumInsertCommitSize Key specifies the SSIS property with the same name. this defines the maximum insert commit size to use in a bulk operation. This value can be tweaked to optimise performance in certain scenarios.

Example

2147483647

Valid Value

A valid integer value

Default Metadata information

KeyValue
Configuration Value2147483647


SsisRowsPerBatch

Description

The SsisRowsPerBatch Key specifies the SSIS property with the same name. this defines the number of rows to use in a bulk operation. This value can be tweaked to optimise performance in certain scenarios.

Example

500000

Valid Value

A valid integer value

Default Metadata information

KeyValue
Configuration Value500000


SsisValidateExternalMetadata

Description

The SsisValidateExternalMetadata Key specifies if the SSIS component should validate the external metadata against the cached information. Setting this to false can be useful if the source metadata should be disregarded due to temporary changes or similar scenarios

Example

True

Valid Value

Boolean Enumerator {True False}

Default Metadata information

KeyValue
Configuration Valuetrue


SsisDelayValidation

Description

The SsisDelayValidation Key specifies if the Ssis component should delay metadata validation. This is useful when the source or reference is not always available and there is a need to delay validation until later

Example

True

Valid Value

Boolean Enumerator {True, False}

Default Metadata information

KeyValue
Configuration Valuetrue


SsisCheckConstraints

Description

The SsisCheckConstraints Key specifies if the destination transformation should check constraints when writing to source. By default, this is disabled to enable faster transfers. Within Staging, Persistent Staging and Data Vault layers’ constraints should not be enforced as it makes the solution less flexible and disallows parallel and out of sequence loading.

Example

False

Valid Value

Boolean Enumerator {True, False}

Default Metadata information

KeyValue
Configuration Valuefalse


SsisCommandTimeout

Description

The SsisCommandTimeout Key specifies the command timeout to use for SSIS tasks

Example

10

Valid Value

A valid Integer value

Default Metadata information

KeyValue
Configuration Value0


SsisDefaultBufferMaxRows

Description

Defines the Maximum number of rows used in a task buffer. The default used is 10,000 rows

Example

10000

More information

https://msdn.microsoft.com/en-us/library/ms141031.aspx

Valid Value

A valid Integer value

Default Metadata information

KeyValue
Configuration Value10000


SsisDefaultBufferSize

Description

Define the default size of the buffer that the task uses, by setting the DefaultBufferSize property. The default buffer size is 10 megabytes, with a maximum buffer size of 2\^31-1 bytes.

Example

10485760

More information

https://msdn.microsoft.com/en-us/library/ms141031.aspx

Valid Value

A valid positive Integer value up to 2\^31-1 bytes.

Default Metadata information

KeyValue
Configuration Value10485760


SsisBufferTempStoragePath

Description

Defines the part where SSIS will store temporary buffer data if needed when processing a package. The default location (defined by the TMP/TEMP environment variables) is used if this key is empty.

Example

E:\FastDisk\Folder\

Valid Value

A valid path.

Default Metadata information

KeyValue
Configuration Value


SsisBLOBTempStoragePath

Description

Defines the part where SSIS will store temporary BLOB data if needed when processing a package. The default location (defined by the TMP/TEMP environment variables) is used if this key is empty.

Example

E:\FastDisk\Folder\

Valid Value

A valid path.

Default Metadata information

KeyValue
Configuration Value


AzureDestStorageAccountName

Description

The Destination Storage Account Name used when writing to an Azure storage container.

Valid Value

A valid Account Name. Account names are unique across Azure, and contains 3-24 lowercase characters and numbers

Default Metadata information

KeyValue
Configuration Value


AzureDestStorageAccountKey

Description

The Destination Account Key used when writing to an Azure storage container.

Valid Value

A valid Account Key.

Default Metadata information

KeyValue
Configuration Value


AzureDestContainerName

Description

The Destination Container Name used for writing to an Azure storage container.

Valid Value

A valid Azure Container Name. container names are 3-63 lowercase alphanumeric and dash

Default Metadata information

KeyValue
Configuration Value


AzureSourceStorageAccountName

Description

The Source Storage Account Name used for sourcing from an Azure storage container.

Valid Value

A valid Account Name. Account names are unique across Azure, and contains 3-24 lowercase characters and numbers

Default Metadata information

KeyValue
Configuration Value


AzureSourceStorageAccountKey

Description

The Source Container Account Key used for sourcing from an Azure storage container.

Valid Value

A valid Azure Storage Account Key.

Default Metadata information

KeyValue
Configuration Value


AzureSourceContainerName

Description

The Source Container Name used for sourcing from an Azure storage container.

Valid Value

A valid Azure Container Name. container names are 3-63 lowercase alphanumeric and dash

Default Metadata information

KeyValue
Configuration Value