Documentation

Documentation
Setup
API
Change log
Downloads

Dashboard

Overview

Dashboard gives an overview of the DQM. In here you can find numerous graphs and information about test results, statuses, schedules and logs. Graph content is based on test suite selection. There are also options to pick public test suites, all test suites or your own private test suites.

Connections

Overview

Connections page is the control centre for all databases that are connected to the DQM.

  • add new connections (add new connection by clicking “Add new connection” button)
  • manage existing connections (change settings by clicking on edit icon)
  • view and manage metadata for a connection (open metadata window by clicking on metadata icon)
  • check connection status (update status by clicking on status icon)

Adding a new connection

Only possible with ADD/EDIT CONNECTIONS privileges.

  1. On Connections page click “Add new connection” button:
  2. Enter information about database server:
  • Driver/Connector – database JDBC driver (see chapter Connector settings to add new drivers)
  • Connection name – custom name for the connection
  • Server – database server address
  • Port – database port
  • Database name – database name in server
  • Properties – additional connection properties
  • User – database user (recommended to use user with only SELECT privileges)
  • Password – password for user
  • Max threads – maximum parallel connections to database
  • Max timeout – maximum number of seconds connection will wait for response from the database/query timeout
  1. Click “Add new connection” button
  2. New connection will appear on the list and connection status will show if connection was successful (to use database’s metadata for test creation see chapter Metadata)

Example Spark SQL/Databricks connection

Make sure User is inserted as a “token” and the generated token is inserted as a password. 
Spark java drivers: https://databricks.com/spark/odbc-driver-download 

Guide on how to create tokens in Databricks:
https://docs.databricks.com/dev-tools/api/latest/authentication.html#token-management

Example Redshift connection

Example Snowflake connection

Deleting an existing connection

  1. Click “edit” icon for the connection you would like to delete
  1. From opened properties window click “Delete connection” button

Metadata

Metadata is used to automatically create test cases, generate SQLs and manage Profiling. Metadata can be added by clicking metadata button for a specific connection: 

  • Select schemas or insert them manually (comma separated):
  • Click “+” button to import schemas
  • Metadata should now be available

Metadata is automatically updated every 120 minutes (can be changed in Settings -> General) to be in sync with available objects and columns in the database. Metadata can also be manually updated or deleted by selecting schemas and clicking “Update selected” or “Delete selected” button:

Rules

Overview

On the Rules page it is possible to set SQL generation rules, which can be used for Test generation (see chapter Test generation). Users can define their custom SQL generation rules based on their needs. Some default rules are defined on initial setup, but should be modified to include added connectors/drivers (see chapter Adding/editing a rule)

  • Editing a rule – edit existing rule for test generation (click on row to open rule)
  • Add a new rule – add a new dynamic rule for automatic test generation (click on “Add new rule” button)
  • Parameters – used in rule generation. Users can add new parameters or use existing default parameters (see chapter Parameters)

Adding/editing a rule

  1. Click on “Add new rule” button
  2. Fill in rule properties
  • Test name – this is the description which is added to all tests generated by this rule. <parameter> will be replaced with custom parameters or metadata from the database. For example: “myschema.mytable – Full duplicates
  • Rule description – explanation for the rule
  • SQL – SQL syntax with <parameters>.
  • Expected result – expected numeric or string value from the query (can be empty).
    • greater than – >0
    • lesser than – <1000000
    • between – >0&<1000000
  • Drivers/connectors – JDBC drivers which this rule applies to
  1. Press “Add new rule” button

Example rules

These are some dynamic rules that use database metadata as parameters. 

Check if object has any rows inserted

Test name: <schema>.<object> – Count of rows
Rule description: Check if object has any rows inserted
Expected result: >0
SQL: 

SELECT COUNT(*) FROM <schema>.<object> 

Check if object has any duplicate rows

Test name: <schema>.<object> – Duplicates
Rule description: Check if object has any duplicate rows
Expected result: =0
SQL: 

SELECT COUNT(*) FROM (SELECT <columns> FROM <schema>.<object> GROUP BY <columns> HAVING COUNT(*)>1) a

Check if object has any columns that are NULL in all rows

Test name: <schema>.<object> – Duplicates
Rule description: Check if object has any columns that are NULL in all rows
Expected result: empty string – leave empty
SQL: 

SELECT ” <forEachColumn> || CASE WHEN COUNT(<column_name>)=0 THEN ‘<column_name>;’ ELSE ” END  </forEachColumn> FROM <schema>.<object>

