The Datavault Builder relies on a diverse set of tables, views and functions on the processing database. As each database offers different possibilities for the technical implementation of the Data Vault based integration flow, these structures can slightly differ depending on the used database.

Therefore, in this chapter the overall same structures (tables and views) are explained, which can as well be used as source for meta-data analytics.

Note

Be aware, that changes to these structures can affect the system stability and performance and may cause failures!

Upper- or lower cased naming is based on the used database technology (e.g. postgres is lowercase, Exasol is uppercase).

Metadata Views

For performance reasons, for certain types of client databases (e.g. Exasol), the metadata views are materialized to speed up the performance in the GUI. If the client database does not actually support materialized views, then a table is created instead with the name of the metadata view and the actual view will have the postfix _v.

dvb_core.access_errormart

The metadata view lists all access errormart views.

Column

Meaning

access_errormart_id

Complete technical ID of the access errormart. Including Schema.

access_errormart_name

Displayed Name of the access errormart.

functional_suffix_id

Technical suffix for the access errormart.

functional_suffix_name

Displayed Name of the suffix.

business_ruleset_suffix_id

Technical suffix of the underlying business ruleset.

business_ruleset_suffix_name

Displayed Name of the suffix of the underlying business ruleset.

parent_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

parent_hub_name

Displayed Name of the underlying hub.

access_errormart_comment

Custom comment about the access errormart.

dvb_core.accesslayers

The metadata view lists all access layers.

Column

Meaning

accesslayer_id

Complete technical ID of the access layer. Including Schema.

accesslayer_name

Displayed Name of the access layer.

functional_suffix_id

Technical suffix for the access layer.

functional_suffix_name

Displayed Name of the suffix.

parent_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

parent_hub_name

Displayed Name of the underlying hub.

accesslayer_comment

Custom comment about the access layer.

dvb_core.business_rules

The metadata view lists all business rulesets.

Column

Meaning

business_ruleset_view_id

Complete technical ID of the business ruleset. Including Schema.

functional_suffix_id

Technical suffix of the underlying business object.

functional_suffix_name

Displayed Name of the suffix of the underlying business object.

business_ruleset_suffix_id

Technical suffix of the business ruleset.

business_ruleset_suffix_name

Displayed Name of the Suffix for the business ruleset.

system_id

Technical ID of the system of the granularity satellite.

system_name

Displayed Name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

start_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

start_hub_name

Displayed Name of the underlying hub.

related_businessobject_view_id

Technical ID of the underlying business object. Including Schema.

business_rules_comment

Custom comment about the business ruleset.

business_ruleset_name

Displayed Name of the business ruleset.

business_rules_view_code

Defining SQL Code of the business ruleset.

is_error_ruleset

Flag, if ruleset is to be delivered into access errormart.

include_in_accesslayer

Flag, if ruleset is to be delivered into access layer.

accesslayer_priorization

Priorization number of dataset in accesslayer. Lower number means higher priorization.

quick_inserts

Configuration of business ruleset editor quick inserts. Stored as JSON.

dvb_core.businessobjects

The metadata view lists all business objects.

Column

Meaning

businessobject_view_id

Complete technical ID of the business object. Including Schema.

functional_suffix_id

Technical suffix of the business object.

functional_suffix_name

Displayed Name of the suffix for the business object.

system_id

Technical ID of the system of the granularity satellite.

system_name

Displayed Name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

businessobject_comment

Custom comment about the business object.

start_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

start_hub_name

Displayed Name of the underlying hub.

businessobject_name

Technical ID of the underlying business object. Including Schema.

businessobject_structure

Stored Metainformation on the used elements in the business object. Stored as JSON.

dvb_core.columns

The metadata view lists all columns in the Datavault Builder Schemas.

Column

Meaning

schema_id

Technical ID of the Schema.

table_nq_id

Technical ID of the table. Without Schema.

column_nq_id

Technical ID of the column. Without Schema and table.

column_id

Technical ID of the column. Including Schema and table.

column_name

Displayed Name of the column.

column_comment

Custom comment about the column.

data_type

Data type of the column.

data_type_id

Technical ID of the data type.

character_maximum_length

Maximum length of the field. If applicable.

numeric_precision

Numeric precision of the field. If applicable.

numeric_scale

Numeric scale of the field. If applicable.

datetime_precision

Datetime precision of the field. If applicable.

ordinal_position

Position of the column within the table/view.

complete_datatype

