Data conventions, contents, and definitions

Elements in a data warehouse have often been transformed or manipulated to mean something to the business. Knowing where it came from and how it originated can be a daunting learning curve for anyone new to data warehousing and business intelligence.

Meta data – Data About Data

The ability to know if a field is expected to hold text, numbers with decimal places, whole numbers, or any combination of different data conventions can be a struggle. Often information builders are required to format business reports for specific business needs. If the data types are not consistent the challenge faced by the developer of the business report can be daunting. A way to ensure this consistency is to build a data dictionary.

Data Dictionaries

Data Dictionaries contain the information about the elements or datum in the data warehouse. Each element is defined by its data type, the location where it can be found, and the location that it came from. Often the data dictionary includes the logic when a field is derived. The logic can be business logic or developer logic but it must be defined. The data dictionary also include the physical location, such as a server DNS (domain name system) name or the IP address. The database name, the instance, the table, and the field name are particularly important to the developer building reports. This information is even more important if the developer must cross multiple systems to gather the necessary pieces of information for the report. A database administration should be responsible for keeping this important document up to date and accurate. Often the database administer provides the the organization and conventions of one or more databases. Typically each database has its own data dictionary. It is a good practice to have one owner of each data dictionary.

Multiple Definitions

In a business one term may mean something entirely different to unrelated departments. For example, the term “sale” can be defined differently by different organizations within a company. To the marketing department the term may mean selling at a discount, to the sales department it may mean the closure of a contract to obtain goods, and yet to the accounting department it may mean the recognition of revenue. The need for a data dictionary becomes apparent when given the multiple definitions of a single term by different users within the company. Without a data dictionary it is often difficult to build accurate and consistent business reports that can be shared across the organization. Readers will find the Data Warehouse Terminology, Data Warehouse Concepts articles are also interesting for database warehouse administration.