Anchor Model Generator

A while back Juan-José van der Linden created a script that would reverse-engineer a database into an Anchor model. He was kind enough to donate that script to the community, and it is available in our forum. Now there’s also a second effort which is available in the form of the script below. This is a work in progress and it will be updated with more features in the future. Perhaps we can merge the best features from JJ’s script into this one or the other way around.

Please note that the script will use column statistics in order to determine if knots should be created, so it may take a long time to run when no statistics are available. It will reuse existing statistics, so a second run of the script is much faster. It tries to determine ties based on primary keys and matching column names.

The following script can be used to generate knot loading code, based on the data stored in the descriptions of the knots in the model after running the script above.

The following script can be used to generate source to target mappings for use with the sisula ETL framework, based on the data stored in the descriptions of the attributes in the model after running the script above.

Published by

Lars Rönnbäck

Co-developer of the Anchor Modeling technique. Programmer of the online modeling tool. Site maintainer. Presenter and trainer.

2 thoughts on “Anchor Model Generator”

  1. Hello,

    Great scripts, I’m currently giving it a try.

    The definition of the table #header could be updated to support the introduction of the column [Persisted Sample Percent] in the output of DBCC SHOW_STATISTICS with SQL Server 2016 (13.x) SP1 CU4:

    create table #header (
    [Name] varchar(555),
    [Updated] datetime,
    [Rows] bigint,
    [Rows Sampled] bigint,
    [Steps] int,
    [Density] float,
    [Average key length] float,
    [String Index] varchar(3),
    [Filter Expression] varchar(555),
    [Unfiltered Rows] bigint,
    [Persisted Sample Percent] tinyint
    );

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-ver15

    Thanks!

    KR
    Adrien

Leave a Reply

Your email address will not be published. Required fields are marked *