Comparing CSV file with RDBMS Table

You are viewing archived documentation for Data Trust v2024.06 (previous version).
Go to the latest v7.6 documentation →

Business rule validation of RDBMS table

Using validation scenario users can validate all right data supported data sources against the pre-delivered set of validation rules or custom business rules which can be created using the right data’s rule builder. Let’s execute a Validation scenario using a business rule using RDBMS table as a data source.

To learn more, let us walk through the below procedure:

• Click the SCENARIO STUDIO module.
• Hover on the ‘Scenario Builder’ snippet from the left pane options.
• Click the ‘Validation’ option.

Note: Either clicking the ‘Validation’ option or the ‘Create Validation Scenario’ button opens a new Validation scenario session page.

Path 01: SCENARIO STUDIO > Scenario Builder > Validation

Path 02: SCENARIO STUDIO > Create Validation Scenario

• A new Validation scenario session page is displayed.
• Under the Data Source tab, a couple of Data Sources such as Queries, RD Database, Custom SQL, RDBMS Table, Tableau, Qlik, Sales Force, CSV, REST API, SAP Table, SAP infoprovider, Gem Fire, PowerBI, and SSRS are available.

• Let’s, drag and drop an ‘RDBMS Table’ widget from the Data Source tab.

Note: All the available Data Source icons including the ‘RDBMS Table’ get disabled on dragging any icon from the ‘Data Source’ tab.

• Click the RDBMS Table widget.

• A ‘Read data from Table’ pop-up window is displayed.
• A ‘Database Type’ and ‘Connection Name’ drop-down lists are displayed.
• The users can ‘Pick Table’ through a ‘Navigator’ button option.

Note: By default, the ‘Pick Table’ field option is disabled, but when the user selects the ‘Database Type’ and ‘Connection Name’ from the drop-down list, the ‘Pick Table’ field option is enabled.

• Let’s select Database Type as ‘PostgreSQL’ from the drop-down list.
• Select Connection Name as ‘PostgreSQL Northwind_QA’ from the drop-down list.
• Click the Navigator button.

• A green tick mark appears beside the ‘PostgreSQL Northwind_QA’ indicating the ‘Connection Name’ selection is successful.
• An ‘Additional Settings’ button is displayed beside the green tick mark.
• Click the ‘Additional Settings’ button.

• An ‘Additional Settings’ pop-up window is displayed with different text field options such as Parallelism, Packet Size, and Packetize By.

Note: By default, Parallelism is set to ‘1’ and Packet Size to ‘50000’.

• A ‘Reset to Default’ button is available if the user wants to reset ‘Parallelism’ and ‘Packet Size’ to default values.
• When hovered on the ‘info’ button on the ‘Additional Settings’ the user is assisted with information.
• Click the ‘Save’ button to save the ‘Additional Settings’.
• The moment the settings are saved the ‘Additional Settings’ pop-up window is closed and the user is navigated back to the ‘Read data from Table’ window.

• Click the Navigator button to display a ‘Navigator’ column on the right side.
• Let’s click the ‘order_details’ from the ‘public’ schema.

Path: Public > Tables > order_details

• ‘Public.order_details’ is added to the Pick Table.

Note: By default, all the column names are selected

• Click the Preview Data button to preview the selected data.

• An ‘Existing Query’ pop-up window is displayed showing different columns namely orderid, productid, unitprice, quantity, and discount.
• Click the ‘X’ button to close the pop-up window.

• The ‘Read data from Table’ session pop-up window is displayed.
• Click the ‘Select’ button.

• The RDBMS Table widget is collapsed saving the data.
• The user is navigated to the Validation session page in create and edit mode.
• Click the Transformations tab.

• Under the Transformations tab, three transformation icons are displayed: Projection, Aggregation, and Filters.

• Let’s, drag, and drop Filters widget from the Transformations tab.
• Click the Filters widget.

