Adding Manual Relationships Between Tables in the TDM Subsetter – SQLServerCentral

I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and a follow up post on using your own backup. One of the things I didn’t show from my own database was that it had no FKs, so the subsetting didn’t quite work as I wanted.

A previous post showed how add starting tables for the subsetter to look at, however that didn’t get me a good data set for testing. This post continues looking at the subsetter by adding manual relationships to our configuration.

This is part of a series of posts on TDM. Check out the tag for other posts.

Declaring a Relationship in the Options File

In my previous post, I’d picked a starting table and had reduced the dbo.players table from 16564 to 1800. However, I only had player information. If I query my subset database, I see there is a player, but I have no batting statistics for this player.

This is because my table has no declared FKs in it. If I check the dbo.batting table, I can see only a PK.

Let’s fix this.

Declaring Manual FK Relationships

In the options file documentation, there is a section that notes manual relationships can be declared with a key called “manualRelationships”. If I copy/paste the example section into my options file, I’ll see this:

I don’t have a SourceTest table, so let me edit things. I’ll set a relationship between dbo.players.playerID and dbo.batting.playerID. This gives me the following in my options file.

Before I run my subsetter, here are the row counts by table.

I’ll re-run this subset command, which includes my option file at the end.

rgsubset run --database-engine=sqlserver --source-connection-string="server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes" --target-connection-string="server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes" --target-database-write-mode Overwrite --options-file E:DocumentsgitTDM-Demosrgsubset-options-bb.json

When I do that, I know see these row counts. Note I now have batting rows.

My player query won’t work, so I still need to declare another relationship with the dbo.teams table. That is shown below:

I can re-run the same command above, and then I see this set of rowcounts (original on left, subset on right).

There is teams data, and if I re-run my queries from the top, I can see stats now.

Now I have a dataset that I can perform development work with in terms of players, teams, and batting.

I can also add more relationships as needed, for example, I’ll add this section to include pitching, batting post, and fielding. Here’s my complete options file:

{
  "jsonSchemaVersion": 1,
  "startingTables": [
    { 
      "table":
      {
        "schema": "dbo",
        "name": "players"
      },
      "filterClause": "birthState="CA""
    }
  ],
  "manualRelationships": [
    {
      "sourceTable": 
        { 
          "schema": "dbo", 
          "name": "players"
        },
      "sourceColumns": [ "playerID" ],
      "targetTable": 
        { 
          "schema": "dbo", 
          "name": "batting" 
        },
      "targetColumns": [ "playerID" ]
     },
     {
        "sourceTable": 
          { 
            "schema": "dbo",
 
            "name": "batting"
          },
        "sourceColumns": [ "teamID", "yearID", "lgID" ],
        "targetTable": 
          { 
            "schema": "dbo", 
            "name": "teams" 
          },
        "targetColumns": [ "teamID", "yearID", "lgID" ]
       },
       {
          "sourceTable": 
            { 
              "schema": "dbo", 
              "name": "players"
            },
          "sourceColumns": [ "playerID" ],
          "targetTable": 
            { 
              "schema": "dbo", 
              "name": "battingpost" 
            },
          "targetColumns": [ "playerID"]
         },
         {
            "sourceTable": 
              { 
                "schema": "dbo", 
                "name": "players"
              },
            "sourceColumns": [ "playerID" ],
            "targetTable": 
              { 
                "schema": "dbo", 
                "name": "pitching" 
              },
            "targetColumns": [ "playerID"]
           },
           {
              "sourceTable": 
                { 
                  "schema": "dbo", 
                  "name": "players"
                },
              "sourceColumns": [ "playerID" ],
              "targetTable": 
                { 
                  "schema": "dbo",
 
                  "name": "fielding"
                },
              "targetColumns": [ "playerID"]
             }
  ]
}

After re-running the subsetter, I have these row counts. Note there are rows in all the tables defined in the options file.

I can keep adding in more tables as needed to ensure the subsetter can walk down the data relationships I need in my database to produce a useable dev/test dataset that’s smaller than production.

TDM can help your devs build better software and with the subsetter, this can create lots of agility to ensure the data you need to accurately build this software is available.

Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.

Video Walkthrough

Check out a video of my demoing this below: