Create and save a Manual transformation

Open the ETL ribbon

In the top menu bar, click ETL. This toolbar tab provides commands for packages, scripts, Imports, Historizations, Transformations, and new dimensions.

Expected Results: the ETL ribbon is visible.

Start the Transformation Wizard

In the New group, click New Transformation. The Transformation Wizard opens.

Expected Results: the Transformation Wizard opens.

Select the starting Transformation type

In Type, choose Dimension, Fact, or Other according to the generated starting definition you need. These follow the regular-transformation route used here. You will switch to Manual in Switch the new Transformation to Manual. Do not select Manual in this wizard when following the route below; its later regular-transformation settings do not describe that separate choice.

The first wizard page sets the starting category, target schema and name, main input, and persistence settings. The wizard's Type list is not the same as the editor's TransType list:

1. Dimension – A regular starting choice for descriptive business data prepared for analytical use. Select it when that is the required output; the pictured selection is not mandatory.
2. Fact – A regular starting choice for measurable business events, balances, or transactions prepared for analytical use.
3. Other – another Regular transformation, rather than a dimension or fact starting choice.
4. Manual – a manually maintained view. Choosing it at this point follows a different wizard route from the one illustrated here.
5. External – Transformation work maintained in an external SQL Server Integration Services (SSIS) package, using Microsoft's data-integration tooling.
6. Script – Transformation work defined through a script rather than this view-editing walkthrough.
7. Union – a Transformation combining compatible inputs through a UNION view.

Expected Results: Type shows the regular starting category required by your design.

Set the schema and name

Select the approved target Schema and enter an unused Name. Use those same technical identifiers in the manual view statement later; no name from the screenshot is required.

1. Schema – the namespace that groups the Transformation with other objects. Select the schema approved for this Transformation; do not move it to another layer just because that schema is visible.
2. Name – the Transformation's technical name. Use the same schema and name in the manual view statement later.

Naming example: a Transformation named Manual_Calendar in schema DWH would use CREATE VIEW [DWH].[Manual_Calendar] AS. This demonstrates agreement between the editor and SQL header; it is not a renaming instruction.

The screenshot includes schema identifiers IMP, STG, TRN, and STAR. The layer terms Staging Layer (IMP), Persisted Staging Layer (STG), and Transformation Layer (TRN) describe architectural roles; a schema identifier must still be checked against your project's layer assignments.

Expected Results: Schema and Name match the values you will use throughout these steps.

Choose the starting history strategy

Review Historizing type against the input's history status. For a non-historized input that needs no historical output, select None. For a historized input, use the previously agreed history strategy; do not choose None merely because it is pictured.

This setting governs history handling when a Regular transformation's main input is historized. Historical output also depends on the query and join logic. After switching to Manual, check that your SQL implements the required behavior; changing a history setting alone is not a substitute for maintaining that SQL.

Expected Results: Historizing type matches the chosen input and history strategy.

Select the main source table

In Main table, select the existing input needed for the Transformation. Identify it by its actual schema and technical name; DWH.DIM_Calendar is only a screenshot example.

The main table supplies the starting data for this regular-transformation route. Do not leave it empty. Carry the selected input’s verified database identifier, columns, and relevant dependencies into your manually maintained statement in Edit and check the manual view definition.

Expected Results: Main table shows the source you intend to read.

Set unknown-member and persistence choices

Select Create unknown member if the starting definition needs an unknown-member row; otherwise, clear it. If storage of the result is required, select Persist transformation, enter the intended destination name in Persist table, and select the approved Persist package. If persistence is not required, clear Persist transformation.

1. Create unknown member – For a Regular transformation, requests a special row with surrogate key 0 to represent an unknown or unmatched member. A surrogate key is a system-assigned identifier. Select it only when that row is required, then review the actual SQL after switching to Manual.
2. Persist transformation – Configures storage of the Transformation’s result in a table. Persistence applies to Regular, Manual, and Union transformations. Select it only with a confirmed destination and execution package; it is separate from saving a definition.
3. Persist table – The destination table for a persisted result, not the input table. Enter the approved destination table name when configuring persistence; no table pictured in the capture is required.
4. Persist package – The SQL Server Integration Services (SSIS) package responsible for persistence work. Select the approved package when persistence is required; selecting it does not run it.

Screenshot example: Create unknown member is checked in the capture. Use the setting required by your design, not the pictured checkbox state.

Expected Results: the unknown-member choice and persistence configuration match the required starting definition.

Continue to table and join settings

Click next at the bottom of the wizard.

Expected Results: the table-selection page shows Table JoinHistType and related-table controls.

Review related inputs and join settings

