Using Cycle's Data Extract Tool

Using Cycle's Data Extract Tool

The Data Extract tool allows you to easily create CSV files from an existing database. The Extract Data window can be accessed via the "Tools > Extract Data..." menu item in Cycle. It can also be accessed from the Inspector panel with the funnel-shaped button:

2.4DataExtract.png

 

Using A Data Extract Model

After opening the Extract Data window, clicking "Add model..." will open a file browser to select a model. A Data Extract Model has the file extension .cycextract. Once a model has been added, it will automatically be copied to a folder named "DataExtractModels" in the currently open Cycle tab's project directory. It can then be found in the "Select extract model" dropdown on the Extract Data window.

The JDA Feature Bundle includes extract models for Non-Picked Inventory, Master Receipts, Orders, Shipments and Terminals.

Note: To create a custom extract model you will need schema knowledge of the database as well as working knowledge of JSON.

This is an example of a .cycextract that extracts terminal data from a JDA WMS:

{
"name": "JDA_SCE_2017_RDT_EXTRACT",
"type": "moca",
"format": "csv",
"params": [
{
"name": "wh_id",
"description": "WH_ID",
"type": "String"
},
{
"name": "rf_ven_nam",
"description": "RF_VEN_NAM",
"type": "String"
},
{
"name": "filter",
"description": "FILTER",
"type": "String"
}
],
"steps": [
{
"fileName": "rf_term_mst",
"script": [
"if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter]"
]
},
{
"fileName": "rftmst",
"script": [
"if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter] | [select * from rftmst where devcod = @devcod and wh_id = @wh_id] catch(@?)"
]
},
{
"fileName": "devmst",
"script": [
"if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter] | [select * from devmst where devcod = @devcod and wh_id = @wh_id] catch(@?)"
]
},
{
"fileName": "locmst",
"script": [
"if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter] | [select * from locmst where stoloc = @devcod and wh_id = @wh_id] catch(@?)"
]
}
]
}

This is an example of a .cycextract that extracts Test Parameter Data from the Cycle Data Store:

{
"name" : "Cycle Test Plan Extract",
"type" : "sql",
"format" : "csv",
"params" : [ {
"name" : "id",
"description" : "TEST PLAN ID",
"type" : "String"
} ],
"steps" : [ {
"fileName" : "test_plan",
"script" : [ "select * from test_plan where id = $id;" ]
}, {
"fileName" : "test_data_set",
"script" : [ "select * from test_data_set where test_plan_id = $id;" ]
}, {
"fileName" : "test_data_record",
"script" : [ "select * from test_data_record where test_data_set_id in (select tds1.id from test_data_set tds1 where tds1.test_plan_id = $id);" ]
}, {
"fileName" : "test_data_column",
"script" : [ "select * from test_data_column where test_data_set_id in (select tds1.id from test_data_set tds1 where tds1.test_plan_id = $id);" ]
}, {
"fileName" : "test_data_value",
"script" : [ "select * from test_data_value where test_data_column_id in ( select tdc1.id from test_data_set tds1,test_data_column tdc1 where tds1.test_plan_id = $id and tds1.id = tdc1.test_data_set_id) order by id;" ]
}, {
"fileName" : "test_data_record_tag",
"script" : [ "select * from test_data_record_tag where test_data_record_id in( select tdr1.id from test_data_record tdr1, test_data_set tds1 where tdr1.test_data_set_id = tds1.id and tds1.test_plan_id = $id) order by id;" ]
}, {
"fileName" : "test_data_tag",
"script" : [ "select * from test_data_tag where id in( select tdrt1.test_data_tag_id from test_data_record tdr1, test_data_set tds1, test_data_record_tag tdrt1 where tdr1.test_data_set_id = tds1.id and tdr1.id = tdrt1.test_data_record_id and tds1.test_plan_id = $id) order by id;" ]
} ]
}

Extracting data from a database

Enter a new Dataset Name or select an existing dataset from the dropdown. Select the Data Extract Model from the dropdown for this extraction. JDBC or MOCA will be selected automatically based on your Data Extract Model. For JDBC databases, select the database type from the dropdown and enter the database name. Both MOCA and JDBC databases require entering the database host, username, and password containing the data to be extracted. The Input Parameters table at the bottom of the window allows you to enter in values to extract from the database. The Keys and Types are auto-created based on the Data Extract Model.

Once this information has been completed, click the "Extract data" button. Once the extract completes a dialog window will be presented detailing any errors with the extract or that the extract completed successfully. If successful, the resulting CSV file can be found in your currently open Cycle tab's resource directory in a folder with Dataset Name given in the Extract Data window. A .cycextractin file will also be created from the information entered into the Input Parameters table.

 

Using the dataset

Now that the dataset has been created, use either of the following Steps to put it to use:

I execute MOCA dataset “<PATH_TO_DATASET>“
I execute SQL dataset “<PATH_TO_DATASET>“

When using these Steps, it will look for any filename beginning with “cleanup” and ending with “sql or msql”. If you want to validate something, a validation script will run if you have a filename that begins with “validate” and ends with “sql or msql”; this also applies to "load...".

Files are run in this order (none are required):

  • Cleanup
  • Load
  • Then CSVs are imported into Cycle (inserted into database)
  • Validate
    • Related Articles

    • How to use Datastore Examples as a Scenario Outline data source

      With the enhancements to Scenario Outlines, it is now possible to use multiple sources for test parameter data. Previously, parameters were set in the Feature in an Examples section specified and maintained by the user. The addition of Example Row, ...
    • Can I use Cycle's Data Store reporting even if I don't have a database available?

      Problem In-depth reporting and analysis of Cycle tests often requires the use of a database to log all of the details of your Cycle executions, but not all users have a readily available database to use for this. Solution Cycle comes pre-packaged ...
    • How to use Web API steps

      Web API steps introduced in Cycle 2.5 can be used to send HTTP requests to exposed web API endpoints. The Cycle Web API steps support POST and GET request methods using the JSON data format. Cycle also includes steps for handling HTTP responses ...
    • How to load and clean up test data efficiently in Cycle.

      Problem In order to properly evaluate system behavior it is required that known inputs produce desired outputs. This is especially critical when working with the JDA WMS due to its multitude of configuration options that drive specific system ...
    • Broadening your scope: Using Cycle to plan new development

      In addition to automation and testing, Cycle is a valuable tool for planning new development. In order to use Cycle for planning, Tryon recommends adopting the approach known as BDD (Behavior Driven Development). BDD is a software development process ...