Home › Reference Guide › Transformations

Transformations

Transformations

A transformation is a process used to modify data. The result of a transformation is always either a single VIEW or a single TABLE.

To create a new transformation, use the Transformation Wizard.

Each transformation has the following common properties:

  1. Name: The name of the transformation
  2. Schema: The schema for the transformation
  3. TransType: The type of transformation
  4. Stars: A list of stars in which the transformation is involved
    • Star: The name of the star
    • IsFact: This should be selected for fact transformations
    • Filter: You can define an additional filter to restrict transformation data for a specific data mart

AnalyticsCreator supports the following transformation types:


Regular Transformation

A regular transformation is a VIEW generated by AnalyticsCreator based on the defined transformation parameters. Tables, table relationships, and transformation columns must be specified, after which AnalyticsCreator automatically creates the transformation VIEW.

Below is a typical regular transformation definition:

AnalyticsCreator Regular Transformation

Regular Transformation Properties:

  1. Historization type: Defines how to work with historicized data.
    • FullHist: Fully historicized transformation. Includes:
      • SATZ_ID
      • DAT_VON_HIST
      • DAT_BIS_HIST
    • SnapshotHist: For predefined snapshot dates (used for dimensions).
    • Snapshot: Uses snapshot dates to combine historicized data (usually for facts).
    • ActualOnly: Uses only current data from historized sources (dimensions or facts).
    • None: Non-historicized data.
  2. Create unknown member: Adds surrogate ID = 0 with default values for unmatched dimension members.
  3. Fact transformation: Check if defining a fact transformation.
  4. Persist table: Name of the table where results will be stored.
  5. Persist package: Name of the SSIS package for persisting results.
  6. SSIS Package: For external or script transformations; launches transformation.
  7. Hub of table: Read-only source for hub transformations.
  8. Sat of table: Source table for satellite transformations.
  9. Link of table: Read-only source table for link transformations.
  10. Snapshots: Snapshot and group info (relevant for Snapshot types).
  11. Tables: Participating tables
    • SeqNr: Unique table sequence number
    • Table: Table name
    • Table Alias: Unique alias used in joins/statements
    • JoinHistType:
      • None – no historicized data
      • Actual – only current data
      • Historical_from – value at start of linked record period
      • Historical_to – value at end of linked record period
      • Full – full historicizing info
    • Join type: INNER, LEFT, RIGHT, FULL, CROSS
    • Force Join: LOOP JOIN, HASH JOIN, MERGE JOIN
    • Reference statement: Optional custom join logic (e.g. T5.ID = T1.CustomerID)
  12. Filter statement: Additional SQL filter (e.g. T5.Country = 'GER')
  13. Sub select: Additional subquery to refine reference logic.
  14. Columns: Transformation output columns
    • Column name
    • TableSeqNr (optional)
    • Reference (optional)
    • Statement: SQL with aliases
    • IsAggr: Aggregated column
    • Default value: Used for unknown members
    • SeqNr: Column sequence
    • PK Position: Primary key position
    • Description
  15. References: Table joins (see Table References)
    • SeqNr1: First table seq number
    • SeqNr2: Second table seq number
    • Reference: Reference name
  16. Predefined transformations: List of referenced transformations
  17. VIEW tab: Read-only view definition

Transformation Compilation and Creation

  • Compile: Use the Compile button to check and validate the transformation logic. Errors will be flagged.
  • Create: Use the Create button to build the transformation VIEW into the DWH. Errors will be reported if present.

Manual Transformation

A manual transformation is a VIEW that is created manually.

Properties:

  • VIEW: Contains the manually created VIEW definition.
  • Rename Columns Table: If you rename a column in the manually created VIEW, enter the old and new column names into this table.

Below is a typical manual transformation definition:

AnalyticsCreator Manual Transformation


External Transformation

An external transformation is a transformation manually created using an SSIS package.

Properties:

  1. Result Table: The table where the transformation results will be stored.
  2. SSIS Package: The name of the manually created SSIS package.
  3. Tables: A list of tables on which the transformation depends. Only the table name is relevant.

Below is a typical external transformation definition:

AnalyticsCreator External Transformations


Script Transformation

A script transformation is a transformation that uses an SQL script.

Properties:

  • Result Table: The table where the transformation results will be stored.
  • SSIS Package: The name of the SSIS package where the transformation script is executed.
  • Script: The SQL script used in the transformation.

Below is a typical script transformation definition:

AnalyticsCreator Script Transformation


Data Mart Transformation

Data mart transformations are views created in the data mart layer. A data mart transformation cannot be created manually. Instead, the Stars – the affiliation of other transformations – must be defined, and the corresponding data mart transformations will be created automatically.

AnalyticsCreator Data Mart Transformation

Every regular or manual transformation can be persisted. This means the content of the VIEW can be stored in a TABLE.


Predefined Transformations

Predefined transformations are field-level transformations based on the field type. For example, below is a definition of a predefined transformation that removes leading and trailing spaces from all fields of type varchar and nvarchar:

AnalyticsCreator Predefined Transformation

Check and Transformation Statements

The Check Statement is used to verify whether a field meets the transformation conditions.
The Transformation Statement contains the actual SQL transformation logic.

Several predefined transformations are built-in, but users can also create their own. Predefined transformations are applied in regular transformations. When creating a transformation, users can select which predefined transformations to apply.

List of Predefined Transformations

Predefined Transformation Description
Trim Removes leading and trailing spaces from string fields (e.g., varchar, nvarchar).
StringNULLToNA Converts NULL values in string fields to "NA".
StringMaxT08000 Trims string fields to a maximum length of 8000 characters.
NumberNULLToZero Converts NULL values in numeric fields to zero.
XmlToString Converts XML data type fields to string format.
HierarchyToString Converts hierarchical data into a string representation.
TimeToDatetime Converts time fields into datetime by appending a default date (e.g., "1900-01-01").
BinaryToStr Converts binary data to a string format.
Anonymization Anonymizes data by replacing sensitive fields with generic or masked values.

Applying Multiple Predefined Transformations

Multiple predefined transformations can be applied simultaneously. Below is an example result when combining multiple transformations on a single field:

[FKART] = RTRIM(LTRIM(ISNULL([T1].[FKART], 'N.A.')))