Somebody once said that relationships are not easy. Well now they are—in SAS 9.4, at least!

SAS objects have inherent relationships and dependencies, and a change to one element can often have an impact on one or many related objects. For example, for SAS Visual Analytics reports to work as designed, they need to have access to the data that was used to create them. However, when looking at a report, it is often difficult to determine which data table is the source, what columns are used in the report, or what LASR library the data uses? Enter the batch relationship reporting tools.

The batch relationship reporting tools were introduced in SAS 9.4. These tools enable you to identify relationships among the content objects in the SAS Folder tree. For example, you can identify:

• the data that a report depends on—down to the column level
• the jobs that contain a given data table or transformation
• the tables that that are associated with a given library

If you are familiar with the SAS BI Lineage plug-in in SAS Management Console, the SAS 9.4 relationship reporting provides similar functionality. It’s main advantaged is that these batch tools cover a much wider variety of objects.

Relationship reporting tools

The batch relationship reporting tools are located in the SAS install directory under SASHome in SASPlatformObjectFramework/9.4/tools.

Generating a report is a two-step process, using these tools:

• Use  the Relationship Loader batch tool (sas-relationship-loader), located in the admin sub-directory of the tools directory, to scan folders and objects, retrieve their relationship information, and load the information into a database in the Web Infrastructure Platform Data Server.
• Use the Relationship Reporter batch tool (sas-relationship-reporter) to read the database populated by the relationship loader and report on the relationship between objects.

As an alternative to using the relationship loader to load the relationships, you can configure a regularly scheduled job that scans and loads relationship data for all of the content objects in the SAS Folder tree. The instructions to setup a regular loads of relationships can be found under Configuring Automatic Relationship Loading in the SAS(R) 9.4 Intelligence Platform: System Administration Guide.

How to load relationships to the database

The first step in reporting is to load the relationships to the database. The sample command below will load all the relationships to the database. You must pass the web server connection properties, the host and the port, the unrestricted administrative user on the metadata server, and the unrestricted user’s password.

./sas-relationship-loader -host sasserver.mypathname.com
-port 1234 -user abcdef@abcdef -password Xyz123 -loadAll

The –loadall option specifies that relationships are to be loaded for all content objects in the SAS Folders tree. An alternative to the –loadall option is to specify content filters to select the objects whose relationship data is to be loaded.

The Relationship Loader will provide feedback to the screen as it loads the relationship information. The information is stored loaded to a database in the Web Infrastructure Platform Data Server.

How to report on relationships among SAS content objects

To report on the relationships, choose from four standard reports or select your own options to produce more fine-tuned results. Reports can be written to the console or to a file in either comma-separated values (CSV) or text file format.

To select the objects whose relationships you want to report (referred to as subjects), you can specify one or more locations in the SAS Folder tree, or you can specify filtering criteria.

The standard reports are:

• Lineage which reports the objects that each subject depends on. An object depends on another object if it cannot function or cannot be defined without the other object, for example, a report is dependent on a LASR table
• Impact which reports objects that depend on or contain each subject. An object contains another object if it cannot exist out with the other object, for example, a table contains columns.
• Direct dependencies which reports the objects that each subject directly depends on.
• Indirect dependencies which reports objects that each subject depends on either directly or indirectly.

Example of a direct dependencies report

Let’s look at an example of a report, the command below uses the –report option to execute the standard report on direct dependencies for objects in the /Demos folder.

./sas-relationship-reporter -host sasserver.mypathname.com -port=1234 
-user abcdef@abcdef -password Xyz123 -report directDependencies “/Demos” 

The output generated shows the Visual Analytic reports, and the objects that they depend on, in this case the LASR tables, LASR library and Load job. The relationship direction is noted with an arrow.

“/Sales/Reports/Dashboard” (SAS report (2G))
–> Is dependent on: “/Sales/LASR Data/DASHBOARD” (Table)
–> Is dependent on: “/Sales/LASR Data/LASR Library” (Library)
–> Is dependent on: “User Written” (JobTransformation) – in “/Sales/LASR Data/DASHBOARD 
– Load Job 0925140747AM” (Job) 

