Metacoda’s Metadata Explorer Plug-in

Update 15Jun2015: The Metadata Explorer plug-in discussed in this blog post was first made publicly available with Metacoda Plug-ins V3.0. It has been enhanced in subsequent releases and now supports additional SAS platform versions beyond those originally mentioned in this post (such as SAS 9.4). For more information on supported SAS versions, please see the Metacoda Plug-ins System Requirements page.

My work at Metacoda involves lots of metadata, as does my work as in SAS® platform administration. I spend a lot of time looking inside SAS metadata repositories, searching for objects, looking at their attributes, following their associations to other objects, and generally getting to grips with the SAS Metadata Model. I write programs that use the SAS Open Metadata Interface, primarily using the SAS Java Metadata Interface, but sometimes using the SAS Language Interface to Metadata usually via PROC METADATA.

When I originally started to explore metadata, I used the metabrowse feature in Foundation SAS. I really liked it, but I’m not always working on, or have easy access to, a machine that has Base SAS installed (and I wanted to do some more extensive searching). I do spend a lot of time inside the SAS Management Console though. I tried using the XML Metadata Interface in SAS Management Console 9.1 for a short while but found it made me think too much about how to do the query, rather than what it was I was looking for. I also found working with the raw XML results returned by the XML Metadata Interface plug-in was a bit challenging too and it distracted me from my original purpose of finding metadata. As an aside, the SAS Management Console XML Metadata Interface plug-in is no longer available by default in SAS Management Console 9.2 and 9.3, but it is still available. If you need it, you can find instructions on how to enable it in a SAS Global Forum 2012 paper.

Now, as many programmers do when they can’t quite find what they are looking for, they write their own tools, and this is what I did. “scratching an itch” is a term I often hear programmers use to describe this. My goals for this ideal metadata exploration tool, which evolved over time, were along these lines:

• To make it so easy for me to search metadata that I could concentrate on what I was looking for, rather than how I might go about finding it.
• To display the results in a tabular form so that I could readily browse and sort them.
• To allow me to explore the results in meticulous detail, looking at any object, all of it’s attributes and values, each of it’s associations and associated objects and then follow those down to associated objects at any level.
• To perform really well so I can find things quickly.
• To protect me from queries that might take too long and/or return too many results.
• To allow me to look at every object of a particular type when I know there wont be many thousands of them.
• To allow me to bypass the simple search and do a more complex one (requiring more thought) when I absolutely had to.
• To allow me to work with multiple searches for multiple trains of thought.

Gradually this grew into a very handy internal Metacoda tool that I personally find invaluable for navigating and exploring SAS metadata. Recently we decided that, since others might find it just as useful, we’d offer it as a free Metacoda utility. This is the Metacoda Metadata Explorer plug-in and we are making it available for free (registration required) in the next major version of our Metacoda Plug-ins. We’ve just released a V3.0 BETA2 for testing if you’d like to try it. At the same time we’re also making one of our existing security plug-ins, the ACT Reviewer, available for free too. I wont attempt to say we are doing it for purely selfless reasons. I’m sure it’s obvious to anyone that one of our goals here is to get more eyeballs on our software, to spread the word, and hopefully get more customers so we can grow as a company and implement all of our other ideas.

So if you are interested, how would you go about getting it? Well firstly you have to have a SAS 9.2 or SAS 9.3 installation that includes a SAS metadata server, and you also need to have access to a SAS Management Console client installation into which you can install the Metacoda Plug-ins. The next step is to head over to the Metacoda Account / Evaluation Request form and fill it in to request an account. We’ll then generate a user id and password for you so you can log in and download the software. If you only want the free stuff then that’s it, other than coming back from time to time to get new versions. If you’d like to try out the other plug-ins too, and we hope you do, then let us know and we’ll give you an evaluation license file to enable them. You can try out the non-free plug-ins without an evaluation license file but they run in a limited evaluation mode and only display a small subset of your metadata. The license file gives you temporary access to all of the metadata. The free plug-ins (Metadata Explorer and ACT Reviewer) give you access to all of the metadata without needing to get a Metacoda Plug-ins license file.

Now down to the techie stuff about the Metadata Explorer. With Metacoda Plug-ins installed, you’ll find it in the SAS Management Console Plug-ins tab, underneath the Metacoda icon and the Utilities folder. Here’s a screenshot of the initial view with those items highlighted:

At the top there’s a search field where you type in a phrase you are looking for and press enter or hit the search button. By default it will search across all public types doing a case-insensitive ‘contains’ search of the Name attribute with a limit of 2,000 objects. Here’s a screenshot of a search for the word “region” showing one of the objects selected and the metadata attributes tab for that object:

… and another showing the associated objects tab:

… you can also double-click or right click on any object or associated object to look at an Advanced Properties dialog for that object. This can be repeated to follow/explore the chain of associations.

