Data conformity, uniqueness, structure and relevance are every day challenges users experience with data. From knowing if data is kept in the right pattern (like Email or Cell phone number) to making sure that an insured customer data is kept with relevant and correct details (for example a customer cannot be listed owning car insurance policy if he is under the driving age according to the record date of birth).
Financial and regulation rules can be enforced and documented, manual excel file validation can be automated and save a lot of time and effort.
Data Validation test wizard is the powerful wizard that enables any user to define critical business rules or technical rules on any Data Source in a user friendly builder, no code is necessary.
Connect your raw data from any data base, file or any Data Source from our supported list and apply any kind of rule on the data to make sure your data applies to critical rules. You will be alerted when any violation occurs, failed data details will be accessible for you to investigate and fix the issue.
Global rules can be applied by sharing your data rule to the library and re-using them on any relevant Data Source.
If our library of functions did not contain what you need, you can also develop your own custom function to be added to the library by using our Java Script rule builder.
How to create a Data Validation
Step 1 - Open a new test
Enter My Projects in the left sidebar and choose the project in which you want to perform the test.
After choosing the project, click on the test option and Add Test. Name the test, give it a short description, define its level of severity and assign the relevant tags.
Tags: the tag option is here to facilitate navigation and categorize search in the platform. For example, giving the tag Sales will later enable you to find all the tests performed referring to Sales data in a single click through the search option (choosing the search icon in the left sidebar).
Step 2 - Connect your Data Set
- A Data Set may be private for a single use or shared to the library to be reused and managed.
- If you already have a Shared Data Set it should appear in the sidebar menu on the left.
- In order to create a new Private Data Set for your test in the data set section click Add New.
- Click on the + and assign to your Data Set name, short description, its Data Source (Sample Data included in the software) and relevant tags. You also can share it to the library by clicking Add to shared library on the right.
- A table now appears on your right, select the columns you want to have in your data set, change data types if needed, Validate query and save.
Step 3 - Create your test flow
- Select the desired source (Customers DWH), they will automatically appear on the right canvas. Feel free to drag the boxes around this flow or to check a sample of the data set with the Sample Data button in the box.
A new tab will open following a new action.
Back to the left flow, click on the Rules option and on the Data validation test: 2 new boxes will appear on the canvas, the test and its result.
Step 4 - Customize your test
Now that the boxes are connected, create your test.
- Click on the upper right circle in the Data Validation box opening 2 new options: Delete (trash) and Edit (pencil) ; choose Edit.
You have 3 options to build a rule:
Two basic rules can be applied upon the columns table – Unique key enforcement by clicking the key icon or Not null enforcement by clicking the check box.
If you click on Add Rule you will be able to build a simple formula that will return true or false. The formula can be build by combining Data Set columns, Application library of functions, variables and operands.
On Add Rule you can also create a calculated column (A) that will be used in the formula (B) and will be kept in the results along with the existing columns. A calculated column will be created and will be available in the formula section under columns. In the formula we’ll define what is the condition for the calculations we did in the first step.
Step 5 - Analyze results
- You can define for each rule a threshold, relevant statuses (green - success, yellow - warning, red - fail) and ranges.
- All The rules that you defined will appear at the bottom.
Click Finish when you are done creating the rules.
When you are back to the canvas flow, Run the test you just created to get test results.
Results table will appear at the bottom of the canvas.
Step 5 - Create your own functions
You can build new functions which will be displayed in the functions list in Step 4
- In the home page choose Administration module.
Click System Admin -> System Settings -> Quality Gates Settings -> Functions -> Add Function.
- In Function window fill the following fields:
- Name - the name of the function, for example: 'Month'
- Template - the arguments of the function, for example: MONTH(date/string/milliseconds)
- Example - MONTH('2015/12/31')
- Family - choose from the list function category: Text, Date, Logical, Math
- Description - for example: 'This functions returns month (from 1 to 12).'