Validate Relational Datamodel - Revision history

Ollvihe at 12:24, 27 March 2026

2026-03-27T12:24:37Z

← Older revision		Revision as of 12:24, 27 March 2026
Line 2:		Line 2:

	The validation includes the following checks:		The validation includes the following checks:
	- Primary key columns must not contain null values.		* Primary key columns must not contain null values.
	- Each row associated with the primary key columns must be unique.		* Each row associated with the primary key columns must be unique.
	- For foreign keys, the target table must have a corresponding row that matches the reference from the source table's foreign key.		* For foreign keys, the target table must have a corresponding row that matches the reference from the source table's foreign key.

	~~Create~~ a new expression script to the project and run the script. When the script run is completed, open the script run log. There is either is message that the data model is valid, or there is a list of found validation errors.		To use this script, create a new expression script to the project and run the script. When the script run is completed, open the script run log. There is either is message that the data model is valid, or there is a list of found validation errors.

	<syntaxhighlight lang="typescript" line>		<syntaxhighlight lang="typescript" line>

Ollvihe at 11:52, 15 January 2026

2026-01-15T11:52:38Z

← Older revision		Revision as of 11:52, 15 January 2026
Line 115:		Line 115:
	</syntaxhighlight>		</syntaxhighlight>

	This script prints a report of the datatable's primary and ~~foreigh~~ keys in the project to the script log:		This script prints a report of the datatable's primary and foreign keys in the project to the script log:

	<syntaxhighlight lang="typescript" line>		<syntaxhighlight lang="typescript" line>

Ollvihe at 11:51, 15 January 2026

2026-01-15T11:51:59Z

@@ Line 113: / Line 113: @@
    WriteLog("Data model is valid.");
+}
 </syntaxhighlight>

Ollvihe at 09:50, 15 January 2026

2026-01-15T09:50:33Z

