Import from AnalyticsCreator

If the existing database was created with AnalyticsCreator, then some metadata can be taken from an AnalyticsCreator repository.

If a synchronization is performed with the AnalyticsCreator, then all extended properties created externally are deleted, i.e. also the connection to the DHW repository database. Therefore, an
Exec repo.usp_main;
must be executed afterwards.

Import descriptions

AnalyticsCreator can be used to create descriptions in the DWH database created by the AC. After connecting DataHandwerk to this database and an initial synchronization, the descriptions can be imported.

EXEC [property].[usp_sync_ExtendedProperties_Sys2Repo_InsertUpdate]

Import PK

Primary keys are available in the CFG.COLUMNS table of the AnalyticsCreator repository database.

With
ColList
As
    (
    Select
        CFG.SCHEMAS.Schema_Name
      , t.Table_Name
      , ColumnList = String_Agg ( c.Column_Name, ',' ) Within Group(Order By
                                                                        c.PK_Ordinal_Position)
    From
        CFG.COLUMNS    As c
        Inner Join
            CFG.TABLES As t
                On
                c.TableID  = t.TableID

        Inner Join
            CFG.SCHEMAS
                On
                t.SchemaID = CFG.SCHEMAS.SchemaID
    Where
        ( Not ( c.PK_Ordinal_Position Is Null ))
    Group By
        CFG.SCHEMAS.Schema_Name
      , t.Table_Name
    )
Select
    Schema_Name
  , Table_Name
  , sql = Concat (
                     'EXEC repo.usp_Index_virtual_set @RepoObject_fullname2 = '''
                   , Schema_Name + '.' + ColList.Table_Name
                   , ''' , @IndexPatternColumnName = '''
                   , ColumnList
                   , ''' , @is_index_primary_key = 1;'
                 )
From
    ColList;

Import Historization Source

Select
    S.Schema_Name       As Target_Schema_Name
  , T.Table_Name        As Target_Table_Name
  , SourceS.Schema_Name As Source_Schema_Name
  , SourceT.Table_Name  As Source_Table_Name
  , sql                 = Concat (
                                     'EXEC reference.usp_RepoObjectSource_virtual_set @RepoObject_fullname2 = '''
                                   , S.Schema_Name + '.' + T.Table_Name
                                   , ''' , @Source_RepoObject_fullname2 = '''
                                   , SourceS.Schema_Name + '.' + SourceT.Table_Name
                                   , ''''
                                 )
From
    CFG.TABLES      As T
    Inner Join
        CFG.TABLES  As SourceT
            On
            T.HistOfTableID  = SourceT.TableID

    Inner Join
        CFG.SCHEMAS As S
            On
            T.SchemaID       = S.SchemaID

    Inner Join
        CFG.SCHEMAS As SourceS
            On
            SourceT.SchemaID = SourceS.SchemaID
Order By
    S.Schema_Name
  , T.Table_Name;

Import Persistence Source

Select
    S.Schema_Name       As Target_Schema_Name
  , T.Table_Name        As Target_Table_Name
  , SourceS.Schema_Name As Source_Schema_Name
  , SourceT.Table_Name  As Source_Table_Name
  , sql                 = Concat (
                                     'EXEC reference.usp_RepoObjectSource_virtual_set @RepoObject_fullname2 = '''
                                   , S.Schema_Name + '.' + T.Table_Name
                                   , ''' , @Source_RepoObject_fullname2 = '''
                                   , SourceS.Schema_Name + '.' + SourceT.Table_Name
                                   , ''''
                                 )
From
    CFG.TABLES      As T
    Inner Join
        CFG.TABLES  As SourceT
            On
            T.PersistOfTableID  = SourceT.TableID

    Inner Join
        CFG.SCHEMAS As S
            On
            T.SchemaID       = S.SchemaID

    Inner Join
        CFG.SCHEMAS As SourceS
            On
            SourceT.SchemaID = SourceS.SchemaID
Order By
    S.Schema_Name
  , T.Table_Name;
  • Import from AnalyticsCreator Repository

    • column references for historizations

    • column references for persistence