Datatype including precision and scale if applicable.

dvb_core.hub_loads

The metadata view lists all defined loads into hubs.

Column

Meaning

hub_load_id

Technical ID of the hub load. Without Schema.

hub_id

Technical ID of the hub. Without Schema.

technical_business_key

SQL Statements which will compose the value of the business key.

short_business_key

Displayed business key configuration.

hub_load_list_entry

Displayed name of the hub load.

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

staging_table_id

Technical ID of the staging table to stage from. Including Schema.

staging_table_view_id_hash

CRC32-Hash of the staging table view id.

keys_are_unique

Uniqueness is checked when loading the hub.

keys_are_prehashed

Keys are already hashed in the source.

datavault_category_id

Technical ID of the related datavault category to load (PSA, Raw Vault, Business Vault)

dvb_core.hubs

The metadata view lists all hubs.

Column

Meaning

hub_id

Technical ID of the hub. Without Schema.

hub_name

Displayed name of the hub.

boid

Technical ID of the hub without hub identifying prefix (h_) and Schema.

hub_id_of_alias_parent

Technical ID of the parent hub if the hub is an alias. Without Schema.

hub_name_of_alias_parent

Displayed name of the parent hub if the hub is an allias.

hub_subject_area_name

Displayed subject area for the hub.

hub_comment

Custom comment about the hub.

hub_is_prototype

Flag, if hub is just a prototype (without load).

dvb_core.jobs

The metadata view lists all jobs.

Column

Meaning

job_id

Technical ID of the job.

system_id

Technical ID of the system the job belongs to.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

job_name

Displayed name of the job.

job_suffix_id

Technical suffix of the job.

job_type

Add new loads automactically. Values: Auto or manual.

parallel_loads

Max. number of globally running loads, which limits another load of the job to be initiated.

job_comment

Custom comment about the job.

job_enabled

Flag, if job is active or not.

next_run

Over all schedules the next planned runtime.

last_run

Last initiation time.

last_run_duration

Duration of the last job load.

dvb_core.latest_datavault_load_info

The metadata view lists the current load info of all datavault loads which were initiated at least once.

Column

Meaning

load_entry_time

Initiation time, when the load was sent into the loading queue.

object_id

Technical ID of the datavault object (hub, link or satellite). Without Schema.

staging_table_id

Technical ID of the staging table to load from. Including Schema.

load_start_time

Last initiation time of the load.

load_end_time

Last end time of the load.

load_duration

Last duration of the load.

load_state

Current state of the load.

load_result

Result of the last load.

load_progress

Number of rows which have currently been processed within the load.

load_total_rows

Number of rows to process within the load. Does not have to be equal to the actual number of rows which are inserted (i.e. for hubs only new entries are added)

login_username

Displayed name of the initiating user.

job_id

Technical ID of the job within the load was initiated if applicable.

pid

Process ID in the core, which initiated the load.

dvb_core.latest_job_load_info

The metadata view lists the current load info of all jobs which were initiated at least once.

Column

Meaning

load_entry_time

Initiation time, when the job was initiated.

job_id

Technical ID of the job.

latest_load_start_time

Time, when the last job started to initiate loads.

latest_load_end_time

Time, when the last job run finished.

succeeded_load_start_time

Time, when the last successful load started to load.

succeeded_load_end_time

Time, when the last successful job run finished.

failed_load_start_time

Time, when the last failed job started to load.

succeeded_load_duration

Duration of the last successful job run.

current_loading_duration

Duration of the current job run.

load_state

Current state of the job.

load_result

Result of the last job run.

username

Displayed name of the initiating user.

where_clause_parameters

Key-Value pairs of parameters which are filled into the where clause for delta loading into staging. Stored as JSON.

is_delta_load

Flag, if the current run is a executed as delta load.

pid

Process ID in the core, which initiated the job.

dvb_core.latest_staging_load_info

The metadata view lists the current load info of all staging loads which were initiated at least once.

Column

Meaning

load_entry_time

Initiation time, when the load was initiated.

staging_table_id

Technical ID of the staging table. Including Schema.

source_table_id

Technical ID of the table / view in the source system.

latest_load_start_time

Time, when the last load was started.

latest_load_end_time

Time, when the last load ended.

succeeded_load_start_time

Time, when the last successful load was started.

succeeded_load_end_time

Time, when the last successful load ended.

failed_load_start_time

Time, when the last failed load was started.

succeeded_load_duration

