Tables

A table represents a database table or view within the data warehouse, and each table belongs to a specific schema. Tables are created automatically when defining a data import, historization, or persisting process.

Views are created when defining a transformation. Additionally, tables can be manually created to store results of external or script transformations.

For most tables, several properties can be configured, including calculated columns, primary keys, identity columns, and indexes.

Table Properties:

1. Table name: table name
2. Table schema: table schema
3. Table type: type of the table
   1. Import table: Filled by external data using SSIS package.
   2. Historicized table: Contains historized data. Includes:
      • SATZ_ID (bigint) – surrogate primary key
      • DAT_VON_HIST (datetime) – start of validity
      • DAT_BIS_HIST (datetime) – end of validity
   3. View without history
   4. View with history
   5. Persisted table without history
   6. Persisted table with history
   7. Data mart dimension view without history
   8. Data mart dimension view with history
   9. Data mart fact view without history
   10. Data mart fact view with history
   11. Externally filled table without history
   12. Externally filled table with history
   13. Data vault hub table with history
   14. Data vault satellite table with history
   15. Data vault link table with history
4. Friendly name: Used in OLAP cubes instead of table name.
5. Compression type: DEFAULT, NONE, ROW, PAGE
6. Description: Description inherited by dependent objects.
7. Hist of table: Names of persist, hub, satellite, or link tables.
8. Has primary key: If checked, adds PRIMARY KEY constraint.
9. Primary key name: Name of the primary key.
10. PK clustered: If checked, creates clustered PK.
11. Columns:
    • Column name
    • Data type, MaxLength, NumPrec, NumScale, Nullable
    • PKOrdinalPos
    • Default (e.g., GETDATE())
    • Friendly name
    • Referenced column (for N:1 relationships)
    • References (read-only, comma-separated list)
12. Identity column:
    • Name, type, seed, increment
    • PK pos: Position in PK

For normal tables (not views), you can optionally define identity and calculated columns (see tab).

Calculated Columns Properties:

1. Column name: Name of the column
2. Statement: SQL statement (macros like @GetVaultHash supported)
3. Persisted: If checked, column will be persisted
4. PKOrdinalPos: Position in primary key
5. Friendly name: Used in OLAP cubes instead of column name
6. Referenced column: Defines N:1 references
7. References: Comma-separated, read-only

Defining Table Relationships in AnalyticsCreator

Relationships between tables can be defined to enable combining tables during transformations. These relationships include N:1 ("1-field" to "1-primary key field") references and more complex associations.

Defining N:1 References

One-Field to One Primary Key Field:
These references can be directly defined within the table definition using the Referenced Column attribute.

• Example: A foreign key in one table referencing the primary key of another.

More complex references can be defined using Table references.

Here is a typical table reference definition:

Table Reference Properties:

1. Cardinality:
   • Unknown
   • OneToOne
   • ManyToOne
   • OneToMany
   • ManyToMany

   Note: It is recommended to primarily use Many-to-One (N:1) and One-to-Many (1:N) cardinalities.

2. Join: SQL join type
3. Table1: Schema and table name of the first table
4. Table2: Schema and table name of the second table
5. Alias 1: Optional. Alias of the first table. Should be defined if reference statement is used
6. Alias 2: Optional. Alias of the second table. Should be defined if reference statement is used
7. Description: Reference name
8. Auto created: If checked, the reference was automatically created during synchronization.
9. Reference statement: Optional. SQL reference statement. Should be used if the reference cannot be described using column references only. Table aliases will be used.
10. Columns: There are columns and statements. Either column or statement should be defined on each reference side.
    • Column1: Column from the first table
    • Statement1: SQL statement
    • Column2: Column from the second table
    • Statement2: SQL statement

Inheritance of Table Relations Across DWH Layers

Table relations will be inherited into subsequent DWH layers. For example, if references are defined between two import tables that are historicized, the same references will be automatically created between the corresponding historicized tables.

If a reference is changed, the changes will propagate into the inherited references unless those references are used in transformations. In such cases, the references will be renamed by adding the suffix _changed(N), and new inherited references will be created.

Therefore, if a "parent" reference is changed, transformations using the inherited reference will not be updated automatically. However, you can manually update them by selecting the new inherited reference.

The inherited references, where the Auto created flag is set, cannot be modified unless you uncheck the Auto created flag.

Defining Relations Between Sources

Relations between sources are defined and will be inherited by the data warehouse objects during synchronization.

The N:1 relation, which refers to a "one field" to a "one primary key field" reference, can be defined directly in the source definition by using the Referenced column attribute.

For more complex references, use Source references.

Inheritance of Source Relations Across DWH Layers

Source relations will be inherited into subsequent DWH layers. For example, if references are defined between two source tables that are imported, the same references will be automatically created between the corresponding import tables.

If a source reference is changed, the changes will propagate into the inherited references, unless those references are used in transformations. In such cases, the references will be renamed by adding the suffix _changed(N) and new inherited references will be created.

Therefore, if a "parent" reference is changed, transformations using the inherited reference will not be updated automatically. However, the user can manually update them by selecting the new inherited reference.