Properties
Each schema, object, column and SSAS Tabular measure can have properties.
The main application area of the properties is the use in the documentation generator.
Object and column properties can also be inherited along the object references or along the column references.
Properties are stored in:
The following properties are not stored in these tables, because the synchronization of the RepoObjects with the dwh database is done via these properties:
-
RepoSchema_guid
-
RepoObject_guid
-
RepoObjectColumn_guid
-
Measure_guid
optional synchronization of properties between repository and dwh
The properties listed above are the only ones that are mandatorily synchronized. Other properties can be optionally synchronized if it is desired to manifest them also in the database in the code of objects.
In SQL Server, extended properties have no creation or modification date. Therefore, it is not possible to determine if and when there were changes. For synchronization of RepoSchema_guid
, RepoObject_guid
and RepoObjectColumn_guid
properties there is a complicated process to allow changes in both repository and dwh in parallel and merge them. With "normal" properties this effort is not made. The database developer must decide for himself if and when he wants to synchronize the optional properties and in which direction.
It is highly recommended to transfer existing properties from the dwh to the repository after "Create, update and connect repository database". For this purpose there is the procedure:
And it is recommended that after this initial transfer of the extended properties from the dwh to the repository, they should only be managed in the repository.
It is possible, but not necessary, to synchronize the properties from the repository to the dwh extended properties:
-
property.usp_sync_ExtendedProperties_Repo2Sys_InsertUpdate - P
-
property.usp_sync_ExtendedProperties_Repo2Sys_Delete - P
deletes all extended properties in the dwh, not defined in the repository!
How to define properties
-
You can insert, update and delete properties directly in the tables.
And this could be complicated if you want to enter multi-line-content or if you don’t know the guid. -
You can use an additional database, into which descriptions from other sources are imported, and from this database externally merge into these tables.
-
You can use procedures
This makes it easier to create new entries, use the names of objects and columns, and assign multi-line values. -
The usp generator contains extra columns for some properties
-
Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.
You can use comments in these objects to define propertiesExample 1. define properties in SQL module comment/* <<property_start>>Description (1) * connect repo datatabase to dwh database using synonyms executing (2) * see details in xref:user-guide:create-update-connect-repo-db.adoc[] <<property_end>> (3) <<property_start>>exampleUsage (1) EXEC [config].[usp_connect_database] (2) @dwh_database_name = 'WideWorldImporters' <<property_end>> (3) */ CREATE Procedure [config].[usp_connect_database] ( @dwh_database_name NVarchar(128)) As Begin ...
sql1 start a new line with <<property_start>>
followed by the property_name to mark the start of a property definition2 use the line(s) between <<property_start>>
and<<property_end>>
to define the property_value3 start a new line with <<property_end>>
to mark the endThese properties will be imported (collected) into property.RepoObjectProperty - U by the procedure property.usp_RepoObjectProperty_collect - P which is also called by docs.usp_AntoraExport - P to ensure that your generated database documentation contains all defined properties.
Properties are used and changed by Properties Inheritance.
It is possible to define descriptions for objects (tables, views, procedures, functions, …) and measures outside the repository database, directly in the sources of the documentation generator. These descriptions then also exist only outside the repository. They are therefore also not subject to inheritance. On the other hand, it is easier to maintain them uniformly there and to place them under version control. |