Related Extract with Disguise SQLServer
BMC AMI Test Drive
e Test Data Privacy – Related Extract with Disguise SQL Server
This tutorial will introduce you to the BMC AMI DevX Data Studio Related Extract to create a relationally intact subset of data while using Disguise Rules to change and protect sensitive data.
Revised: 2024/03/14 00:00
BMC AMI DevX Data Studio- Create a Related Extract Specification
- Application Relationships
- Execute Extract with AR and Selection Criteria
Getting Started
Instructions:
- This guide contains many screenshots to provide a visual reference
- Please note each place that you must enter your own specific ID or number
- You must complete each step before proceeding to the next to successfully follow the test drive script
The BMC AMI DevX Test Drive Experience requires that the user must have completed the Building Your Environment exercise before starting any tutorial script.
If, at any point during your experience, your host connection times out, you may need to log back in to the TestDrive host connection.
If at any time during the execution of this script the Common Enterprise Services Login popup is shown, enter your test drive ID and password under User ID and Password, check the Save credentials box and then depress the ENTER key or click OK.
BMC AMI DevX Data Studio
BMC AMI DevX Data Studio simplifies the complex task of managing and preparing data for testing. This enables developers and test engineers to perform data-related tasks without writing programs or scripts, coding SQL, or using multiple utilities.
They can easily create specifications to extract related sets of data from one environment and load them into another. Extracting just the relevant data needed for testing is done using easy-to-use selection criteria or starting key values. The extracted data will remain relationally intact based on the referential integrity (RI) or application relationships (AR). Data Privacy rules can be applied at the time of extract to protect sensitive data. Data can then be loaded into the environment of choice by supplying the necessary target information in a Related Load job.
In this exercise you will:
- Create a Related Extract specification
- Extract all related data using Referential Integrity
- Create Application Relationships
- Create Selection Criteria to extract only specific data
- Disguise Data during Extract
Create a Related Extract Specification
- Open the DevX Data Studio perspective in Workbench for Eclipse
- Select the Data Explorer tab in the top left.
- Double click the FAEXRepos Repository in the Data Explorer tab on the left pane or right-click and select Open.
- Right-click on Related Extract within the Data Explorer and select New.
In this example you will extract data from multiple SQL Server tables. Note that you can use this same process against DB2, DB2 z/OS, Oracle, and Sybase.
- Name the related extract job SQL_SERVER_EXTRACT.
- Click the Browse button beside Driving Object to select a table as a starting point within the database.
- For Type leave the selection of Database (JDBC).
- Select SQL Server Sample from the Host drop down menu.
- Type dbo (in lowercase) for the schema Name.
- Click List.
- Enter "testdrive for the userid and password if prompted for credentials.
- Select the ORDER_TABLE and Click OK.
This will populate the Driving Object field and show the Host connection.
- Click Finish.
- Check the box Use referential integrity and Click the Discover Relationships button below it.
- Click the Browse button to navigate to a file location to extract the data.
- Click on Temporary Files.
- Click the Select Folder button.
You are now at the Extract Overview Screen with navigation tabs along the bottom.
- Click the Objects tab at the bottom of this section.
You now see all tables related to the ORDER_TABLE through referential integrity and will be included in the extract.
- Select the Order_Table row.
- Right click to see the Selection Criteria menu.
For that table you can apply selection criteria, determine process type, and see properties.
- Click the Relationships tab at the bottom of the screen.
This view shows the objects as parents and dependents and the type of relationship. In this example all objects are related through RI (referential integrity). Application Relationships would have AR in that column.
- Click the Visualization tab at the bottom of this section.
The Visualization view shows the objects graphically. Solid lines indicate the tables are related through RI (referential integrity) which is maintained in the database.
- Right click on any table and select properties to view for that object. Properties will appear in a tab below.
- Click the Data Privacy tab at the bottom of this section.
- Click the Browse button beside Project Name.
- Select the TDPRepos repository from the drop down.
- Highlight your project and click OK.
- If you do not see your project select TEST DRIVE DISGUISE PROJECT.
- Click the Keys tab at the bottom of this section.
Extracts may be driven by a key file to subset the data and get precise information.
Key files may also be created from an extract to drive another extract, possibly in a different database.
- Click the Options tab at the bottom of this section.
Extract Options include limits, discovery options, and more advance database options.
- To execute this Related Extract click the Blue Arrow Execute button in the top right.
Processing details will show up in the Console tab. You may double click this tab to maximize the view.
The report shows the number of rows extracted from each table and the data privacy applied to each eligible column. It shows for each table which columns had a disguise rule applied and how many rows were changed. Scroll through to understand how the rules were applied according to the Source Data Identifiers in the Project.
- Double click this tab to maximize the view.
- Scroll through the report.
- Double click on the Console tab to minimize this view.
- Close Related Extract job by clicking on the X in the tab.
- Save your changes.
You have now successfully created a Related Extract job and extracted rows from multiple tables related through Referential Integrity and applied disguise rules to replace sensitive data in columns containing Names, Phone Numbers, Social Security Numbers, Credit Card Numbers, Dates, and Email addresses.
Application Relationships
Application Relationships are used to relate objects when there is no RI maintained within the database or between certain tables. This allows the Related Extract to extract only related rows from these tables in the same manner as it would with RI. These Application Relationships are maintained within the product and make no changes to the database.
- In the Data Explorer tab right click on Application Relationship and select New.
In this example, the Order table is programmatically related to the Customer table but there is no RI in the database.
- Type CUSTORD in the name field.
- Give it a description for future reference.
- Click Add Objects to select these two tables.
- Type dbo (in lowercase) for the Schema name.
- Click List.
- Select the CUSTOMER_TABLE and click Add.
- Highlight the ORDER_TABLE.
- Click Add and Close
- Click Finish.
- Click on the Objects tab along the bottom.
- Highlight the first table in the list.
- Use the middle icon top right to Create New Relationship.
- Type CUSTORD in the Relationship Name field.
- Use the drop down to select the ORDER_TABLE as the dependent on the right.
To create the Application Relationship we will relate the Customer Number column in the Customer table to the Cust Num column in the Order table.
- Click on Customer Number on the left under Customer Table.
- Click on Cust Num on the right under Order Table.
- Click on the Add Association icon to create relationship.
- Verify that the columns are now listed under Associations at the bottom.
- Click Create and Close.
Note: Multiple tables can be related through AR. If there were more tables selected, the Create button would allow additional relationships to be created. Also, tables can be related through more than one column.
- Click on the Relationships tab along the bottom to see the newly created Relationship.
- Click on the Visualization tab.
This shows a graphical representation of the objects and the relationships.
- Click the X in the tab at the top to close the newly created Application Relationship.
- Click save.
This Application Relationship can now be incorporated into a Related Extract job. This type of relationship will be treated in the same manner as an RI relationship during the extract process. The parent row extracted will pass along the key to the dependent table based on the column used to relate the tables and any rows there that match will also be extracted.
Selection Criteria
Selection Criteria allows specific data to be extracted based on data values. In this example all rows from the Order table where the Order Type is equal to Purchase will be selected. Then during the extract all related rows will be extracted from the other tables.
- In the Data Explorer tab right click on Selection Criteria and select New.
Give the Selection Criteria a meaningful name to make it reusable.
- Type "Purchases" in the name field.
- Click Browse to select a table.
- Type dbo (in lowercase) for the Schema name.
- Click List.
- Select the ORDER_TABLE and click OK.
- Click Finish.
- Click the Conditions tab along the bottom of this section.
- Click on the Add a new condition icon on the top right.
- Click on the <Select field> under the Field Name column.
- Use the drop-down menu and select the ORD_TYPE column.
- Click in the space under Condition Type to activate the drop-down menu.
- Select Equal to from the list.
- Click in the space under the Value column and select the box with "…".
- Type PURCHASE for our selection value.
- Click OK.
The selection criteria is now complete. You can see the SQL where clause below.
- Click the X in the tab to close the Selection Criteria.
- Click Save to save the Selection Criteria.
Execute Extract with AR and Selection Criteria
For this exercise we will open the Related Extract job created earlier. This time we will be adding in additional tables through the use of Application Relationships. We will also be limiting the number of rows extracted by using Selection Criteria.
- In the Data Explorer tab, right click the existing SQL_SERVER_EXTRACT job and select Open. Expand Related Extract if not already open.
- Check the box to Use application relationships.
- Click on the Select Relationship button.
- Select the CUSTORD relationship previously created and click OK.
- Click the Discover Relationships button.
This will refresh the RI relationships and will now include the additional AR relationships.
- Click the Objects tab along the bottom to see all objects now included in the Extract.
Note you now see the Customer table because of the AR created to the Order table. There is also an additional table, Contact table.
- Click on the Relationships tab along the bottom.
Note the Customer table is related to the Order table with a Relationship Type of AR. Once the Customer table is related, it will include in the extract the Contact table since they are related through RI.
- Click again on the Objects tab.
- Select the Order table and right click.
- Hover over Selection Criteria and choose Select.
For that table you can apply selection criteria, determine process type, and see properties. This can be activated and applied to any table, not just the driving table for more complex criteria to get just the data needed.
- Select the Selection Criteria previously created and click OK.
- Click on the blue arrow icon top right to execute.
This will extract all rows from the Order table where Order Type is equal to Purchase. Then it will extract the related rows of data from the other tables related through Referential Integrity, including the Customer table now related through an Application Relationship. Since we had previously selected a Data Privacy Project any sensitive data will also be disguised.
Double click the Console tab to maximize.
Check the Rows Extracted numbers. We are now only selecting 18 rows from the Order table instead of the 42 from the previous execution with no selection criteria.
You have now successfully extracted related data using Referential Integrity and Application Relationships while applying Selection Criteria to subset that data based on specific data values and Disguised the data so that it is now compliant with existing privacy regulations. This data is now ready to be loaded into a testing or development environment.
This completes the Related Extract with Disguise section.