“/Sales/Reports/VAD – Monthly Sales and Business Profit” (SAS report (2G))
–> Is dependent on: “/Sales/LASR Data/PROFIT_COMPANY_PRODUCT_MONTH” (Table)
–> Is dependent on: “/Sales/LASR Data/LASR Library” (Library)
–> Is dependent on: “User Written” (JobTransformation) – in “/Sales/LASR Data/PROFIT_COMPANY_PRODUCT_MONTH – Load Job 0925140749AM” (Job)

Example of an impact report

What if you want to determine the impact of changing one LASR table? This example selects an individual LASR table and generates an impact report. The output shows that five Visual Analytics Reports would be impacted by any change to the PROFIT_COMPANY_PRODUCT_MONTH LASR table.

./sas-relationship-reporter -host sasserver.mypathname.com -port=1234 -user abcdef@abcdef 
-password Xyz123 -report impact “/SharedData/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH(Table)”

The output generated shows the Visual Analytic reports, and the objects that they depend on. The relationship direction is noted with an arrow.

“/Shared Data/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH” (Table)
< – Is dependent on: “/Demos/VAD – Monthly Sales and Business Profit” (SAS report (2G))
< – Is dependent on: “/Demos/VAD – Sales and Profit Analysis” (SAS report (2G))
< – Is dependent on: “/Demos/VAD-Sales and Cost Trend – Profit by Product” (SAS report (2G))
< – Is dependent on: “/Demos/VAE – Profit and Sales Bubble_report” (SAS report (2G))
< – Is dependent on: “/Demos/VAE – Profit by Product Hierarchy_report” (SAS report (2G))
< – Is dependent on: “Table metadata update” (JobTransformation) – in “/Shared Data/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH – Load Job 11251           40817PM” (Job)
< – Contains: “/Shared Data/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH – Load Job 1125140817PM” (Job)

Using relationship content filters to subset reports

There are many ways to use filters to subset the reporting. Relationship filters can subset the relationship types and scope to be included in the report output. Relationship content filters select the related objects that are to be included in the report output.

To the subset the subject of the reports you can use the object path, as we did in the first two examples, or you can use content filters to select the objects that you are interested in. Content filters can be defined on a variety of metadata attributes including created or modified date, attribute name and value, name, description, keyword or object type.

If you want to report on certain types of subject, for example SAS Visual Analytics reports, the first step is determining the public object types to include in the query. To do that open up the object type dictionary in SAS Management Console. In SAS Management Console select the Folders tab and expand System ? Types ? to browse the public type definitions.

Select SAS report (2G), right-click and select Properties then select the Advanced tab. Note the TypeName is Report.BI, this is the value that needs to be passed to the types option of the tool. It is the public type of the object. NOTE: the values must be passed in this exact case.

This example will create a lineage report for each SAS Visual Analytics report in the repository.

./sas-relationship-reporter -host sasserver.mypathname.com -port=1234 -user abcdef@abcdef 
-password Xyz123 -report lineage -type Report.BI

For each report the output shows the table that the report is dependent on, the library that the table is dependent on and the columns that the table contains.

“/User Folders/Susan/My Folder/Cars Summary Report” (SAS report (2G))
–> Is dependent on: “/Shared Data/VisualStatistics/LASRTables/CARSSASHELP” (Table)
–> Is dependent on: “/Products/SAS Visual Analytics Administrator/Visual Analytics LASR” (Library)
–> Contains: “Cylinders” (Column)
–> Contains: “DriveTrain” (Column)
–> Contains: “EngineSize” (Column)
–> Contains: “Horsepower” (Column)
–> Contains: “Invoice” (Column)
–> Contains: “Length” (Column)
–> Contains: “Make” (Column)
–> Contains: “Model” (Column)
–> Contains: “MPG_City” (Column)
–> Contains: “MPG_Highway” (Column)
–> Contains: “MSRP” (Column)
–> Contains: “Origin” (Column)
–> Contains: “Type” (Column)
–> Contains: “Weight” (Column)
–> Contains: “Wheelbase” (Column)

This blog has only scratched the surface of the Batch Relationship Reporting Tools.  The tools are fully documented in the SAS 9.4 Intelligence Platform: System Administration Admin Guide.