Actimize Data E2E Validation Framework – Java

Actimize Test Strategy

Step_01 – ODS to Orders & Trades Fact – Dimensions – Validation

ODS – Required Validation

1. ODS Data is either received through DB Link or API Call – Validate the records counts on day to day to data ingestion from Source to ODS Tables.
2. Validate the Data received from the Source has the right data type as agreed
3. Validate the Date / Time / Financial fields are received as per the required format
4. Validate Nulls / Blanks / Spaces are not received for Logical columns from Source system
5. Validate the data length is loaded from Source is as expected
6. Validate Copy or Duplicate records are not received from Source system
7. Region overlap Test

ODS – Test Automation

In Selenium (Cucumber) framework, we can create a Feature file (gherkin) for each scenario. The QA must write the SQLs to validate above scenarios, with Expected results. The SQLs can be maintained in external spread sheet as param file. As and when the data is loaded from the source, we execute the feature file and it returns the results.

Example feature file: Given , when then , and , but are gherkin keywords and each line will have the Junit code and when it executed in TestNG control file it creates the emailable report with detailed results based on the expected results that we have written in the Junit code.

#FILE: features/gherkin.APAC_ValidateRegionOverlap.feature

Feature: Spoofing Model – Source Data Validation

  Rule: The APAC Region Data should not be overlapped to EMEA or AMER regions  

   Given Connect to database (We can configure this with a variable to run across environments – meaning same test scripts can be run across DEV/SIT/UAT/PROD environments)

   And Source test SQLs (The SQL’s written by QA will be taken by this line from PARAM file – this can be configured)

   When <Region> Region data is not loaded into EMEA or AMER region for <Model> run on <BUS_Date>(In this Java code we validate in all the records loaded into ODS, is there any APAC data is loaded into EMEA or AMER region

   Then <Region> Region data should not loaded into EMEA or AMER region for <Model> run on <BUS_Date> (This line will have the reporter statement and we can see the results either pass or fail (if APAC data into AMEA or AMER))

(The below section is for parameterizing by changing these data we can run the same scripts for different items)

Example:

Region|DatabaseConnection|Model|BUS_Date|

APAC|DEV|SPOOFING|SYS_Date|

Step_02 – Orders / Trades FACT + Dimension validation

STG to FACT – ETL

1. Validate the record reconciliation after filtering the unwanted data
2. Validate the Direction Calculation – If the direction is not given, we need to calculate the side of the order using the Notional of the Order
3. Validate the Version Calculation – using the status received from the source
4. Validate the Financial fields – Validate the VWAP, Notional, Accounting Notional, Base currency principal amount, Strike, Price, Base currency and Accounting currency)
5. Validate the Market Data – (Validate the BID / ASK / price of the product for the given business day)
6. Validate the Dimension data (Product / Counterparty / Account / etc.,) (Validate the Product Counterparty, Trader information is changing slowly as dimension)
7. Validate the Date and Time fields (There are few transformation of the Execution Time Stamp, Trade Date to UTC, Open Date, Closed Date, etc., this should be validated
8. Validate the trades / Orders of firm or individual (Firm and Individual Trade or Order data nomenclature is different, and this should be validated)
9. Validate the order of the different order versions

https://btobits.com/fixopaedia/fixdic44/tag_39_OrdStatus_.html

1. All Case and when data transformation ETLs should be validated
2. Duplicate Trade or Order should not be loaded into the FACT Table
3. As and when the Trader dimension is changed the trade or Order should reflect the right trader / counterparty / product information)
4. Data look back and Look forward requirements and this should be validated in UDM area
5. Data load dependency between models
6. Data load dependency between Order / Trade / Market Data
7. Nonfunctional test- data load

RCM alerts vs Selenium test alerts:

If the alerts triggered by AIS in RCM Alerts table (Deleted = 0) and not closed (Not Suppressed) then then the model is triggering the alerts as expected.

If not, we need to validate, False positive & False Negative

False Positive (AIS created alerts count > Selenium prototype alerts count)

1. Exclusions are not working – example , AMER orders has the status as CANCELLED however EMEA has the status as CANCELED , we need to exclude all Cancelled records , due to this spelling there were False Positive alerts are triggered and we have written the Alert Suppression logic to Suppress or close the alerts . Our selenium scripts will be running after each model ran and find out if it’s False Positive or False Negatives if the count of the alerts triggered by the AIS is not matching with the Test SQL prototype alerts triggered
2. Inclusion are added incorrectly – We want to Include only Exchange XYZ, but the DEV team has added the Exchange PQR – this will create the False Positive alerts which should need to be soft deleted by Suppression logic. Our Selenium scripts are parameterized with all required Inclusions, if it detects any alerts then the script will fail and log it as an Issue.
3. If the Look forward or Look back Thresholds are not working then it will create the False Negative alerts, our automation scripts will find out this using boundary between Look back and Look forward tests

False Negative (AIS created alerts count < Selenium prototype alerts count)

