Replace suppliers (Random Lookup)

Sometimes shuffling or scrambling is not good enough and you want to replace all existing values with other data. DATPROF Privacy offers to randomly select a value from a lookup table. You can create your own lookup table and fill it with synthetic data and use that as a lookup table.

In the current Gasware training database there is no lookup table with synthethic test data available, but with an easy script we can generate our own.

Create new Lookup Table

Connect to your database using Oracle SQL Developer (Shortcut on the Desktop) and run the following script to add a new lookup table with some 'synthetic test data'. You can change the names of the organisations if you like. We will use this table later on with the random lookup.

create table DPF_ORGANIZATIONS (value VARCHAR(200));

insert into DPF_ORGANIZATIONS values ('Aarons Inc.');
insert into DPF_ORGANIZATIONS values ('AECOM');
insert into DPF_ORGANIZATIONS values ('Chesapeake Energy');
insert into DPF_ORGANIZATIONS values ('Clean Harbors Inc');
insert into DPF_ORGANIZATIONS values ('Cinemark Holdings Inc');
insert into DPF_ORGANIZATIONS values ('Ingredion Inc');
insert into DPF_ORGANIZATIONS values ('Owens-Illinois');
insert into DPF_ORGANIZATIONS values ('Transocean');
insert into DPF_ORGANIZATIONS values ('Urban Outfitters');
insert into DPF_ORGANIZATIONS values ('Werner Enterprises Inc');
insert into DPF_ORGANIZATIONS values ('Aqua America');
insert into DPF_ORGANIZATIONS values ('Weight Watchers');
insert into DPF_ORGANIZATIONS values ('Aqua America');

commit;

Make sure that the table is created and commited. Now we can proceed to use this table within our template. If you don't know how to run this script in SQL Developer, just skip this page and continue with the training.

Masking suppliers

We will change the name of the suppliers.

Select the SUPPLIER table (if not selected already)
Right click the NAME column and click Add function → Random lookup...

The function editor will open in which we can fill in the Random lookup details. The random lookup function consists of a lookup table and mapping the original columns to the columns in the lookup table. Because the lookup table could be temporary table that is not part of your imported tables, you have to fill in the details yourself. Lookup tables can also be within another schema, in that case you have to make sure that the user that you will use to execute the masking template has at least read (select) privilege to find the lookup table in the other schema. In that case you can prefix

For the Lookup table enter DPF_ORGANIZATIONS
Double click the empty Original column and select NAME
Now double click the empty Lookup column and enter: VALUE (this is the column of the lookup table, see the above script)
Click OK to add the Random lookup function and close the function editor

Great! This one was a bit harder, but you are making progress.

If you got stuck, check the following video. Make it full screen for a better viewing experience