Database documentation generator

Abstract

  • setup Antora (one time task)

  • setup xxx-antora-sqldb and optional xxx-antora-docs repositories

  • setup Antora Playbook project repository

  • recurring steps

    • Export files for Antora

      Example 1. AntoraExport
      --ensure consistent and existing repository guid before sql parsing:
      --call repo.usp_sync_guid (quick and minimal) or repo.usp_main (takes more time)
      Exec repo.usp_sync_guid;
      
      --use sqlparser to parse sql definitions for views and import parsing results
      --for example in the folder containing the sqlparser.py call
      --py sqlparser.py --server localhost\sql2019 --database dhw_mydatabase
      
      --persist sql parsing results:
      Exec sqlparse.usp_sqlparse
      
      --ensure consistent repository after sql parsing:
      Exec repo.usp_main;
      
      --export Antora documentation sources
      Exec docs.usp_AntoraExport;
    • generate the static site

      In the Antora Playbook project call Antora to build the local or the final site.

Why Datahandwerk Documentation Generator?

Unique selling propositions and requirements

  • It’s free and open source

    • It can be used anywhere where it is allowed and possible to install Antora

  • Combination of business and technical documentation

    • It should be possible to combine business and technical content in one (or more) holistic documentation(s).

    • There should be links between the documentation of technical database objects and domain-oriented articles.

  • Common data source for optionally different documentation.

    • It’s possible to create different documentations for different purposes: a user potentially has different interests than a auditor or a database developer.

    • There should be common data sources for the documentations so that changes in the documentation sources have the same effect wherever they are used. A measure, KPI, column, or concept should be documented in one place.

  • Documentation under version control

    • The secondary data sources of the documentation(s) created from different primary sources and combined should also be under version control. From this, different documentation can be created in different versions and from different components.

  • Easy switching between different versions of the documentation

    • While some users are only interested in the documentation of the current version, for other users it may be important to have several versions documented together and to be able to switch between the versions.

  • Use of plain text formats (markup languages)

    • For optimal version control of the content, it makes sense to use plain-text formats for the sources of the documentation, such as Markdown or AsciiDoc.

  • portable documentation

    • If documentation is created as a static website with relative links, then it will work in any location on a filesystem as well as on a web server.

  • Docs as Code

    What does "Docs as Code" mean?

    Create and manage documentation like code:

    • under version control

    • with tests

    • and a build system for build and deployment.

    One key is to use a plain text format for documentation. Here are some links to websites that describe the concept in a bit more detail.

special requirements for technical database documentation

  • Virtual indexes, PK and FK

    In SQL Server, only real indexes, PK and FK can be stored, and only to tables.

    On the other hand there are important virtual relations between objects, which should be defined and documented

    • virtual PK of views (or also tables)

    • virtual foreign key relationships

    • virtual indexes, for example the combination of different columns with the same functional meaning. These indexes of the same meaning can be assigned to the same "index semantics group" to map a domain-oriented relationship.

  • virtual references

    Information about referenced and referencing objects can be taken from the metadata of databases.

    With columns this looks rather bad and is only possible under certain conditions.

    In an associated repository you can store virtual references:

    • References between tables whose data flow results from procedures.
      The SQL Server only shows that the procedure depends on tables. However, it does not know from where to where data flows.

    • virtual references between columns of different objects can be

      • by analyzing the code of views with a sqlparser

      • add them manually or by code

    • cross-database references

  • Data-Lineage

    The documentation should contain Data-Lineage on object level and on column level. For this purpose, the information that can be taken directly from the SQL Server (referenced and referencing objects) is to be used.

    Especially important and valuable is the use of additional virtual column references.

  • Inheritance of properties along a data lineage

    It should be possible to define functional descriptions of tables and columns at exactly one point and to inherit these descriptions along the column data lineage. One must be able to force this inheritance on the one hand, one must be able to cancel it also consciously, if at a certain place the description is to be changed.

    It is possible to store descriptions for objects or columns already in the "Source" (or to import them from their source), which are inherited up to the "Datamart". They are also taken over from there into the subsequent SSAS model.

  • Take over code of calculated columns into description

    For calculated columns it should be possible to take over the code of the calculation as description. If possible, the code could contain descriptions in comments.

    Unfortunately, this is not possible in calculated columns of tables, because SQL Server reformats calculated columns when saving them.

  • Extracting documentation from comments of views and procedures

    All SQL servers, which can be created by code, allow the use of comments. It should be possible to extract specially marked areas from comments and use them in the documentation. This can be descriptions, application examples, etc.

    Here an example, how in the code of a procedure in the comment different additional information is deposited and extracted as description and examples:

  • Combination of descriptions stored in the databases with descriptions that can be stored and maintained externally

  • Use of extended properties

Alternatives for technical documentation of databases

If it is "only" about a technical documentation of databases, then you can find very good and usable tools here. However, these tools are not suitable for a combined business and technical documentation. Nevertheless, I have practiced this for many years, trying to place all technical descriptions somehow in the database (for example in extended properties), so that there is one source for the documentation. You can get quite far with this, especially

  • if you find a mechanism to inherit descriptions along dependencies

  • if one can enrich the databases with further metadata:

    • virtual PK

    • virtual PK-FK relations

    • virtual relations (dependencies)

Most of these products do not support the use of markup languages like Markdown or AsciiDoc in descriptions, except for these, supporting Markdown:

  • SchemaSpy

  • SentryOne Database Mapper

The best tool seems to be:

  • SentryOne Database Mapper

  • by far: Datadeo

  • then the rest with often comparable features (no diagrams, output only as tables, dependencies only on object level)

Markup language AsciiDoc

My clear recommendation of an markup language is AsciiDoc. Why, I have briefly described in a blog article (in German):

Markdown is the most widely used, but there is not one Markdown, but several "dialects". A major disadvantage of Markdown is that you cannot include content from other files as "include". A Markdown file is always a single entity and can only reference (include) images (and maybe videos).

reStructuredText offers no advantages over Markdown or AsciiDoc, in my opinion.

AsciiDoc convinced me as the clear winner in the comparison:

  • Especially valuable is the possibility to include content from other documents as includes.

  • Variables can be used.

  • There is one syntax, and not dozens of dialects.

  • The syntax is as simple as Markdown.

  • The possibilities of the formatting are substantially more extensive, than those of Markdown.

  • With Kramdown AsciiDoc (Markdown to AsciiDoc) you can convert Markdown to Asciidoc.

Links to AsciiDoc:

Antora - Docs-As-Code with AsciiDoc

Since I decided to use the more powerful markup language AsciiDoc and against Markdown, as justified above, the best framework for docs-as-code with AsciiDoc in my opinion is Antora. This tool is created and maintained by the same developers who develop Asciidoctor. I have come to the conclusion that if you want to run Docs-as-Code with AsciiDoc, it will be hard to find anything better.

How does it work?

In the repository database, the configT.type - V view hard-codes which object types are exported to the documentation. In repo.RepoObject - U it can be defined in the column is_DocsExclude that individual RepoObject should be additionally excluded from the documentation.

The basic idea is based on the use of include in asciidoc documents.

  • For each object to be documented, different files are exported to different folders, which always have the same file name. The pattern for this is SchemaName.ObjectName.adoc

    • folder pages

      Each file has the same content, an include of one or more template files.

      The exact content is specified in config.Parameter - U and can be customized for specific projects.

      Example 2. page template content
      include::partial$template/master-page-1.adoc[]
      include::partial$template/master-page-examples.adoc[]
      include::partial$template/master-page-4.adoc[]
      include::partial$template/master-page-5_references.adoc[]
      include::partial$template/master-page-6.adoc[]
    • folder partials\template

      contains the templates that define the concrete contents.
      (The real content has no spaces at the beginning of each line. These are necessary here to prevent an include).

      = {docname} (1)
      
      include::partial${docname}.adoc[tag=existing_properties] (3)
      include::partial${docname}.adoc[tag=boolean_attributes]
      
      type:
      include::partial${docname}.adoc[tag=SysObject_type]
      (
      include::partial${docname}.adoc[tag=SysObject_type_name]
      ), modify_date:
      include::partial${docname}.adoc[tag=SysObject_modify_date]
      
      RepoObject_guid:
      include::partial${docname}.adoc[tag=RepoObject_guid]
      
      ifdef::is_repo_managed[] (2)
      is_repo_managed: 1
      endif::is_repo_managed[] (2)
      
      ifdef::is_ssas[] (2)
      is_ssas: 1
      endif::is_ssas[] (2)
      
      ifdef::ExistsProperty--MS_Description[] (2)
      
      == Description
      
      include::partial${docname}.adoc[tag=ms_description]
      
      endif::ExistsProperty--ms_description[] (2)
      1 By using {docname} the name of the file corresponding to the object ("SchemaName.ObjectName") is applied in the template.
      2 using ifdef, ifndef, endif it is possible to include content only if certain conditions are met.
      3 include object specific content from the objects main partial file, using tags
      See: Include Content by Tagged Regions
    • folder partials\content

      contains one partial document for each object, containing all object specific content, which should or could be used in the documentation for this object.

      The content is addressed and used via the template files.

    • folder partials\navlist

      contains different Antora navigation lists

