The Transformation Wizard is used to create a new transformation. To start it, use the object context menu and select: "Add → Transformation" in the diagram.

Typical Transformation Wizard Window

Supported Transformation Types

• Regular Transformations: Described in tabular form, results in a generated VIEW.
• Manual Transformations: Hand-created VIEWs defined manually by the user.
• Script Transformations: Based on SQL scripts, often calling stored procedures.
• External Transformations: Created outside AnalyticsCreator as SSIS packages.

Main Page Parameters

1. Type: Transformation type:
   • Dimension: FullHist, creates unknown member, JoinHistType: Actual
   • Fact: Snapshot, no unknown member, JoinHistType: Historical_to
   • Other: FullHist, no unknown member, JoinHistType: Historical_to
   • Manual, External, Script: as named
2. Schema: Schema name
3. Name: Transformation name
4. Historizing Type:
   • FullHist
   • SnapshotHist
   • Snapshot
   • ActualOnly
   • None
5. Main Table: Only for regular transformations
6. Create Unknown Member: Adds surrogate ID = 0 (for dimensions)
7. Persist Transformation: Save VIEW to a table
8. Persist Table: Name of persist table
9. Persist Package: SSIS package name
10. Result Table: For external/script types
11. SSIS Package: For external/script types

Table Selection Page

Allows selection of additional tables. Tables must be directly or indirectly related to the main table.

Parameters

• Table JoinHistType
  • None
  • Actual
  • Historical_from
  • Historical_to
  • Full
• Join Options:
  • All N:1 direct related
  • All direct related
  • All N:1 related
  • All related
  • Use hash keys if available

Parameter Page

Configure additional parameters (for regular transformations only).

• Fields:
  • None
  • All key fields
  • All fields
• Field Names (if duplicated):
  • Field[n]
  • Table_Field
• Field Name Appearance:
  • No changes
  • Upper case
  • Lower case
• Key Fields NULL to Zero: Replaces NULL with 0
• Use Friendly Names as Column Names

Stars Page

• Stars: Data mart stars for the transformation
• Default Transformations:
  • No Defaults (facts)
  • All Defaults (dimensions)
  • Selected Defaults
• Dependent Tables: Manage dependent tables

Script Page

Used for script transformations. Enter the SQL logic that defines the transformation.

INSERT INTO IMP.LastPayment(BusinessEntityID, RateChangeDate, Rate)
SELECT 
    PH.BusinessEntityID, PH.RateChangeDate, PH.Rate
FROM 
(
    SELECT 
        BusinessEntityID,
        MAX(RateChangeDate) LastRateChangeDate
    FROM 
        [IMP].[EmployeePayHistory]
    GROUP BY 
        BusinessEntityID
) T
INNER JOIN [IMP].[EmployeePayHistory] PH ON 
    PH.BusinessEntityID = T.BusinessEntityID AND 
    PH.RateChangeDate = T.LastRateChangeDate