Challenge Overview
The Fund Accounting Yield Application (FAYA) requires an automated way of validating functionality from an acceptance tester’s point of view. The Test Automation tool should validate all calculated values that are saved to the FAYA Java Application database recently build by topcoder community.
This diagram show you the high level overview of the tool components. Read requirements for more details.
1. Load the Expected Results in to the FAYA_TEST database
- This feature of the test tool will allow the customer to load expected results for a given business date in to the FAYA_TEST database
- The command line tool should accept arguments to specify what stream to load expected results, user should be able to specify a specific stream, or all of them.
- The logic should be re-runnable, meaning: if expected results already exist, it should just overwrite them
2. Compare the values in the FAYA Application database to the expected values in the FAYA_TEST database
- This feature will allow the customer to compare values in the FAYA Application database to the expected values in the FAYA_TEST database
- The tool should accept arguments to allow for comparison of each of the three work streams or all of them
- Each time the tool is executed for same data, it should create a new set of compare results (do not overwrite the previous results).
- The time for which the comparisons were run should be saved in the FAYA_TEST database
- The results should be stored at both a record by record level and summary level
- Produce a report (CSV or XLS)
3. Reports
- This feature will allow the customer to produce a simple report (CSV or XLS)
- A separate report for each of the three streams is required
- The report is automatically produced at the end of each comparison
- The report will contains the results at both a record by record level and at summary level. It is Ok to generate two files for that : Summary file, and detailed file.
1. Money Market Funds
The expected results for the Money Market stream will be in an Access DB. The client will provide a query to extract the data from the Access DB. The testing tool will need to populate the FAYA_TEST database with this data using the mapping provided by the client. The query includes final calculated data and data that was used for the calculation. All this data needs to be loaded in to the FAYA_TEST database by the Testing Tool. The list of tables / columns that need to be compared will be provided by the client. The data will be for a single calendar date
2. Distribution Funds
The legacy systems generate two spreadsheets that contain the Distribution Yield data. These are the expected results. The Testing tool will need to load the data from these two spreadsheets in to the FAYA_TEST database. A field by field mapping will be provided by the client. The data will be for a single calendar date
3. SEC Funds – this is broken down in to 3 levels
1. Security Level
- The legacy system produces a file called the “modified PARMSY” file which contains Position records. A Position is the same as a stock holding
- The format of the PARMSY file is a fixed width format, showing Position level detail
- Since a stock may be held by multiple Portfolios (funds), the Security information would occur multiple times in the PARMSY file. The Security level data need only be saved once, the information at a Security level would be the same across Position records where it is held
- The PARMSY file contains the input data that was used for the calculations and also the resulting calculations
- The testing tool will need to load the data that was used for the input and the calculated data from the PARMSY file in to the FAYA_TEST database – these values are the expected results
2. Position Level
- Extract Position level information from the PARMSY file and load the data in to the FAYA_TEST database – these values are the expected results
- The testing tool will need to insert Position records if not present, or update it if present
3. Portfolio and Class level
- A CSV feed will be provided by the client along with a mapping of columns in the feed
- The Testing Tool will need to insert / update the data from the CSV feed in to the FAYA_TEST database – these values are the expected results
1. Money Market
- The SQL Query to run against the Access Database is called BridgeToolSQLExtraction.docx
- Excel_to_DBMapping.xlsx (MM CLASS AND FUND DATA) has the mapping for Excel to database table and column
- Using this query, we pulled 45 days of test data from the bridge tool. This document is called qrySECYieldBackUpPull.xlsx
- This document specifically should be loaded into the testing tool to assist with testing - the data is aged between 6/7/2016 and 7/24/2016.
- The corresponding FAYA database query to be used for comparison is in the MMClass_Fund_Data.sql file (fund / portfolio level query), along with the MMIncome sheet in MMandDistSampleData.xlsx file (class level query).
2. Distribution Yield
- The Data sheet is called 2016-07-31MonthendSECRatesandYieldsSummary
- Data from this sheet should be loaded into FAYA Test database
- Excel_to_DBMapping.xlsx (DIST CLASS AND FUND DATA) has the mapping for Excel to database table and column
- The corresponding FAYA database query is called Dist_Class_fund_Data.sql – to be executed from FAYA database after calculations
- Compare the results for the funds from excel sheet in step a with query from the previous step.
3. PARMSY file
- Sample File layout and Data is provided within the file named 20160729parmsy.xlsx
- Excel_to_DBMapping.xlsx (PARMSY File) has the mapping for Excel to database table and column
- Load the 20160729parmsy.xlsx into FAYA_Test Database
- The corresponding FAYA database query is in PARMSY_QUERY.sql, use the fetched fields to compare it with the PARMSY file data loaded in FAYA_TEST
- You can create a file for summary and file for detailed results.
- When tool finish executes, it should prompt the location of output files with the file name generated (use timestamp as part of the file name).
- Exception block should log enough contextual information to help understand the problem to fix it
- Perform basic validation
- When tool is executed it should check all configurations are valid prior to execution any logic
- Throw exceptions if input file formats are incorrect
- Prompt a manual to help user understand how to use the tool or when improper execution is running
- The client wants to run this comparison process every day during the production parallel phase.
- The tool should be configurable to point to the location of the feeds (expected results data), and be able to configure the Data Source for FAYA Application database and FAYA_TEST database.
- You need to provide test data to load FAYA Application, it should varies between matching and mismatching data so we can see how report will address it.
- Create seperate README file for how to setup and execute the tool. It should be placed under the tool folder.
- Use mybatis for DB interaction
- You are not expected to use any of the existing code, you will only need to use the DDL and load it with test data.
- The folder name should be faya-test-tool, it should include all source code, and its own pom file.
- Oracle 11g
- mybatis
- Log4j 1.2 http://logging.apache.org/log4j/1.2/
Challenge Overview
The goal of this challenge is to design and build a command line Java based Testing Tool and a FAYA_TEST Database. The FAYA Application comprises of 3 work streams – Money Market, Distribution Yield, SEC yield.This diagram show you the high level overview of the tool components. Read requirements for more details.
Challenge Requirements
Logic and Flow Requirements
Here is how the tool should work:1. Load the Expected Results in to the FAYA_TEST database
- This feature of the test tool will allow the customer to load expected results for a given business date in to the FAYA_TEST database
- The command line tool should accept arguments to specify what stream to load expected results, user should be able to specify a specific stream, or all of them.
- The logic should be re-runnable, meaning: if expected results already exist, it should just overwrite them
2. Compare the values in the FAYA Application database to the expected values in the FAYA_TEST database
- This feature will allow the customer to compare values in the FAYA Application database to the expected values in the FAYA_TEST database
- The tool should accept arguments to allow for comparison of each of the three work streams or all of them
- Each time the tool is executed for same data, it should create a new set of compare results (do not overwrite the previous results).
- The time for which the comparisons were run should be saved in the FAYA_TEST database
- The results should be stored at both a record by record level and summary level
- Produce a report (CSV or XLS)
3. Reports
- This feature will allow the customer to produce a simple report (CSV or XLS)
- A separate report for each of the three streams is required
- The report is automatically produced at the end of each comparison
- The report will contains the results at both a record by record level and at summary level. It is Ok to generate two files for that : Summary file, and detailed file.
Loading Expected Results Requirement
Note the following when loading expected results data :1. Money Market Funds
The expected results for the Money Market stream will be in an Access DB. The client will provide a query to extract the data from the Access DB. The testing tool will need to populate the FAYA_TEST database with this data using the mapping provided by the client. The query includes final calculated data and data that was used for the calculation. All this data needs to be loaded in to the FAYA_TEST database by the Testing Tool. The list of tables / columns that need to be compared will be provided by the client. The data will be for a single calendar date
2. Distribution Funds
The legacy systems generate two spreadsheets that contain the Distribution Yield data. These are the expected results. The Testing tool will need to load the data from these two spreadsheets in to the FAYA_TEST database. A field by field mapping will be provided by the client. The data will be for a single calendar date
3. SEC Funds – this is broken down in to 3 levels
1. Security Level
- The legacy system produces a file called the “modified PARMSY” file which contains Position records. A Position is the same as a stock holding
- The format of the PARMSY file is a fixed width format, showing Position level detail
- Since a stock may be held by multiple Portfolios (funds), the Security information would occur multiple times in the PARMSY file. The Security level data need only be saved once, the information at a Security level would be the same across Position records where it is held
- The PARMSY file contains the input data that was used for the calculations and also the resulting calculations
- The testing tool will need to load the data that was used for the input and the calculated data from the PARMSY file in to the FAYA_TEST database – these values are the expected results
2. Position Level
- Extract Position level information from the PARMSY file and load the data in to the FAYA_TEST database – these values are the expected results
- The testing tool will need to insert Position records if not present, or update it if present
3. Portfolio and Class level
- A CSV feed will be provided by the client along with a mapping of columns in the feed
- The Testing Tool will need to insert / update the data from the CSV feed in to the FAYA_TEST database – these values are the expected results
Files and Queries used by the Tool
You are provided with files that contains sample data, and the queries you need to use to extract data, load data in FAYA_TEST, and to load data from FAYA application:1. Money Market
- The SQL Query to run against the Access Database is called BridgeToolSQLExtraction.docx
- Excel_to_DBMapping.xlsx (MM CLASS AND FUND DATA) has the mapping for Excel to database table and column
- Using this query, we pulled 45 days of test data from the bridge tool. This document is called qrySECYieldBackUpPull.xlsx
- This document specifically should be loaded into the testing tool to assist with testing - the data is aged between 6/7/2016 and 7/24/2016.
- The corresponding FAYA database query to be used for comparison is in the MMClass_Fund_Data.sql file (fund / portfolio level query), along with the MMIncome sheet in MMandDistSampleData.xlsx file (class level query).
2. Distribution Yield
- The Data sheet is called 2016-07-31MonthendSECRatesandYieldsSummary
- Data from this sheet should be loaded into FAYA Test database
- Excel_to_DBMapping.xlsx (DIST CLASS AND FUND DATA) has the mapping for Excel to database table and column
- The corresponding FAYA database query is called Dist_Class_fund_Data.sql – to be executed from FAYA database after calculations
- Compare the results for the funds from excel sheet in step a with query from the previous step.
3. PARMSY file
- Sample File layout and Data is provided within the file named 20160729parmsy.xlsx
- Excel_to_DBMapping.xlsx (PARMSY File) has the mapping for Excel to database table and column
- Load the 20160729parmsy.xlsx into FAYA_Test Database
- The corresponding FAYA database query is in PARMSY_QUERY.sql, use the fetched fields to compare it with the PARMSY file data loaded in FAYA_TEST
Reports
- The reports generated will simply be pulled from the FAYA_TEST database and placed in xls/csv file.- You can create a file for summary and file for detailed results.
- When tool finish executes, it should prompt the location of output files with the file name generated (use timestamp as part of the file name).
Validation, Exception Handling and Logging Requirements
- Add debug statements, try to address all important areas that should simplify debugging the tool- Exception block should log enough contextual information to help understand the problem to fix it
- Perform basic validation
- When tool is executed it should check all configurations are valid prior to execution any logic
- Throw exceptions if input file formats are incorrect
- Prompt a manual to help user understand how to use the tool or when improper execution is running
General Note
- The validation is different for each of the three work streams due to different sets of calculations. The loading of expected results is different for each of the three work streams because of the different legacy processes involved for each work stream- The client wants to run this comparison process every day during the production parallel phase.
- The tool should be configurable to point to the location of the feeds (expected results data), and be able to configure the Data Source for FAYA Application database and FAYA_TEST database.
- You need to provide test data to load FAYA Application, it should varies between matching and mismatching data so we can see how report will address it.
- Create seperate README file for how to setup and execute the tool. It should be placed under the tool folder.
- Use mybatis for DB interaction
Source Code
- Existing Source Code for FAYA Java Application is provided in challenge forums.- You are not expected to use any of the existing code, you will only need to use the DDL and load it with test data.
- The folder name should be faya-test-tool, it should include all source code, and its own pom file.
Technology Stack
- Java 8- Oracle 11g
- mybatis
- Log4j 1.2 http://logging.apache.org/log4j/1.2/
Final Submission Guidelines
Submission Deliverables
Below is an overview of the deliverables:
- - Fully Implemented Java Comand Line Testing Tool functionality defined by the requirements outlined in challenge specification.
- A complete and detailed deployment document (as a README file) explaining how to deploy the application including configuration information.
Final Submission
For each member, the final submission should be uploaded via the challenge detail page on topcoder.com.