Note: this rule might not work for all database engines because of differences in syntax (confirmed to work for Postgres, Vertica, Redshift).

Deleting existing rules

  1. Find the rule you want to delete and click on it
  1. Click “Delete rule” button

Parameters

Parameters are part of rules that are replaced by database metadata or user input to dynamically generate test cases. Default parameters are replaced by database metadata:

  • <schema> – replaced with database schema name
  • <object> – replaced with table or view name
  • <columns> – replaced with all (or selected) column names separated by commas. Column names are wrapped based on database syntax:
    • postgres, oracle, sap iq – “column”
    • MS SQL server – [column]
    • MySQL, MariaDB – `column`
  • <pk_columns> – replaced by primary key columns (comma separated). If primary keys are not found then the test is not generated.
  • <forEachColumn> </forEachColumn> – content between these two parameters are repeated for all (or selected) columns. It is also possible to use parameters <column_name> and <column_data_type> in the content.

Add custom parameters for rules

  1. Insert new parameter name and click on “+”
  1. New parameter will be displayed in the list of parameters
  2. This parameter can now be used in rule creation and test generation
  3. Parameters can be deleted from the list by clicking on X

Test suites

Overview

Test suites page allows users to create and manage test suites, which are used to package test cases.

  • Filter between private, public, all or find specific test suites
  • See test suite schedule for the next 24 hours
  • Execute – execute the test suite
  • Report – download HTML report with latest results for the test suite

Adding a new test suite

  1. Press the “Add new test suite” button on the sidebar
  2. Fill in the test suite properties
  • Test suite name – display name for test suite
  • Active – test suite is added to archive if set as not active
  • Public – check if test suite should be visible and executable by all users
  • Tags – see chapter Adding tags to a test suite
  • Schedule – schedule when the test suite should be executed.
  • Allowed errors – % value of errors allowed. When the test failure percentage is larger than the allowed threshold, an alert will be sent (0% to send an alert if any of the tests fails)
  • Webhook – Webhook to which an alert is sent if error threshold is exceeded
  • Emails – comma separated emails to which report is sent if error threshold is exceeded
  1. Click on “Add new test suite” button

Deleting a test suite

  1. Find the test suite you want to delete and click “edit” icon
  1. Press the “Delete test suite” button
  2. When prompted with following message
  • Delete only test suite – only test suite will be deleted. All tests will remain in DQM without a test suite reference
  • Delete with test cases – all tests that are in the test suite will also be deleted

Modifying a test suite

  1. Find the test suite and click “edit”
  2. Modify properties that need to be changed
  3. Press “Save changes” button

Adding tags to a test suite

  1. Locate the test suite where you want to change tags and click “edit”
  2. Insert or delete tags from “Tags” field

Note For different colors you can also use red:, blue:, green:, black: (e.g. “blue:tagName”)

  1. Press “Save changes” button

Archived test suites

It is possible to see archived test suites by clicking the “View archive” button on the sidebar. Archived test suites can be made active by changing its state to Active when editing a test suite (see chapter Adding a new test suite – Active).

Executing a test suite

  1. Find the test suite you want to execute
  2. Press the execute button

Downloading HTML test suite report

  1. Locate the test suite 
  2. Press the report icon

Opening a test suite

  1. Press on the name of the test suite you wish to open
  1. You will be redirected to tests view

Profiling

Overview

Profiling page allows users to set profiling rules for selected objects and columns. This allows the DQM to analyze data based on profiling rule execution results and report results to find possible anomalies.

Adding a new object to profile

  1. Press “Add objects” button on the sidebar
  2. Filter out objects you wish to profile
  1. Choose an object to profile by pressing “+” button or checking checkboxes and clicking “Insert selected” button
  2. New profiling object will now be shown on the page

Note: If “Add columns” is set as enabled, then all columns with rules will be generated automatically when adding an object.

Modifying profiling object

Opening profiling object allows to:

  • Schedule rules to run
  • Set alerts for scheduled executions
  • Add SQL filters to profiled object
  • Execute rules manually
  • Add/remove columns to profile
  • Add/remove rules for a column
  • Delete object/remove profiling for an object

Deleting a profiling object 

  1. Open profiling object and click “Delete object” button
  1. Select object from list by clicking “Add objects” button and click “-” icon

Executing profiling rules

  1. Open profiling object
  1. To execute all column rules for the object, click “Execute all rules” button
  1. To execute a specific rule for a column, press “refresh” icon