Setup for Antora usage

Antora’s default site generator handles all aspects of creating a documentation site, from fetching and aggregating to converting and arranging the content to publishing the files of the generated site.

We recommend using at least two git repositories, one for the documentation content, another for the playbooks and the final generated documentation.

It is a good idea to use additional docs repositories for additional manually created documentation.

The Antora documentation explains how to install Antora globally. But it is recommended to install it locally in the repository where the documentation should be created.
In the following code adapt the antora version.

node -e "require('fs').writeFileSync('package.json', '{}')"
npm i -D @antora/cli@3.0.0-beta.2 @antora/site-generator-default@3.0.0-beta.2
npm i -D asciidoctor asciidoctor-kroki

setup sqldb-docs repositories

Antora can collect the content from different repositories. So, if you want to combine the database documentation with other content, you can (and should) distribute this content to different repositories. For example, the DataHandwerk documentation combines architecture, user guid, and database documentation in a common and interlinked documentation. And we use multiple source repositories:

steps to create a new source repository

  • create a new git repositories per dwh database, for example https://gitlab.com/mygroup/dhw-antora-sqldb

    • you can create and use local repositories

    • you can combine different documentation source repositories in one documentation

      • each dwh database has its own repository database (because of possible naming conflicts)

      • for different database use different parameter settings in config.Parameter - U for AntoraComponent and AntoraModule
        As you prefer, you can document each database as a separate component or as different modules of a common component. Each component has its own navigation bar, switching between components is done with a component selector, by default in the lower left corner.

  • at least one commit is required to enable Antora to use any repository

  • create required folder structure

the template structure:

  • 'docs' and 'docsmanual' are two (2) start_paths, used in the Antora Playbook yaml files

    • 'docs' should be filled using the export procedure [docs].[usp_AntoraExport]
      when exporting the files in subfolders 'partials' and 'pages' are deleted, that’s why you should not add manuell context into these folder

    • 'docsmanual' contains additional manual created and updated content. We use this separate start_path to avoid automatic deletion of files by [docs].[usp_AntoraExport]

  • the next level folder in both start_path is the folder 'modules', containing Antora modules

  • parallel to the folder 'modules' a file 'antora.yml' is required to define the Antora component. See What’s a Component Version? and What’s antora.yml?

    Both 'antora.yml' files in 'docs' and 'docsmanual' needs to match.
    name: mycomponent (1)
    title: My Project (2)
    version: 0.1.0 (3)
    nav:
    - modules/sqldb/nav.adoc (4)
    1 use the same component name in multiple antora.yml to combine them in one component
    2 define the component title only in one file, not in both
    3 use the same version(s) in related antora.yml to get a consistent documentation,
    use ~ to Define a Component with No Version
    4 define the nav (Navigation structure) only in one file, not in both
  • the default module in both start_path is 'sqldb'. if you don’t use the default AntoraModule 'sqldb' then rename the folder to match your 'AntoraModule' parameter settings in config.parameter.adoc[]. You also need to adapt the nav entry in 'antora.yml' to match

  • the start_path 'docsmanual' contains an additional component ROOT. The file 'index.adoc' in the folder 'pages' defines the home page of the component. You need to update the content.

  • 'nav.adoc' - each module needs one (1) file 'nav.adoc' to define the navigation structure. See: Navigation Files and Lists

    • if a module has multiple source folders, for example the module 'sqldb' is included under both start_path 'docs' and 'docsmanual' you need to care about having only one 'nav.adoc' under 'docs\modules\sqldb' or 'docsmanual\modules\sqldb'
      We suggest using the 'docs\modules\sqldb\nav.adoc' because it contains the links to automated exported content.

      • When you document relational databases then you should keep the include::partial$navlist/nav-by-schema.adoc[]

      • to document SSAS Tabular models, uncomment

        * xref:objectrefcyclic.adoc[]
        * xref:other/indexsemanticgroup.adoc[]
    • example file content (unindent the content, if you want to use it, the indent should avoild include processing.)

       include::partial$navlist/nav-by-schema.adoc[]
       * xref:objectrefcyclic.adoc[]
       * xref:other/indexsemanticgroup.adoc[]
  • setup 'AntoraComponent', 'AntoraModule' and 'AntoraComponentFolder' parameter settings in config.Parameter - U
    You can direct edit the table content or you can use tsql

--avoid "\" at the end of the Parameter_value!
Update
    [config].[Parameter]
Set
    [Parameter_value] = 'D:\Repos\aaa\bbb\MyDataBase_sqldb\docs'
