Discover Visual Analytics Lineage using SAS and APIs

1 Like

I'm new to working at SAS, but not working with SAS. I recently worked with a customer who had a need centered on the lineage of SAS Visual Analytics (SAS VA) reports. I found this great SAS Blog post Discover Visual Analytics Report Paths with REST APIs from my colleague @cindywang. Cindy's article was helpful in learning about making API calls for reports and folders. I used those calls, incorporated them into my SAS code, and extended the functionality to filter columns as needed. The purpose of this article is to outline what I did and provide access to the code I created and used.

The following links are helpful to get a fast track on the SAS Viya REST API (SAS RA):

Visualize lineage within SAS VA

You may ask yourself: what exactly do we use do we use lineage for? Consider the relationship between data and the data usage to better understand how they're affected by changing the underlaying source data. Also, most companies like to have an insight in when, how and where their data is used and even more important which data isn't used.

Therefore, we will build here a complete script to gather this information from the SAS VA. First, one needs to obtain and understand the information that explains dependencies. While column level lineage is researched and developed within SAS, a role out would probably need to wait until end of year. As an alternative one can use the SAS RA. Here I will explain how to create o the following report:

Browsing through lineage

Display report relationships

From content to information

Using the REST API to collect report information and content has been a subject of many articles, such as how to access information via SAS VA. For instance use the code below to retrieve the content of a report:

/* create the report content */
%let BASE_URI=%sysfunc(getoption(SERVICESBASEURL));
filename rcontent temp ENCODING='UTF-8';

proc http METHOD = "GET" oauth_bearer=sas_services
    url = "&BASE_URI/reports/reports/&reportUri./content" out = rcontent;
    headers "Accept"="application/vnd.sas.report.content+json";
run;

libname rcontent json;

The library assigned as rcontent in the above example contains certain tables depending which elements are used within a VA report. The general table BUSINESSITEMFOLDER_ITEMS is always available and lists the columns from source, or constructed in the report. To separate the source columns directly used from this list of columns we apply the following command:

/*Query data source columns used in report  objects */
from rcontent.BUSINESSITEMFOLDER_ITEMS BF, rcontent.DATADEFINITIONS_BUSINESSITEMS DF
where ((BF._element='DataItem') and DF.base=BF.name);

Now that we filtered out the directly used columns, we are left with derived columns such as calculated items, custom category or even geographical items. Additionally, we also have the columns not used in the report. Here we will decide not to list these columns in the end result, but for certain purposes it might be interesting to add the derived columns. Instead, we will look at what source columns are used in these so-called derived columns by the use of the following table:

Table 1: Tables related to SAS VA items. Each source column in the BUSINESSITEMFOLDER_ITEMS table used in a SAS VA item, the table is listed and the join columns are mentioned creating a relationship between the two.
SAS VIYA ITEM	ITEM TABLE NAME	JOIN COLUMN	JOIN COLUMN BF
CALCULATED ITEM	ITEMS_EXPRESSION	VALUE
CUSTOM CATEGORY	ITEMS_GROUPINGPARAMETERS	PARAMETER	NAME
HIERARCHY	ITEMS_LEVELLIST	REFERENCE	NAME
GEOGRAFICAL	ITEMS_GEOINFOS	ORDINAL_ITEMS	ORDINAL_ITEMS
PRIMARY KEY	ITEMS_PRIMARYKEY	ORDINAL_ITEMS	ORDINAL_ITEMS
PARTITION	ITEMS_PARTITIONS	ORDINAL_ITEMS	ORDINAL_ITEMS
ORDINAL ITEM?	ITEMS_EDITORPROPERTIES	ORDINAL_ITEMS	ORDINAL_ITEMS

Using this information, we add the source columns that are used in the derived columns to our directly used source columns, with one exception. That is, the calculated columns because the these columns are mentioned as expressions, which we need to unravel to obtain the used source columns first. For each column we then record where it is used, so our report filters on calculate columns as an example. See the ITEMS_EXPRESSION table below with the expressions mentioned for each column.

As a final step to this exercise, we will relate reports we collected the lineage from in the previous steps to each other by similarities. I define these relationships here as:

column relations – reports share a common column
dataset relations – reports share a common dataset
library relations – reports share a common library

While these will do for the purpose of this article, there are other similarity measures to considered. But we will use the most obvious ones. We do so by using the columns collected from the previous steps.

As seen above it is simple to collect the lineage from the server of VA reports. We saw that there needs a little understanding of were to find what and by gathering this information we were able to give a brief overview of the lineage collected.

Do more with lineage

For displaying network, graphs one is free to explore other similarities than were stated in this article. Additionally, the inclusion of the derived columns to the lineage creates a more informational result. Finally, the collection of information about the derived columns is another example to get more information from the SAS Viya Report REST API.