← Older revision		Revision as of 09:50, 15 January 2026
Line 52:		Line 52:
	function getInvalidRowsForForeignKey(datatable, foreignKey) {		function getInvalidRowsForForeignKey(datatable, foreignKey) {
	let targetDatatableName = foreignKey.TargetDatatable;		let targetDatatableName = foreignKey.TargetDatatable;
	if (targetDatatableName ~~== null \|\| targetDatatableName == _EMPTY~~ \|\| targetDatatableName == "") {		if (IsNull(targetDatatableName) \|\| targetDatatableName == "") {
	errorsOccurred = true;		errorsOccurred = true;
	WriteLog("In datatable \"" + datatable.Name + "\" target table is incorrectly empty in foreign key \"" + ToJson(foreignKey) + ".");		WriteLog("In datatable \"" + datatable.Name + "\" target table is incorrectly empty in foreign key \"" + ToJson(foreignKey) + ".");

Ollvihe at 09:30, 15 January 2026

2026-01-15T09:30:19Z

← Older revision		Revision as of 09:30, 15 January 2026
Line 51:		Line 51:

	function getInvalidRowsForForeignKey(datatable, foreignKey) {		function getInvalidRowsForForeignKey(datatable, foreignKey) {
			let targetDatatableName = foreignKey.TargetDatatable;
			if (targetDatatableName == null \|\| targetDatatableName == _EMPTY \|\| targetDatatableName == "") {
			errorsOccurred = true;
			WriteLog("In datatable \"" + datatable.Name + "\" target table is incorrectly empty in foreign key \"" + ToJson(foreignKey) + ".");
			return [];
			}

			let targetDatatable = Project.DatatableByName(targetDatatableName);
			if (IsNull(targetDatatable)) {
			errorsOccurred = true;
			WriteLog("In datatable \"" + datatable.Name + "\" foreign key target datatable \"" + targetDatatableName + "\" is not found.");
			return [];
			}

	let TARGET_COLUMN_PREFIX = "Target";		let TARGET_COLUMN_PREFIX = "Target";
	let foreignKeyColumns = foreignKey.Columns;		let foreignKeyColumns = foreignKey.Columns;
	let targetDataFrame = ~~Project.DatatableByName(foreignKey.TargetDatatable)~~		let targetDataFrame = targetDatatable
	.SqlDataFrame		.SqlDataFrame
	.Select(foreignKeyColumns.{ let col = _;[(TARGET_COLUMN_PREFIX + col.Target): col.Target] }[0]);		.Select(foreignKeyColumns.{ let col = _;[(TARGET_COLUMN_PREFIX + col.Target): col.Target] }[0]);

Ollvihe at 13:59, 11 December 2025

2025-12-11T13:59:30Z

← Older revision		Revision as of 13:59, 11 December 2025
Line 5:		Line 5:
	- Each row associated with the primary key columns must be unique.		- Each row associated with the primary key columns must be unique.
	- For foreign keys, the target table must have a corresponding row that matches the reference from the source table's foreign key.		- For foreign keys, the target table must have a corresponding row that matches the reference from the source table's foreign key.

			Create a new expression script to the project and run the script. When the script run is completed, open the script run log. There is either is message that the data model is valid, or there is a list of found validation errors.

	<syntaxhighlight lang="typescript" line>		<syntaxhighlight lang="typescript" line>

Ollvihe at 13:57, 11 December 2025

2025-12-11T13:57:40Z

← Older revision		Revision as of 13:57, 11 December 2025
Line 1:		Line 1:
	This page ~~contains~~ a script ~~that validates~~ a relational model. The script checks all datatables in the project where the script is run.		This page features a script designed to validate a relational model stored in QPR ProcessAnalyzer datatables. The script checks all datatables in the project where the script is run.

	The following ~~validation~~ checks ~~are performed~~:		The validation includes the following checks:
	* Primary key columns ~~should~~ not contain null values.		- Primary key columns must not contain null values.
	* Rows should be unique for the primary key columns.		- Each row associated with the primary key columns must be unique.
	* Foreign keys ~~refer to existing data, i.e.~~, the target table ~~should contain~~ a row the source table foreign key ~~is referring to~~.		- For foreign keys, the target table must have a corresponding row that matches the reference from the source table's foreign key.

	<syntaxhighlight lang="typescript" line>		<syntaxhighlight lang="typescript" line>

Ollvihe at 13:14, 11 December 2025

2025-12-11T13:14:48Z

← Older revision		Revision as of 13:14, 11 December 2025
Line 81:		Line 81:
	if (Count(duplicatePrimaryKeys) > 0) {		if (Count(duplicatePrimaryKeys) > 0) {
	errorsOccurred = true;		errorsOccurred = true;
	WriteLog("Datatable \"" + datatable.Name + "\" has following duplicate rows for the primary key ~~columns~~: " + StringJoin(", ", duplicatePrimaryKeys) + (Count(duplicatePrimaryKeys) >= MAX_ROWS_TO_REPORT ? " (+ more)": ""));		WriteLog("Datatable \"" + datatable.Name + "\" has following duplicate rows for the primary key: " + StringJoin(", ", duplicatePrimaryKeys) + (Count(duplicatePrimaryKeys) >= MAX_ROWS_TO_REPORT ? " (+ more)": ""));
	}		}

Ollvihe at 13:13, 11 December 2025

2025-12-11T13:13:34Z

← Older revision		Revision as of 13:13, 11 December 2025
Line 81:		Line 81:
	if (Count(duplicatePrimaryKeys) > 0) {		if (Count(duplicatePrimaryKeys) > 0) {
	errorsOccurred = true;		errorsOccurred = true;
	WriteLog("Datatable \"" + datatable.Name + "\" has following duplicate primary ~~keys~~: " + StringJoin(", ", duplicatePrimaryKeys) + (Count(duplicatePrimaryKeys) >= MAX_ROWS_TO_REPORT ? " (+ more)": ""));		WriteLog("Datatable \"" + datatable.Name + "\" has following duplicate rows for the primary key columns: " + StringJoin(", ", duplicatePrimaryKeys) + (Count(duplicatePrimaryKeys) >= MAX_ROWS_TO_REPORT ? " (+ more)": ""));
	}		}

Ollvihe at 13:09, 11 December 2025

2025-12-11T13:09:53Z

← Older revision		Revision as of 13:09, 11 December 2025
Line 81:		Line 81:
	if (Count(duplicatePrimaryKeys) > 0) {		if (Count(duplicatePrimaryKeys) > 0) {
	errorsOccurred = true;		errorsOccurred = true;
	WriteLog("Datatable \"" + datatable.Name + "\" has following duplicate primary keys: " + StringJoin(", ", duplicatePrimaryKeys) + (Count(duplicatePrimaryKeys) > MAX_ROWS_TO_REPORT ? " (+ more)": ""));		WriteLog("Datatable \"" + datatable.Name + "\" has following duplicate primary keys: " + StringJoin(", ", duplicatePrimaryKeys) + (Count(duplicatePrimaryKeys) >= MAX_ROWS_TO_REPORT ? " (+ more)": ""));
	}		}

Line 89:		Line 89:
	if (Count(invalidForeignKeyRows) > 0) {		if (Count(invalidForeignKeyRows) > 0) {
	errorsOccurred = true;		errorsOccurred = true;
	WriteLog("Datatable \"" + datatable.Name + "\" has in the foreign key " + ToJson(foreignKeys[index]) + " following invalid rows: " + StringJoin(", ", invalidForeignKeyRows) + (Count(invalidForeignKeyRows) > MAX_ROWS_TO_REPORT ? " (+ more)": ""));		WriteLog("Datatable \"" + datatable.Name + "\" has in the foreign key " + ToJson(foreignKeys[index]) + " following invalid rows: " + StringJoin(", ", invalidForeignKeyRows) + (Count(invalidForeignKeyRows) >= MAX_ROWS_TO_REPORT ? " (+ more)": ""));
	}		}
	}		}