CHAPTER 9
Metadata is an interesting topic because every tool space in the data warehouse arena including business intelligence (BI) tools, ETL tools, databases, and dedicated repositories claims to have a metadata solution, and many books are available to advise you on the best metadata strategies. Yet, after years of implementing and reviewing data warehouses, we’ve yet to encounter a true end-to-end metadata solution. Instead, most data warehouses have manually maintained pieces of metadata that separately exist across their components. Instead of adding to the metadata hoopla, this chapter simply covers the portions of metadata that the ETL team needs to be aware of—either as a consumer or a producer. We propose a set of metadata structures that you need to support the ETL team.
PROCESS CHECK Planning & Design:
Requirements/Realities → Architecture → Implementation → Release to Ops
Data Flow: Extract → Clean → Conform →, Deliver
Because the ETL system is the center of your data warehouse universe, it often assumes the responsibility of managing and storing much of the metadata for the data warehouse. One might think that there is no better place than the ETL system for storing and managing metadata because the environment must already know the specifics of all data to function properly. And the ETL process is the creator of the most important metadata in the data warehouse—the data lineage. The data lineage traces data from its exact location in the source system and documents precisely what transformation is done to it before it is finally loaded. The data lineage includes the data definition of the source system database and also of the final resting place in the data warehouse. If you are using an ETL tool, attributes other than the data lineage can also live in the ETL environment. But can the ETL environment really capture and manage all metadata in the data warehouse? No way.
If you’d like to explore data warehouse metadata in more detail, a noteworthy text is Metadata Solutions: Using Metamodels, Repositories, XML, and Enterprise Portals to Generate Information on Demand by Adrienne Tannenbaum (Addison Wesley 2002). We want to start by trying to define exactly what is meant by metadata.
Defining Metadata
A leading cause for the difficulty behind metadata implementations is that the exact definition of metadata is ambiguous, and defining exactly what metadata is a very difficult task. In 1998 we tackled the definition of metadata in the Data Warehouse Lifecycle Toolkit book. In looking over those words, we still find them surprisingly relevant. Here’s what we wrote:
Metadata—What Is It?
Metadata is an amazing topic in the data warehouse world. Considering that we don’t know exactly what it is, or where it is, we spend more time talking about it, more time worrying about it, and more time feeling guilty that we aren’t doing anything about it. Several years ago, we decided that metadata is any data about data. This wasn’t very helpful, because it didn’t paint a clear picture in our minds as to what exactly this darn stuff was. This fuzzy view gradually clarified and recently we have been talking more confidently about the back room metadata and front room metadata. Back room metadata is process related and guides the extraction, cleaning, and loading processes. Front room metadata is more descriptive and helps make our query tools and report writers function smoothly. Of course, process and descriptive metadata overlap, but it is useful to think about them separately.
Back room metadata presumably helps the DBA bring the data into the warehouse and is probably also of interest to business users when they ask where the data comes from. Front room metadata is mostly for the benefit of end user, and its definition has been expanded to not only be the oil that makes our tools function smoothly but a kind of dictionary of business content represented by all data elements.
Even these definitions, as helpful as they are, fail to give the data warehouse manager much of a feeling for what he or she is supposed to do. But one can apply a traditional IT perspective to metadata. At the very least, we should:
Make a nice annotated list of all of it
Decide just how important each part is
Take responsibility for it or assign that responsibility to someone else
Decide what constitutes a consistent and working set of it
Decide whether to make it or buy it
Store it somewhere for backup and recovery
Make it available to people who need it
Quality-assure it and make it complete and up to date
Control it from one place
Document all of these responsibilities well enough to hand this job off (soon)
The only trouble is that we haven’t really said what metadata is yet. We do notice that the last item in the preceding list really isn’t metadata; it’s data about metadata. With a sinking feeling, we realize we probably need meta meta data data.
Source System Metadata
To understand this better, let’s try to make a complete list of all possible types of metadata. We surely won’t succeed at this first try, but we will learn a lot. First, let’s go to the source systems, which could be mainframes, separate nonmainframe servers, users’ desktops, third-party data providers, or even on-line sources. We will assume that all we do here is read source data and extract it to a data-staging area that could be on the mainframe or could be a downstream machine.
Source specifications:
Repositories
Source schemas
Copy books
Proprietary or third-party source schemas
Print spool file sources
Old format for archived mainframe data
Relational source system tables and DDL
Spreadsheet sources
Lotus Notes databases
Presentation graphics (for example, PowerPoint)
URL source specifications:
Source Descriptive Information:
Ownership descriptions of each source
Business descriptions of each source
Update frequencies of original sources
Legal limitations on the use of each source
Access methods, access rights privileges, and passwords for source access
Process Information:
Mainframe or source system job schedules
The COBOL/JCL or C or Basic or other code to implement extraction
The automated extract-tool settings if we use such a tool
Results of specific extract jobs, including exact times content and completeness
Data-Staging Metadata
Now let’s list all the metadata needed to get data into a data-staging area and prepare it for loading into one or more data marts. We may do this on the mainframe with hand-coded COBOL or by using an automated extract tool. Or we may bring the flat file extracts more or less untouched into a separate data-staging area on a different machine. In any case, we have to be concerned about metadata describing
Data Acquisition Information:
Data transmission scheduling and results of specific transmissions
File usage in the data-staging area, including duration volatility and ownership
Dimension Table Management:
Definitions of conformed dimensions and conformed facts
Job specifications for joining sources, stripping out fields, and looking up attributes
Slowly changing dimension policies for each incoming descriptive attribute (for example, overwrite create new record or create new field)
Current surrogate key assignments for each production key, including a fast lookup table to perform this mapping in memory
Yesterday’s copy of a production dimension to use as the basis for Diff Compare
Transformation and Aggregation:
Data-cleaning specifications
Data enhancement and mapping transformations (for example, expand abbreviations and provide detail)
Transformations required for data mining (for example, interpret nulls and scale numerics)
Target schema designs, source to target data flows, and target data ownership
DBMS load scripts
Aggregate definitions
Aggregate usage statistics, base table usage statistics, and potential aggregates
Aggregate modification logs
Audit, Job Logs, and Documentation:
Data lineage and audit records (where EXACTLY did this record come from and when)
Data transform run time logs, success summaries, and time stamps
Data transform software version numbers
Business descriptions of extract processing
Security settings for extract files extract software and extract metadata
Security settings for data transmission (that is, passwords certificates)
Data-staging area archive logs and recovery procedures
Data-staging archive security settings.
DBMS Metadata
Once we have finally transferred data to the data warehouse or data mart DBMS, another set of metadata comes into play:
DBMS system table contents
Partition settings
Indexes
Disk-striping specifications
Processing hints
DBMS-level security privileges and grants
View definitions
Stored procedures and SQL administrative scripts
DBMS backup, status-backup procedures and backup security.
Front Room Metadata
In the front room, we have metadata extending to the horizon, including
Business names and descriptions for columns tables groupings and so on
Precanned query and report definitions
Join specification tool settings
Pretty print tool specifications (for relabeling fields in readable ways)
End user documentation and training aids, both vendor supplied and IT supplied
Network security user privilege profiles
Network security authentication certificates
Network security usage statistics, including log on attempts access attempts and user ID by location reports
Individual user profiles with link to human resources to track promotions transfers resignations that affect access rights
Link to contractor and partner tracking where access rights are affected
Usage and access maps for data elements, tables, and views reports
Resource charge-back statistics
Favorite Web sites (as a paradigm for all data warehouse access)
Now we can see why we didn’t know exactly what metadata was all about. It is everything, except for the data itself. All of a sudden, data seems like the simplest part. In a sense, metadata is the DNA of the data warehouse. It defines all elements and how they work together.
So, how do you capture and manage these forms of metadata? You don’t. At least the ETL team doesn’t. Over the past few decades, consortiums, alliances, committees, organizations, and coalitions have been formed to solve the metadata quandary. To this day, no universal solution exists. We have found that as an ETL team member you need certain metadata to do your job, and that it is convenient to focus on selected items from the preceding list and organize them into three major categories:
1. Business metadata. Describing the meaning of data in a business sense
2. Technical metadata. Representing the technical aspects of data, including attributes such as data types, lengths, lineage, results from data profiling, and so on
3. Process execution metadata. Presenting statistics on the results of running the ETL process itself, including measures such as rows loaded successfully, rows rejected, amount of time to load, and so on
In addition to the three categories of metadata, you need to consider another aspect of metadata: standards. Standards are another attempt by IT to make work throughout your organization consistent and maintainable. In Chapter 10, we define the role of the metadata librarian and propose a set of responsibilities for this person. Your organization most likely has many standards in place, and you can adopt specific data warehouse and ETL standards from the recommendations found throughout this book. If you seek to further investigate metadata standards, we’ve provided sources for your perusal in the next section.
As you read this chapter, please refer to Figure 9.1, showing the three main categories of ETL system metadata, together with each of the separate metadata tables discussed in the text.
Figure 9.1 Metadata sources in the back room of the data warehouse.
Here is a comprehensive list of the places so far in this book where we have urged you to collect and use metadata:
Origins and processing steps for each staged data set (Chapter 1)
Metadata repository as an advantage of the vendor-supplied ETL tool (Chapter 1)
Need for metadata architecture: source tables, cleaning, and processes (Chapter 1)
Presenting useful metadata to end users (Chapter 1)
Extract transformations applied (Chapter 2)
Compliance metadata (Chapter 2)
XML data descriptions of metadata (Chapter 2)
Lack of metadata in flat files (Chapter 2)
Impact analysis metadata (Chapter 2)
Planning for building metadata describing lineage, business definitions, technical definitions, and processes (Chapter 2)
Logical data map (Chapter 3)
Capturing calculations for derived data during extract (Chapter 3)
Source database descriptions (Chapter 3)
ETL tools reading ERP system metadata (Chapter 3)
Results of data profiling (Chapter 4)
Error event tracking fact table (Chapter 4)
Audit dimension (Chapter 4)
Column survivorship (conforming results) table (Chapter 4)
Surrogate key highest value (Chapter 5)
Aggregate navigator data (Chapter 6)
Process data to kick off OLAP cube building (Chapter 6)
Bulk loader control file (Chapter 7)
Metadata supporting recovery processes (Chapter 8)
Job schedule metadata (Chapter 8)
Database connection information (Chapter 8)
ETL system parameters (Chapter 8)
Job dependencies (Chapter 8)
Job operational statistics such as performance and resource use (Chapter 8)
MetaData repository reporting (Chapter 8)
Table purge policies (Chapter 8)
We hope you aren’t too dismayed by these long lists. The rest of this chapter makes a serious attempt to propose specific metadata structures for tracking this kind of information!
Business Metadata
The assignment of who is responsible for business metadata in the data warehouse is often argued. Some say it is the responsibility of the data warehouse business analyst and should be created during the requirements-gathering process. Others believe the source system business analyst should create business terms because most data warehouse attributes originate in the source systems. Yet others think it is part of the data modeler’s tasks to create and maintain business metadata because it is a natural part of the logical data model.
It’s not up to you to settle those arguments, but there is some business metadata that the ETL team influences and must be maintained to accurately reflect reality. The ETL team should not be responsible for generating business metadata but must communicate changes that need to be applied to the appropriate personnel. From the ETL perspective, business metadata is proxy metadata. Proxy metadata is obtained from one system and made available to another without any explicit manipulation. Some business-intelligence tools are designed to look into ETL repositories to get business definitions to present to its users—making the tool a one-stop shop for data and data’s metadata.
To keep things interesting, we must make you aware that the data warehouse can be in a situation where different business definitions are defined for the same attribute. Remember, by design, that data in the data warehouse is sourced from multiple systems, and each system can potentially have a different definition for the same attribute. For example, the marketing department defines a customer as anyone who has a registered account with the company, while the sales department might consider only persons who have actually made purchases to be customers. As a recommended practice, the data warehouse manager should bring all of the owners of an overlapping element into a room and have them agree on a single definition from an enterprise standpoint. We describe this process throughout the book as conforming business definitions, labels, and measures of the enterprise. That meaning might be from one of the source systems or an entirely new one. The enterprise definition is stored in the data warehouse and in ETL tool.
The ETL team is part of the back room of the data warehouse and should not get involved in creating business metadata. However, you should understand the purpose of the data you are working with and review business definitions as you need them.
Business Definitions
Often, the data warehouse team gets caught up in designing the architectural database for query performance and optimizing the ETL process for load performance. Once the database is designed and loaded, the team concentrates on the business intelligence of the data warehouse, making elegant user interfaces and slick graphical reports. But to the business, the most important ingredient in the data warehouse recipe is the definition of the elements available to them. If the user cannot make sense of data, as sophisticated as it may be, it has absolutely no value to the organization.
Business definitions are a crucial requirement to the completion of the data warehouse. Not only are end users dependent on business definitions; the ETL team needs business definitions to give context to the data they are loading. You will notice how important the business definitions are to the ETL team if you attempt to present them with the data model and data lineage before the business definitions are complete. If you rush to complete the data lineage so the ETL development can begin, you’ll be forced to continually explain the purpose of the main data elements in the data model to the ETL team before they have enough information to work on them. Therefore, shift the development priorities away from expediting the data lineage and to concentrating on the association of the business definitions with the data warehouse data elements before the data lineage is handed off to the ETL team. A simple business definition matrix includes three main components:
Physical table and column name. The business interpretation of data elements in the data warehouse is based on the actual table and column names in the database. The physical names need not be presented to end users if the BI tool presents only the business names and completely hides the physical instance of the data structures. However, the ETL team deals only with physical names and needs this information to associate the business definitions to the appropriate data elements.
Business column name. The database stores data elements in technical terms consisting of prefixes, suffixes, and underscores. The business community needs a translation of the technical names to names that make sense and provide context. For example, the business name for EMP_STTS_CD might be Employee Status Code or just Employee Status. We discourage the use of cryptic names. Remember, the business name is how the column is represented in the BI tool. Furthermore, the business name often becomes column and row headings in user reports.
Business definition. A business definition is one or two sentences that describe the business meaning of an attribute. Each attribute in the data warehouse must have a business definition associated with it. If the business cannot define the attribute, it usually indicates that the attribute has no analytic value and probably does not need to be stored in the data warehouse. If the business demands that the attribute remain in the data warehouse, a business definition must be defined for it.
The business definition matrix can be as simple as a three-column spreadsheet. However, you should strive to make this particular metadata as centralized and sharable as your technical environment allows. Virtually all major ETL tools support capturing and storing business metadata. The ETL tool should work with your data-modeling tool and database to obtain business definitions and with your BI tool to present business names and definitions to your end users.
Source System Information
The ETL team needs to know every intimate detail of each table it accesses for the data warehouse. Imagine that you need to fill an open position on your team. Before you hire someone, you have your HR department find candidates, prescreen them, introduce them to you, and arrange for formal interviews so you can ensure they are the right fit for the position. During the interviews, you identify any weaknesses that might need some work before candidates are deemed satisfactory for the position.
When you populate a data warehouse, the data modeler creates the position; the data warehouse architect finds and prescreens the source. Then data must be analyzed thoroughly to identify its weaknesses, and a plan of action must be initiated to make data satisfactory enough to be included in the data warehouse. Some data will be perfect; other data elements will need some transformation or be rejected because of inadequate quality.
When you analyze source systems, you need certain pieces of metadata. At a minimum, you need the following metadata attributes:
Database or filesystem. The name commonly used when referring to a source system or file. This is not the technical server or database instance. Names such as Sales database or Inventory Management System are common values for this piece of metadata.
Table specifications.The ETL team needs to know the purpose of the table, its volume, its primary key and alternate key, and a list of its columns.
Exception-handling rules. You must be informed of any data-quality issues and advised on how they should be handled by the ETL process.
Business definitions.The infamous yet rarely available business definitions. Do your best to have these provided to you. These one-to-two-sentence definitions are invaluable when you are trying to make sense of data.
Business rules. Every table should come with a set of business rules. Business rules are required to understand the data and to test for anomalies. Every business rule must be tested, and each exception to a rule must be documented and resolved by the source system or the ETL process. Forms of business rules include an account of when a table receives new rows, updates, or deletions. If you are lucky, business rules are enforced in the source database management system (DBMS) by way of referential integrity, check constraints, and triggers.
Investigating source systems takes a substantial amount of time during the data-analysis phase of the data warehouse project. A lack of source system metadata incurs excessive research and troubleshooting by the data warehouse team. To curb cost overruns, all source system metadata must be available to the ETL team before the development of any ETL process.
Data Warehouse Data Dictionary
When we refer to the data dictionary, we are not referring to the DBMS catalog. The data warehouse data dictionary is a list of all of the data elements in the data warehouse and their business descriptions. Similar to the source system business definitions, the data warehouse data dictionary contains the physical table and column names and the business names and definitions. In the case of data warehouse business metadata, spreadsheets are insufficient. Many data warehouse environments depend on the ETL repository to store the data dictionary because BI tools are designed to look there to obtain metadata for presentation.
Many BI tools are designed to work cohesively with ETL metadata repositories. When you are selecting your toolset, make sure that your ETL tool has an open repository that can be read by any query tool or at least comes with an adapter or broker for this purpose.
Logical Data Maps
The logical data map is the lifeline of the ETL team. Read Chapter 3 for detailed information about logical data maps from an ETL work-specification perspective. From a metadata perspective, the logical data map consists of the source-to-target mapping that explains, logically, exactly what happens to the data from the moment it is extracted from its origin to when it is loaded into the data warehouse.
The logical data map is a crucial piece of metadata. The ETL team first uses the document as a functional specification to create the physical ETL jobs, then again to validate the work with the end users. The document provides guidance when questions arise during user-acceptance testing (UAT). It is also used when data goes through quality-assurance (QA) testing, when the ETL team provides a walkthrough of each mapping with the QA team. Finally, the ETL team reviews the document with the DBA team to provide information about the data transformations so they can support the processes in the event of a failure.
CROSS-REFERENCE Refer to Chapter 3 for the exact metadata elements created in the logical data map and recommendations on how to create, maintain, and utilize the information.
Technical Metadata
Technical metadata serves many purposes and is the most interesting type of metadata to the ETL team. Technical metadata can encompass everything from column names and data types to storage and the configuration of your RAID arrays. As a member of the ETL team, you needn’t be too concerned with hardware configurations and the like. Most interesting are the physical attributes of the data elements in all databases involved in the data warehouse. To get essential metadata, seek out the physical data model of each of your source systems and the target data warehouse. If your sources include flat files, you need the file layouts for each file you will be working with.
System Inventory
The ETL team must have access to and thoroughly understand the technical metadata of each system in the data warehouse environment to accurately build the physical ETL jobs. The technical definition of data is probably what technicians think of first when they are asked about metadata. After all, that really is data about data—it is the container and the structure of the data. The ETL team must be aware of data definitions for at least three environments:
Source databases
Staging-area tables (for example, extracted, cleaned, conformed, prepared for delivery)
Data warehouse presentation area
It is possible that an entity relationship diagram is provided for each environment. At a minimum, a listing that includes the following elements for each system is required.
Tables. An exhaustive list of tables or files that are—or might be—required during the extract and load process. Often, only source system tables in the logical data mapping are provided. Yet, there are associative tables that are usually not specified but are required. Whenever the source system has many-to-many relationships, a well-designed system has associative tables to enable the relationship.
Columns. For each table, you will need a list of the required columns for your data mapping. Hopefully, the source system DBA can provide a list of only the columns that you need. It works best if the column listing excludes unnecessary columns.
Data types. Each column is defined with a data type. Data types vary among different database systems. Luckily, most dedicated ETL tools implicitly convert equivalent data types. For example, an INTEGER from SQL Server automatically becomes a NUMBER when it is loaded into Oracle. Be aware that a DBA can define custom data types known as User Defined Datatypes. User Defined Datatypes are based on the database’s core data types but can extend their definition to include things such as column length, whether the data type can accept NULL values, or whether the data type contains special characters, for example, telephone numbers.
Relationships. Relational databases are designed to support referential integrity (RI). RI is enforced by relationships designed between tables to ensure unique and consistent data entry. Relationships are outlined in data models by linking foreign keys (FK) in a table to primary keys (PK) in another.
Data Models
Data models in the form of physical schema diagrams (either normalized or dimensional) are really just a graphical display of metadata but not particularly metadata itself. However, they can be an invaluable asset to the ETL team arsenal because they enable you to quickly identify and confirm relationships at a glance. Even though table joins are depicted in logical data mapping, you cannot count on it being perfectly complete. We recommend that you hang the physical schema diagrams from all of the source systems (and the data warehouse) on the wall of your office. Having physical schema diagrams hung around you alleviates sifting through rolls of diagrams piled in a corner. Furthermore, the hung diagrams make an instant reference point that expedites the resolution of questions and issues—not to mention the positive impression it makes on managers!
Data Definitions
Data definitions must be consistent between each of their potential data stores. Each time your data touches down to a database or file, it is vulnerable to data truncation or corruption. If data definitions are not alike between environments, the ETL team must explicitly convert the data to avoid catastrophe. In addition to the attributes listed in the previous section, the following data-definition metadata elements must also be supplied to the ETL team.
Table name. The physical name of the table or file that contains the data
Column name. The physical name of the column within a table or file
Data type. Data elements with a table are categorized into types. Common data types are Numeric, Character, Date, and Binary. You will find that custom data types are allowed in most DBMSs. Custom data types are based on common types and usually enforce formatting rules. Data types are mutually exclusive and cannot coexist within a single column.
Domain. The set of values allowed to be entered into a column is known as its domain. The domain can be enforced by a foreign key, check constraints, or the application on top of the database. If the application enforces the domain, a list of allowed values must be provided by the programming team to the ETL team.
Referential integrity. If your data comes from a database, you will most likely find foreign keys that point to a primary key of another table to ensure the data is unique. If referential integrity (RI) is enforced at the application level, the programming team must provide the RI rules. Typically, RI in the data warehouse is deemed unnecessary overhead because all data is entered in a controlled fashion—the ETL process—and does not have RI enforced at the database level.
Constraints. Constraints are another form of a physical implementation of a business rule. Database constraints can eliminate NULL values, enforce foreign key look-ups, ensure compliance with allowed values, and so on.
Defaults. A default in the context of ETL metadata is the assignment of a string, number, date, or bit, in the case when the actual value is not available. In your source system, column defaults are usually assigned at the database level. In the data warehouse, the defaults should be assigned in the ETL process. It is recommended that defaults in the data warehouse are used consistently.
Stored procedures. Stored procedures, which store prewritten SQL programs in the database, offer great insight to how your source data is used. Each data warehouse project inevitably involves analyzing stored procedures that exist in the source systems.
Triggers. A trigger is an SQL procedure automatically executed by the DBMS when a record is added, deleted, or updated. Like stored procedures, triggers offer information about how data is used. Triggers often enhance foreign key constraints by adding additional checks to records added to a table. Triggers also load audit tables when records are altered or deleted from a table. Audit tables loaded by triggers are a vital source of deleted data for the data warehouse.
Business Rules
Business rules can be categorized as business or technical metadata. We like to refer to business rules as technical because they are the essence of the ETL process—which is very technical. Each and every business rule must be coded in the ETL process. Business rules can include anything from allowed values to default values to calculations for derived fields. In source systems, business rules are enforced by stored procedures, constraints, or database triggers. But most often, business rules exist only in the application code. In older, especially mainframe, environments, the actual source code no longer exists and only the compiled portion of the application remains. In those cases, business rules are extremely difficult to obtain. It usually requires interviewing data analysts and programmers who support the application. The metadata for business rules varies between functional or technical documentation and source code in the native programming language of the application or pseudocode.
Business rules must be incorporated into logical data mapping. Sometimes business rules are omitted from logical data mapping and go unnoticed until the first attempt at the ETL process is complete and the exclusions are detected by users during UAT. As new business rules are learned, the metadata in the logical data mapping must be updated to reflect the new rules.
ETL-Generated Metadata
So far in this chapter, we have focused on metadata created outside of the ETL environment and provided to the ETL team from other sources. The remainder of this chapter addresses metadata generated by the ETL team and used either within the team to manage the ETL processes or by end users or other data warehouse members to better understand the data within the data warehouse.
As ETL physical processes are built, specific metadata must be generated to capture the inner workings of each process. ETL metadata falls into four main categories:
ETL job metadata. The ETL job is a container that stores all of the transformations that actually manipulate the data. The job metadata is very valuable because it contains the data lineage of the elements in the data warehouse. Every ETL task—from extraction to load and all transformations in between—is captured in the ETL job metadata.
Transformation metadata. Each job consists of many transformations. Any form of data manipulation within a job is performed by a dedicated transformation.
Batch metadata. Batching is a technique used to run a collection of jobs together. Batches should have the ability to be configured to run sequentially or in parallel. Also, batches should be able to contain subbatches. Subbatches are common in data warehousing. You may have a batch that loads dimensions and a batch that loads facts. Those batches can be batched together to load a specific data mart. Batches are scheduled to run periodically or according to any triggering mechanism.
Process metadata. Each time a batch is executed, process metadata is generated. Process metadata is crucial for depicting whether the data warehouse was loaded successfully or not.
If you are not new to ETL, you’ve probably noticed that the metadata categories are not listed in container order but in the order that they are generally built or generated in the ETL process. For example, even though batches contain jobs, you must build your jobs before you can batch them. Each type of ETL metadata contains its own specific attributes that need to be created, maintained, and published. The subsequent sections examine the specifics of the technical metadata in the ETL environment.
For consistency throughout this book, we refer to the physical implementation of the data mapping as a job and their containers as batches. The terms may be consistent with some ETL tools but not others. Our generic designation of the terms does not infer any particular technology. If your technology calls your physical source-to-target mappings something other than job, substitute job with your own term for the purposes of capturing its metadata.
If you implement your ETL solution without a dedicated tool, you are not excused from generating the metadata outlined in this chapter. Tools are meant to ease the burden of these tasks. Without them, you will have to produce and maintain metadata manually. If you must create ETL metadata by hand, use spreadsheets kept in a version-control system such as PVCS or SourceSafe.
ETL Job Metadata
ETL metadata can be very technical, and end users tend to shy away from it. Source-to-target mapping usually contains programming code and can be cryptic to business users or nontechnical end users. However, source-to-target mapping is crucial for understanding the true data lineage of the data in the data warehouse. Metadata is sought after by the data warehouse team as well when credibility of the data is in question and its integrity needs to be proven.
The optimal way to present source-to-target mappings is to utilize a dedicated ETL tool. These tools offer reports to present the information to users. Some tools are better at it than others. Make sure to ask your potential ETL vendor to show you how their tool handles the presentation of source-to-target mappings.
Figure 9.2 illustrates the elements of ETL job metadata that need to be created, stored, and shared. The following elements must be tracked to
Figure 9.2 ETL job metadata.
help manage your jobs and share their identity and functionality with your managers and users.
Job name. The name of the physical ETL job
Job purpose. Brief description of the primary focus of the process
Source tables/files. The name and location of all source data
Target tables/files. The name and location of all resulting data after it has been transformed
Reject filename. The name and location of the file or table that stores any records intended to be loaded but are not in the ultimate target
Pre-processes. Any other jobs or scripts on which the job is dependent to run before it can be executed
Post-processes. Any other jobs or scripts that the job needs to run to complete its process
Jobs
A job is a collection of transformations that perform the physical extraction, transformation, and load routines. The metadata for a job is the physical source-to-target mapping. Jobs should be named after the target table or file that they are loading. If your ETL process has many segments, each job must contain a prefix that indicates its purpose. ETL jobs generally fall into one of three categories:
Extraction. EXT_table name>. Indicates that the job’s primary purpose is to extract data from the source system.
Intermediate stage (for example, cleaning and conforming). STG_. The STG prefix signifies that the job does not touch the source or the target. It is an intermediate process that lives only in the staging area. If your process touches down in the staging area more than once, append a counter to the prefix (for example, STG1, STG2, STG3, and so on).
Target. TRG_
. Indicates that the job loads the target data warehouse. Alternatively, we’ve seen these jobs named FAC_
and DIM_
to indicate if the target table is a fact or dimension, respectively. We don’t see much value in that, but it is an acceptable convention if it provides value for your situation.
Transformation Metadata
Transformation metadata is information about the construction of the ETL process. The ETL developer spends most of his or her time constructing or reusing data transformations. Transformations are composed of custom functions, procedures, and routines that can include cursors, loops and memory variables, making them extremely difficult to document and offer as metadata. Any manipulation performed on your data during the ETL process is considered a transformation. If you are writing your ETL in SQL, you need to identify each of the distinct sections in your procedures and label them to be consistent with the common attributes of transformation metadata.
Dedicated ETL tools predefine transformations common to the data warehouse environment and provide them as part of their package. Prebuilt transformations expedite ETL development and implicitly capture transformation metadata. Common data transformations that exist in most ETL jobs include:
Source data extractions. This could be as simple as an SQL SELECT statement or involve FTP or reading XML DTDs or mainframe Copy Books.
Surrogate key generators. These can simply call a database sequence or involve complex routines that manage memory or involve third-party software. The last number inserted into the data warehouse is also metadata that needs to be maintained and presented as required.
Lookups. Primarily used to get surrogate keys from the dimensions during a fact table load or referential integrity in the staging area. If you are using raw SQL, this involves all of your inner and outer joins and IN statements.
Filters. This rule determines which rows are extracted and loaded. Metadata is the business rule or constraint used to apply the filter. Filters can be applied anywhere in the ETL process. It is a recommended practice to filter your data as early in the process as possible.
Routers. Conditionally routes rows like a CASE statement
Union. Merges two pipelines with compatible row sets
Aggregates. When the fact table is not the same grain as the atomic-level transaction, you need to apply aggregates to source data. Metadata associated to the aggregate includes any calculations within the aggregate function, the function itself—count, sum, average, rank, and so on—and the columns on which the aggregate functions are grouped. The grouped columns declare the granularity of the aggregate.
Heterogeneous joins. When your source’s data comes from different systems, they are usually joined outside of any single database (unless you use a database link). The way you join heterogeneous systems outside of the DBMS environment needs to be defined and presented as metadata.
Update strategies. The update strategy contains business rules that determine if a record should be added, updated, or deleted. It also contains the slowly changing dimension policy.
Target loader. The target loader tells the ETL process which database, table, and column needs to be loaded. Additionally, this documents if any bulk-load utility is used to load data into the data warehouse.
Each transformation gets data, manipulates it to a certain degree, and passes it to the next transformation in the job stream. Metadata attributes that describe the transformation include:
Transformation name. A single job can have a multitude of transformations, and identification of each is crucial for management and maintenance. Each transformation must have a unique name that is meaningful and complies with standard naming conventions. Transformation-naming conventions are outlined in this chapter.
Transformation purpose. The purpose of the transformation must be easily identified. Many ETL tools color-code their predefined transformations. If you are hand-coding your ETL processes, make sure metadata is captured both within your code and your transformation matrix.
Input columns. The data elements fed into the transformation
Physical calculations. The actual code used to manipulate data
Logical calculations. The textual equivalent of the physical calculations is required to make sense of the sometimes cryptic code required to physically manipulate data.
Output columns. The result of the data transformation sent to the next transformation
Transformation Nomenclature
Transformations are the components of an ETL job. Each type of transformation requires a slightly different naming format. For maintainability, adhere to the following naming conventions while building your ETL transformations:
Source data extractions. SRC_
Surrogate key generators. SEQ_
Lookups. LKP_
Filters. FIL_ (for example, FIL_SUPPRESS_BOTS to suppress hits on a Web site generated by bots)
Aggregates. AGG_ (for example, AGG_HITS_BY_MONTH to aggregate Web site hit counts to the monthly level)
Heterogeneous joins. HJN__
Update strategies. UPD__
Target loader. TRG_
Batch Metadata
Once all of the jobs are designed and built, they need to be scheduled for execution. The load schedule is a crucial piece of metadata for the team responsible for incrementally loading the data warehouse. Figure 9.3 illustrates what a typical load schedule might look like for a clickstream data mart.
Figure 9.3 Clickstream data mart load schedule.
In Figure 9.3, notice that the load schedule contains information for the following metadata attributes:
Load Schedule
Dependent batches. Batches are nested to contain several levels of batched jobs to execute many jobs in parallel or to maintain integrity between jobs. For example, a batch that loads dimensions of a data mart must complete successfully before fact table jobs can be executed. Remember, the data warehouse database does not enforce referential integrity. Dependent batches are one of the ways to enforce integrity in the data warehouse.
Frequency. Portions of the data warehouse are loaded monthly, weekly, daily, or are continuously fed data. This piece of metadata defines how often the batch is executed.
Schedule. If a job is run daily, this metadata attribute captures the exact time the batch is executed. If it is run monthly, the exact day of the month is represented. Batches must have the ability to be scheduled on any give time of day, day of week, month, or year.
Recovery steps. Actions required in the event of a midprocess failure. Recovery steps can be a lengthy process and are usually offered in a separate document. The steps to recover from a failed process must be walked through with the team that supports the operation of the execution of batched ETL jobs to ensure they understand the procedure.
Data Quality Error Event Metadata
Chapter 4 is an in-depth tutorial on capturing metadata describing data quality. The three main tables are depicted in detail in that chapter, but for uniformity, we list the data elements we captured in the cleaning and conforming steps.
First, the screen table includes:
The ETL Injection Stage describes the stage in the overall ETL process in which the data-quality screen should be applied.
The Processing Order Number is a primitive scheduling/ dependency device informing the overall ETL master process the order in which to run the screens. Data-quality screens with the same Processing Order Number in the same ETL Injection Stage can be run in parallel.
The Severity Score is used to define the error severity score to be applied to each exception identified by the screen.
The Exception Action attribute tells the overall ETL process whether it should pass the record, reject the record, or stop the overall ETL process upon discovery of an error of this type.
The Screen Category Name is used to group data-quality screens related by theme—such as Completeness, Validation, or Out-of-Bounds.
The SQL Statement captures the actual snippet of SQL or procedural SQL used to execute the data quality check. If applicable, this SQL should return the set of unique identifiers for the rows that violate the data-quality screen—so that this can be used to insert new records into the Data Quality Error Event fact.
Second, the main error event fact table includes:
The Staged Record Identifier, which uniquely identifies the error record
The Error Severity Score, which assigns a severity from 1 to 100 to the error condition
The error event fact table has foreign keys to calendar date, time of day, ETL batch, table, and source system dimensions. These dimensions provide the context for the measures in the error event fact table.
The audit dimension includes the following fields described in Chapter 4:
Overall Data Quality Score
Completeness Score
Validation Score
Out of Bounds Score
Number of Screens Failed
Maximum Error Event Severity
Cleaning and Conforming Timestamps, including the begin times and end times of specific job runs
Overall ETL Timestamps, including the begin times and end times of complete end-to-end ETL jobs
The Overall ETL Release Version Numbers to identify the consistent suite of ETL software tools in use at a point in time
Other Audit Version Numbers such as allocation version, currency conversion logic version, and conforming logic version, depending on the business-rules environment
Process Execution Metadata
Virtually all process metadata in the data warehouse is generated by the ETL. Each time a job or batch is executed, statistics or indicators of success need to be captured. Load statistics are a vital piece of metadata that captures information about the execution of the ETL process and contains information about the actual load-process results.
Run Results
Metadata elements that help you understand the activity in your jobs or batches and rate the success of their execution include:
Subject name. This can be the data mart or a description of a batch of programs being run for a specific area.
Job name. The name of the program executed
Processed rows. The total number and percentage of rows read and processed from the source system
Success rows. The total number and percentage of rows loaded to the data warehouse
Failed rows. The total number and percentage of rows rejected by the data warehouse
Last error code. The code of the last database or ETL exception raised during the load process
Last error. The textual description of the last database or ETL exception raised during the load process
Read throughput. Throughput is used to measure the performance of the ETL process. It is normally represented in rows per second. This is used to capture if the source system is causing a bottleneck.
Write throughput. Throughput is used to measure the performance of the ETL process. It is normally represented in rows per second. This is used to capture if the target data warehouse database is causing a bottleneck.
Start time. The date, time, and second that the job is initiated
End time. The date, time, and second that the job ends, regardless of its success
Elapsed time. The difference between the Start time and End time. This is an important element for analyzing performance. In most cases, rows per second are not enough, because it can vary depending on the number of rows being loaded.
Source file name. The name of the table or file where the data in the process originates. This can include more than one table or file.
Target file name. The name of the table or file where the data in the process is targeted. This can include more than one table or file.
Process-execution metadata should be retained in a data store so trend analysis can be performed. Metadata can reveal bottlenecks in the process, and trending can expose portions of the data warehouse that lack the required scalability. Measures of data quality should also be trended.
Exception Handling
This data records exceptional conditions that arose in the running of the ETL system and what action was taken:
Subject name. This can be the data mart or a description of a batch of programs being run for a specific area
Job name. The name of the program executed
Exception Condition. One of a standard set of exception conditions
Severity.
Action Taken.
Operator.
Outcome.
Batches Scheduled
Batches are a collection of ETL jobs scheduled for execution. The name of the batch should reveal the subject area being loaded, the frequency that the jobs are run, and whether the jobs within the batch are executed sequentially or in parallel.
Metadata Standards and Practices
One aspect of metadata that is worth investigating is standards. Many organizations attempt to standardize metadata at various levels. If you are interested in standards on things such as naming conventions or domain standards, you may find the standards maintained by the Library of Congress to be helpful (www.loc.gov/standards/standard.html). Furthermore, links to additional standards organizations are offered. On their Web site, you’ll find links to:
Metadata Encoding and Transmission Standard (METS). A standard for encoding descriptive, administrative, and structural metadata regarding objects within a digital library
American National Standards Institute (ANSI). The organization that coordinates the U.S. voluntary standardization and conformity-assessment systems
International Organization for Standardization (ISO). The body that establishes, develops, and promotes standards for international exchange
Metadata relative to the ETL process includes not only standards in values and conventions but also in methodology on storing and sharing metadata. Organizations geared toward the organization and storage of metadata include:
Dublin Core. The Dublin Core Metadata Initiative (DCMI) is an open forum created to develop metadata standards. DCMI hosts periodic working groups and conferences that promote metadata standards and practices worldwide. More information about Dublin Core can be found on their Web site at www.dublincore.org.
Meta Data Coalition. The now defunct Meta Data Coalition (MDC) was created in 1995 as a consortium of approximately 50 vendors and end users whose efforts attempted to provide technical direction to exchange metadata between products in the data warehouse environment. During its existence, MDC helped to establish and encourage consistent means of sharing metadata. In 2000, MDC folded into the Object Management Group (OMG), who has a much larger agenda in the metadata space and combined MDC’s objectives with their own broader plan.
Common Warehouse Metamodel. The Common Warehouse Metamodel (CWM) was created as a result of the merger of MDC and OMG. You can find detailed information about CWM in books dedicated to the topic and the OMG Web site www.omg.org/cwm.
Establishing Rudimentary Standards
To maintain manageable jobs for all of your enterprise data warehouse ETL processes, your data warehouse team must establish standards and practices for the ETL team to follow. Whether you follow the recommendations of the organizations in this section or follow the practices outlined throughout this book, at the most rudimentary level, your organization should adhere to standards for the following:
Naming conventions. Corporations usually have namingconvention standards in place for their existing software and database development teams. The data warehouse may be required to follow those standards. In practice, we tend to deviate from those standards when we can, especially while naming columns. But often, you must conform to corporate policies. Therefore, any corporate policies must be documented and provided to the data warehouse team.
Architecture. Best-practice guidelines need to be captured as metadata for your ETL environment. In many cases, high-level architecture decisions are made before the ETL team is established or on the recommendation of your ETL vendor. Decisions such as whether you should run your ETL engine on your data warehouse server or on a dedicated box, whether you should have a persistent staging area (PSA), or whether your target data warehouse should be normalized or dimensional should be based on best practices that need to be established, documented, and followed.
Infrastructure. Should your solution be on Windows or UNIX, mainframe or AS/400? Corporate standards influence the decisionmaking process for the products and hardware used for the ETL. Some ETL engines run only on UNIX or the mainframe, while others run on Windows. You must have established corporate infrastructure metadata before any purchasing decision is made for your ETL environment. Once your environment is established, the metadata of its infrastructure must be documented and reviewed with your internal infrastructure support team.
Naming Conventions
Naming conventions for the objects in the data warehouse environment should be established before the ETL team begins any coding. Conventions for tables, columns, constraints, indexes, checks, and so on should be offered by the existing DBA team within your organization or the data warehouse manager. The ETL team must adopt any conventions that exist in the rest of the data warehouse environment.
If you decide that your existing corporate naming conventions are not applicable to your ETL environment, you must document your alternative naming conventions and seek approval from your corporate standards committee. Once approved, the corporate standards committee should incorporate the new ETL-specific conventions with their existing naming standards.
None of the organizations we recognize earlier in this section recommend naming standards specific to the ETL process. If your ETL processes consist only of raw SQL, follow your in-house programming practices and naming standards, with the exception of conventions explicitly defined in this chapter. In this section, we share conventions that prove to be effective in the ETL environment.
The naming conventions recommended in this section include the common transformations found in most ETL tools. Use these conventions regardless of your tool, even if you are coding your ETL by hand. Tools can come and go—do not set up new standards each time you change tools. Use the vendor-recommended naming conventions for transformations that are not mentioned in this book. It is acceptable to slightly modify these naming conventions for your purposes—the important thing is be consistent within your environment.
Impact Analysis
One of the key advantages of maintaining ETL metadata is to enable impact analysis. Impact analysis allows you to list all of the attributes in the data warehouse environment that would be affected by a proposed change. Ultimately, you should be able to analyze the impact of a change that would occur in any component of the data warehouse and list all of the attributes in all of the other components. An impact analysis solution must be able to answer the following questions:
Which ETL jobs depend on this staging table?
Is this table in the source system used by the data warehouse?
Would the deletion of this source system column affect the ETL process?
Which source systems populate this dimension?
Which ETL jobs and data warehouse tables will need to be modified if we change this data type from VARCHAR(2000) to CLOB?
Tools designed specifically for ETL should be able to answer all of these questions. Without an ETL tool, you need to maintain spreadsheets to capture every table and column from the source systems and their mapping into the data warehouse. Each time an ETL job is altered, the spreadsheet needs to be manually modified to stay current.
Summary
In this chapter, we have brought order to the traditional chaos of metadata surrounding a data warehouse, first by focusing only on the metadata needed to manage the ETL system, and next by dividing the ETL metadata into three categories.
1. Business metadata. Describing the meaning of the data in a business sense and consisting of separate tables tracking business definitions, source system information, the data warehouse dictionary, and logical data mapping
2. Technical metadata. Representing the technical aspects of data, including attributes such as data types, lengths, and lineage, and consisting of separate tables tracking system inventory, data models, data definitions, business rules, ETL jobs definitions, specific data transformations, and batch job definitions
3. Process execution metadata. Presenting statistics on the results of running the ETL process itself, including measures such as rows loaded successfully, rows rejected, and amount of time to load. We proposed particularly important process metadata in the cleaning and conforming steps, including the screen dimension table, the error event fact table, and the audit dimension table. All of this metadata consists of separate tables tracking run results, exception handling, and the immediate operational schedule.