1. If all the calculations are derived as expected but the alert is not triggered is a False Negative this can be identified by the SQL that was written and given to automation suite. The test automation suit is expecting a Spoofing alert for the Product EURUSD for the Counterparty XZY as they breached the deminimus thresholds and breached price sensitive time period threshold. However, this alert was not triggered by AIS. Our script will give the instigating Order ID that should have generated the alert and other Threshold breach statistics to diagnose why this alert was not triggered.

Thresholds Test:

The automation script is written to predict the no of alerts triggered by changing the different thresholds. The feature file is written per model and with all required Thresholds, the users can change one threshold at a time or many, this can be changed in the parameter and we can execute the automation scripts on the current settings and values, this will give the no of alerts to be generated in any environment. This alerts count can be compared against the AIS triggered alerts with the Thresholds setting in RCM.

Inclusions Test:

The automation script is designed with all types (order / trade / Product / Exchange) and once the model run is completed, we will run the scripts to test the alerts are triggered only for the included Inclusions in RCM settings if not the scripts will fail and gives the instigating trade or order information which was triggered apart from the included list.  If any of the included attributes not created an alert after all the logic met then this will be considered as False Negative alert and will be captured in False Negative test and Inclusion test, this will help us to keep tight validation so that we will not miss any expected alerts.

Exclusions Test:

As soon as the Model run is completed the exclusion feature file will be executed to check is there any alert is triggered for the Trade / Order / Product / Exchange exclusions are mentioned in the param file and this can be configured. If any of the Excluded items triggered an alert, then it will be logged as False Positive alerts can be suppressed after the model run using some logic.

Alert Suppression Logic Test:

If any alert is not expected but it was generated and can be suppressed now and can be used later will be controlled by Suppression logic, example , the BANK wants to do surveillance on status NEW Request of an Order and later decided not to use them instead of excluding the Order Status, they will want to create this alert and post which they want to make it Deleted – 1, in future if the want they can reactivate the alerts in database and when RCM jobs are run , these alerts will be shown in the RCM Screen.

Is the Trade/Order Exclusion are working as expected? This can be validated across all the UDM Data using the selenium scripts, the selenium script will read the whole UDM data as RESULSET and sliced using metadata method.

RCM Validation:

• Alerts Screen data to the Database data validation
  • We have to write the script to get the alerts data and get the RCM data and validate the data is displayed as it is from database
• Alerts transaction data validation
  • All the financial data is displayed as it derived from the database
• RCM Settings Screen – Add / Remove Inclusion, Exclusion, Threshold, List
  • We have selenium scripts to validate the delta in all the settings and the no of alerts triggered per settings changed.
• Alerts permission validation
  • We have to write scripts to validate the set of users can access only one Region of the alerts
  • A set of users only can perform few activities of an alert
  • A set of used only can close the alerts
  • User can view or can’t view any alert type
• Alerts Drill out validation
  • Alert drill outs in RCM is driven by a SQL view created on Trade or Order data. We have to validate the Drill outs are created as per the user requirements.
• Dart – Trade and Order validation
  • Dart is used by the alert analyst to check the trade or order data of an instigating trade or order. A view will be created to bring all needed data for the users into RCM Dart Scree. As a QA we must validate are we populating the right Dart data into RCM Screen
• Alerts workflow validation
  • Since alert open to Close it can go under different status one after another, this alert workflow should be validated.
• Alerts Close and Re-open tests
  • Scripts to validate the Close the alerts under certain criteria or re-open it under certain criteria should be validated using the automation scripts.
• Alerts Consolidations – WASH alerts
  • There may be many incidents created for a transaction, but all the incidents should be consolidated into an Alert and displayed into RCM Screen. This Alerts consolidation should be validated using automation scripts
• Duplicate alert test – For a give alert detection logic there should be only one Alert
• Alerts Calibration – Slicing and dicing of the alert / breach data for users to understand the data

Selenium / Cucumber / Maven / TestNG framework implemented in current project

The excel sheet will have multiple rows, each row will contain the test SQLs for different models.

The green common java class is created so generic by changing the parameters to pick the test SQLs and what test to perform. Same scripts can be used across all the models the only thing that we have to do is configure it once with all below values,

1. Test SQLs ODS to UDM_STG
2. Test SQLs UDM_STG to UDM_CDS
3. Test SQLs Model Logic
4. Test SQLs that has the Exclusions as parameters
5. Test SQLs that has the Inclusions as parameters
6. Test SQLs that has the Thresholds as parameters
7. Test SQLs to identify the alerts to be suppressed
8. All the threshold values in the spread sheet
9. All the Exclusion values in the Spread Sheet
10. All the Inclusion values in the Spread sheet

We have ran the data validate between tables which had 95 columns * 100,000 rows every cell to cell comparison took 12mins to compare and spot the mismatch exactly on which column and what was not matched.

Forward approach:

Let’s take all Orders and Trades and run our scripts to get expected alerts and compare it with AIS generated alerts

Reverse approach:

There are many instances, the users will reach out to the QA team to identify,

1. Why the alert is not triggered for this order id? – We can find it if we run our forward approach test scripts
2. Why the alert is triggered for this order id? – We can find if we run our reverse approach test scripts