Duration of the last successful load.

current_loading_duration

Duration of the current load.

load_state

Current state of the load.

load_result

Last result of the load.

load_progress

Number of loaded rows.

load_total_rows

Number of rows to load.

load_progress_percent

Percentage processed rows in relation to total rows.

username

Displayed name of the initiating user.

from_system_load

Flag, if the staging load was initiated as part of a total system load in staging.

job_id

Technical ID of the initiating job if applicable.

pid

Process ID in the core, which initiated the load.

dvb_core.linksatellites

The metadata view lists all link satellites.

Column

Meaning

table_nq_id

Technical ID of the linksatellite table. Without Schema.

linksatellite_id

Technical ID of the linksatellite without schema and technical relevant suffix(es).

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

functional_suffix_id

Technical suffix of the linksatellite.

functional_suffix_name

Displayed Name of the suffix for the linksatellite.

linksatellite_subject_area_name

Displayed subject are for the linksatellite.

linksatellite_comment

Custom comment about the linksatellite.

parent_link_id

Technical ID of the parent link.

parent_link_name

Displayed name of the parent link.

linksatellite_name

Displayed name of the linksatellite.

dvb_core.load_log_datavault

The metadata view lists the final result of datavault loads without intermediate steps (such as waiting or loading).

Column

Meaning

load_entry_id

Identifying log sequence number.

load_entry_time

Inititation time of load.

object_id

Technical ID of the datavault object (hub, link or satellite). Without Schema.

staging_table_id

Technical ID of the staging table to load from. Including Schema.

load_start_time

Start time of the load.

load_end_time

End time of the load.

duration

Duration of the load.

load_state

Final state of the load.

load_result

Final result of the load.

load_total_rows

Total rows to process within the load.

login_username

Displayed name of the initiating user.

load_progress

Number of rows which have been processed within the load.

pg_username

User group of initiating user.

dvb_core.load_log_staging

The metadata view lists the final result of staging loads without intermediate steps (such as waiting or loading).

Column

Meaning

load_entry_id

Identifying log sequence number.

load_entry_time

Inititation time of load.

source_table_id

Technical ID of the table / view in the source system.

staging_table_id

Technical ID of the staging table to load from. Including Schema.

load_start_time

Start time of the load.

load_end_time

End time of the load.

duration

Duration of the load.

load_progress

Number of rows which have been processed within the load.

load_total_rows

Total rows to process within the load.

load_state

Final state of the load.

load_result

Final result of the load.

login_username

Displayed name of the initiating user.

pg_username

User group of initiating user.

dvb_core.satellites

The metadata view lists all satellites.

Column

Meaning

table_nq_id

Technical ID of the satellite table. Without Schema.

satellite_id

Technical ID of the satellite without schema and technical relevant suffix(es).

boid

Technical ID of the parent hub without hub identifying prefix (h_) and Schema.

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

functional_suffix_id

Technical suffix of the satellite.

functional_suffix_name

Displayed Name of the suffix for the satellite.

satellite_subject_area_name

Displayed subject area for the satellite.

satellite_comment

Custom comment about the satellite.

staging_table_id

Technical ID of the staging table to stage from. Including Schema.

parent_hub_id

Technical ID of the parent hub. Without Schema.

parent_hub_name

Displayed name of the parent hub.

satellite_name

Displayed name of the satellite.

datavault_category_id

Technical ID of the related datavault category to load (PSA, Raw Vault, Business Vault).

dvb_core.staging_tables

The metadata view lists all staging tables.

Column

Meaning

staging_table_id

Technical ID of the staging table to stage from. Including Schema.

staging_table_name

Displayed name of the staging table.

staging_table_display_string

Displayed name of the staging table including technical ID.

staging_table_comment

Custom comment about the staging table.

staging_table_type_name

Type name of the staging table (e.g. Table or View).

staging_table_type_id

Type id of the staging table (e.g. r for Table or v for View).

schema_id

Technical ID for the schema of the staging table.

schema_name

Displaye name of the schema.

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

source_table_id

Technical ID of the source table / view loading from. Including Schema.

source_name

Displayed name of the source table / view.

source_table_type_id

Type id of the source table (i.e. TABLE or VIEW).

source_object_id

Technical ID of the source object in the source.

source_schema_id

Technical ID for the schema of the source object.

batch_size

Batch size to stage the table with (-1 if not defined).

where_clause_general_part

