How to get Teradata metadata - basic architecture/terminology question

Database
Enthusiast

How to get Teradata metadata - basic architecture/terminology question

(Not Teradata knowledgeable) I am getting ready to configure a metadata extraction tool and need to request the appropriate database access(es). Generally speaking, the tool uses read-only access to a DBMS's catalog or system tables.
We want to extract metadata (table definitions, etc) from across several of what I would call "schemas" or "projects".

So we might want to extract metadata for the Supplier Database, the Claim Warehouse, and the Claim ODS, for example.

Not knowing much about Teradata, I do not know what terminology to use in asking for the access - Database, Schema, Warehouse, Instance? We want to select Teradata system catalog data which spans "schemas" or "projects" in order to analyze the naming, definitions, etc. after they are loaded into the metadata repository tool we are using.

I assume that the "user" would be the metadata extraction tool itself, running a scheduled job during off-hours.

Would the request be different if we were asking for access to actual data, rather than metadata? How would the request differ?
How do we express our requirement to the DBAs?

Any illumination will be appreciated.
CW
4 REPLIES
Enthusiast

Re: How to get Teradata metadata - basic architecture/terminology question

The data dictionary views you would likely need access to include:

DBC.Tables - Includes DatabaseName, TableName
DBC.Columns - Includes DatabaseName, TableName, ColumnName, ColumnType, ColumnLength, and other attributes about the columns of the table.
DBC.Indexes - Should include information about the indexes created on a given table. (Can't remember if this is the base table or view)

Things to keep in mind:

In Teradata objects such as Macros and Stored Procedures are included in the data dictionary table DBC.TVM. The TableKind column helps distinguish from the various types of objects in this table.

In Teradata a user is a database with a password. It can have perm space and own objects as well.

Hope this helps.
Enthusiast

Re: How to get Teradata metadata - basic architecture/terminology question

That is helpful, Rob. We will be mainly interested in tables, columns, views, and "view columns".

1. One of my team members (also not familiar with Teradata directly) has mentioned that they hear a system development project team talking about a "Virtual Data Mart (VDM)" in Teradata. Is that anything different from a set of SQL views defined to give the appearance of a dimensional organization of the underlying data?

2. Can you clarify your statement that "a user is a database"?

3. Lastly, how are the objects organized into groups for assigning privileges? Is there such a thing as a "schema" to group a set of related, interdependent objects?

Thank you for your assistance. It is much appreciated!
CW
Enthusiast

Re: How to get Teradata metadata - basic architecture/terminology question

1) A Virtual Data Mart is likely a set of views or aggregation tables used to facilitate reporting. The information about the objects will be retained in the same set of views/tables in the data dictionary views.

2) A user is a special database in Teradata in that it can logon to the system as it has a password. I mentioned it because you may see records returned from DBC.Tables where the DatabaseName is that of a user. A user can own objects such as views, macros, tables, and stored procedures. Some do not require the user to have any permanent space assigned to them. I mentioned it purely as an FYI.

3) Objects are typically grouped in databases. The best practice in Teradata is to assign accessrights to objects using roles and then assign one or many roles to a user depending on that users need (or function in the organization). It is not uncommon to find objects grouped in databases based on the "type" of table it is. For example, you may find dimension tables (lookup) grouped together in a single database or multiple databases by subject and then the fact tables (historical tables containing metrics) stored in a single or multiple databases by subject. I would suggest that your team work with either the DBA or data modeler/architect to get a "lay of the land" for your particular environment.
Enthusiast

Re: How to get Teradata metadata - basic architecture/terminology question

Thanks much!! Very helpful. Cheers.