Maintenance Forms

Welcome to Maintenance Forms. This page covers points of interest for the various maintenance forms, which you can open from the Ribbon.

Form: Abbrev Expansion

This is one of the steps in the Normalization process of column names: Camel Case Split > Remove Non Alpha > Strip Filler Tokens > Synonym Normalization > Abbrev Expansion > Reduce To Canonical.

In this step tokens that are listed in the Abbrev Expansion form are expanded. For example, if you have a column named "SSN", the Camel Case Split step does nothing, the Remove Non Alpha step does nothing, the Strip Filler Tokens step does nothing, the Synonym Normalization step does nothing, but the Abbrev Expansion step finds "SSN" in its list and expands it to "social security number". The Reduce To Canonical step then reduces that to "social security number", which is the canonical name.

Form: Lookup Tables

This form is for entering information about Lookup Tables. A Lookup Table is a table that contains reference data that is used during the Auto-Assign process to help select the best converter for a column. If the data in your column contains the same values as a Lookup Table (ratio above MinMatchRatio), that is a strong signal that this is the best converter.

Lookup tables may contain a "Weight" column to support weighted lookups during the Apply Converters process. For example the US Cities lookup table has a Weight column that contains the population of each city. This makes it so that for example Los Angeles (population: 12 million) will be randomly chosen more often than Surprise, AZ (population: 155 thousand).

Weighted lookups are expensive computationally, so we provide a "PreFill Cumulative Weight" button to do that work once, ahead of time. The code will add a CumulativeWeight column if it doesn't yet exist. For linked lookup tables, you have to create it yourself.

Note: MinMatchRatio can be tuned per lookup column. If the lookup table contains most values that can occur in such database column, then you can set MinMatchRatio to a high value like 0.9. If the lookup table contains only some of the values, then you can set MinMatchRatio to a lower value like 0.3.

The "Canonical name" column is a locked field and is populated by the same Normalization process that is used for column names.

That canonical name does double duty, which is easy to miss: besides matching columns to this lookup, it joins the vocabulary that the Normalization process reduces column names to — the same list that Column Names contributes to. Registering a lookup whose canonical name is "address" is what makes Billing Address, Primary Residence Address and Shipping Address all reduce to address, for every column in the database, not only for lookup matching. Because Normalization runs during Scan, re-scan after adding a lookup table for existing columns to pick up the new canonical name.

The "DataTypeID" column is especially important for linked lookup tables that may not be available at all times. Of course they will be linked during the Apply Converters process.

Some of the lookup data comes from our friends at SimpleMaps: US, UK and Canada, and from other sources.

Form: Binary Lookup Tables

Anonymize includes a utility Binary Lookup Tables which can be used to populate such tables. If the table has an OriginalSource column, it will be populated as well when loading images or files. The Edge Browser control is used to display the files. This works for images and PDF documents, but not necessarily for every file type.

Form: Detection Patterns

This form supports three categories of patterns: regular expressions, data ranges, and content analyzers.

Regular expressions are matched against a sample of the column's values during the Auto-Assign process; the fraction of values that match is the converter's evidence. VBA's RegExp-compatible syntax is expected, matching is case-insensitive, and patterns should be anchored (^...$) so they match whole values rather than fragments. A pattern with a blank Country applies to every country; a pattern with a Country is used only when it matches the main form's Country setting. The evidence a converter collects here is then held against its minimum in Country Converters — note that both tables have a MinMatchRatio column, doing different jobs: there it is the converter's minimum evidence; here it belongs to the analyzer rows described below.

Content analyzers are the rows whose Pattern is a bracketed name, such as [IsHtmlOrRichText], [IsXml] and [IsJson]. These are not regular expressions — each is a special value that routes to a built-in analyzer (an XML well-formedness check, a JSON parse, an HTML/Rich Text tag test), so do not edit the bracketed names. For these rows MinMatchRatio is a firing threshold: when at least that fraction of the sampled values passes the analyzer, the column is claimed outright and anonymized with Lorem text in the matching format.

Data ranges use MinValue and MaxValue; the Country and Pattern columns are ignored. DataType scopes a range to a data type: the "Percentage 0..1" range applies only to Decimal columns, so an integer 0/1 flag column does not match it. Blank means any type. When a converter has several ranges they are alternatives, not cumulative — only the best-fitting one counts — and for Random Number the winning range is proposed as the Low/High arguments, which keeps the output within plausible bounds without revealing the column's real minimum and maximum.

