Types of Metadata

There are two distinct classes of metadata:

1. Structural Metadata - used to describe the structure of computer systems such as tables, columns and indexes.
2. Guide Metadata - used to help humans find specific items and is usually expressed as a set of keywords in a natural language.

Relational Database Metadata

Each relational database system has its own method for storing metadata. This may include:

• Tables of all tables in database, their names, sizes and number of rows in each table.
• Tables of columns in each database, what tables they are used in, and the type of data stored in each column.

This set of metadata is referred to as the catalog.

The SQL standard specifies a uniform means to access the catalog, called the INFORMATION_SCHEMA, but not all databases implement it, such as Oracle databases.

Data Warehouse Metadata

Data warehouse metadata systems may be separated into two sections:

1. Back room metadata - are used for Extract, transform, load functions to get OLTP data into a data warehouse .
2. Front room metadata - used to label screens and create reports

Types of metadata in a data warehouse include:

• Source system metadata
• Source specifications, such as repositories, and source schemas
• Source descriptive information, such as ownership descriptions, update frequencies, legal limitations, and access methods
• Process information, such as job schedules and extraction code
• Data staging metadata
• Data acquisition information, such as data transmission scheduling and results, and file usage
• Dimension table management, such as definitions of dimensions, and surrogate key assignments
• Transformation and aggregation, such as data enhancement and mapping, DBMS load scripts, and aggregate definitions
• Audit, job logs and documentation, such as data lineage records, data transform logs
• DBMS metadata, such as: DBMS system table contents and processing hints

Business Intelligence Metadata

Business Intelligence metadata describes how data is queried, filtered, analyzed, and displayed in Business Intelligence software tools, such as Reporting tools, OLAP tools, Data Mining tools.

Examples:

OLAP metadata: The descriptions and structures of Dimensions, Cubes, Measures (Metrics), Hierarchies, Levels, Drill Paths

Reporting metadata: The descriptions and structures of Reports, Charts, Queries, DataSets, Filters, Variables, Expressions

Data Mining metadata: The descriptions and structures of DataSets, Algorithms, Queries

Business Intelligence metadata can be used to understand how corporate financial reports reported to Wall Street are calculated, how the revenue, expense and profit are aggregated from individual sales transactions stored in the data warehouse. A good understanding of Business Intelligence metadata is required to solve complex problems such as compliance with corporate governance standards, such as Sarbanes Oxley (SOX) or Basel II.

Back To Top