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.
AnalyticsCreator supports several transformation types:
1. Regular Transformations
Described in tabular form and result in a generated VIEW
.
2. Manual Transformations
Hand-created VIEW
s 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
Free Trial
REGISTER NOW AND ACTIVATE YOUR FREE TRIAL