Create, update and connect repository database

Abstract

1. Architecture background

First we need to create a "repository database" and connect it with the "dwh database".
Here are some excerpts from the architecture description:

== 02 Architecture Constraints

  1. The project is based on Microsoft SQL Server (mssql):

    • current restriction: SQL Server 2017 or above.

      • initial restriction: SQL Server 2019 or above.
        because features of the Graph database were used, which are only available in SQL Server 2019.

      • Features of graph database are no longer used due to insufficient support by Visual Studio database projects and by DACPAC containing data. It should therefore be possible to use SQL Server 2017 (not tested).

  2. It must always be possible for BI developers to use all mssql and Azure features in DWH databases.

  3. a minimally invasive repository database supplements DWH database metadata

    • A repository database is externally connected to a DWH database via synonyms to system catalog views of the DWH database.

    • Changes can be made both in repository and in DWH database. Synchronization is required for this.

    • for additional minimal invasive interventions extended properties are used, which can be assigned to many database objects and also columns, parameters etc.

    • The synchronization concept takes into account that renaming and refactoring can be performed in the repository as well as in the DWH database.

    • The DWH database works without the repository and all traces of a repository can be removed (extended properties can be dropped).

    • It is possible to connect to the DWH database in read-only mode, without writing back and using extended properties. In this case, the database can be documented, for example.

    • SSAS Tabular databases can only be documented. This requires at least compatibility level 1200, since from this version on the model definition is expressed in JSON.

    • SSIS projects can only be documented.

structurizr-DWHBDevelopment-Container
structurizr-DWHBDevelopment-DWHdbDevelopmentProjectB-Component

2. create repo database

As an example, we use WideWorldImporters as dwh database.

  • create a new empty repository database dhw_WideWorldImporters
    dhw is an abbreviation for DataHandwerk. WideWorldImporters is used to mark that it is a repository for WideWorldImporters.
    You can also use any other name for your repository database, for example repo_WideWorldImporters.

You can skip creating a new database and use the publish function from the database project.

3. update repo database or publish from database project db_DataHandwerk

The structure of the repo database must be updated from the dhw git (database project: db_DataHandwerk).

structurizr-DataHandwerkgit-SystemContext

  • clone the project https://gitlab.com/datahandwerk/DataHandwerk-toolkit-mssql/-/tree/main

  • using SQL Server Data Tools for Visual Studio, open the database project:
    "D:\Repos\GitHub\DataHandwerk\DataHandwerk-toolkit-mssql\db_DataHandwerk\db_DataHandwerk.sqlproj"
    Your folder could be another

  • Now there are several options

    • use publish

      • select "publish…​" in context menu
        create update connect repo db publish 1

      • Edit connection

        • create update connect repo db publish 2

        • create update connect repo db publish 3

      • check "Advanced…​"

        • exclude extended properties
          create update connect repo db publish 6

      • publish

        • create update connect repo db publish 4

      • create update connect repo db publish 5

    • use an existing dacpac (under snapshots) or create a dacpac

      • create update connect repo db dacpac 1

      • use VS Schema Compare, SSMS, Azure Data Studio (or other tools) to deploy the dacpac

        • create update connect repo db dacpac 2

        • create update connect repo db dacpac 3

        • follow the wizard

      • the dacpac also includes the postdeployment script

    • use Schema Compare

      • use the prepared schema compare and adapt it or create your own

      • create update connect repo db sc 01

      • create update connect repo db sc 02

4. connect repository database to dwh database, using synonyms

The repository database is connected to the dwh database using synonyms. Execute config.usp_connect_database - P to connect the synonyms to the correct DWH database.

using publish or dacpac deployment to update the repo database will reset synonyms.
reconnect the repo database to the right dwh database after using publish or dacpac deployment by executing config.usp_connect_database - P
Example 3. connect repository database to the dwh database 'WideWorldImporters'
EXEC [config].[usp_connect_database]
@dwh_database_name = 'WideWorldImporters'

5. check config.Parameter

check and edit the content of config.Parameter - U

the parameter ('dwh_readonly', '') is set to 1 by default to avoid write back of extended properties into the dwh. This works fine for

6. synchronize repository with dwh database extended properties

6.1. Description

main procedure

this central procedure must be executed regularly, try to get e feeling, when it is required
It does:

  • EXEC repo.usp_sync_guid to synchronize repository database and dwh database

    • some dwh database extended properties (ep) are synchronized with repository database

      • ep RepoObject_guid for each database object

      • ep RepoObjectColumn_guid for each database object column

  • index processing

    • combination of real and virtual indexes

    • virtual and real foreign key

    • code generation and updates for persistence procedures

  • process references and data lineage

  • inheritance of properties

see Procedure steps for details.

use links in Referenced objects to get details of called sub procedures

Example 4. synchronize repository with dwh database extended properties
EXEC [repo].[usp_main]

See details: repo.usp_main - P

7. optional: import extended_properties from dwh database into repository properties

Example 5. synchronize repository with dwh database extended properties
EXEC [property].[usp_sync_ExtendedProperties_Sys2Repo_InsertUpdate]

See details: property.usp_sync_ExtendedProperties_Sys2Repo_InsertUpdate - P