End-to-End Scenario for Data Discovery
CA TDM Data Model scans your Environment to identify tables and table relationships to create a reusable Data Model. You can set Scan Exclusions to ignore unnecessary tables, to tailor your Data Model to your needs. Within a Data Model, you can view an Entity-Relationship diagram for all tables in your environment, edit and add table relationships, perform a PII Scan to identify any PII data (see ), and mask the PII data that you find.
CA TDM Data Model scans your Environment to identify tables and table relationships to create a reusable Data Model. You can set Scan Exclusions to ignore unnecessary tables, to tailor your Data Model to your needs. Within a Data Model, you can view an Entity-Relationship diagram for all tables in your environment, edit and add table relationships, perform a PII Scan to identify any PII data (see Scan Data Model for PII), and mask the PII data that you find.
The Data Model PII Scan process is similar to the PII Audit process, but you can use the results of the PII Scan to directly mask data in a Data Model. A guide to how you can do this is available at Mask Data with CA TDM Portal.
The Data Model End-to-End Scenario outlines the step-by-step process for a Test Data Engineer (TDE) to create a Generic Data Model. The basic flow to create a Data Model is as follows:
Setting Up a Data Model
Before you can start with the Data Model creation process,you need to perform the following steps:
You are now ready to perform Data Discovery to create a Data Model.
Perform Data Discovery to create a Data Model
As a TDE, when you have an Environment ready with Connection profiles, you can create a Data Model.
Follow these steps:
- Choose an Environment from which to create your Data Model. You can do this in one of two ways:
- Method One
- Click on theModeling, Environmentssection of the Portal.
- Click on the Environment you want to use to create your Data Model.
- ClickPerform Data Discovery.TheScan Options / Scan Exclusionspage of theData Modelsection of the Portal opens.
- Method Two
- Click on theModeling, Data Modelsection of the Portal.
- ClickGet Started.TheSelect Environmentpage opens.
- Select an Environment from the list in the left pane. ClickNext.TheScan Options / Scan Exclusionspage of theData Modelsection of the Portal opens.
- Select Scan LevelYou have the following options here:
- Basic - Scan Key relationships only (default)The Data Discovery scans the environment for key relationships (i.e. primary and foreign key relationships defined in the data sources) only.This option is faster, but it does not discover relationships unless you define them in the data sources. You can add table relationships manually later - see Make Changes to Table Relationships for more information.
- Advanced - Scan Key and Column relationshipsThe Data Discovery scans the environment for key relationships (i.e. primary and foreign key relationships defined in the data sources), and also scans all columns within your environment for their relationships to each other across data sources.
- Select Scan ExclusionsBefore you perform a Data Discovery scan, you can add a specific Database, Schema, or Table to scan exclusions. The scan excludes the specified Database, Schema, or Table from the Data Discovery scan and does not form part of the Data Model.You can use basic wild card characters to add scan exclusions such as*(used to match zero or more characters) and?(used to match a single character).For example: for a table exclusion if you enter the value*sys, all tables in the environment that end insysare excluded from the scan and no relationships are retrieved for them.If you change a scan exclusion on an existing model, you must re-scan the Data Model for the changes to take effect.For example, to remove a table from an existing Data Model, you can add the table as an exclusion and perform a re-scan.
- After you add any necessary exclusions, clickScanto perform a Data Modeling scan.If you already have a Data Model, CA TDM prompts you to Confirm that you wish to overwrite it.TheData Modelpage opens. While the Data Discovery process is in progress, this page displays a summary.
- When the process completes, theData Modelpage displays your Data Model in the List View.
You can perform one or more of the following actions on the Data Model:
Manage Table Relationships
After performing a Data Model scan, the Data Model screen opens. This firstly shows the List view, as a hierarchy of table relationships for all tables and columns in the selected environment. At the top-right of the screen, next to the text 'View' there are buttons to switch between List View, Entity Relationship diagram and PII Heatmap view.
Tables that have associated table relationships are identified with a link icon. Expand the tree structure in the List view and select a table. All table relationships for the selected table appears in the right pane. If you click a column, this hides the relationships for all other columns. Clicking again on the table shows the relationships for all columns. This allows you to focus on one particular column without the distraction of all the others.
Click in the Search field to filter the list of schemas, tables and columns with the text you enter.
You can add, edit, or delete table relationships to adjust table relationships that were automatically discovered. To better understand table relationships, you can access the Details view for a specific table. You can assign tables and columns aliases within the Data Model view.
To perform actions on tables and columns, click the
Edit Aliasbuttons in the toolbar at the top of the screen in the Data Model view. These actions are also available from a context menu in the list view - when you hover over a table or column, an ellipsis symbol appears in the Actions column. Click the ellipsis to show these actions for that column or table.
Assign aliases to a column or table within the Data Model, to make them more accessible or memorable than their real names, as they appear in the database.
The following Actions are available from the Actions drop-down menu (from the
Entity Relationship Diagram):
- Re-scan Data ModelClick here to create a new Data Model. This option overwrites the existing Data Model and its relationships.
- Run/Re-run PII ScanClick here to begin the PII Scan process. If you re-run the PII Scan, this scan overwrites any custom tags you added to columns in the data model.
To view details of a table's relationships to other tables by column, select a table in the list view and click
In the Details view, you can:
- View all tables related to the selected table by column. In the left pane, all columns with at least one relationship are displayed in bold.
- Click a column in the selected table to specifically highlight relationships for that column in other tables. Click a table or column name from the left pane to get an overview of all the table relationships for the selected table. Click the down arrow to expand and view all columns in a table. You can expand only one table at a time. From here you can make changes to table relationships.
Register Data Model Tables
When you click
Register Data Model Tablesfrom the
Actionsmenu, the Register Data Model Tables page opens, with a list of all tables in your Data Model. Click the tick icon to the left of a table name to select that table. Click Register to register selected tables. When you Register tables, they appear on the
Objectspage under the
Modelingsection. This process is equivalent to the one described at Create and Register Derived Objects.
Tables with duplicate names
Tables with duplicate names from different databases can exist in your Data Model. In the list on the Register Data Model Tables page, these duplicate tables appear in
bold. You cannot register tables with duplicate names. If you try to register multiple tables with identical names, no tables are registered and you receive the error message "Selected table names must be unique".
If you register a table whose name is not unique, the Status of all tables with this name changes to
Registered. The Differences column displays
Changes Foundfor all tables whose contents in the database do not match the registered contents. The Differences column displays
No Changes Foundfor the table you registered, unless you subsequently make changes to this table in the database. If you register another of the tables with the same name, this overwrites the originally registered table.
Entity Relationship diagram
Use the icons in the top-right corner to toggle between the List view and Entity-Relationship diagram for all table relationships in an environment. Circles represent tables in the environment, and links between them indicate that the at least one column connects the two tables. All table names are not visible at once in the entity-relationship diagram. When you hover over a circle, this displays the name and alias for the table that circle represents, and all paths to related tables are highlighted. Click a table to view the following details for that table:
- Data Source name
- Database name
- Table name
- Number of related tables
- Summary of table relationships
You can add, edit, or delete table relationships from the entity-relationship diagram for an Environment and view details for a specific table. For more information about how to add, edit, or delete table relationships, see Add/Edit/Delete Table Relationships.
All Data Sources are color coded in the entity-relationship diagram and listed under Data Model Details. You can filter the data sources that you want to be currently visible. After you select a table you can click
Detailsto view the table Details for that specific table.
Make changes to Table Relationships
From the List View and Entity Relationships diagram, you can see and make changes to tables' relationships to other tables.
- From the List View, click a table in the left-hand pane and either click theEdit Relationshipsbutton in the top bar, or click on the ellipsis icon on the table's row in the list, and clickEdit Relationships.TheRelationshipspage opens. This lists all columns with relationships to columns in other tables.
- From the Entity Relationships diagram, click a table (a node on the diagram) and click theEdit Relationshipsbutton in the top bar.TheRelationshipspage opens.
Add a Table Relationship
Relationshipspage, you can add relationships from the primary (selected) table, to columns in other tables.
Follow these steps:
- ClickNew Relationshipin the top bar.A new row appears at the bottom of the list of columns.
- Select a Column from the drop-down list of columns in that table.
- Select aRelated Tablefrom the drop down list of other tables.
- Select aRelated Columnfrom the drop down list of that table's columns.
Edit a Table Relationship
Relationshipspage, you can edit relationships from the Primary (selected) table, to columns in other tables.
You cannot edit table relationships of key columns.
Follow these steps:
- For the Primary Column whose relationship you want to change, select the appropriate Related Table from the drop-down menu.
- Select the appropriate Related Column from the drop-down (this contains all of the Related Table's columns).
Delete a Table Relationship
Relationshipspage, you can delete relationships from the Primary (selected) table, to columns in other tables.
- To delete a column's relationship, click the delete icon in the row of the Primary Column whose relationship you wish to delete
- To delete all relationships for a table, clickDelete All Relationships.
Assign an alias to a database entity
Tables and columns can have aliases. When you hover over a database entity in the List View or Entity Relationship diagram, a tooltip displays the entity's real name (as it appears in the database), and its alias.
To assign an alias to a table or column, follow these steps:
- A dialog appears, with a field for the alias name. When a table or column has no alias, this field is blank.Select a table or column in the List View (or Table Details pane), or a table in the Entity Relationship diagram, and clickEdit alias.
- ClickSaveto assign the alias, orCancelto discard changes.
Aliases for database entities are propagated to the Find & Reserve Model. Changes are not limited to the Data Model.
Display aliases or real names of database entities
When a database contains entities with aliases, you can choose whether to view these aliases or the entities' actual names in the list view.
In the Data Model view, there is a
Show Aliasestoggle under Display Settings in the right pane of the list view. If you switch this to Yes, entities' aliases appear, if the entities have aliases. When you hover over a database entity in the list view, a tooltip appears with the entity's real name (as it appears in the database), and its alias.