Add persisting content and choose a replacement strategy

Expand Packages

In the navigation tree of the intended Data Warehouse (DWH) project, expand Packages.

Locate the Persisting package

Expand Persisting under Packages. Locate the existing package that should contain the new operation.

Open the package editor

Right-click the intended package and select Edit package from its context menu.

Check the package and its current content

Review the package editor before adding content:

1. Package name — Identifies the package you opened. Check that it is the package intended for this operation.
2. Package type — Check that it displays Persisting package. This identifies the package category; the processing type of each operation is checked separately in Review the new operation.
3. Content — Lists existing operations. Each pictured row shows a Transformation followed by its destination table. Compare these with the operation you intend to add so that you do not create it again.

If the identity or category is wrong, return to Locate the Persisting package and open the correct package. If the intended operation already exists, review it through Review the new operation. Otherwise, continue to add it.

Open Persisting Wizard

Click Add content below the Content list. Persisting Wizard opens to create another persisting operation in the package.

Set the source, destination, and replacement strategy

In Persisting Wizard, configure the three fields:

1. Transformation — Select the existing Transformation whose output you want to store. A Transformation applies processing logic to input objects and produces a reusable result. Check the schema and object name to identify the correct source.
2. Persist table — Enter the destination table name. A Persisting table stores the physical result of the Transformation. Use your project's naming convention and check whether the name identifies an existing destination whose data will be replaced during execution.
3. Persist package — Select the existing package checked in Check the package and its current content, or retain it if already selected. This assigns the new operation to that package.

Next, select one of the three radio choices below the fields. Full persisting replaces the complete destination result with the Transformation's view data. Choose how that replacement should be performed:

1. No partition switching — Select this for Full persisting that truncates the destination table, removing its existing rows, and then refills it from the view.
2. Partition switching — Select this when your destination design supports replacing the result through partition switching. A temporary table is created and loaded first. After loading, it is switched with the persisted table, and the table holding the old data is dropped. Check the column order of the persisted and temporary tables: differences can cause switching errors.
3. Renaming — Select this as the alternative when differing column order prevents partition switching. A temporary table is created and loaded first. After loading, the old persisted table is dropped, and the temporary table, including its indexes and constraints, is renamed to become the persisted table.

These choices are alternatives; select only one. All three descriptions concern what happens during Full persisting execution. Confirm that replacing the existing destination result is intended before finishing the definition.

Naming example: the image uses DWH.DIM_Categories as the source, DIM_Categories_T as the destination, and Persist_Calendar as the package. The suffix _T is fixed text, not a placeholder.

Finish the persisting definition

Review Transformation, Persist table, Persist package, and the selected replacement strategy. Click Finish to create the definition.

Continue to Review the new operation to check the result. Clicking Finish alone does not demonstrate that the destination has been populated.

Review the new operation

4.8.1. In the navigation tree, expand Packages and Persisting, then right-click the intended package and select Edit package. In Content, locate the row for your Transformation and destination table. Confirm that it is in the intended package.

4.8.2. Expand the package in the navigation tree, locate that persisting item, right-click it, and select Edit persisting. Check its Package assignment and the Partition switching and Renaming settings against the choice made in Set the source, destination, and replacement strategy.

4.8.3. Review Type in the persisting editor. For the full-replacement behavior described in Set the source, destination, and replacement strategy, check that it is Full. Use these distinctions to identify a mismatch:

1. Full — Replaces the complete persisted result, using the configured replacement strategy.
2. Merge — Compares the view with the persisted table through a MERGE statement and processes changes.
3. Historical — Uses historical information to detect and process changes. It requires a historized view containing the surrogate key SATZ_ID and validity fields DAT_VON_HIST and DAT_BIS_HIST.
4. Incremental (insert-only) — Inserts new rows detected using the maximum value of the selected Incremental column. It is intended for data that is never changed or deleted; that column must have a numeric or date/time data type.
5. Manual — Uses a persisting SQL routine that you can write or edit yourself. The other types use generated logic.

If the processing type or replacement settings differ from the intended design, resolve the mismatch before treating the configuration as complete. The wizard's radio choice alone does not establish the processing type.

4.8.4. Review Procedure in the persisting editor to check the generated SQL routine and its intended destination effects. This field is read-only unless Type is Manual. Reviewing the definition does not execute it.