Metadata for Data Warehousing and Business
Data that Describes Your Data Warehouse
Metadata is one of the important keys to the success of the
data warehousing and business intelligence effort.
Metadata management answers these questions:
- What is Metadata?
- How can Metadata be Managed?
- Extracting Metadata from Legacy Systems
What is Metadata?
Metadata is your control panel to the data
warehouse. It is data that describes
the data warehousing and business intelligence
- Tables (Records, Segments, Entities,
- Columns (Fields, Attributes, Data
Metadata is often used to control the handling of data and
The power of metadata is that enables data warehousing
personnel to develop and control the system without writing
code in languages such as: Java, C# or Visual Basic. This
saves time and money both in the initial set up and on going
Data Warehouse Metadata
Data warehousing has specific metadata requirements.
Metadata that describes tables typically includes:
- Physical Name
- Logical Name
- Type: Fact, Dimension, Bridge
- Role: Legacy, OLTP, Stage,
- DBMS: DB2, Informix, MS SQL Server, Oracle, Sybase
Metadata describes columns within tables:
- Physical Name
- Logical Name
- Order in Table
- Decimal Positions
- Default Value
- Edit Rules
How can Data Warehousing Metadata be Managed?
Data warehousing and business intelligence metadata is best
managed through a combination of people, process and tools.
The people side requires that people be trained in the
importance and use of metadata. They need to understand
how and when to use tools as well as the benefits to be gained
The process side incorporates metadata management into the
data warehousing and business intelligence life cycle. As
the life cycle progresses metadata is entered into the
appropriate tool and stored in a metadata repository for
Metadata can be managed through individual tools:
- Metadata manager / repository
- Metadata extract tools
- Data modeling
- BI Reporting
Metadata Manager / Repository
Metadata can be managed through a shared repository that
combines information from multiple sources.
The metadata manager can be purchased as a
software package or built as "home grown" system. Many
organizations start with a spreadsheet containing data
definitions and then grow to a more sophisticated approach.
Extracting Metadata from Input Sources
Metadata can be obtained through a manual process of keying
in metadata or through automated processes. Scanners can
extract metadata from text such as SQL DDL or COBOL programs.
Other tools can directly access metadata through SQL catalogs
and other metadata sources.
Picking the appropriate metadata extract tools is a key part
of metadata management.
Many data modeling tools include a metadata extract
capability - otherwise known as "reverse engineering".
Through this tool, database information about tables and
columns can be extracted. The information can then be
exported from the data modeling tool to the metadata