Business Intelligence Metadata Model

This page is an extract from BIDS Metadata Management Solution authored by Kamlesh Mhashilkar- Head, BIPM Services of Tata Consultancy Services

Overview

Metadata is an extremely important component of BI architecture. A well-designed metadata model enables effective administration, change control, and distribution of the data supporting the BI system. Metadata includes business rules, data sources, summarization levels, data aliases, data transformation rules, technical configurations, data access rights, data usage and much more. In principle, it’s “data about data”.

The most important part of metadata implementation is the integration between various components of metadata stored in data modeling tools, ETL tools, databases and OLAP tools. In a few implementations, the metadata architecture and its components need to be designed and built in which case, identification and integration of these metadata components need to be done aiming at a robust metadata base

NOTE- Please read this page in conjunction with Data-Warehouse Metadata and section of Enterprise Metadata .

Metadata Paradigm

The following diagram shows the three principal limbs of integrated metadata model for Data-Warehouse and BI overall..

In BI architecture, the metadata spans across three areas namely,

• Business Metadata,
• BI System Technical Metadata and
• Source Systems Metadata.

The levels of metadata details (or abstract), which are shown along the limbs, give information about the extent of details of the various metadata areas. These levels map very well across the three limbs of metadata model.

The metadata in the element level of all the areas (i.e. Business Terms, BI Data Elements and Source Data Fields) forms the base of the metadata implementation. It gives the “Glossary” which is generated from the most detailed information of all the metadata areas. And hence the most voluminous metadata lies in this zone itself.

Links need to be developed among these areas in order to set the metadata platform which can be utilized by various user communities as well as the administrators and developers. These links need to be established along various levels of abstract of individual areas as well as along same levels of abstract across the areas. Setting the links among higher levels of abstract across the metadata areas is not mandatory. But the links need to exist at the most detail level (i.e. the element level) across the areas.

A considerable manual effort is involved in incorporating the links and similar effort is needed for incorporating any changes in the links. Linked-list concept can be utilized in the search engine to reduce this effort. In that case the links need to be set only along the levels in metadata areas and across the areas at element level. Any query across the metadata areas will pass through the “Glossary” zone and provide the appropriate link in the other area. But to achieve high performance in such search query the linked-list structure needs to be tuned.

Levels of Abstract in Metadata Areas

The following table gives details of different levels of abstract in the metadata areas.

High: Concept Level

At concept level, the report categories are derived from the business subject areas, which are handled by various departments and divisions and hence are taken care into different systems. These systems from various departments eventually become the data providers for the BI system.

Medium: Entity Level

At entity level, the technical entities like tables, cubes, reports are designed with the help of the business entities which are analyzed during the business / system requirements. The source systems entities, which contain this information, are mapped to the technical entities. These mappings are used for the data load purpose, but at next level of abstract i.e. element level. The traceability between the business entities and source system entities exist with the system owners. This traceability is utilized to perform the gap analysis at entity level during the requirements gathering and BI analysis phase.

Low: Element Level

The most detailed level of BI metadata exists at data element level. The business terms from business metadata are mapped to the data fields in tables, reports and dimensions / measures in the multi-dimensional cubes which form the technical metadata. The business users extensively use this metadata information. The mappings between the BI data fields and the source system data fields are used in Extraction, Transformation and Loading processing. The traceability between the business terms and source system data fields aid in identifying the gaps that may exists in the BI system with respect to the business elements as well as the source system. Also it helps in identifying the gaps in the existing source system with respect to the business elements so that a better BI solution can be implemented.

The element level information from the three metadata areas yields the “Glossary” for the metadata implementation. This detailed metadata information forms the base of the metadata model with links to higher level of abstracts as well as other metadata areas. This is the only zone through which the cross metadata area search passes and hence it is important to design this zone for high performance search engines. Use of linked lists may aid in this case.

Metadata Implementation Classification

The metadata implementation takes place across multiple principal limbs of the metadata model. This implementation gets referenced as per the coverage of implementation around the principal limbs.

The following are the distinct implementations of metadata in any BI system, which have been discussed in detail, subsequently.

• Back-Room Metadata
• Front-Room Metadata
• Counterpoint Metadata

Back-Room Metadata

Back-Room Metadata spans across the Data Source and BI Technical Metadata areas and hence occupies a large scope. It encompasses the following components of metadata.

• ETL Metadata (Control as well as Process Metadata)
• Data Models (primarily, data structures)
• Security Profiles (Roles and ACLs)
• Audit Trail (e.g. Data usage and actions)

It is primarily utilized by the administrators, supervisors, developers and designers.

Front-Room Metadata

Front-Room Metadata spans across the BI Technical Metadata and the Business Metadata, which is primarily used by the business users. Hence it needs to be developed in conjunction with business users. It covers the following components

• Data Model (Data structures masked with business terminology)
• BI System Front-End Components Metadata
• Standard Reports
• Data Structures in end-user layer for ad-hoc querying against RDBMS as well as MDDB
• User documents (e.g. manuals, glossary, business information documents, listing of special business events for justifying data trends)

Counterpoint Metadata

Counterpoint Metadata, as the name suggests, ensures the harmony in the BI system by establishing a trace for the handshake between Back-Room and Front-Room Metadata. Back-Room metadata ensures that the data from various data source systems is moving into appropriate BI system elements. Further Front-Room metadata aids in presenting this data to the business users in their native language i.e. business terminology. By the time the source data reaches the business users, it passes through a large number of processes and components and hence it is difficult to trace the data along this long path. In order to shorten this path without sacrificing quality and also ensure speedy design and impact analysis, Counterpoint Metadata is implemented. This spans across the Data Source Metadata and the Business Metadata giving traceability from source data to the business information (or vise versa) at various levels of abstraction. This is primarily used by analysts and designers yielding the following advantages.

• During requirement analysis and design phases, it is advantageous to understand the gaps in the data sources with respect to the business, before implementing the BI solution.

• During maintenance and support phase, this metadata enables rapid impact analysis, in case of
  • Changes in business definition
  • Changes in data source
  • Addition of a new data source mapping to existing business functionality.

Note- BIDS Solutions encompass the proprietary solutions from TCS covering Business Intelligence and Data Warehousing landscape.