Tables

bles in AnalyticsCreator 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: Table which is filled by the external data using SSIS package. 2. Historicized table: Table containing historicized data. Every historicized table contains 3 mandatory fields (names are configurable): SATZ_ID (bigint) – surrogate primary key DAT_VON_HIST (datetime) – start of a validity period DAT_BIS_HIST (datetime) – end of a validity period 3. View without history: Result of transformation containing no historicized data 4. View with history: Result of transformation containing historicized data 5. Persisted table without history: Table used to persist the result of non-historicized transformation 6. Persisted table with history: Table used to persist the result of historicized transformation 7. Data mart dimension view without history: Result of non-historicized data mart dimension transformation 8. Data mart dimension view with history: Result of historicized data mart dimension transformation 9. Data mart fact view without history: Result of non-historicized data mart fact transformation 10. Data mart fact view with history: Result of historicized data mart fact transformation o Externally filled table without history: Table filled by non-historicized external or script transformation 11. Externally filled table with history: Table filled by historicized external or script transformation o Data vault hub table with history: Historicized table containing data vault hub records 12. Data vault satellite table with history: Historicized table containing data vault satellite records o Data vault link table with history: Historicized table containing data vault link records 4. Friendly name: if defined, will be used in OLAP cubes instead of table name. Friendly name will be inherited by depending objects 5. Compression type: table compression type. DEFAULT (settable using parameter TABLE_COMPRESSION_TYPE), NONE, RAW, PAGE 6. Description: table description. Will be inherited by depending objects 7. Hist of table: Persist of table, Hub of table, Satellite of table, Link of table: name of according table 8. Has primary key: if checked, the PRIMARY KEY COSTRAINT will be added to the table 9. Primary key name: name of primary key. 10. PK clustered: if checked, the clustered primary key will be created 11. Columns: table columns 1. Column name: name of column 2. Data type, MaxLength, NumPrec, NumScale, Nullable: SQL column properties 3. PKOrdinalPos: column position in primary key (1,2…) 4. Default: SQL DEFAULT statement (for example GETDATE()) 5. Friendly name: if defined, will be used in OLAP cubes instead of column name. Friendly name will be inherited by depending objects. 6. Referenced column: here you can define the “one-field” N:1 references to the primary keys in another tables. 7. References: comma-separated list of referenced columns. Is read-only 12. Identity column: if defined, the identity columns will be added to the table 1. Name, type, seed, increment – SQL IDENTITY properties 2. PK pos: column position in primary key (usually – 1) For the normal tables (not views) you can optional define identity column and calculated columns (Calculated columns – tab). Calculated Columns Properties: 1. Column name: name of column 2. Statement: SQL Statement. You can use AnalyticsCreator macros (see Macros). You can use predefined @GetVaultHash – macro to create hash keys. 3. Persisted: if checked, calculated column will be persisted 4. PKOrdinalPos: column position in primary key (1,2…) 5. Friendly name: if defined, will be used in OLAP cubes instead of column name. Friendly name will be inherited by depending objects 6. Referenced column: here you can define the “one-field” N:1 references to the primary keys in another tables. 7. References: comma-separated list of referenced columns. Is read-only ⬅ Previous Page | ➡ Next Page