On the table-selection page, review Table JoinHistType, the related-table list, and the key-reference choices below the buttons. If your query needs only the main input, leave the related-table list empty; for the non-historized case, select None. If your starting definition needs related inputs, click the applicable relationship button below, inspect every added table, and select the agreed history-join and key-reference settings.

The controls visible in this guide mean:

1. Table JoinHistType – The join historization type: how historical versions are handled across joined inputs. None specifies no history-specific join behavior. Choose the setting required by the actual inputs rather than copying the screenshot.
2. All N:1 direct related – includes tables directly related to the main table through many-to-one relationships. N:1 means many records on one side can relate to one record on the other; for example, many orders can belong to one customer.
3. All direct related – includes directly related tables regardless of relationship cardinality, or how many records relate on each side.
4. All N:1 related / All related – also considers indirect relationships: tables reached through other related tables. The N:1 option restricts the relationship type; All related does not.
5. Delete / Delete all – removes the selected entry or all entries from this wizard's related-table selection. These are not instructions to delete source database tables.
6. Business-key and hash-key reference choices — A business key identifies an entity in business data; a hash key is derived from values using a hash function. Select Use business key references if possible or Use hash key references if possible to express your project's preference. Select Use only hash key references or Use only business key references when the design restricts joins to that key type. Review the resulting references against the actual relationships; the selected hash-key choice in the image is not a recommendation.

Relationship example: All N:1 direct related can include Customers for Orders when that relationship exists, but it includes every qualifying direct relationship—not necessarily Customers alone. Review the full resulting list; these table names are examples, not required inputs.

After reviewing these choices, click next.

Expected Results: the field-settings page opens.

Choose the starting field settings

Choose the required Fields, Field names, and Field names appearance. Review the supported Key field names template, then set Key fields NULL to zero and Use friendly names as column names according to the intended output. None of the pictured values or checkbox states is required solely because it appears in the image.

These choices control the wizard's generated starting definition. Later, inspect and edit the actual manual SQL; do not assume a wizard choice will rewrite SQL that you maintain yourself.

1. Fields – None includes no source fields; All key fields includes keys only; All fields includes every field from the selected Transformation tables.
2. Field names – Field[n] adds a sequential number when names are duplicated; Table_Field adds the table-name prefix to distinguish duplicated names.
3. Field names appearance – No changes preserves casing; Upper case and Lower case change the generated field-name casing.
4. Key field names – The key-field naming template. The pictured FK_{TableName} combines the literal prefix FK_ with the placeholder {TableName}. Preserve the placeholder spelling and braces when using this naming pattern, and use the prefix required by your project.
5. Key fields NULL to zero – converts missing key values, represented by SQL NULL, to 0 in the generated logic. Zero is a replacement value, not proof of a valid matching member.
6. Use friendly names as column names – uses a defined readable column name instead of its technical name. Select it when downstream consumers need those friendly names; clear it when they need technical names.

After reviewing the settings, click next.

Expected Results: the Stars and Default transformations page opens.

Choose Star assignments and predefined rules

In Stars, select the required entries on the left and click >> to move them to the right-hand Transformation list. Click << to remove unintended selections; leave the selected list empty if no Star assignment is needed. Under Default transformations, choose the required scope. These choices configure the starting definition; review the final manual SQL independently.

1. Stars – Identifies the Stars for which the wizard creates Datamart outputs. A Star groups a Fact with related Dimensions. Choose assignments required by your design; the screenshot’s selections or empty list are not prerequisites.
2. No defaults – Requests no predefined rules. Choose it when no predefined rule is required for the starting definition.
3. All defaults – Applies all configured predefined rules. Choose it only when every configured rule is intended, not because it is selected in the image.
4. Selected defaults – uses the selected subset of Predefined transformations from the list.

For Selected defaults, select the required existing rule in the available list, click that area’s >> button, and verify it in the selected list. Rule examples: Trim and StringNULLToNA are pictured rule names. Inspect the saved definitions and matching conditions; do not infer an acronym expansion, replacement value, or behavior from a name.

Expected Results: the selected Stars and predefined-rule scope match the intended starting output.

Check the starting definition, then finish the wizard

4.12.1. If any earlier setting needs review, click back to return to that page, then click next to return to the final page.

4.12.2. Confirm the intended regular starting category, schema, unused name, main input, and history strategy.

4.12.3. Confirm unknown-member and persistence settings, related inputs, field settings, Star assignments, and predefined rules against the required starting definition. Confirm that the project's target database and Transformations Createviews setting are still the approved ones before completing the wizard.

Click finish. If validation reports a missing or conflicting value, correct it before continuing.

Expected Results: the Transformation editor opens for the newly created Transformation. You have created its starting definition; the manual SQL has not yet been saved.

Switch the new Transformation to Manual

In the editor, confirm the Name and Schema identify the new Transformation—not an existing production object. In the left-hand TransType list, select Manual.

