Data standard inspection tool_1.Overview

Introducing a data standard inspection tool that can be helpful for field DAs (Data Architects) who suffer from repetitive manual work in data standardization work. By reducing manual time, you can focus on more productive tasks and eliminate or reduce overtime.

This tool was created with Excel VBA. (reference: Excel VBA Course(1): Overview of Excel VBA)

1. Overview of Data Standards Check Tool

Data standardization, one of the tasks of a data architect, is quite a daunting task.

Collect the current property name (logical name, physical name), data type, and length from the database table/column comment, table definition, etc., separate the semantic unit into words, and bind columns with the same meaning to the same logical name, physical name, and data. The process of defining a single term with type and length requires many manual repetitions.

(Note. This article does not explain the detailed concept of data standardization. Please refer to the URL below.)
https://dataonair.or.kr/db-tech-reference/d-guide/da-guide/?mod=document&uid=269

Data standardization can be summarized as a process of reviewing basic dictionaries and current attribute names repeatedly, creating word/term/domain dictionaries in Excel sheets, and supplementing and finalizing them through review with standard managers.

In this process, several detailed tasks are executed, such as:

• Attribute logical name change
• Change word physical name
• Assignment/deactivation of word attribute classifier
• Specify domain classification name for attribute classifier
• Register two (or more) words as compound words
• Specify synonym relationships: make only one word canonical and other synonyms non-canonical
• Change the domain's data type, length
• etc…

For the current property name and data type/length, the above operations are repeated to 1) use standard words, 2) designate standard domains, and finally 3) make standard terms and standardize them.

Most data architects (DAs) who perform data standardization work in the field are doing manual work using Excel. There are products of various companies for metadata management solutions that manage data standards, but most of these products focus on the function of managing the standard data dictionary after it is created rather than the function of creating a standard data dictionary.

I also went through a lot of difficulties while performing data standardization work at various clients. In 2012, I first tried tooling, and I developed a tool with basic functions in April 2016 by constantly developing it in my spare time. The tool was developed using Excel VBA, and the screen is as follows.

This tool can be found in the github repository below.

https://github.com/DAToolset/ToolsForDataStandard

Alternatively, you can download directly from this URL.

https://github.com/DAToolset/ToolsForDataStandard/blob/main/Property standard check tool_v1.33_20210606_1.xlsm?raw=true

---

In the next article, we will take a look at the composition of the data standard dictionary and the standard checking function.

<< List of related articles >>

• Data standard inspection tool_1.Overview
• Data standard inspection tool_2.1. Screen composition, 2.2. Standard inspection function
• Data standard inspection tool_2.3. Data standard dictionary composition
• Data Standard Check Tool_3. Standard Check Case Results
• Data standard check tool_4.Attachment
• How to Fix Data Standard Check Tool_v1.33 Error “Not enough memory”
• Data Standard Check Tool Description Contents , Download