If you want more control then you can click the “Search Options” button to open the search options dialog. This allows you to do things like:

• Toggle between case-sensitive and case-insentitive searches.
• Switch between searching public types or model types and choose an arbitrary subset of types to search.
• Change the object limit to look for more, or less objects, and if you’re feeling very brave, to make it unlimited.
• Switch between searching the name only, the name and the description, or all character attributes.

Here are a couple of screenshots of the search options dialog showing both the public type list and the model type list.

Many times I find the simplicity of this search is enough for my needs, allowing me to find objects without having to think too hard about how to find them. However sometimes you need more power and so there is also the ability to run special mode queries. This is done my enclosing the query text in {{{ and }}}. The first type of special mode query is the _ALL_ query which returns all objects (subject to any type selection and object limit specified in the search options). All other special mode queries are treated as raw XMLSELECT search criteria, as you might use if you are programming against the SAS Open Metadata Interface yourself, but with the benefit of being able to easily browse and explore the results. XMLSELECT searches can be quite hard to get your head around but are very powerful in narrowing down your search. To give you an idea of the flexibility that’s available when you need it, here are a few examples of special mode queries (mostly standard XMLSELECT queries with a few extra curly brackets):

{{{ _ALL_ }}}

Return ALL objects taking into consideration model types selected and limit specified in the search options. Should be used with care.

{{{ *[@Name contains ‘server’] }}}

Find any object whose Name attribute contains ‘server’ subject to case-sensitivity, model types selected, and limit specified in the search options.

{{{ *[@Name=:’sas’] }}}

Find any object whose Name attribute begins with ‘sas’ subject to case-sensitivity, model types selected, and limit specified in the search options.

{{{ *[@Name=’sasadm’] }}}

Find any object whose Name attribute equals ‘sasadm’ subject to case-sensitivity, model types selected, and limit specified in the search options.

{{{ *[@MetadataCreated gt ’01Jul2012:00:00:00′ or @MetadataUpdated gt ’01Jul2012:00:00:00′ ] }}}

Find any object created or modified after the specified date/times subject to model types selected, and limit specified in the search options.

{{{ Identity[@Name contains ‘sas’] }}}

Find any object with model type of Identity or any of it’s sub-types (i.e. Person or IdentityGroup) whose name contains ‘sas’ subject to case-sensitivity, and limit specified in the search options.

{{{ SASLibrary[@Name ne ”] }}}

Find any SAS library without a blank name (i.e. all libraries) subject to limit specified in the search options.

{{{ SASLibrary[@Name ne ”][DeployedComponents/ServerContext] }}}

Find any SAS library (without a blank name) that is assigned to a SAS application server.

{{{ SASLibrary[@Name ne ”][DeployedComponents/ServerContext[@Name=’SASApp’]] }}}

Find any SAS library (without a blank name) that is assigned to a SAS application server with the name ‘SASApp’.

{{{ PhysicalTable[@Name ne ”][Columns/Column[@Name = ‘deptid’]] }}}

Find any table (with a non-blank name) that contains a column with the name ‘deptid’, subject to case-sensitivity, and limit specified in the search options

{{{ Column[@Name ne ”][Table/Table[@Name = ‘depts’]] }}}

Find all columns (with a non-blank name) on any table named ‘depts’ in any library.

{{{ Column[@Name ne ”][Table/PhysicalTable[@Name = ‘depts’]][TablePackage/SASLibrary[@Name=’hr library’]] }}}

Find all columns (with a non-blank name) on any table named ‘depts’ in any library named ‘hr library’.

{{{ Column[@Name ne ”][Table/PhysicalTable/TablePackage/SASLibrary[@Name=’hr library’]] }}}

Find all columns (with a non-blank name) on any table in any library named ‘hr library’.

{{{ ClassifierMap[@PublicType=’StoredProcess’ and @MetadataCreated gt ’01Jan2012:00:00:00′] }}}

Find all SAS stored processes (via the public type) created after the specified date/time.

If you have a SAS 9.3 installation, then you can also do several of the queries above (but not all), in the new Search tab in SAS Management Console 9.3.

The Metacoda Metadata Explorer plug-in doesn’t provide any facilities to make changes (add, update, delete etc.) to any of the metadata objects found. As with our other plug-ins, the software is completely read-only at this stage. This allows you to explore your metadata with the confidence that you won’t break it. If you find something you need to change then I’d recommend using the standard features in the SAS Management Console or other SAS clients to make those changes. We may provide update features at some point in the future, but at this stage we haven’t had much call from our customers for this capability – but some have told us they do like the safety of read-only access :)

I hope you find this utility interesting, want to give it a go, and get as much use out of it as I do. If you have any suggestions or questions please let me know.