(what are?) Shared Table Definitions

Information Server 8.x introduced a concept called a “Shared Table Definition.” This is a “container” for Table and Column metadata. It holds the Column names and other critical metadata (sql data type, length, etc.) for your relational tables. We call it a “Shared Table” because it is automatically shared among all of the tooling within Information Server and Foundation Tools.

A Shared Table is immediately visable (and usable) in Information Analyzer, FastTrack, Metadata Workbench and Business Glossary. It is also available for DataStage and QualityStage via the Metadata Sharing option within the DataStage Designer. Shared Tables are “like” DataStage Table Definitions, but with a broader, more enterprise scope. DataStage Table Definitions are still supported as always, but are unique to the DataStage environment. Inside of DataStage, a Shared Table appears in the Repository listing with a little white “book” icon above it. I always call it the “dove” icon because it looks to me like a bird. 🙂

Shared Tables often reflect a “real”, physical table. They have a multipart name that includes Host, Database, Schema and Table and finally Column names. Shared Tables can be created in a variety of ways:

— Importing tabular metadata via “Connector” inside of FastTrack, Information Analyzer, or the DataStage Connector Wizard (under import…table defs).
— Importing tabular metadata via the Import/Export Manager using Bridges and/or Brokers
— via the “Create Shared Table from Table Definition” option from within DataStage
— via a new utility that allows the creation of Shared Tables and Data Files (also known as Shared Files) from .csv [saving that subject for another blog entry]
— via export of physical models from InfoSphere Data Architect (which could have imported the model(s) from other design tools)
— via Optim export from InfoSphere Discovery

Once they are created, you can see them in the “tree” views of FastTrack, IA, and Workbench, and also delete them in most of those Information Server clients…..each client is looking at the exact same copy of the metadata — so there is absolute consistency between the views, regardless of which client you are using or which role you play in the organization.

These Shared Tables drive the integration activity, or the review of such activity, throughout Information Server, and they also serve as an important “anchor” for concepts that help enable strong information governance. Shared Tables retain associated profiling information (domain population details, data quality assessment history, etc.) from InfoSphere Discovery and Information Analyzer. Shared Tables can be assigned a Data Steward — a subject matter expert who is most familiar with a particular table and its value to the organization. Shared Tables and their Columns can be assigned to a Business Term — or to multiple Business Terms….to futher define them and qualify their purpose and meaning to the enterprise. Perhaps most important, Shared Tables are the key sources and targets for Data Lineage reporting and analysis. Shared Tables are “connected” for data lineage purposes via DataStage Jobs or Extension Mappings that represent processes external to Information Server and Foundation Tools.

Related to a Shared Table is a “Data File”. A Data File is also a container for Fields (or Columns), but is associated with a sequential or flat file instead of an RDBMS. A Data File has similar characteristics, and participates in data lineage and metadata enhancement (Stewards, Terms, etc.) like Shared Tables, but has a different icon when viewed within the Metadata Workbench. The naming convention for a Data File is Host, DataFile name, and Data File Structure….and then Fields. Today, Data Files are uniquely used by DataStage, and created from there using the “Create Shared Table from Table Definition” as noted above. The difference is in the “Locator” tab of the original DataStage Table Definition. If it says “sequential”, then a Data File will be created instead of a Shared Table. However, a new utility just became available that allows Data Files to be created at the command line. I will be writing another entry in the blog to describe this new utility that provides new flexibility for creating these useful objects.

Ernie

Advertisements

3 Responses to “(what are?) Shared Table Definitions”

  1. Jonathan Moulds Says:

    Great Post Ernie.

    I have a question on shared table definitions though.

    In an environment with a DEV, TEST, UAT & PROD environment these shared tables will often exist in different schema’s and in most cases different hosts. For instance a table called CUSTOMER will live in a schema called DEV_DW in dev & UAT_DW in uat and just DW in production. Using the shared connector import the table definition would be called DEV_DW.CUSTOMER with the locator and all references pointing to DEV_DW. How to we go about making the definition more generic so that when we promote between environments the shared metadata is still maintained but the reference to a specific environment is removed.

    Thanks In Advance.

    • dsrealtime Says:

      I’ve seen a variety of ways that this is done, the most common being that there is an Information Server that has all of the schemas and then only the “production” set of tables might get exported to the production metadata environment, if the policy is to not perform any import/export manager or Connector imports to that production machine. The tricky part is not the migration of the tables, but the migration of Business Terms in business glossary that might “point” to those tables as assigned assets. Business Glossary has available in its archiving and migration mechanisms the ability to use a mapping file that will globally change all reverences to “devSchema” to “prodSchema” and so forth, depending on the property in question.

  2. Jacob Chen Says:

    Very helpful info Ernie.

    Sometimes, developers use complicated user-defined SQL, e.g. mulitiple table join, to pull the data out of the database in the DataStage job. I am wondering how do u build the data lineage in such case.

    Thanks,


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: