Create and save a Regular transformation

Open the ETL ribbon

Click ETL on the toolbar to open the ribbon for data-processing workflows.

Open the Transformations list

On the ETL ribbon, click Transformations in the List group. The list displays existing transformations.

Start a new transformation

In the transformations list, click New.

Expected Results: the Transformation Wizard opens.

Select the type and schema

On the first page of Transformation Wizard, select Type and Schema for the intended output. Use Dimension, Fact, or Other for this Regular-transformation workflow.

1. Type — Select Dimension for descriptive business data, Fact for measurable events or transactions, or Other for another output. All three choices create a Regular transformation. The editor’s TransType setting is checked later.
2. Schema — The namespace for the new transformation. Select the target schema required by your Data Warehouse (DWH) project. The pictured DWH is an example schema identifier, not a required value.

Schema examples: IMP, STG, and TRN appear in the selection list. The corresponding layer terms are Staging Layer (IMP), Persisted Staging Layer (STG), and Transformation Layer (TRN), but each schema’s actual assignment depends on your project. STAR is another pictured schema identifier. Select the intended target schema rather than matching the image.

Name the transformation and select its input

Enter Name, select the intended Main table, and select Historizing type for that input’s history and the required output.

1. Name — The transformation’s technical name. Enter the name required by your project’s naming convention and check that it is not already used in the target schema. Transformation 1 is an example.
2. Historizing type — Controls how the transformation represents historical versions of the main input. Select None for non-historized data. For historized data, select FullHist for the full available history, SnapshotHist for historical data restricted to specified snapshots, Snapshot to combine historical data at predefined snapshot dates, or CurrentOnly for current records. The same current-records strategy is also named ActualOnly; select the label available in your interface. A snapshot is a named definition of the dates to use. FullHist and SnapshotHist require historical key and validity-field mappings; SnapshotHist and Snapshot also require an existing snapshot or group. Complete these checks in Confirm the editor settings and Review column mappings and generated SQL.
3. Main table — The primary input that supplies the transformation’s starting data. Select it by its schema and object name, using the input whose columns and history match your intended output. DWH.DIM_Calendar is a calendar example; DIM_ is a literal prefix in that example name.

Check: the selected input has the fields required for the output. For a history-based result, identify its historical key and validity dates now. For a snapshot-based result, identify the existing snapshot or group to assign after the wizard.

Configure unknown-member and persistence settings

Set Create unknown member and Persist transformation for the required output. If persistence is required, enter Persist table and select Persist package before continuing.

1. Create unknown member — Select this checkbox when the output needs a special row with surrogate key 0 to represent an unknown or unmatched member; otherwise clear it. A surrogate key is a system-assigned identifier. This setting applies to Regular transformations.
2. Persist transformation — Select this checkbox when the transformation result must be stored in a table, then configure its destination and package. If a stored result is not required, clear the checkbox. Saving this definition does not run the persistence package.
3. Persist table / Persist package — For persistence, enter the destination table name in Persist table and select the intended SQL Server Integration Services (SSIS) package in Persist package. Use the destination and existing package specified for your project. These settings identify where and how to persist the output; Main table identifies the input.

Select related tables and join behavior

Click next to open the related-table page. If the output needs additional inputs, select Table JoinHistType and the reference choice for those relationships, then click the related-table button whose scope matches your design. Inspect every table added to the list. If the main input is sufficient, leave the related-table list empty.

1. Table JoinHistType — Controls how historical versions are matched when joining tables. Select None for an input without historical data. For a historical input, select Actual to use current records, Historical_from for records valid at the linked record’s validity start, Historical_to for records valid at its validity end, or Full to retain full historical interval matching. Choose the behavior required by the relationship. This setting is separate from the transformation’s Historizing type.
2. All N:1 direct related — Click this button to include all tables directly related to the main input by a many-to-one relationship. N:1 means that many records relate to one record, such as many orders to one customer.
3. All direct related — Click this button to include all directly related tables, regardless of cardinality. Cardinality describes how many records can relate on each side.
4. All N:1 related / All related — These buttons also include indirect relationships through other tables. All N:1 related limits the selection to many-to-one relationships; All related includes relationships regardless of cardinality. Review the whole resulting list.
5. Delete / Delete all — To remove an unwanted table from the wizard selection, select its entry and click Delete. Click Delete all to clear the related-table selection. These actions remove wizard entries, not database tables.
6. Business-key and hash-key references — A business key identifies an entity in business data; a hash key is derived from values by a hash function. Select the reference choice that matches your existing relationships. Use hash key references if possible prefers available hash-key relationships over business-key relationships. The other choices are Use business key references if possible, Use only hash key references, and Use only business key references. Use an “only” choice when the design requires that key type and the required references exist. Check the resulting joins in the editor before saving.

Relationship example: All N:1 direct related can include a Customers table when Orders has a many-to-one relationship to it. The button includes every qualifying direct relationship, so remove any unwanted entries. When the list matches the intended inputs, click next.

Select fields and set their names

On the field-settings page, select the required field scope and naming choices. Enter the key-field naming template and set the two checkboxes according to the required output.

1. Fields — Select All fields when the initial output needs descriptive fields as well as keys. Select All key fields when the output needs only key fields. None includes no input fields and requires a separate column-definition workflow. For the wizard-generated mappings reviewed in this guide, select All fields or All key fields according to the output you need.
2. Field names — For duplicated field names, select Field[n] to add a sequential number or Table_Field to add the table name as a prefix. Use the naming pattern required by the consumers of this output.
3. Field names appearance — Select No changes to preserve field-name casing, Upper case for uppercase names, or Lower case for lowercase names.
4. Key field names — The naming template for key fields. The pictured FK_{TableName} combines the literal prefix FK_ with the placeholder {TableName}. Retain the placeholder’s spelling, capitalization, and braces when using this template, and check the generated names in the editor.
5. Key fields NULL to zero — Select this checkbox when missing key values, represented by SQL NULL, should become 0; otherwise clear it. Zero does not establish that a matching row exists. Check any unknown-member requirement separately.
6. Use friendly names as column names — Select this checkbox to use defined friendly names instead of technical field names. A friendly name is a readable display name. Clear the checkbox when the output must retain technical names.

Naming example: Upper case changes CalendarDate to CALENDARDATE. Use that choice only when uppercase output names are required. Review all field settings, then click next.

Assign Stars and select predefined rules

On the final wizard page, select each required entry in Stars and click the upper >> button to move it to the right-hand Transformation list. To remove an assignment, select it on the right and click the upper << button. Leave that list empty when no Star assignment is needed.

Under Default transformations, choose whether to apply reusable Predefined transformation rules. These rules apply expressions to fields according to their configured criteria.

1. Stars — A Star groups a Fact with related Dimensions. Assigning a Star in this wizard requests a datamart Dimension or Fact for that Star. Select only the Stars that should use this transformation.
2. No defaults — Select this choice when no Predefined transformation rules should be applied.
3. All defaults — Select this choice when all configured Predefined transformation rules are required. Review the project’s rules before using it.
4. Selected defaults — Select this choice when only particular Predefined transformation rules are required, then move those rules into the lower right-hand list. Check every selected rule against the intended output.

For Selected defaults, select a required rule in the lower left-hand list and click the lower >> button. Repeat for the required rules. Select an unwanted rule on the right and click the lower << button to remove it.

Rule example: a Trim rule can apply LTRIM(RTRIM(FieldName)) to varchar and nvarchar text fields to remove leading and trailing spaces. Check the actual project rule, because existing rules can be redefined. To inspect a rule before using it, follow Apply a Predefined transformation rule.

Review the wizard and finish

Use back to review earlier pages, and next to return to the final page. Confirm the name, schema, main input, history behavior, unknown-member and persistence settings, related inputs, fields, Stars, and rules. Click finish when they match the intended output.

Expected Results: the transformation editor opens with the starting definition.

Confirm the editor settings

In the transformation editor, check Name and Schema. Correct either value if it does not identify the intended output.

Select or retain Regular in TransType, the transformation-type setting. A Regular transformation uses AnalyticsCreator-managed inputs, joins, columns, filters, and references to generate a view: a named database query. The Definition tab holds these settings, and VIEW shows the generated SQL.

Check Hist Type against the history behavior selected in Name the transformation and select its input. When a readable display name is needed, enter Friendly name. Enter a Description explaining the intended output. A friendly name and description do not replace the technical Name. Example: Calendar attributes can describe a transformation that actually produces calendar attributes.

For SnapshotHist or Snapshot, use the editor’s right-hand Snapshot group / Snapshot grid to select the intended existing snapshot or group. Check its dates or member snapshots against the reporting requirement. Follow Choose a Transformation historization type and assign snapshots for the assignment workflow. This selects existing definitions; creating a snapshot dimension is a separate task.

Review column mappings and generated SQL

On Definition, inspect the Columns grid. For each output column, check its name, open its Reference cell, and select the intended input field when the mapping needs correction. Compare the input’s meaning and data type with the required output.

1. Column Name — The output column’s name. Check it against the required output and naming convention. The calendar names shown in the screenshot are examples.
2. Reference — The input field that supplies the output column. Open this cell’s selection list and select the field with the required meaning and data type. Mapping example: a calendar Date output can reference DWH.DIM_Calendar.Date when that is the intended input.
3. Statement — The expression applied to the field. For a direct field mapping, review the Reference and the generated SQL together; the calendar example shows blank Statement cells for several direct references. Retain an existing expression when it already produces the required calculation.
4. ISNULL(@this, 0) — The key-field expression shown in the screenshot replaces a NULL value with 0. The placeholder @this refers to the current field by default; the project’s Trans Field Alias setting can change that placeholder. Retain or enter this expression only when zero is the intended replacement and the configured alias is @this.
5. Other grid headings — The grid also shows TableSeqNr, SeqNr, PK Position, IsAggr., DefaultValue, and Friendly name. Keep the generated values unless your transformation design calls for a specific change. The displayed key and sequence values are examples, not values to reproduce.

History check: for FullHist or SnapshotHist, the output must expose the historical surrogate key, validity start, and validity end, each mapped to the corresponding input field. The sample identifiers SATZ_ID, DAT_VON_HIST, and DAT_BIS_HIST represent those roles; use your input’s actual names. A calendar key in the screenshot is not evidence of complete history mappings.

Click VIEW to inspect the generated SQL. Check that it uses the intended inputs, joins, output names, expressions, and history behavior. For each join, compare the key fields and any history conditions with the relationship selected in Select related tables and join behavior. Return to Definition for changes. If a required column or relationship is missing, correct the definition before saving.

Save and reopen the definition

Before saving, confirm that the definition has the intended name, schema, TransType = Regular, inputs, columns, and history behavior. Check any configured persistence and Star assignments.

Confirm the target database and the Data Warehouse (DWH) project’s Transformations Createviews setting with the project owner: 0 = No, 1 = Compile only, and 2 = Yes. This determines whether saving also compiles or creates the database view. Saving does not run a persistence package.

Click Save. If an error appears, use the message to identify the affected object or expression and resolve it before treating the save as successful.

Return to ETL → Transformations and double-click the saved entry, identified by its name and schema. Compare the reopened settings with Expected Results.