Where
    [Parameter_name]  = 'AntoraComponentFolder'
    And sub_Parameter = '';
  • set ('AntoraDeleteFilesInModuleFolders', '') parameter settings in config.Parameter - U to '1'
    it is '0' by default to avoid uncontrolled deletion of data

setup optional docs source repository

DataHandwerk dhw-antora-sqldb contains automatically generated documentation (under 'doc') and additonal manual content (under 'docsmanual') related to the database documentation. Other documentation (architecture, user guid) is located in an additional repository dhw-antora-docs

you could use the template folder 'docsmanual' from https://gitlab.com/datahandwerk/DataHandwerk-toolkit-mssql/-/tree/main/template-antora-db/docsmanual to get the required structure

you can also use xcopy to get a folder structure to start your own documentation:

xcopy D:\Repos\gitlab\DataHandwerk\dhw-antora-docs D:\Repos\aaa\bbb\antora-docs /t /e

add index.adoc to be used as starting point

It is useful to have one (1) file index.adoc per Antora component. Create this file in pages of the ROOT module of each used Antora component.

setup Antora Playbook project

learn about the Antora Playbook

An Antora playbook makes it easy for technical writers to control what content is included in your site, what user interface (UI) is applied to it, and where the site is published using a playbook file. The settings in the playbook file, in combination with CLI options and environment variables, tell Antora how to operate.

A playbook is usually located in a playbook project. A playbook project repository is responsible for generating a documentation site. It’s strictly a configuration as code repository—​it does not contain any content. Instead, it contains a playbook file, and, in certain situations, supplemental UI files and extension code.

Create a new project folder, for example D:\Repos\aaa\bbb\MyProject-site

Copy some files and folders from the DataHandwerk project and adapt them:

  • supplemental-ui (Folder)

  • lib (Folder)

  • antora-playbook-local.yml

  • antora-playbook-public.yml

  • .gitignore

DataHandwerk documentation uses the following Playbook project: https://gitlab.com/DataHandwerk/datahandwerk.gitlab.io
It is also the output for the generated documentation.

Some content explained

  • two (2) playbooks are included, one to create local output, one to create the final public site.

    • They are different in the output folder.

      • the 'local' folder is included into .gitignore to exclude from git repository

    • both playbooks are different in the kroki-fetch-diagram: true option. It takes a lot of time to generate all diagrams and this option is used only to create the final public site.

    • both could be also different in sources for testing

      site:
        title: DataHandwerk Docs
        url: https://DataHandwerk.gitlab.io
        start_page: dhw::index.adoc (1)
        # start_page: dhw:arc:architecture.adoc
        keys:
          google_analytics: 'G-abcdefghij'
      content:
        sources:
        - url: D:\Repos\gitlab\DataHandwerk\dhw-antora-docs
          branches:
          - HEAD
          # - v*
          start_path: docs
        - url: D:\Repos\gitlab\DataHandwerk\dhw-antora-sqldb
          branches:
          - HEAD
          # - v*
          start_paths: docs, docsmanual
        # - url: https://github.com/DataHandwerk/DataHandwerk-docs
        #   branches: main
        #   start_path: docs
      asciidoc:
        attributes:
          page-pagination: ''
          experimental: ''
          :plantuml-server-url: http://www.plantuml.com/plantuml
          # You can use the kroki-fetch-diagram option to download the images from Kroki at build time. In other words, while viewing pages you won't rely on Kroki anymore.
          # However, in Antora, this is not currently compatible with inline SVG images.
          # kroki-fetch-diagram: true (3)
        extensions:
        - asciidoctor-kroki (2)
      ui:
        bundle:
          url: https://gitlab.com/antora/antora-ui-default/-/jobs/artifacts/master/raw/build/ui-bundle.zip?job=bundle-stable
          snapshot: true
        supplemental_files: ./supplemental-ui (5)
      output:
        clean: true
        dir: ./local (4)
      urls:
        redirect_facility: static
      runtime:
        fetch: true (6)
      1 in case you have only the database documentation you need to set the start_page to some page in your module (for example 'myproject:sqldb:dbo.MyTable.adoc'). 'dhw' or 'myproject' is the project name defined in the antora.yml
      2 required to render diagrams
      3 kroki-fetch-diagram is required for the final site, but it takes a lot of time and should be disabled if you want to check the results locally. Keep in mind that some big diagrams will not be shown if this option is disabled, but they will be rendered for the final site if enabled
      4 put the output dir for the local playbook into .gitignore to exclude from version control
      5 the antora-ui-default needs to be adapted a bit to show your own menu content
      6 force source repository fetch in General instead of using it every time in the antora command
  • the ".gitignore" file should contain some entries to avoid committing some content into the repository.

    /local (1)
    node_modules
    npm
    .idea
    package-lock.json
    build
    1 exclude the site generated from the local-target playbook from source control
  • D:\Repos\gitlab\DataHandwerk\datahandwerk.gitlab.io\supplemental-ui

    Before creating a completely custom Antora UI, you can use the default UI within certain limits and overwrite some components.

    To do this, you can look at the repository of the Antora Default UI and try to understand its elements to overwrite individual files. The structure used is located in the src folder.

    • \partials\header-content.hbs
      this file should be customized to adapt menu structure in the header

    • \partials\head-styles.hbs
      one line is added to use \css\custom.css to overwrite some css styles

    • \css\custom.css
      some changes to enlarge the wight of navigation bar, TOC section and maximum content with.
      The font size of the navigation bar was decreased to avoid too much line breaks for long object names.