TransType means Transformation type. These are the editor values:

1. Regular – a view generated by AnalyticsCreator from maintained Transformation settings.
2. Manual – a view whose SQL you maintain yourself. Select this value.
3. Datamart – a data mart view generated automatically by AnalyticsCreator.
4. External – Transformation work in a manually maintained SSIS package.
5. Script – Transformation work expressed as a script.
6. Direct – a generated view accessing an external database through a DIRECT Connector.
7. Union – a generated UNION view combining inputs.

Check the nearby Hist Type against the intended history strategy. Friendly name is a readable display name, and Description records the purpose. Enter values describing your actual output; Calendar dates and Manual view of calendar dates and years are examples. If you are not adding a display name or purpose note, leave those fields unchanged.

Scope: changing an existing Transformation to Manual changes how its logic is maintained. Use this walkthrough for a new object, not for converting an existing production Transformation. The Hub of table, Satellite of table, Link of table, and Direct source fields visible nearby belong to other configurations and are not used in this example.

Expected Results: TransType shows Manual.

Edit and check the manual view definition

1. Enter the complete VIEW statement

   Click the VIEW tab next to Definition. The VIEW tab contains the manual SQL text in the screenshot; do not look for this text on Definition.

   Read the generated starting SQL, including its input references, output columns, unknown-member logic, and any predefined rules. Replace or adapt it with the complete, reviewed CREATE VIEW statement needed by your task. Its schema and name must match the new Transformation, and every input and column must exist. Calendar example: the statement below demonstrates the structure only. Use it only if those verified inputs and that output are actually required. It returns the source's Date and Year values under new output names; it does not reproduce the longer pictured query, add an unknown-member row, or apply its NULL-replacement rules.

   CREATE VIEW [DWH].[Manual_Calendar] AS
   SELECT
       [T1].[Date] AS [CalendarDate],
       [T1].[Year] AS [CalendarYear]
   FROM [DWH].[DIM_Calendar] AS [T1];

   1. CREATE VIEW – defines the view's schema, name, and query. Its target name must agree with the Transformation you are editing.
   2. T1 – a local SQL alias for the source in the FROM clause, not a separate table or product setting.
   3. AS CalendarDate / AS CalendarYear – assigns explicit output column names to Date and Year.
   4. FROM – identifies the actual database source. Use its technical schema and name rather than guessing from a friendly label.

   Reading the screenshot SQL: ISNULL supplies a replacement for NULL; LTRIM and RTRIM remove leading and trailing spaces. The pictured expression ISNULL([T1].[Year], 0) replaces a missing year with 0. SATZ_ID is the sample surrogate-key name shown in the query; VeryShortDate is a source-column identifier. These are not controls to change. Confirm the actual source definitions instead of assuming that every project uses these names.

2. Record the output-column renames

   If you changed output column names, enter the actual old and new names in the column-renaming table to the right of the SQL text. Do not enter mappings when no rename is intended. Rename example: the table below records the aliases used by the calendar query above.

   Old column name	New column name
   Date	CalendarDate
   Year	CalendarYear

   If your original output names differ because of prior naming choices, use those actual old names. Record only renames you make; do not add these example mappings to an unrelated view.

3. Check the source references before saving

   Dependencies: a dependency is an object referenced by your SQL. Compare all FROM and JOIN inputs with the planned objects. The calendar example uses one input, but your statement may require others. AnalyticsCreator cannot infer every dependency in manually maintained SQL. Confirm the dependency configuration for every referenced table, view, or function before saving when view creation is enabled, and before deployment in all cases. If you add an input that was not part of the starting definition, have its dependency setup reviewed before proceeding. See the Manual transformation.

   Keep this as a view query, not a multi-statement batch that loads data or executes unrelated commands. See Microsoft's CREATE VIEW syntax and restrictions.

Expected Results: VIEW contains a complete statement with the intended source and output names; the rename table records your actual changes.

Review and save the manual definition

4.15.1. Confirm that Name and Schema match the SQL header, and TransType is Manual.

4.15.2. Check that the statement is complete: no ellipsis, missing FROM clause, or unresolved placeholders remain. Confirm that the SQL itself implements the intended history, unknown-member, and value-handling behavior.

4.15.3. Check every source and column against its technical database name, and confirm the dependency configuration for the referenced inputs.

4.15.4. If you renamed output columns, check each Old column name and New column name entry against the actual changes. If there are no renames, leave the table empty.

Recheck the target database and the project's Transformations Createviews setting described in Prerequisites. Saving can create a view depending on that setting.

Click Save at the bottom right of the editor, as shown. If an error appears, read it and correct the identified issue; do not treat an attempted save as confirmation.

Expected Results: the editor accepts the save without an error. Complete the read-back checks below to confirm the stored result.