Note: If Scheduling is enabled, all object rules will be executed based on schedule

Manage profiling rules

Profiling rules can be added/removed/changed by clicking on “Manage rules” button from sidebar. Each rule can be assigned for specific database drivers/connectors and data types. A rule must have a unique name.

A rule can be assigned to all applicable columns by clicking the “Actions” button and then selecting “Assign” for the selected rule. The option “Clear” will remove rule from all columns that are no longer applicable by data type or driver/connector.

Manage data types

Data types for rules can be added/removed/changed by clicking on “Manage data types” button from sidebar. Rule types categorize which rule can be created/assigned for a column based on its data type. Data types with parentheses must be inserted without the parentheses (e.g. VARCHAR(150) as VARCHAR ).

Test cases

Overview

From Test cases page you can:

  1. Connection filter – filter by databases
  2. Test suite filter – filter by test suites
  3. Status filter – filter by status
  4. Description/SQL – search by test SQL or description values
  5. Tags – search by test suite tags
  6. Save tests – save changed tests
  7. Execute – execute selected tests
  8. Delete – delete selected tests
  9. More options 
    1. Import tests – import tests from exported JSON file
    2. Export selected as file – export selected tests as JSON file
    3. Export selected to clipboard – export selected tests to clipboard in JSON format
    4. Duplicate tests – duplicate selected tests
    5. Assign  – assign new test suite to selected tests
    6. Set threshold – set prediction threshold to selected tests
  10. Test generation – generate test cases(see chapter Object based test generation)
  11. Add new test case – manually add new test case row
  12. Settings 
    1. Query timeout – maximum number of seconds query is allowed to run before timeout. This will be overwritten if connection allowed query timeout is smaller
    2. Parallel queries – maximum number of parallel queries allowed to run per execution. This will be overwritten if connection allowed parallel queries are smaller
    3. Rows displayed – maximum number of rows displayed on the page. Mostly for loading performance
  13. Save settings – save modified settings

Adding new test

  1. Click “Add new test case” button on the sidebar
  2. Modify test properties
  • Test type – query or compare. For a comparison test see chapter Compare type test case
  • Description – test description
    • Extra info icon – additional info about the test case displayed in test report if test fails
  • Expected – expected result – either numeric or string value
  • Test suite – test suite where test will be added
  • Save – save this test
  • Execute – execute this test
  • Generate SQL – generate SQL automatically (see chapter Generating SQL for a single test case)
  • SQL – test execution SQL
  • Target – test target database connection

Display test results

It is possible to see test results (for tests that have failed and use COUNT to get results) by clicking on “Show test results” button in test history. The query will be executed (COUNT will be replaced with column names or *) and results are displayed. By default maximum of 500 rows are displayed (to change this value see chapter General settings – Failed test query results displayed)

Compare type test case

It is possible to compare data from different data sources by comparing SQL results. You can either compare numeric values (SUMs, COUNTs, AVGs etc), Strings or data row by row/column by column. If comparing datasets, then a sample of missing data is displayed if the Compare results setting is checked (see chapter General settings – Save compare results). Please note that comparing large data sets (millions of rows) is limited by Java memory.

Click “Show compare results” to view differences in data after execution. It is possible to see differences in each column by clicking on a row from either side:

Test result threshold

If a test expects a numeric value, then it is possible to set a percentage threshold to find deviations in data. If the threshold is exceeded then test status is set as “Error”. Erroneous results can be removed from history by ticking them and clicking:

Modifying a test case

  1. Open the test case by clicking on it in tests list
  2. Modify test properties (see chapter Adding new test)
  3. Click save button

Deleting test cases

  1. Select test cases (you can use shift+left click to select multiple)
  1. Click “Delete” button on the sidebar

Test generation

Object based test generation

Object based test generation allows you to create multiple tests for multiple objects

  1. Click on “Test generation” on the sidebar
  2. Type will be “Object based”
  1. Filter out objects for test generation
  • Connection(s) – database connections filter
  • Schema – database schema filter
  • Object – table or view name (filters as a wildcard)
  • Column(s) – search for objects that have certain column(s). This input value also replaces the <column> parameter (see chapter Parameters)
  1. Select tests you want to generate for objects
  1. Generate selected tests
  • Test suite – test suite name, where tests are generated into
  • Allow duplicate test cases – if duplicated tests are allowed in test suite
  • Generate selected – generate checked tests

Dynamic test generation