Export files for Antora

Example 3. Usage
--ensure consistent and existing repository guid before sql parsing:
--call repo.usp_sync_guid (quick and minimal) or repo.usp_main (takes more time)
Exec repo.usp_sync_guid;

--use sqlparser to parse sql definitions for views and import parsing results
--for example in the folder containing the sqlparser.py call
--py sqlparser.py --server localhost\sql2019 --database dhw_mydatabase

--persist sql parsing results:
Exec sqlparse.usp_sqlparse

--ensure consistent repository after sql parsing:
Exec repo.usp_main;

--export Antora documentation sources
Exec docs.usp_AntoraExport;

generate the static site

The parameter 'dwh_readonly' is 0 by default.
In this case no Schema_guid, RepoObject_guid or RepoObjectColumn_guid will be written as extended properties into the connected DWH database.
This parameter should and can remain 0 if a database should only be documented but not modified.
Antora uses git repositories as sources. You must put the source repositories under Git version control, there must be at least one commit.
Antora Export can delete any content in the target folders. You can set this in config.Parameter - U setting 'AntoraDeleteFilesInModuleFolders' to 1.

export the automated documentation

--initial setup
Exec repo.usp_main;

--use the sqlparser to extract column references
--...
--after using the sqlparser persist the results

Exec sqlparse.usp_sqlparse

--process the sqlparser results
Exec repo.usp_main;

--export files for automated documentation
Exec docs.usp_AntoraExport

In the Antora Playbook project call Antora to build the local or the final site.

npx antora antora-playbook-local.yml --stacktrace
npx antora antora-playbook-public.yml --stacktrace
npx antora antora-playbook-local.yml --stacktrace --log-file antora-local.log
npx antora antora-playbook-public.yml --stacktrace --log-file antora-public.log

The result is a static site. You can browse the content locally on the PC where you created the documentation.

If you want to publish the site as GitHub page, you need to create .nojekyll: https://docs.antora.org/antora/2.3/publish-to-github-pages/#nojekyll

use local PlantUML Server

It is possible to use a local PlantUML server.

https://sourceforge.net/projects/plantuml/
here you can download a plantuml.war file to be used in a local webserver lice tomcat.

document additional and external references

See examples:

It is possible to store additional and external references in order to document them. This is especially useful if there are references between different relational databases and these databases are part of the documentation. Likewise, relationships between SSAS Tabular models and a relational source database can be documented.

Additional references can be entered in the reference.additional_Reference - U table.

  • There are columns for the referenced and for the referencing objects and columns.

  • The entry of the columns is optional; AntoraComponent, AntoraModule, Schema and Object are mandatory.

  • the content of the table reference.additional_Reference - U is used by reference.usp_additional_Reference - P and this procedure is called by repo.usp_main - P. Thus, additional references are used automatically.

  • in the table reference.additional_Reference_database_T - U you need to enter values into the columns

    • 'RepoDatabaseName'
      the current repository needs information about the corresponding repository

    • 'DatabaseName'
      this information is used in the documentation generator as the database name of the referenced or referencing databases.

Synchronization of cross-repository references

If cross-repository references are defined in different repositories in reference.additional_Reference - U, then they should be able to be synchronized between these repositories. It should be possible to use the references in both repositories (for example for documentation) without having to enter the references twice.

Several steps are required:

  • Because a repository cannot readily know in which other repositories references are entered with itself, and because we cannot or do not want to scan all repository databases, we need to enter metadata about the correspondig repository databases.

    • cross-repository references are entered in one repository in reference.additional_Reference - U.

    • at least one entry must also be copied to the same table in the corresponding repository, to let them know about referenced and referencing repositories.

  • reference.usp_additional_Reference - P (or the parent procedure repo.usp_main - P) must be executed in the second repository.

  • in the table reference.additional_Reference_database_T - U you need to enter values into the columns 'DatabaseName' and 'RepoDatabaseName' to link to the corresponding repository

  • The procedure property.usp_external_property_import - P (which is also called in the parent procedure repo.usp_main - P)

    • imports other corresponding entries into reference.additional_Reference - U

    • imports properties from corresponding repository, if the PropertyName is marked for inheritance.

      • condition for RepoObject properties:
        WHERE property.PropertyName_RepoObject_T.has_inheritance = 1

      • condition for RepoObjectColumn properties:
        WHERE property.PropertyName_RepoObjectColumn_T.has_inheritance = 1

document SSAS Tabular models

See example: SSAS Tabular Models

Create a new dhw repository and don’t try to mix relational db documentation and SSAS Tabular documentation. Maybe it is possible to combine them in one repository, but there could be naming conflicts between relational table names and SSAS Tabular table names. Anyway we did not test.

SSAS Tabular databases are documented as similar as if they were relational databases, so that broadly the same logic can be used. And there are differences as well.

  • SSAS Model Name ⇒ Schema Name

    • This way it is possible to document multiple SSAS Models in one component and one module, if the model names are different

    • we can use one repository for all ssas models instead of the required one repository per relational dwh database

  • SSAS Table Name ⇒ Table Name

  • SSAS Measures are documented twice

    • in the table where they are defined

    • in a pseudo table '_measures' where all measures of the model are documented in one place

  • Translations: one extra Antora Module per translation is used

    if the SSAS model contains the translation (cultures) 'en-us' and 'de-de' then 2 additional modules are used, where the suffix is defined by the culture. I this example the main Antora module is 'ssasdb'

    • ssasdb

    • ssasdb-de-DE

    • ssasdb-en-US

    • to distinguish between sql server database and ssas database don’t use 'sqldb' as Antora module name, but use for example 'ssasdb'

  • set the Parameters in config.Parameter - U

    • ('AntoraComponent', '') and adapt 'antora.yml' name; for example 'ssas'

    • ('AntoraComponentFolder', '')

    • ('AntoraDeleteFilesInModuleFolders', '') ⇒ '1'

    • ('AntoraModule', '') ⇒ for example 'ssasdb'

    • ('sync enable', 'dwh') ⇒ '0'

    • ('sync enable', 'ssas') ⇒ '1'

    • ('sync enable', 'ssis') ⇒ '0'

  • ensure the existence of one Antora module per used translation (for example 'ssas' and 'ssas-de-DE')

Define the SSAS Models you wan’t to document.

  • create one row per ssas model

    • enter the model name to be used in the documentation in 'databasename', for example 'AdventureWorks'

    • enter the local file location of the model into 'model_filename', for example 'D:\Repos\local\AdventureWorks\AdventureWorks_models\de-de\Model.bim'

    • if you want to document multiple SSAS models, you can add multiple entries into the table

  • get the sql to import data, using

    Select
        ImportModelSql
      , databasename
      , model_filename
      , model_utf8
      , isModelJson
    From
        ssas.model_json_ImportModelSql

    Copy the content from column 'ImportModelSql' and execute it. this content could be

Update ssas.model_json Set model_utf8 =
(
Select BulkColumn
From
OpenRowset ( Bulk 'D:\Repos\local\AdventureWorks\AdventureWorks_models\de-de\Model.bim', Single_Blob )
As j
)
Where databasename = 'AdventureWorks'
GO
  • execute the query on 'ssas.model_json_ImportModelSql' again to check, if 'isModelJson' = 1. If this is not the case, then you have an issue.

  • execute repo.usp_main

    Exec repo.usp_main
    • check the output. Any errors?

    • check repo.RepoObject - U

      • do you see the SSAS tables? And do you see only them?
        Fine

      • do you see some strange tables, for example objects containing in the repodatabase?
        You forgot to set the parameter ('sync enable', 'dwh') ⇒ '0'
        Correct this, remove unwanted RepoObjects

    • check the table repo.RepoObjectColumn - U

    • check the view repo.Measure - V

If you want to export not only the main model documentation, but also translations, you need to configure this in ssas.model_json_3411_cultures_translations_model_T - U!

set is_CultureDocsOutput = 1
for the Cultures, you want to export

the case of the culture in 'cultures_name' and in the export folder should match. That’s why we use 'de-DE' but not 'de-de'.

  • the implementation could be changed to use only lower case.

  • execute repo.usp_main a second time!
    it is important to execute this procedure several times, because some tables are filled in the first execution which are required in the next execution: translation settings and some other content.

    Exec repo.usp_main
  • execute docs.usp_AntoraExport

    Exec docs.usp_AntoraExport
  • check the exported content

  • create a git repository and do an initial commit

  • update your Antora playbook and create the documentation

