Metadata And Data Warehousing

1. Metadata

What is Metadata???

Metadata may be defined as’ Data about data’ or ‘Data ( mostly definitions) about the structures containing other data’ . for e.g. a table has four columns , two constraints containing hundreds of records. So in this case , metadata would be <table name>  along with  <column name> , < constraint types> , < column datatype>, < column size> etc.

What is Metadata Repository??

Metadata Repository is the database where metadata is stored.

What is Metadata Registry ??

A Metadata Registry is a central location where metadata definitions are stored.

Classification of Metadata

A ) Based on the Data warehouse Architecture Layer :

For detail of Data warehouse architecture refer Data warehouse Architecture

1. Metadata associated with Source Systems ( OLTP) :

Metadata for source systems includes various definitions of the data structures like tables , indexes , views . These definitions may include the table names , column names of respective tables , their datatypes , constraints if any , the column size . But the OLTP systems are operational systems that store the day to day current transactions, moreover they are not integrated and the people who query them are IT Professionals who are already well versed with the structure and functionality of the systems. Hence Metadata is of passive use for OLTP source systems in contrast to data warehouse where it plays the key role.

An interesting point to note is that , when we query the data dictionary views [user|all|dba]_tab_columns, [user|all|dba]_cons_columns, [User|All|dba]_tab_columns , [User|All|dba]_constraints , we are actually querying  Metadata  Repository.A data dictionary is a data structure that stores metadata.The output that we get i.e. table name , column name ,data type , constraint name and constraint type,  is the metadata.

2. Metadata associated with Staging Layer :

Staging layer includes cleaning of the extracted data from the source and its transformation before being loaded into the data warehouse. The Metadata for the staging layer includes information about the source definitions , target definitions , mappings , transformations , workflows and much more.

2. Metadata associated with Data Warehouse :

compared to the operational databases , Data Warehouse are huge systems and store large quantity of data integrated from multiple sources. Moreover the people who query Data Warehouse systems are mostly business intelligence people who are not technical. Hence with respect to Data Warehouse systems , the metadata plays a key role . It is like a DNA for the data warehouse systems. It contains the information about what data is stored in Data Warehouse , what kind od data is stored , what are the sources and target , when it was last updated and much more. Thus or business intelligence people , metadata makes it easy to access the data warehouse which otherwise would be a big challenge.

B) Other Classification of Metadata :

 As per the other classification , there are three types of Metadata :

1. Business Metadata

2. Technical Metadata

3. Process Metadata