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. 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. 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). 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 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