Programatically listing data sources in SAS Visual Analytics

[#1]

"These reports look great! What data sources do they use?" This is a question that most every Visual Analytics report developer has been asked. And depending on the number of reports your colleague is describing, you might be facing a time consuming task of manually opening each report in SAS Visual Analytics and looking at the "Data" menu. 

Well I have great news! The SAS Viya reports service can answer this question for you.  All you need is access to SAS Studio 5.1 and a few lines of SAS code.

Let's imagine that you have folder in your Viya environment named "Report Examples."  Within this folder there are three SAS Visual Analytics reports: "Example Cars Report", "Example Class Report" and "Example Sales Report". These reports are shown in the image below:

So you need to quickly determine what CAS data sets are used by these three reports.  No problem!  The SAS code demonstrated in this article will produce the following output:

The above table shows a clean, comprehensive listing of all three report and their CAS data sources.  Pretty neat!  Imagine how useful this would be if you needed to look up data sources for 20 or 30 reports!  In this article, I'll show you the SAS code that will take a list of SAS Visual Analytics reports and leverage reports service tell you what their data sources are.

Let's begin by looking at the list of reports from which we want to create a list of data sources.  The SAS code below creates a simple data set with the report's Names and URIs. If you're unsure how to retrieve a Visual Analytics report's URI, my previous article will show you how.

/* Create a set of reports */
data reportList;
   length reportName $ 100 ID $ 100;
   input reportName $ ID $;
   infile datalines dlm=',';
   datalines;
Example Cars Report,277356fc-45b0-4693-b0d5-2cbb40ca3ec2
Example Class Report,3c66d0b4-e53c-4594-97b7-a5ab338ba523
Example Sales Report,e6887494-0e0b-46ae-a822-ae0f17f82e9f
;
run;

The output of this data set is shown below:

We can now use this data set to feed a SAS macro (named get_VA_report_datasrc) which will list each report's CAS data sources.  This macro will have two parameters passed to it: reportName and reportURI.

Let's begin by constructing the URL for our request.  According to the documentation, in order to retrieve this information we'll need to use the reports service to make a "GET" request to this URL.

https://www.example.com/reports/reports/{reportId}/content.

Because we are submitting our code in SAS Studio 5.1, we can dynamically determine the base address for the service call and place it in the macro variable "BASE_URI".  This is achieved by submitting the following %let statement:

%let BASE_URI=%sysfunc(getoption(servicesbaseurl));

Also we can see that we will need the report's ID (or URI) from our 'reportList' data set to be placed in the request.  This can dynamically be fed to a SAS macro using a parameter named: 'reportURI'.  Using these two macro variables call becomes:

&BASE_URI/reports/reports/&reportURI/content/.

Now that we have the base URL we can start constructing our PROC HTTP code to retrieve the report's contents. 

The get_VA_report_datasrc macro begins with creating a temporary filename (named rcontent) to capture the output from the report's service.  Next comes the PROC HTTP request with our new, dynamic URL.  There is an out= statement which will capture the service's response.  Also, notice that we input a value vnd.sas.report.content+json in the "headers" section.  We do this to ensure that the reports service returns a json string as the response. After this, the call is sent and the response is returned.

Since the format of the response content that is now held in the 'rcontent' is a json string, we can now use the JSON libname engine to read this content. By simply assigning a libname to the rcontent file, the response from the reports service becomes available as SAS datasets within the library rcontent.  Specifically, the data set that contains the report's list of CAS data sources is named datasources_casresource.  We take this list of CAS data sources from this data set and place them in a new table (named listdatasources) along with the report's name and URI.  Finally, the listdatasources data set is merged back into the reportList data set.

%macro get_VA_report_datasrc(reportName,reportUri);
/*retrieve service endpoint*/
%let BASE_URI=%sysfunc(getoption(servicesbaseurl));

/* create filenames to hold responses*/
filename rcontent temp;

/* Make request */
proc http 
	 oauth_bearer=sas_services	 
	 method="GET"
     url="&BASE_URI/reports/reports/&reportUri/content" 
	/* place response in filenames */
	 	out=rcontent;
		headers
		"Accept"="application/vnd.sas.report.content+json";
run;

/* read in response */
libname rcontent json;

/* create reporting dataset */
data listdatasources;
	length reportName $ 100 id $ 100 table $ 32 library $ 32;
	set rcontent.datasources_casresource;
		reportName = "&reportName";
		id = "&reportUri";
		keep reportName id table library;
run;

/* merge reporting dataset to the reportList dataset */
Data reportList;
	Merge reportList(in=T1) listdatasources(in=T2);
		If T1;
		by ID;
run;
%mend get_VA_report_datasrc;

And that's it!  Our macro is complete.  We call this macro within a data step by using the CALL EXECUTE routine and passing the value of the reportName and reportURI variables its &reportName and &reportURI parameters respectively.

data _NULL_;
set reportlist;
call execute('%get_VA_report_datasrc(' || trim(reportName) || ',' || trim(id) || ');');
run;

The output from the macro is an updated version of the reportList SAS data set.

All we have to do now is print out the results to our SAS Studio 5.1 output window.  This can be done using PROC REPORT:

/* Print the results! */
title "The Report(s) Data Sources are Listed Below"; 
proc report data=reportList nowd;
	columns ID reportName Library table;
	define ID / group 'Report ID';
	define reportName / group 'Report Name';
	define Library /display 'CAS Library';
	define table /display 'CAS Table';
run; 

And that's it.  We have successfully used the reports service to tell us what data sources are used in a report!  From the output above, we can see all three Visual Analytics reports and their associated data sources (all of which are within the "PUBLIC" library in our Viya environment!)

How to make this example work for you

This example was created in SAS Viya 3.4.  All code in this article is intended to be submitted in a SAS Studio 5 session within a Viya 3.4 environment which contains the SAS Viya services that are being called. 

On GitHub, you will find all the code discussed in this article.  The code includes the creation of an example reportList SAS data set with example reports.  This data is then fed to the %get_VA_report_datasrc using the CALL EXECUTE routine.  While the reportList can also be sourced from other REST API calls, it must contain the variables reportName and ID.

 Take Me to GitHub!