Get trial

English

Script Page

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. Transformation Wizard - Context Menu Transformation Wizard Window AnalyticsCreator supports several transformation types: 1. Regular Transformations
Described in tabular form and result in a generated VIEW. 2. Manual Transformations
Hand-created VIEWs defined manually by the user. 3. Script Transformations
Based on SQL scripts, often calling stored procedures. 4. External Transformations
Created outside AnalyticsCreator as SSIS packages. 1. Type: Transformation type: - Dimension: Used for dimensions
- Historizing type: FullHist
- Create empty member: Yes
- Table JoinHistType: Actual
- Fields: All fields
- Fact: Used for facts
- Historizing type: Snapshot
- Create empty member: No
- Table JoinHistType: Historical_to
- Fields: All key fields
- Other: Generic transformation
- Historizing type: FullHist
- Create empty member: No
- Table JoinHistType: Historical_to
- Fields: All fields
- Manual: Manually defined view
- External: External SSIS-based transformation
- Script: SQL-based transformation
2. Schema: Schema name
3. Name: Transformation name
4. Historizing Type: - FullHist: Fully historicized with [SATZ_ID], [DAT_VON_HIST], [DAT_BIS_HIST] - SnapshotHist: For predefined snapshot dates - Snapshot: Combines data for snapshot dates - ActualOnly: Uses only actual data - None: For non-historicized data 5. Main Table: Only for regular transformations
6. Create Unknown Member: Adds a row with surrogate ID = 0 (used in dimension-fact joins)
7. Persist Transformation: Store VIEW result in a table (relevant for regular/manual transformations)
8. Persist Table: Name of the table to store persisted results
9. Persist Package: SSIS package for persisted results
10. Result Table: For external/script transformations
11. SSIS Package: For external/script transformations Allows the selection of additional tables to be used in the transformation. Tables must be directly or indirectly related to the main table. Table Selection - Join Histype 1. Table JoinHistType (for historicized tables): - None: No join condition - Actual: Joins with most current data - Historical_from: Joins using start of validity - Historical_to: Joins using end of validity - Full: Full historic join (most complete) 2. Join Options: - All N:1 direct related - All direct related - All N:1 related - All related - Use hash keys if available Configure additional parameters (for regular transformations only). Transformation Fields Page 1. Fields: - None: Define manually - All key fields: For fact transformations - All fields: For dimensions 2. Field Names (if duplicated): - Field[n]: Sequentially numbered - Table_Field: Table name + field name 3. Field Name Appearance: - No changes - Upper case - Lower case 4. Key Fields NULL to Zero: Converts NULL to 0 in key fields
5. Use Friendly Names as Column Names: Uses friendly names from source tables Stars Page 1. Stars: Data mart stars where the transformation belongs
2. Default Transformations: - No Defaults: Used for facts - All Defaults: Used for dimensions - Selected Defaults: Use list box to choose 3. Dependent Tables: Manage tables dependent on the transformation
(Image placeholder – missing) Used for script transformations. Enter the SQL logic that defines the transformation. (Image placeholder – missing) sql 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 ⬅ Previous Page | ➡ Next Page

Updated