Fix PMA for US Addresses

What Does It Do?

The OUD-DS3 best practice is for primary metro area (PMA) to be aligned with the zip5 for US addresses.

This integrity check identifies address records where the primary metro area (PMA) address attribute is not in alignment with the definition for the address’s zip5 so that the PMA address attribute can be updated. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-ADDRESS-Misaligned Zip5 and PMA(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries all active non-UM US address records where the PMA address attribute is not in alignment with the PMA defined for that zip5 range or where the PMA address attribute does not exist but should.

These addresses need to have any existing PMA attribute removed in DART, and then the addresses can be re-run through the built-in DART validation tool to re-add the correct PMA attribute.

Resources and References

S:\IDS\MES\SQL Scripts\DATA QUALITY\ADDRESS\

Snapshot GUID: BBDW.FACT_ConstituentAddress.ConstituentAddressSystemID