optional: add relations to the relational data source

  • in the table ssas.model_json_33_dataSources_T - U you need to enter values into the columns 'referenced_AntoraComponent' and 'referenced_AntoraModule'

  • Exec repo.usp_main ⇒ to get the next table filled

  • in the table reference.additional_Reference_database_T - U you need to enter values into the columns 'DatabaseName' and 'RepoDatabaseName'

  • Exec repo.usp_main ⇒ to get the next table filled

  • SSAS models contain metadata about the data sources, these are extracted, combined with the entries of the two tables above and inserted into the reference.additional_Reference - U table.

optional: document measure properties

By default, only the description and expression are documented per measure.

Additional properties can be entered into the property.MeasureProperty - U table, for example via import from external sources.

Whether and how these properties are used in the generated documentation is controlled in the property.PropertyName_Measure_T - U table:

  • In the OrderInAntoraMeasureDetails column, the order is specified. Only property_name with OrderInAntoraMeasureDetails > 0 will appear in the documentation.

  • In the following columns the formatting of the content can be specified

    • NoOfCollapsibleChars
      a [%collapsible] can be defined
      reasonable values are 0 (no collapsible) or values >= 5
      a variable value is used to avoid conflicts with nested blocks

    • NoOfExampleChars
      an example block ==== can be defined
      reasonable values are 0 (no example block) or values >= 4
      a variable value is used to avoid conflicts with nested blocks

    • NoOfListingChars
      a listing block ---- can be defined
      reasonable values are 0 (no listing block) or values >= 4
      a variable value is used to avoid conflicts with nested blocks

document SSIS projects

See example: Workflow package.dtsx

Multiple SSIS projects can be combined into one DHW repository.

SSIS projects are documented differently than relational databases and SSAS tabular models:

  • several SSIS projects can be combined in one Antora component

  • each SSIS project is documented as a separate Antora module

  • each SSIS package is documented as a separate page

Create a new dhw repository to document SSIS projects. It should be possible to combine SSIS projects with relational databases or SSAS tabular models in one repository, but this was not tested.

  • set the Parameters in config.Parameter - U

    • ('AntoraComponent', '') and adapt 'antora.yml' name; for example 'ssis'

    • ('AntoraComponentFolder', '')

    • ('AntoraDeleteFilesInModuleFolders', '') ⇒ '1'

    • ('AntoraModule', '') ⇒ value is ignored for ssis documentation

    • ('sync enable', 'dwh') ⇒ '0'

    • ('sync enable', 'ssas') ⇒ '0'

    • ('sync enable', 'ssis') ⇒ '1'

Define the SSIS projects you wan’t to document.

  • 'AntoraModule' - define the Antora Module to used per project

  • 'ProjectPath' - enter the project path containing SSIS packages and connections

  • if you want to document multiple SSIS projects, you can add multiple entries into the table

  • use the column 'sql_import' to get the sql to import data from 'ProjectPath' into the repository. It looks like

    Exec ssis_t.usp_GetPackageDetails 'D:\Repos\aaa\bbb\ServiceNow\SSIS\SSIS_ServiceNow'
    Exec ssis_t.usp_GetProjectConnection 'D:\Repos\aaa\bbb\ServiceNow\SSIS\SSIS_ServiceNow'
    Exec ssis.usp_import
  • execute this code

  • now several tables in schema 'ssis' should contain data

  • adapt the content of the Antora component folders

    • create required component folders (copy from template)

    • check and update 'antora.yml' files in start_path 'docs' and 'docsmanual'

      name: ssis
      title: Demo SSIS
      version: ~
      nav:
      - modules/performanceanalytics/nav.adoc
      - modules/servicenow/nav.adoc
    • update the content of the startpage (.\docsmanual\modules\ROOT\pages\index.adoc)

    • create / update 'nav.adoc' per Antora Module

      .ServiceNow
      include::ssis:servicenow:partial$navlist/navlist-package.adoc[]
      .PerformanceAnalytics
      include::ssis:performanceanalytics:partial$navlist/navlist-package.adoc[]
  • process the repository and export the sources for the documentation

    Exec repo.usp_main
    Exec docs.usp_AntoraExport
  • check the exported content

  • create a git repository and do an initial commit

  • update your Antora playbook and create documentation

Logic and implementation is based on this blog article SSIS Package documentor
from 2016-11-04 (first published: 2015-09-01)
author: https://www.sqlservercentral.com/author/Divya-Agrawal

the article explains how does it work and provides a link for a procedure script: https://www.sqlservercentral.com/wp-content/uploads/2019/05/CodeReviewAcceleratior_v10.sql

The original procedure has been modified and extended, tables and steps have been moved into separate steps and objects. Adaptations were made for newer SSIS versions and for contents that were not considered by the original procedure.

external descriptions

In principle, it is possible and useful to use AsciiDoc include directives in descriptions stored in the repository databases to include external content. This content is usually located in the Antora data source git repositories for the domain-oriented descriptions. This allows objects, columns, etc. to be described outside of the repository databases. This has several advantages:

  • Easy editing with text editors.

  • Possibility of version control ('docs-as-code').

If the 'description' property of objects or columns contains an include, then this content can also be inherited along the Properties Inheritance. This can be especially useful for columns to specify the descriptions of the columns only once.

<<property_start>>Description
include::partial$docsnippet/reference-from-persistence.adoc[]
<<property_end>>

descriptiontags

In addition, there is a formal method to store descriptions for tables and SSAS measures outside the repository database directly in the git repositories used by Antora as data sources. This makes it possible to define these optional descriptions and use them in the documentation without having to explicitly store the descriptions as include.

However, this requires that the file names and the tags used follow a precise syntax.

Since it can be difficult to use the filenames and tags correctly, a template is exported for each RepoObject. The export is done in AntoraExport in the docs startpath under partial$descriptiontags/schemaname.objectname.adoc. Note the prefix '' (underscore) of the foldername. These files are deleted and recreated on each export. Therefore, the contents cannot be added at this point, with the next AntoraExport the contents would be overwritten.

Um diese Templates auszufüllen, können einzelne (oder alle) Dateien in den startpath docsmanual in den Ordner partial$descriptiontags/schemaname.objectname.adoc kopiert werden - ohne den Prefix '_'. Hier können diese Dateien nun mit Inhalt gefüllt werden und diese Inhalte bleiben beim AntoraExport erhalten.

Example 4. descriptiontags content for config.Parameter
= config.Parameter

// tag::description[]
* default parameter values are defined (hard coded) in xref:sqldb:configt.parameter_default.adoc[] and available in xref:sqldb:config.parameter.adoc#column-parameter_default_value[config.Parameter.Parameter_default_value]
* default parameter values can be overwritten by project specific content using xref:sqldb:config.parameter.adoc#column-parameter_value[config.Parameter.Parameter_value]
* resulting content is available in
** xref:sqldb:config.parameter.adoc#column-parameter_value_result[config.Parameter.Parameter_value_result]
** xref:sqldb:config.parameter.adoc#column-parameter_value_result_date[config.Parameter.Parameter_value_result_date]
** xref:sqldb:config.parameter.adoc#column-parameter_value_result_datetime[config.Parameter.Parameter_value_result_datetime]
** xref:sqldb:config.parameter.adoc#column-parameter_value_result_int[config.Parameter.Parameter_value_result_int]
// uncomment the following attribute, to hide exported descriptions

:hide-exported-description:
// end::description[]

== Measures
  1. What is the ':hide-exported-description:' attribute for?

    There could be external descriptions and those exported via AntoraExport at the same time.

    • Without this attribute (if it is left commented out), both external and exported descriptions are used.

    • If the attribute is set (as in the example), then exported descriptions for the concrete object or measure are not used in the documentation.

  2. Why is this so complicated?

    • we need different locations for content that is recreated with each AntoraExport and for content that must not be deleted during AntoraExport.

      • in the startpath docs the contents of pages and partials will be deleted and recreated each time

      • in the startpath docsmanual the contents of pages and partials are preserved

      • since both startpaths belong to the same Antora module, the file paths within the module must be unique.
        Therefore we need two different folder names and decided to use 'descriptiontags' and '_descriptiontags'.

  3. Is there a way to make this simpler?

    Maybe, we are open to constructive suggestions and improvements.

These formal files are a technical interface for the documentation. They may contain the descriptions. However, it is also possible to maintain the actual descriptions elsewhere and reference them here via includes. This is especially useful if the domain is responsible for the contents of the descriptions and these descriptions are maintained in other repositories or simply elsewhere. This is where the technical documentation takes place.
Since the descriptiontags descriptions are defined externally and are not known in the repository database, these descriptions cannot be inherited.

Nevertheless, it is possible and useful to use these descriptions on other objects. In the following example, an external description for config.Parameter - U is also used in the configT.Parameter_default - V view by including an include in a commented out section of this view.

<<property_start>>Description
include::sqldb:partial$descriptiontags/config.parameter.adoc[tag=description,opts=optional]
<<property_end>>

In this case, these descriptions are again subject to possible inheritance.