4 ways to query the SAS Data Management repository

[#1]

Customers often ask where they can get the SAS Data Management Repository database schema in order to query from it. We don't provide that schema, however in SAS Data Management Studio, you can access four nodes to get information from the repository.

1) Repository Info node

The Repository Info node can list all the repositories that are defined on the Administration riser in SAS Data Management Studio -- that's the only Repository node that can list information coming from several repositories.

It can also list other objects (rules, tasks, sources, dahboards) that are in the current repository, which is the repository that contains the data job that contains this node. You can use this node to identify specific rules or tasks that you want to reference in a data job.

The properties dialog includes a "Listing Method" field that will allow you to select what output you want:

• Repositories lists the repositories defined on the Administration riser,
• Dashboards lists the dashboards from the current repository,
• Execution Sources lists the sources from the current repository,
• Tasks lists the tasks from the current repository,
• Rules lists the rules from the current repository.

This node can also return Tasks filtered by Name or Id, and Rules filtered by Name or Id, or Task Name or Id. When you select one of the Listing Method that requires a filter, you can either chose to filter the data using a field, or a value: use "value" if you already know the RULE_ID or TASK_ID you want to filter on; use "field" if your node has a parent that contains your filtered values.

2) Repository Primary node

The Repository Primary node lists summary information about the executions of all tasks in the current repository. The information for each execution includes the date and time of the execution, the task ID, the business rule ID, and other information that could be used as a reference in other nodes in a data monitoring job, such as number of triggers, status, dimension, importance, etc.

3) Repository Detail node

The Repository Detail node lists detailed information about the executions of all tasks in the current repository that have had triggers. It needs an EXEC_ID (Execution) in input, either from a field, or from a manual value you enter. It will then return the information for each execution, including values for each row returned by the task. You can then filter on a Rule Id too, using a value, or a field.

This node is often used in combination with other monitoring nodes. For example, you could create a data job where output from the Repository Primary node was sent to another node that selected the latest execution ID for a particular task. This execution ID could be sent to the Repository Detail node, which would list detailed information about that execution.

4) Repository Log node

The Repository Log node lists fields that are associated with the business rules that are defined in the current repository. This node is used by some solutions. It needs a Task Id and Rule Id, either coming from a parent node, or manually entered.