SQL based where clause, which is added to each staging load, defining a subset from the source which can be loaded like “full”.

where_clause_delta_part_template

SQL based where clause template added to the where clause sent to the source when loading delta.

is_delta_load

Flag, if currently loaded data in the staging table is a delta.

is_up_to_date

Flag, if all Business Keys and Hashes have been calculated for loaded data.

dvb_core.subject_area_name

The metadata view lists all subject areas.

Column

Meaning

subject_area_name

Displayed name of a subject area.

dvb_core.system_connections

The metadata view lists all systems with connection properties.

Column

Meaning

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

source_type_api

Technical ID of the API type to connect through (e.g. jdbc_driver, rest_api).

source_type_id

Technical ID of the source type to connect to (e.g. postgres, mssql).

source_type_url

Technical string used to connect to the source.

source_type_parameters

Parameters to connect to the source. Stored as JSON.

dvb_core.systems

The metadata view lists all user defined systems.

Column

Meaning

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

dvb_core.tables

The metadata view lists all tables in the Datavault Builder schemas.

Column

Meaning

table_id

Technical ID of the table. Including schema.

table_nq_id

Technical ID of the table. Without schema.

schema_id

Technical ID for the schema of the table.

schema_name

Displayed name for the schema of the table.

table_name

Displayed name of the table.

table_comment

Custom comment about the table.

type_id

Type id of the staging table (e.g. r for Table or v for View).

type_name

Type name of the staging table (e.g. Base Table or View).

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

dvb_core.tables_simple

The metadata view lists all tables in the Datavault Builder schemas without display names and comments.

Column

Meaning

table_id

Technical ID of the table. Including schema.

schema_id

Technical ID for the schema of the table.

table_nq_id

Technical ID of the table. Without schema.

type_id

Type id of the staging table (e.g. r for Table or v for View).

type_name

Type name of the staging table (e.g. Base Table or View).

system_id

Technical ID of the source system.

dvb_core.tracking_satellites

The metadata view lists all tracking satellites.

Column

Meaning

table_nq_id

Technical ID of the tracking satellite table. Without Schema.

tracking_satellite_id

Technical ID of the tracking satellite without schema.

tracking_satellite_name

Displayed name of the tracking satellite.

tracked_object_id

Technical ID of the tracked datavault object. Without Schema.

tracked_object_name

Displayed name of the tracked datavault object.

is_tracking_a_link

Flag, if the tracked object is a link.

is_tracking_a_satellite

Flag, if the tracked object is a satellite.

is_delta_load_satellite

Flag, if the tracking satellite is tracking changes of delta loads.

is_full_load_satellite

Flag, if the tracking satellite is tracking changes of full loads.

last_full_load_time

Timestamp of the previous full load of the tracking satellite.

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

tracking_satellite_subject_area_name

Displayed subject area for the tracking satellite.

tracking_satellite_comment

Custom comment about the tracking satellite.

staging_table_id

Technical ID of the staging table to stage from. Including Schema.

parent_hub_id

Technical ID of the parent hub. Without Schema.

parent_hub_name

Displayed name of the parent hub.

satellite_name

Displayed name of the satellite.

datavault_category_id

Technical ID of the related datavault category to load (PSA, Raw Vault, Business Vault).

dvb_core.view_relations

The metadata view lists all view dependencies in the Datavault Builder schemas.

Column

Meaning

table_id

Technical ID of the referenced table/view. Including schema.

table_schema_id

Techncial ID for the schema of the referenced table/view.

table_nq_id

Technical ID of the referenced table/view. Without schema.

table_type_id

Type id of the referenced table/view (e.g. r for Table or v for View).

dependent_view_id

Technical ID of the dependent view. Including schema.

dependent_view_schema_id

Techncial ID for the schema of the dependent view.

dependent_view_nq_id

Technical ID of the dependent view. Without schema.

dependent_view_type_id

Type id of the dependent view (e.g. v for View or m for Materialized View).

dvb_core.views

The metadata view lists all views in the Datavault Builder schemas.

Column

Meaning

view_id

Technical ID of the view.

view_nq_id

Technical ID of the view. Without schema.

schema_id

Technical ID for the schema of the view.

schema_name

Displayed name for the schema of the view.

view_is_materialized

Flag, if the the view is materialized.

view_code

SQL code of the view definition.

metadata_name

Value of name-key in JSON comment of view.

metadata_comment

Value of comment-key in JSON comment of view.