Note: the Postal Code patterns remain in this table but no longer vote (Postal Code's DetectionMode is None — see Country Converters for why five bare digits are not evidence). They still serve a purpose: they are the country postal shapes that the column-name-driven assignment confirms values against. Editing them changes what that confirmation accepts, not the voting.

Form: Filler Words

Column names often contain filler words that don't add meaning. They should be stripped out in the Normalization process. Examples include hungarian prefixes or suffixes, and other noise words that don't add meaning.

Treatment-bearing tokens should not appear in this table, such as: number, num, value, percent, amount, name, date. As an example, Tax Amount, Tax Percent, and Tax Number require both tokens, or they will be reduced to "Tax" and be ambiguous.

Form: Tracking Columns

Tracking columns are the likes of CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, etc., used for recording who created or updated a record, and when.

Naming a column here has two effects. The first, and the one you will notice most, is that the column is left alone: the Scan process marks it with the NoConverterReason "Audit/tracking column" and no converter is auto-assigned. That is deliberate — a tracking column records who touched the record, not the subject's own data, so it identifies nobody and is often useful to keep intact while testing. You can still assign a converter manually if a particular column does hold something sensitive, such as an operator's full name.

The second effect is subtler. When determining if a table is a lookup table (a.k.a. "closed-vocabulary code table"), the tracking columns are ignored. Criteria used include: narrow table, unique text column, and if no unique index, unique values in the text column.

Tables that are determined to be lookup tables are not auto-anonymized, for good reasons: they typically do not contain sensitive information, changing the texts would break the ID - Label correspondence, and break realism. You can still manually assign a converter if you wish.

Form: Column Names

The Column Names form maps a column's canonical name to a Converter. It does two jobs at once, and the first is easy to miss: the names entered here — together with those in Lookup Tables — are the vocabulary that the Normalization process reduces column names to. Registering "phone number" is what makes Phone, PhoneNumber, Telephone, BusinessPhone, AltPhone, SecondaryPhone, Work Phone and a dozen other spellings all arrive at the canonical name phone number. One record covers them all.

Reduction follows the English head rule: a name collapses to a canonical only when the canonical is the trailing part of the name. "Business phone number" ends with "phone number" and becomes phone number; a canonical at the front or in the middle is treated as a modifier and left alone, so "Fax Time" stays as it is and "Phone Carrier Name" is not turned into a phone number. When several canonicals could apply the longest one wins, so "email address" beats "address". The rule is positional, so a name that ends with a canonical by coincidence is reduced too — "Do Not Fax" becomes fax number — but the data type and value checks downstream keep such a column from actually being anonymized as a phone number.

Matching during Auto-Assign is exact, which is why the reduction matters. Enter records in canonical form: "phone number", not "PhoneNumber". Note that Normalization runs as part of the Scan process, so after adding a record you must re-scan for existing columns to pick up the new canonical name — re-running Auto-Assign alone is not enough. If a column is still not matched, check its canonical name first; the remedy may be a Filler Words or Abbrev Expansion record so that the name reduces as you expect.

For Phone Number and Postal Code a matching record is decisive: Anonymize confirms that the sampled values fit (a valid area code for phone numbers, the country's postal shape for postal codes) and then assigns the converter directly. This is what rescues a phone column stored without decoration — bare digits such as 6025551212 look like any other ten-digit number to the Data Values voter, so without the name the column would be anonymized as a generic code. It is also the only way Postal Code is ever assigned; see Country Converters.

For every other converter the record is a boost: it strengthens a converter that the data already supports, which settles close calls. It cannot rescue a converter the data contradicts — if the sampled values carry no evidence for it, no boost will make it win. VoteWeight sets the size of the boost; leave it at 1.0 unless you want one record to outrank another.

The shipped records are worth studying. The ordinal names (sort order, display order, sequence, rank, line number, section number) map to Shuffle, because those values mean nothing outside their relative order. "email address" maps to Email, which keeps such a column from being matched against an address lookup. "company name" maps to ColumnName_ID. Use the Notes column to record why you added a record.

Form: Country Converters

This form sets a minimum evidence bar for converters that recognize columns by their data values. During the Auto-Assign process, the Data Values voter samples each column and measures what fraction of the values match a converter's detection patterns — for example, how many values look like email addresses. A Country Converters record says: do not accept this converter unless at least MinMatchRatio of the sampled values match. A column that falls short is passed over for that converter and falls through to a more generic one. This is what keeps a Notes column that happens to contain a few email addresses from being anonymized as an Email column. A blank Country applies the record to all countries.

You can adjust MinMatchRatio in small steps: raise it if a converter claims columns it should not, lower it if it misses columns it should claim. Leave the existing records in place — deleting one removes the safety bar entirely, and that converter can then win on the thinnest of evidence. Add a record when a value-recognized converter (Email, Phone Number, Lorem) over-claims in your data, typically when adding support for a new country.

Do not add records for converters that do not recognize columns by value. You can tell the two kinds apart by the DetectionMode control in the Converters form: only converters whose DetectionMode is Semantic consult Country Converters. Postal Code is the instructive example — its DetectionMode is None, and deliberately so: a US ZIP code is just five digits, indistinguishable from a job number or a purchase order number, so Anonymize never assigns Postal Code from values alone. Instead it requires the canonical column name to say "postal code" (or a synonym) and then confirms that the values fit the country's postal shape. A Country Converters record for Postal Code therefore has no effect. The same holds for any converter whose format is too plain to serve as evidence by itself.