Skip to content

How to compare repositories

Jump to bottom Edit New page
Germo Görtz edited this page Nov 2, 2019 · 2 revisions

objects to compare

Working on several AC Projects, it is useful to compare objects and their content in different repositories and to merge content from one repositoy into another

  • parameters
  • makros
  • object scripts
  • skripts
  • predfined transformations
  • ...

You can use repository templates containing objects to be reused. Then you can use data comparing tools to merge data from one repository into a second.

See an example AC tree with some macros, scripts and object scripts:

ac_tree_macro_script_objectscript

simple objects to compare by name

Some objects are used and referenced by Name. The name should be used to compare these objects, but not the ID

  • makros
    [CFG].[MACROS]
    compare by [MacroName]
  • object scripts
    [CFG].[OBJECT_SCRIPTS]
    the unique key used in AC is ([Table], [Name]),
    but [Table] can be NULL and for easy usage it is a good idea to use unique [Name] in to:
    compare by [Name]
  • parameters
    [CFG].[PARAM_VALUES]
    compare by [Param_Name]
  • skripts
    [CFG].[SCRIPTS]

simple objects to compare by ID

Some objects are used and referenced by ID. Comparing these objects the content per ID should be keept.

  • predfined transformations
    [CFG].[PREDEFINED_TRANSFORMATIONS]
    [PreTransID] need to be used to compare
    it the same [PreTransID] has different meaning in different repositories, you have an issue! Try to use the same predefined transformations in all your repositories.
  • ...

data comparing tools

There are free and paid data comparing tools on the market.

Here is an overview https://dbmstools.com/data-compare-tools/sqlserver

Visual Studio - SQL Server Data Tools

if you work with Visual Studio you can use the SQL Server Data Tools.

Locate the tool in Menu > Tools > SQL Server New Data Comparison

VS Data Compare - New Data Comparison

Select the databases to compare

Select the objects to compare. It is important to distinguish between objects wich should be compared by name and objects to compare by ID. To compare objects by name exlude the ID-Column and change the Comparison Key from PK_xyz (which is the ID) to UK_xyz (which is the unique key based on other fields).

VS Data Compare - Select Objects and Columns

Here are the results:

vs_dc_result

Select objects to add or to udate and create change scripts.

isues and open questions

How to define an individual Comparison Key?

It looks like in VS Data Compare you can't define your own Comparison Key. For example if you would like to compare OBJECT_SCRIPTS you can't define to use only [Name] as alternative unique key. You can only use keys defined in the source. And you can't compare views because view have no PK or UK.

If it is possible to define own Comparison Keys or to compare views, please describe this here in the WIKI! I did not search because I use "SQL Data Examiner".

Requested to add or change UK definitions directly in AC

Currently there is no UK defined in [CFG].[PARAM_Values] and we can't select the UK by name.

We requested some feature request in the AC Bug tracker:

SQL Data Examiner

http://www.sqlaccessories.com/sql-data-examiner/

I am using the commercial "SQL Data Examiner" since 2005. Here I could explain how to use, if requested.

Comparing projects are located in the repo at

AnalyticsCreator_Toolkit\SQL Data Examiner\projects

It is easy to define customer keys:

sql_data_examiner_custom_key

These are the results:

sql_data_examiner_results

Version Control on locally saved repositories

Save repositories localy (AC: Shift + 'Save' Button). These scripts contain the whole repository including data.

Currently some schemas are not included in the script. You can restore repository scripts using the AC (AC: Shift + 'Open' button) and AC will create or overwrite a repository database.

If you want to restore a repository from a script without AC, you need to create required schemas in the target database: CFG, INTERN.

Using version control like GIT, different versions can be compared. This could work for a few changes. It can also be useful to compare the status before and after a repository update with a new version of the AC.

Here we see that a new predefined transformation has been added:

repo_script_compare_versions

RepoManipulator

A RepoManipulator database can connect to 2 AC repositories and simplify the manipulation of repositories.

some links:

Clone this wiki locally