metadata_businessobject_structure

Value of businessobject_structure-key in JSON comment of view.

metadata_quick_inserts

Value of quick_inserts-key in JSON comment of view.

metadata_code

Value of code-key in JSON comment of view.

metadata_is_error_ruleset

Value of is_error_ruleset-key in JSON comment of view.

metadata_include_in_accesslayer

Value of include_in_accesslayer-key in JSON comment of view.

metadata_accesslayer_priorization

Value of accesslayer_priorization-key in JSON comment of view.

dvb_core.x_business_rules_distinct

Helper view to get distinct subset of business ruleset properties.

Column

Meaning

start_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

start_hub_name

Displayed Name of the underlying hub.

functional_suffix_id

Technical suffix of the underlying business object.

functional_suffix_name

Displayed Name of the suffix of the underlying business object.

dvb_core.x_business_rules_system

Helper view to get distinct subset of business ruleset properties.

Column

Meaning

start_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

start_hub_name

Displayed Name of the underlying hub.

functional_suffix_id

Technical suffix of the underlying business object.

functional_suffix_name

Displayed Name of the suffix of the underlying business object.

system_id

Technical ID of the system of the granularity satellite.

system_name

Displayed Name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

related_businessobject_view_id

Technical ID of the underlying business object. Including Schema.

dvb_core.x_businessobjects_distinct

Helper view to get distinct subset of business object properties.

Column

Meaning

start_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

start_hub_name

Displayed Name of the underlying hub.

functional_suffix_id

Technical suffix of the business object.

functional_suffix_name

Displayed Name of the suffix of the business object.

dvb_core.x_businessobjects_system

Helper view to get distinct subset of business objects properties.

Column

Meaning

start_hub_id

Technical non-qualifying ID of the underlying hub. Without Schema.

functional_suffix_id

Technical suffix of the underlying business object.

system_id

Technical ID of the system of the granularity satellite.

system_name

Displayed Name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

dvb_core.x_hubs_distinct

The metadata view to get distinct subset of hub properties.

Column

Meaning

hub_id

Technical ID of the hub. Without Schema.

boid

Technical ID of the hub without hub identifying prefix (h_) and Schema.

hub_name

Displayed name of the hub.

hub_name_of_alias_parent

Displayed name of the parent hub if the hub is an allias.

hub_subject_area_name

Displayed subject area for the hub.

hub_comment

Custom comment about the hub.

dvb_core.x_hubs_system

Metadata view to get distinct subset of hub properties.

Column

Meaning

hub_id

Technical ID of the hub. Without Schema.

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

dvb_core.x_jobs_system

Metadata view to get distinct subset of job properties.

Column

Meaning

system_id

Technical ID of the system the job belongs to.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

dvb_core.x_latest_load_info

The metadata view lists the current load info of all staging and datavault loads which were initiated at least once.

Column

Meaning

entry_time

Initiation time, when the load was initiated.

source_id

Technical ID of the source table / staging table. Including Schema.

target_id

Technical ID of the staging table / datavault object.

start_time

Time, when the last load was started.

end_time

Time, when the last load ended.

duration

Duration of the current load.

state

Current state of the load.

result

Last result of the load.

progress

Number of loaded rows if applicable.

total_rows

Number of rows to load.

username

Displayed name of the initiating user.

job_id

Technical ID of the initiating job if applicable.

pid

Process ID in the core, which initiated the load.

dvb_core.x_satellites_system

Metadata view to get distinct subset of satellite properties.

Column

Meaning

satellite_id

Technical ID of the satellite without schema and technical relevant suffix(es).

boid

Technical ID of the parent hub without hub identifying prefix (h_) and Schema.

system_id

Technical ID of the source system.

system_name

Displayed name of the system.

system_color

Displayed color of the system.

system_comment

Custom comment about the system.

functional_suffix_id

Technical suffix of the satellite.

functional_suffix_name

Displayed Name of the suffix for the satellite.

parent_hub_id

Technical ID of the parent hub. Without Schema.

parent_hub_name

Displayed name of the parent hub.

dvb_core.y_blocking_procs

Helper view to debug errors related with blocking procedures.

Column

Meaning

blocking_pid

Process ID of the blocking query.

blocking_user

User ID of the blocking query.

blocking_query

SQL query which is preventing the execution.

blocked_pid

Process ID of the blocked query.

blocked_user

User ID of the blocked query.

blocked_query

SQL query which is prevented from execution.