Since we launched MapWorks (and even earlier as part of our data consulting practice) we’ve talked to many people who are searching high and low for a cost-effective answer their metadata chaos.  Spreadsheets and quick/dirty “solutions” to data mapping and lineage aren’t solutions when they can’t be maintained. When we ask about the current state of affairs we get answers ranging from “Yeah, we should really do that…we don’t know where anything is!” to “We tried that a while ago . . . and it’s not going very well.” and everything in between. After the weeping and cursing subsides, we start talking about how they can get a handle on their metadata.

One of the first questions we get is about how to get existing metadata into a tool like MapWorks.  Most data shops already have stockpiles of metadata – spreadsheets, a tool for the business users, another tool for the technical teams. There are usually dozens of systems, each with potentially hundreds of data points and it’s a big task to get all of that into a new tool. Manually entering all that information into a new metadata repository is a costly and time consuming project, not to mention the time it will take to periodically reconcile what currently exists in your organization’s databases to what has been documented in your metadata repository. We’ve been there, we know that pain and that’s why we’ve created a simple, three-step metadata import processes that will make it easier. What takes hours or days takes now takes minutes with some simple steps.

Three simple steps for importing your existing metadata into MapWorks:

  1. Download the template spreadsheet from the Export/Import menu in the MapWorks Mapping module
  2. Fill in the information about your data structures
  3. Upload the spreadsheet to MapWorks

To assist you with getting the metadata for the spreadsheet, we created a series of SQL queries that you can run to automatically extract the metadata directly from your databases. Just copy the results of the query and paste them into the spreadsheet and voilà!

You can access the SQL scripts from this link.

These scripts will need to be run using an account/login that has access to the necessary metadata tables or views for the desired databases. This will vary according to the database being used. If you run into issues or are looking for similar queries for other databases, please drop us a line at answers@themapworks.com and we will see what we can do.

Also, note that these scripts will extract the data used by MapWorks starting with the 05/23/2017 MapWorks release. If a new metadata format is added to MapWorks (i.e. new columns added to the template spreadsheet), we’ll keep the scripts updated.

Avoiding Duplicates

Not sure if something has already been imported into MapWorks? No problem. When you import the information, MapWorks will compare what you are importing to what already exists within MapWorks and allow you to either import new items or update existing items in MapWorks. This is very useful for a periodic reconciliation of what exists in your organization to what is documented in MapWorks. Just pull the metadata from your organization’s databases and upload it to MapWorks. The differences will be highlighted for you.

For ongoing maintenance, we recommend that you use MapWorks as a tool to capture any upcoming changes to your metadata and mappings prior to them being implemented. You can then use the MapWorks Releases module  to track when those changes are implemented and keep a historical record of the changes that have occurred over time. By doing this, you will be proactively updating your metadata repository in MapWorks and it will minimize the need to reconcile the changes after the fact.