Persistence generator

The procedure repo.usp_persistence_set - P is not called automatically. If a persistence is to be created or changed, this must be done manually!
Before you execute the procedure repo.usp_persistence_set - P, check, if the persistence source should have a real or virtual PK. Some persistence actions are not available, if no PK is defined in the source. Execute repo.usp_Index_virtual_set - P to define virtual PK, whenever this is possible.
If SSMS is used to retrieve the generated code, then the SSMS configuration should be checked and adjusted.

Persistence definition

use repo.usp_persistence_set - P to define (or change) a persistence.

--use explicite parameters to create a delete-update-insert persistence procedure without history

Exec repo.usp_persistence_set
    @source_fullname = '[SchemaName].[SourceViewName]'
  ----define optinal persistence_table_name, if not the default will be used
  --, @persistence_table_name = 'zzz_qqq_tgt'
  --these will define the structure of the table:
  , @has_history = 0
  , @has_history_columns = 0
  --behavior of the procedure:
  , @is_persistence_check_for_empty_source = 0
  , @is_persistence_truncate = 0
  , @is_persistence_delete_missing = 1
  , @is_persistence_delete_changed = 0
  , @is_persistence_update_changed = 1
  , @is_persistence_insert = 1
  , @is_persistence_persist_source = 0
  , @prescript = Null
  , @postscript = Null
  , @ColumnListNoCompareButUpdate = Null
  , @ColumnListNoCompareNoUpdate = Null
  , @ColumnListNoInsert = Null
  , @ColumnListIgnore = Null
----not implemented:
--, @is_persistence_merge_delete_missing = 0
--, @is_persistence_merge_update_changed = 0
--, @is_persistence_merge_insert = 0

--prepare code for persistence table and procedure

EXEC repo.usp_main

--get the code for the new table

SELECT
    [RepoObject_guid]
  , [RepoObject_fullname]
  , [SqlCreateTable]
FROM
    [repo].[RepoObject_SqlCreateTable]
WHERE
    [RepoObject_fullname] = '[SchemaName].[SourceViewName_T]';

--Execute the code from column [SqlCreateTable]

--get the code for the persistence procedure, you can limit using WHERE

SELECT
    [usp_id]
  , [SqlUsp]
  , [usp_fullname]
  , [usp_name]
  , [usp_schema]
FROM
    [uspgenerator].[GeneratorUsp_SqlUsp];

-- execute the code from column [SqlUsp]

--try to execute the generated procedure

EXEC [SchemaName].[usp_PERSIST_SourceViewName_T];
  • This will insert new required entries into repo.RepoObjectColumn - U.

  • It will update properties for entries of existing target columns

  • It will not delete entries for columns in the persistence target, which have been added to the persistence table definition (it is possible to have additional columns, for example calculated columns or just additional columns to input some data independently of the source)

In order for the persistence to be updated correctly, one (or sometimes two) execution(s) of repo.usp_main - P are required:

  • the new or changed columns have to be synchronized between DWH and repository database

  • repo.RepoObjectColumn.RepoObjectColumn_column_id must get a value, this happens depending on the fulfilled prerequisites already in the first or only in the second pass

The SQL code for the persistence target table is located in repo.RepoObject_SqlCreateTable.SqlCreateTable

Persistence procedure code generation

DataHandwerk provides the code for a persistence stored procedure for each managed persistence. The code of this procedure will change depending on:

The Persistence generator is used to create the persistence procedure code. Depending on changed settings in repo.RepoObject_persistence - U the generated code will change. After changing settings it is required to execute repo.usp_main - P

The final code of the procedure is available in uspgenerator.GeneratorUsp_SqlUsp.SqlUsp

change exisisting persistence

A change script for existing tables is not yet created! You need to compare the generated code and the existing code!
You could use the generated code in a Visual Studio database project and deploy it to the database using Schema compare.

Currently, it could be tricky to rename columns in persistence source and to promote this into the target. It still needs some testing and investigation to suggest the best practice. It looks like you should:

  • start to change the source

  • EXEC repo.[usp_persistence_set]

  • check [repo].[RepoObjectColumn_gross]

  • EXEC [repo].[usp_main]

  • check [repo].[RepoObjectColumn_gross]

  • maybe you need to edit the content of the column [persistence_source_RepoObjectColumn_guid]

It can be very difficult to change the design of graph tables in SQL Server, especially when Edge Constraints are used. The code created by SSMS or by Visual Studio code is then unusable and does not work. And Azure Data Studio it is even worse.