Access XMETA repository using SQL Views

Background

The contents of XMETA Metadata repository for InfoSphere Information Server 9.1 and prior releases is not directly accessible to any external application. This is due to the fact that it is designed using the EMF model. These models are often very abstract and generic, which makes them difficult to be understood by an application – especially if the application is not familiar with the semantics of the model. Further, the database tables and views of the metadata repository are not uniform across the different database platforms supported by Information Server. The problem is further compounded by the fact that database table and views names are very cryptic and not uniform across databases. Proper relationships are also not defined between database tables. The solution for this problem is SQL views.

What are SQL Views

SQL Views are set of predefined / custom database views defined on top of the XMETA metadata repository thus allowing users to access subset of XMETA metadata repository in read only mode. These views not only hide the internal information (eg: XMETA stores Dates as long values whereas views retrieves them as Dates field) and it's complexities of XMETA repository, it also provides uniform access across different database platform. SQL Views are grouped into separate schemas (aka View Schema) based on product/model area. These views can be directly accessed by any external or internal application using ODBC/JDBC. A dedicated Model Schema is also available for each of the View schema with tables derived from the views having same names and structure. These model schema can be used for development purpose. Primary and foreign keys that represents the original model references and inheritance relationships are defined on the tables. These model tables and associated keys can be imported to generate the reporting model from which different reports can be designed.

Why to use SQL Views

SQL Views makes XMETA metadata repository accessible through internal and external applications via JDBC/ODBC. SQL Views decouple applications from the underlying persistent model and minimize the impact of model upgrades and development locksteps. It also provides the work around from some of the internal proprietary XMQL limitation (eg: support for scalar functions). These views are uniform across database platforms and are easily consumable. Persistent data representations are converted to more friendly types in the views when applicable (eg: Long integers to SQL timestamps). All the internal system attributes (eg: lockids, blobs holding objects) are filtered out of the views. By creating SQL Views in separate and dedicated schema, the security is enforced at the database level whose access can be restricted. SQL views also gives the ability to filtered out in the view definitions

Where to use them:

SQL Views makes it possible to integrate any application which needs to access XMETA's metadata using JDBC/ODBC. It also can be used to integrate with any reporting tool like IBM Cognos. Starting InfoSphere Information Server 11.3, suite bundle comes with predefined set of sample Cognos report which can be directly used in IBM Cognos environment with minimal or no changes.

Available SQL Views with InfoSphere Information Server 11.3

Starting with InfoSphere Information Server 11.3, SQL Views are available in 3 different functional/domain areas. These functional areas are Common Metadata, Data Quality and Information Governance.

  - Common Metadata Views

Common Metadata contains SQL Views for subset of Common Model object types that represent database metadata that in usually imported by bridges or connector. These SQL Views describe hosts, databases, schemas, tables, views, stored procedures,and columns. In addition there are views for notes (annotations), users, and stewardship assignments.

- Data Quality Views

Data Quality contains SQL Views for object types from the data quality domain that are created in InfoSphere Information Analyzer: projects, data rules, data rule sets, data rule definitions, data rule set definitions.

- Information Governance Views

It contains SQL Views for object types from the Information Governance domain that are created in InfoSphere Information Governance Catalog, including categories,terms, labels, information governance policies, and information governance rules. This also includes SQL Views that link information governance objects to the data rules that implement them, and to other common metadata assets.

Disclaimer: “The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.”