Metadata for Data Warehousing and Business Intelligence

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 system:

1. Reports
2. Cubes
3. Tables (Records, Segments, Entities, etc.)
4. Columns (Fields, Attributes, Data Elements, etc.)
5. Keys
6. Indexes

Metadata is often used to control the handling of data and describes:

1. Rules
2. Transformations
3. Aggregations
4. Mappings

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 management.

Data Warehouse Metadata

Data warehousing has specific metadata requirements. Metadata that describes tables typically includes:

1. Physical Name
2. Logical Name
3. Type: Fact, Dimension, Bridge
4. Role: Legacy, OLTP, Stage,
5. DBMS: DB2, Informix, MS SQL Server, Oracle, Sybase
6. Location
7. Definition
8. Notes

Metadata describes columns within tables:

1. Physical Name
2. Logical Name
3. Order in Table
4. Datatype
5. Length
6. Decimal Positions
7. Nullable/Required
8. Default Value
9. Edit Rules
10.Definition
11.Notes

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 through metadata.

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 further use.

Metadata can be managed through individual tools:

Metadata manager / repository
Metadata extract tools
Data modeling
ETL
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 manager.

Data Warehouse Design & ETL Concepts

Data Warehouse Design
Data warehouses and data marts are built on dimensional data modeling where fact tables are connected with dimension tables. This is most useful for users to access data since a database can be visualized as a cube of several dimensions.



Dimensional Data Modeling
Dimensional Data Modeling comprises of one or more dimension tables and fact tables.
Dimensional Data Modeling is used for calculating summarized data. For example, sales data could be collected on a daily basis and then be aggregated to the week level, the week data could be aggregated to the month level, and so on.



Dimension Tables
Dimension table is one that describes the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are sometimes called lookup or reference tables.
For example, Product dimension table will store information about products(Product Category, Product Sub Category, Product and Product Features) and location dimension table will store information about location( country, state, county, city, zip.



Fact Tables
The centralized table in a star schema is called as FACT(measures) table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.
For example Sales Amount can be the measure for any business.



Measure Types
Additive - Measures that can be added across all dimensions.
Non Additive - Measures that cannot be added across all dimensions.
Semi Additive - Measures that can be added across few dimensions and not with others.

Star Schema
Star Schema is a relational database schema for representing multidimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema is slicing down, performance increase and easy understanding of data.





Snowflake Schema
A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. Dimension table hierarchies are broken into simpler tables.






ETL Concept
Extraction, transformation, and loading. ETL refers to the methods involved in accessing and manipulating source data and loading it into target database.
The first step in ETL process is mapping the data between source systems and target database(data warehouse or data mart). The second step is cleansing of source data in staging area. The third step is transforming cleansed source data and then loading into the target system.
Note that ETT (extraction, transformation, transportation) and ETM (extraction, transformation, move) are sometimes used instead of ETL.



Glossary of ETL
Source System
A database, application, file, or other storage facility from which the data in a data warehouse is derived.
Mapping
The definition of the relationship and data flow between source and target objects.
Metadata
Data that describes data and other structures, such as objects, business rules, and processes. For example, the schema design of a data warehouse is typically stored in a repository as metadata, which is used to generate scripts used to build and populate the data warehouse. A repository contains metadata.
Staging Area
A place where data is processed before entering the warehouse.
Cleansing
The process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process.
Transformation
The process of manipulating data. Any manipulation beyond copying is a transformation. Examples include cleansing, aggregating, and integrating data from multiple sources.
Transportation
The process of moving copied or transformed data from a source to a data warehouse.
Target System
A database, application, file, or other storage facility to which the "transformed source data" is loaded in a data warehouse.

Data Warehouse Concepts

What is Data Warehouse?

A data warehouse is collection of historical data; it is subject oriented data, integrated, time variant and non volatile data for decision making process. Following are key points of data warehouse:
· Data warehouse is a relational and multinational database.
· Designed for query and analysis rather than transaction process.
· Data warehouse usually historical data and derived from online transaction data.
· Data Stored in Demoralized form
· Data warehouse environment often consist an ETL solution, OLAP design.
· The data warehouse is nothing more than the union of all the constituent data marts.

Elements of the data warehouse:

Source System: Multiple Sources, Excel, People Soft, RDBMS, Flat File and SAP etc.
Data Staging Area: ETL- Extraction, Transformation and Loading.
Presentation Server: Physical Data Warehouse.
Dimensional Modeling: Alternative of E/R Modeling, designed based on Dimension and Fact table.
Business Process: A business process is usually a set of activities like "order processing" or "customer pipeline management," but business processes can overlap, and certainly the definition of an individual business process will evolve over time.
Data Marts: Designed for particular line of business activity like Sales, Marketing and Finance etc. A data mart is a complete “pie-wedge” of the overall data warehouse pie.
Operation Data Store: ODS is more than the real enterprise data warehouse, data is refreshed in near time and used for routine business activity.
On-Line Analytic Process: Online Analytical Processing is an approach that helps organization to take advantages of DATA. Technically, OLAP cube allows one to analyze data across multiple dimensions by providing multidimensional view of aggregated, grouped data.
Relation OLAP: ROLAP stands for Relational Online Analytical Process that provides multidimensional analysis of data, stored in a Relational database.
Multidimensional OLAP: MOLAP provides the analysis of data stored in a multi-dimensional data cube.
Hybrid OLAP: HOLAP a combination of both ROLAP and MOLAP can provide multidimensional analysis simultaneously of data stored in a multidimensional database and in a relational database
Desktop or Database OLAP: DOLAP provide multidimensional analysis locally in the client machine on the data collected from relational or multidimensional database servers.
End User Application: Application which provides decision making information in targeted format. Like Excel sheet and Graphical etc.
End User Data Access Tool: Client of the data ware house to access and review the data warehouse information.
Metadata: All of the information in the data warehouse environment that is not the actual data itself.