{"id":5430,"date":"2014-07-02T10:01:33","date_gmt":"2014-07-02T14:01:33","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=5430"},"modified":"2017-11-06T13:41:29","modified_gmt":"2017-11-06T18:41:29","slug":"finding-data-errors-first","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/master-data-metadata-management\/finding-data-errors-first\/","title":{"rendered":"Using Data Templates to Find Data Format Errors"},"content":{"rendered":"

\"address_formats\"<\/a>Introduction<\/b><\/em><\/p>\n

In Working towards Data Quality<\/a>, we\u00a0defined data quality (DQ) as a\u00a0state in which\u00a0data can be used for operations. What makes the quality of data high is the paucity of errors. We also outlined\u00a0the responsibility of data architects, governance, and MDM<\/a> teams to define, identify, and correct bad data.<\/p>\n

In this article,\u00a0we introduce the concept of data templates in IRI software, and show how they can be used to spot errors of form and format in data.<\/p>\n

<\/b>
\nWhy Automation Matters<\/em><\/b><\/p>\n

Such errors would be obvious to\u00a0anyone reading\u00a0the data carefully. Of course, this is not a practical solution at the terabyte level.<\/p>\n

What the DQ\u00a0group needs are programs that look at data, in an application-independent manner, and identify records whose data are wrong on their face. As an example from\u00a0the previous\u00a0article, we mentioned the month of \u201cYraurbef,\u201d which is always wrong; this article was not written on June 31; and my salary does not contain \u201cabc\u201d.<\/p>\n

The program that does the checking can run at\u00a0the same time output is being produced for regular production purposes; i.e., data integration \/ transformation, migration \/ replication, reporting \/ provisioning, and data masking. Given large amounts of data, and the likelihood of not knowing everything to predefine or verify, the prudent approach is to run the jobs\u00a0iteratively until all the\u00a0errors are identified and corrected, or\u00a0otherwise noted.
\n<\/a><\/p>\n

Templates & Composites<\/b><\/em><\/p>\n

A template is a structure used by SortCL<\/a> programs (in the IRI CoSort<\/a> package) to describe a particular format of a source and target\u00a0data field, like dates<\/a>, as well as any literal characters that always occur within it. The syntax and semantics of this structure are well known to C programmers. Readers who are not familiar with C can quickly learn the options. Templates are a way to define a new, custom data type, or master data<\/a> format.<\/p>\n

Using these canned structures,\u00a0or library routines,\u00a0also allows the I\/O system to create and recognize data in\u00a0a precise format for quality assessment.\u00a0On input, values are extracted according to the\u00a0template you define, and are transferred into SortCL internal values. On output, the internal values are written in the proper form specified by the output template. Whether reading or writing, if the data are not in the specified form, an error message is given.<\/p>\n

Templates support simple and composite values. A composite value is one or more simple or composite values grouped together under a single name. The idea is to fix individual items relative to each other. The composite can also contain static values; i.e., character strings, special characters, and punctuation. Such static values allow the composite forms to conform to internal standards, and to be more familiar and understandable to end-users.<\/p>\n

Templates can be constructed in the IRI Workbench<\/a>\u00a0GUI (built on Eclipse\u2122)\u00a0or scripted directly in SortCL programs with a text editor. Importantly, existing templates can be centralized and re-used to maintain data conformity between applications. For those taking an enterprise view, templates are a corporate resource to be shared<\/a> \u2013 named, centrally maintained, and imported into applications as needed.<\/p>\n

<\/b>
\nTemplate Use Examples – Finding Format Errors
\n<\/em>
\n<\/b>Example 1. Finding Errors in Text and Dates<\/em><\/p>\n

\/TEMPLATE=(abc_dates = \"abc %b\/%e\/%y\")\r\n\/INFILE=dates\r\n     \/FIELD=(f1,STRUCTURE = abc_dates(month,day,year))\r\n. . . .\r\n<\/pre>\n
The abc_dates<\/em> template describes records that look precisely like this:<\/p>\n
4 characters |abc |\r\n3 character month name (Jan Feb ..) followed by a slash \u201c\/\u201d\r\n2 digit day of the month followed by a slash \u201c\/\u201d\r\n2 digit year \r\n<\/pre>\n
dates (input):<\/p>\n
abc Jan\/29\/13\r\naBc Dec\/ 2\/01\r\nabc Jun\/31\/69\r\nabc Ocp\/12\/71\r\n<\/pre>\n
Error Message                       Reason<\/strong>\r\n@ dates + 2:02 'B'!= b'             abc_dates\" 'B' not equal to 'b' at 2nd char\r\n@ dates + 3 date [1969 6 31]        abc_dates\" no such date\r\n@ dates + 4:05 month |Ocp\/12\/71|    abc_dates\" no such month\r\n<\/pre>\n
Note: Names of months (e.g., Jan, January …) and names of days (e.g., Tue, Tuesday \u2026)\u00a0are recognized and generated according to the LOCALE setting.<\/p>\n
Example 2. Finding Errors in Numeric Representation<\/em><\/p>\n
\/TEMPLATE = (Bill_fmt = \"%3i %9s %7.2f\")\r\n\/INFILE = Purchases\r\n\u00a0\u00a0\u00a0 \/FIELD = (Bill_fmt, STRUCTURE = Bill_fmt(Amt, Item, Price))\r\n\/REPORT\r\n\/OUTFILE= Results\r\n\u00a0\u00a0\u00a0 \/FIELD = (Amt, POSITION = 5, SIZE = 5.0, TYPE = NUMERIC)\r\n\u00a0\u00a0\u00a0 \/FIELD = (Item, POSITION = 12, SIZE = 9, TYPE = ASCII)\r\n\u00a0\u00a0\u00a0 \/FIELD = (Price, POSITION = 21, SIZE = 10, TYPE = NUMERIC)\r\n<\/pre>\n
Purchases (input) \u00a0 \u00a0 \u00a0Results (output)<\/strong>\r\n4 Carrots 3.29         4 Carrots 3.29\r\n.2 Onions 1.98         0 Onions 1.98\r\n10 Potatoes 3.26       10 Potatoes 3.26\r\n1O Chickens 13.42      1 Chickens 13.42\r\n10 Chickens 13,42      10 Chickens 13.00\r\n<\/pre>\n
Without looking at the next paragraph, the reader might want to try to explain why the output has bad results. The output looks right, and would be reported as such, or become part of any calculations. Fortunately, messages are being generated because the input data does not conform to the specified requirements of its template. These messages are displayed:<\/p>\n
@ Purchases + 2:01 value | .2 | \"Bill_fmt\"\r\n@ Purchases + 4:01 value | 1O | \"Bill_fmt\"\r\n@ Purchases + 5:17 value |13,42| \"Bill_fmt\"\r\n<\/pre>\n
On Record 2, Amt may only be an integer of length 3; on record 4, the \u201c0\u201d in \u201c10\u201d is really the capital letter \u201cO\u201d; and on record 5, the decimal point is really a comma \u201c,\u201d. This is easy to see when the messages are given; but\u00a0without them, the errors would not be found if they were nestled in a million records.<\/p>\n
<\/b>
\nNext Article – Finding Context Errors<\/em><\/b>
\n<\/strong><\/p>\n
In our next discussion<\/a>, we will talk about tools for discovering data that is application dependent \u2013- data that looks possible but is wrong in context.<\/p>\n
Topics include, finding:<\/p>\n