• A ‘Filter data from the public.order_details’ pop-up window is displayed.
• Under the ‘Filter’ section, different options such as Available Fields, Available Operators, input, (,), and Variable are displayed.

• Now drag Available Fields, Available Operators, and input.
• Select ‘orderid’ from the Available Fields drop-down list.
• Select ‘Equals to’ from the ‘Available Operators’ drop-down list.
• Under the input box, add the ‘10248’ number.

Note: The user can see the complete condition as an Expression under the Expression section.

• Click the ‘Save’ button to save the Filters widget.

• The Filters widget is collapsed saving the data.
• The user is navigated to the Validation session page in create and edit mode.
• Click the ‘Validation Rules’ tab.

• The Validation Rules tab has the following Validation conditions as Duplicates, Reference Data, Range Check, Length Check, Presence Check, Pattern Check, Lowercase, Uppercase, Positive, Negative, Special Character, Custom Business Rule, Data Format and Dataset Count.

• Let’s drag and drop the ‘Duplicates’ icon from the Validation Rules tab.
• Click the ‘Duplicate Check’ widget.

• A ‘Duplicate Check’ pop-up window is displayed.
• A Rule Description field box and Ignore Nulls checkbox is displayed.
• Under Available Fields, the user can see the columns such as orderid, productid, unitprice, quantity, and discount.
• A ‘Fields Description’ checkbox and a ‘Search’ box are displayed on the right-hand side.

• Assume, the user selected ‘unitprice’ to be unique.

Note: As the user clicks the ‘unitprice’ it is highlighted in green color.

Note: When users click the ‘unitprice’ field again, its Duplicate condition gets deselected and the highlighted green color disappears. Simultaneously, multiple Available Fields can be selected at a time.

• Under the ‘Rule Description’ field, let’s add ‘unitprice unique’.

Note: As the user clicks the ‘unitprice’ a ‘Business Rule’ (Following field ‘unitprice’ should not be duplicated) is added accordingly.

• Click the ‘Save’ button to save the ‘Duplicate Check’ condition widget.

• The user is navigated to the Validation session in create and edit mode.
• Click the ‘Additional Options’ tab.

• Various icons such as Email, HPALM, IMDB, Jira, XRAY, and Azure Dev Ops Board are displayed.

• From the ‘Additional Options’ tab, drag and drop an ‘Email’ icon.

Note: Email icon gets disabled as the user drags the ‘Email ‘icon onto the Validation scenario in create and edit page

• Click the ‘Email Settings’ widget.

• An ‘Email Settings’ pop-up window is displayed and has the following checkbox options.

1. Email when Found Exception
2. Email when no exceptions are found
3. Enable External Scenario Exceptions Link
4. Include download link to the exceptions data file
5. Attach execution summary result PDF file

• Apart from these checkbox options, other text field options such as Email Subject with Show Variables button, Email From, Email Priority, and Email Template are displayed.

• Let’s click the ‘Email when found Exception’ checkbox and add an email address.
• Click the Save button.

Note: Email is greyed due to confidential data.

• An ‘Email Settings’ widget is collapsed saving the widget data and the user is navigated to validation session in create and edit mode.
• Other buttons such as Create New, Execute Now, Return to Explorer, Enable external link for this scenario, Duplicate Validation Scenario, and Where Used List are displayed beside the ‘Additional Options’ tab.
• Click the Execute Now button.

• The validation scenario is successfully executed displaying a green tick mark status.

Note: The monitor mode is enabled immediately after the execution process.

• Click the ‘Duplicate Check’ widget count.

• The user is navigated to the ‘Validations Scenario Exceptions’ session page.
• Under the ‘Validation Execution Summary’ tab the user notices a green tick mark for the Duplicate Check condition (unitprice unique), Total Number of Exceptions : 0, and Percentage of Exceptions : 0.
• A download button is available to download the ‘Validation Scenario Exceptions’ in a CSV format.