Dynamic test generation allows you to create multiple tests for specific components

  1. Click on “Test generation” button on the sidebar
  2. Set type to “Dynamic”
  1. Modify generation properties
  • Connection – target connection for test cases
  • Parameters – custom parameters used in selected tests (for custom parameters see Add custom parameters for rules)
    • Inserting multiple parameters will generate multiple test cases
  • Test suite – where tests are generated into
  1. Click “Generate selected”

Generating SQL for a single test case

  1. Open your test case by clicking on it
  2. Press “Generate SQL”
  1. Modify test generation properties
  • Connection – database filter
  • Parameters – parameters that are replaced in selected SQL (see chapter Add custom parameters for rules)
  • SQL – Automatically generated SQL 
  • Test selection – which type of test is generated
  • SQL auto-update – Check if you want your test case to be automatically updated in case of metadata changes (only if all columns are used)
  • Generate SQL – generate SQL with selected options

Settings

General settings

Note: This page is only accessible by users with ADMIN privileges.

From General settings page you can see:

  • Clear memory – manually clear used memory
  • Resource usage graph 
    1. red – current used memory
    2. orange – memory occupied by Java
    3. green – total usable memory
    4. black – CPU usage
  • Tool restart button – to restart DQM if needed
  • Enable guest view – if this is enabled, then guests can view dashboard without logging in
  • Exclude tests with status ‘error’ when sending alerts – Test cases with database error results are excluded when calculating test suite results to send alerts
  • Metadata update (minutes) – interval of automatic metadata updates for added connections
  • Parallel executions for scheduled test suites – Maximum number of scheduled test suites executed in parallel (recommended 2)
  • Compare rows limit – maximum amount of database rows to be queried in compare test cases. This limit can be set to ensure that the DQM won’t run out of memory
  • Compared differences displayed – maximum number of rows displayed in compare results (set to 0 to not save any results on local database)
  • Failed test query results displayed – Maximum number of rows displayed for failed test (recommended 500)
  • HTML test suite report row limit in email content – Maximum number of detailed test rows included in email content (recommended 100)

License settings

Note: This page is only accessible by users with ADMIN privileges.

From License settings page you can see:

  • Licensed to – company name that owns the license
  • Valid until – license expiry date
  • Maximum connections – amount of connections allowed with current license
  • Maximum users – amount of users that can be created with current license
  • License hash

Authentication settings

LDAP/Active Directory

Note: This page is only accessible by users with ADMIN privileges.

From LDAP  settings page you can see:

  1. Type – LDAP or Active Directory
  2. Create user on first log in – automatically create user with role “User” if not registered on first log in
  3. Server – server url
  4. Root DN – LDAP root DN. If your group name has whitespaces then wrap it between quotes (e. g. OU=”group name”)
  5. Search by – LDAP filter to search for user. For example uid or sAMAccountName
  6. Domain – Active Directory domain

Note: certification file must be added when using ldaps (ldap over TLS/SSL).

OAuth2

Note: Client endpoint is not needed when using OpenID

Connector settings

Note: This page is only accessible by users with ADMIN privileges.

Users

Note: Only users with ADMIN privileges will be able to see all users. Regular user will only see own account

Adding a new role

Roles can be used to allow users to only access specific database connections, grant privileges to add/edit connections, rules, profiling objects or test suites. Default role for a new user is “User” which has access to all databases, can add/edit rules, test suites, profiling rules and objects. “User” can not add/edit connections.

Adding a new user

A new user must have a username and at least one role selected. For LDAP/AD users the password field can be empty. If a password is set for LDAP/AD users then such users can access the tool with both LDAP/AD password and locally set password.

Alerts settings

Note: This page is only accessible by users with ADMIN privileges.

From Alerts settings page you can see:

Email settings:

  • SMTP host – host of your email server
  • SMTP port – port of your email server
  • Email – email that will send out reports
  • Password lock – click if you wish to edit password
  • Password – password for email if authentication is needed

Slack settings:

  • Name – custom name for a Slack channel
  • Slack webhook – Slack webhook address

Database settings

Note: This page is only accessible by users with ADMIN privileges.

From Database settings page you can see:

  1. Reset database – format DQM local database
  2. Create a backup – create a backup of the current state of the DQM’s local database
  3. Export database – download selected database file
  4. Import database – upload database file to list
  5. Confirmation – required for restoring or deleting a backup
  6. Restore database – restore database from selected backup
  7. Delete backup – delete selected backup