~Contributed by Varsha Chawla, SAS~

What could a business analyst possibly like better than understanding the data in a database? Try understanding the metadata (data about data) and knowing how to access this in an ORACLE database. According to Carole Jesse, Senior SAS Analyst at Prime Therapeutics, databases have their blemishes and their beauty marks. Understanding metadata can greatly help analysts with determining transferability of code, indexing in order to allow faster retrieval of records, and understanding which tables can be merged based on primary keys.

Carole started her presentation by saying that most organizations have an IT (RDBMS, server admin, data) and business side of the house. In addition to those teams, they are supposed to have an advanced analytics data support team. Really? Not in Carole’s world! She thinks this is an urban legend, which is exactly why she decided to take matters into her own hands.

Carole’s paper covers five Base SAS scripts. She explores SYS views and then focuses on the ALL_ and USER_ families since these are accessible to analysts. She then goes through a macro for creating a listing of the first set of observations in each view from a customized list of view names. This is super useful for those who simply want to explore some of the content in the SYS views. Carole then discusses how to determine table partitioning and who has certain database roles and privileges, and she finally provides a way to analyze the index structure for a particular table in a schema. She mentions both LIBNAME and pass-through SQL queries, but I get the feeling that she favors the latter.

Enough of the details, at this point, I have learned a ton and having used ORACLE databases before, I start to wonder how any business analysts or statistical modeler ever lived without these useful metadata tips. Although Carole’s talk only lasted about 20 minutes, I feel confident that attendees took away years of experience and advice. And that’s what SAS Global Forum is all about. Great job, Carole!

A friend of Carole's video-taped her presentation and posted it to YouTube, so you can now watch her presentation online.