Using DDC to Force VA Objects to Refresh

[#1]

In the series of articles that talked about integrating VA (SAS Visual Analytics) with SAS Viya Jobs via DDC (Data-Driven Content) objects, you saw a few examples where Viya Jobs were used to create CAS tables on the fly to be consumed by VA reports. To have the VA report refreshed with the new table content generated by the job, you leveraged VA’s capability to refresh objects.

Figure 1- Typical flow using auto-refresh feature

At a high level, the process can be described as (please, refer to the previous animated gif):

1. The DDC object in VA has an URL that points to the SAS Viya Job, which is configured with _action=form, so the Viya Job Form takes over the Viya Job Code execution and the form becomes the DDC implementation. Also, the other VA object that uses the CAS table created by the SAS Viya Job is configured with auto-refresh, which means that the object makes recurrent checks for table updates.
2. A change or action in the report that leads to a change in the data that gets passed to the DDC triggers the DDC execution: a JSON message with data and/or parameters is passed from VA to the DDC implementation code (JavaScript).
3. The DDC implementation code processes the JSON message and eventually makes an HTTP call to execute the Viya Job Code with _action=execute (or _action=json).
4. The Job executes and updates the CAS table.
5. Next time the VA object configured with auto-refresh checks for changes on the CAS table it identifies the table has changed and fetches its content to refresh the object.

Although this works perfectly, there are a few things you need to be aware of:

1. The refresh feature only works for reports open in view mode – it’s not available when you are editing the report. As a report author, you would need to go back and forth between the edit and view mode to test. This has been mentioned in the series of articles presented in the beginning.
2. You cannot prevent the auto-refresh from happening at the exact moment that the table is being recreated by the job code. If VA tries to fetch the table to refresh the object at the exact same time that the table has been dropped to be updated, the VA report issues an error. Because this is a rare event, this has not been mentioned in the series of articles for simplicity, but you should always account for this possibility to prevent occasional errors. One way you can prevent this from happening is by guaranteeing that the table always exists, and you can do that by working on a temporary table, and when the table is ready to be updated, you delete the existing records (instead of dropping the table) and append the temporary table into the empty table. This is not a big deal, but it adds extra steps to the job code.
3. VA objects that are configured for automatic refresh are not the best candidates for user interactivity simply because the user never knows the exact moment the object will be refreshed.

This article presents a solution that lets you refresh VA report objects on demand, and it solves all three limitations above. I must highlight, though, that those limitations are not critical at all, and you will be just fine if you leverage the out-of-the-box auto-refresh feature that VA provides, as long as you can guarantee that inexistent tables aren’t fetched automatically by VA report objects.

For those who want to take a different spin on how a DDC implementation can be used to control when VA report objects should fetch their CAS table data and get their content refreshed, keep reading.

The principle behind the solution is simple and relies on selection messages sent from the DDC to the VA report. If you want to know more about the basics of DDC and the messages exchanged with VA, please refer to this publication.

At a high level, the solution works like this: whenever the job ends execution successfully (meaning new data is available in the CAS table created by the job), the DDC sends a message back to VA informing that certain rows are being selected, and all report objects that are setup as target of a filter action, where the DDC is the source, will use the selected item from the message as the filter criteria to fetch data from the CAS table, and that refreshes the object.

 Figure 2- Typical flow using selection message

The previous animated gif illustrates the process, and as you can see, the difference, compared to the auto-refresh approach discussed earlier, relies on the filter action from the DDC to the object to be refreshed, and the selection message sent at the end (step #4).

The selection message tells VA that that certain data has been selected in the DDC. To help you understand, imagine the DDC implementation uses the data assigned to its Roles pane to produce a bar chart. That bar chart shows invoice by region (Asia, Europe, Africa, etc.). If you wanted to allow users to click in the bar chart and let VA know for example that Asia had been selected, you would have to set an event handler in the DDC bar chart implementation to detect when a bar has been clicked on, and send a selection message to VA informing which region had been selected so that other VA report objects that have actions associated with the DDC could act accordingly (such as filter the object to display only data for Asia). A selection message is a simple JSON structure that contains two values: the ID of the DDC object (so VA knows the origin of the selection message and therefore knows the VA objects that have actions defined with that origin), and the data selected. The data selected is simply a list of rows, in reference to the data that the DDC received from VA:

Figure 3- Example of selection message

Independently of what the DDC produces as the output (visible content like the previous bar chart or a table loaded in memory), we can always send selection messages to VA as if a chart had been clicked on.

You may be thinking that if the selection/filter of the data is what causes the VA object to refresh, how can we accomplish the refresh but display all the data, and not just a subset due to the filter being applied? That’s where the trick comes in.

Ideally the selection message would be such that it would tell the VA object that nothing had been selected (empty list of rows in the JSON message), and it would fetch all the data, but selecting nothing, which is the same as displaying all the data, is the default state of an object, and an object that is already displaying all the data will not be affected by a message that tells it to display all the data. The solution is to send two messages, separated by a time interval: the first message selects/filters a subset of the rows, the first row for example, and the second message selects/filters nothing, forcing all data to be fetched. It’s worth mentioning that the data/table that the DDC receives in the VA message and the CAS table that is created by the Viya Job and feeds other VA objects need to have a mapping, in other words, a data item that is common to those two tables, so the filter can occur (we will be talking more about it in a moment).

Figure 4- Two selection messages to implement the refresh

Let’s take a closer look into the JavaScript code that sends those two messages, forcing the VA object to refresh.

Figure 5- JavaScript fuction ForceVAObjectRefresh

The function forceVAObjectRefresh() implements what we have just described. It receives two arguments: resultName and millisecondsBeforeRefresh. The argument resultName is the ID of the DDC, which came in the JSON message that the DDC received from VA, and millisecondsBeforeRefresh is the amount of time you want to wait between the two selection messages that the DDC sends to VA. It was set as an argument to the function because you may need to tune its value according to your environment. You want to give it enough time for the first selection message to be processed by VA, before the second message is sent, otherwise the filter action that the DDC has with other objects may not be triggered. Usually, 1 or 2 seconds should work. To send the messages, you leverage the function va.messagingUtil.postSelectionMessage(), provided to you in messagingUtil utility file available in GitHub. The first time the function is called, you select row 0 (the first row). The second time the function is called you select nothing, which is interpreted by VA as no filter applied, but this second call only happens after the time you specified in parameter millisecondsBeforeRefresh, which is passed internally to function setTimeout(). Due to the asynchronous nature of this process, a Promise is being used to allow you to properly act once the function is fully executed, such as write the message “Done!” in the VA report if you want.

NOTE: function forceVAObjectRefresh() is available in GitHub in the messagingUtil.js utility file.

So far so good, but there is one more thing to discuss: the data items assigned to the DDC Roles pane, that in turn define the columns of a summary table in the JSON message that the DDC receives from VA, and the CAS table generated/updated by the Viya job not necessarily have the same columns, so how does the filter occur, if there may be no mapping between those tables? This is what you will see next.

All DDC objects must have at least one data item assigned in their Roles pane. Like said, the items assigned in the DDC Roles pane determine the columns passed from VA to the DDC, and the data is aggregated by the categorical columns (like a group by in SQL). If no categorical column exists, the DDC receives a table with only one row (the aggregated values for each numeric column that have been assigned). For this solution to work, you must have at least one categorical data item assigned to the DDC. The reason for that is because you need a data item that can be used to filter the output CAS table that feeds the VA objects, and only categorical data items are used in filter actions. When you are already sending data to your DDC, by assigning them to the DDC Roles pane, this requirement of at least one categorical data item may be already met, and when the DDC sends a message back to VA to filter the first row, it is the first row of the table that VA had sent to the DDC, so VA knows the values of the categorical data items of that first row. If the data item assigned to the DDC Roles pane has at least one similar column compared with the table that the Viya Job creates and stores in CAS, the action filter is already functional, but if the columns on that table have no corresponding data items in the DDC roles assignment, you must define their relationship via table mapping.

Independently if the mapping already exists or if you need to explicitly define it, the value of the categorical data item in the first row may not exist in the CAS table created by the Viya Job. When this happens, you see a brief warning message “No data matches the current filter” right after the first message is processed by VA and until the second selection message finishes the refreshing by bringing all the data, which is not ideal.

Figure 6- Filtering first row may produce an empty result set

In addition, it turns out there might not even be a mapping between those tables. What do you do in that case? Fortunately, the solution is simple: create a categorical calculated item that contains a fixed value (all values the same for all rows, such as “anything”) and assign this calculated item in the DDC Roles pane, then create the same categorical calculated item in the table that came from the Viya Job. This column becomes the natural table mapping column. In fact, creating that VA report calculated item in both tables (the table with data items assigned to the DDC Roles pane and the CAS table generated by the Viya Job), passing that calculated item to the DDC, and using it in the table mapping is highly recommended, even if a good candidate for a table mapping column already exists. This is because it contributes for a smoother data refresh in VA, either because it guarantees there will always be data to be filtered (the result set is never empty therefore no warning message is ever displayed), or because it produces the same result set, which includes all the data, whenever the first or second message that the DDC sends to VA is processed: remember that all values are the same in that calculated item, so if you filter the CAS table by the value of the calculated item in the first row of the aggregated table passed to the DDC, or if you don’t filter the CAS table at all, you will always get all the records in the result set, and the refresh experience is much better because there is no change when the second selection message is processed, although it is still needed for the solution to work. Besides, you have the chance to name the calculated item appropriately, such as “Refresh”, or even something more explainable like “For data refreshing”, to remind you that that data item is there solely for implementing the data refresh.

Figure 7- Filtering first row is the same as selecting all rows

To summarize, these are the things you need to do (supposing you ran the Viya Job at least once and the Viya Job output CAS table already exists and was already added to the VA report):

1. Create a categorical calculated item in the VA report with a fixed value (e.g. “anything”) in both the table that feeds the DDC as well as the one that was produced by the Viya job and feeds the VA objects to be refreshed.
2. Make sure the calculated item is used in the table mapping between those two tables.
3. Assign the calculated item to the DDC Roles pane.
4. In the DDC object, define a filter action with the VA objects to be refreshed.
5. Modify your DDC implementation code to call the function forceVAObjectRefresh() when the Viya Job finishes execution.

One more thing… when users open the VA report for the very first time, it might happen that the Viya Job output table doesn’t exist yet. In that case, the VA object will not be refreshed once the job finishes because initially there was no data in the object to be filtered. Once the job is executed and the table is created, users should just reopen the report, and they are back in business.

For an example of VA report and complete DDC implementation as a Viya Job, please check the attached example.zip file. Just start with the readme.txt file and follow the steps. The deployment of this example could have been simplified if we used a package, but it would require admin rights to import the package, so I’ve opted to provide you with the individual files. It’s a little bit more work to deploy it, but it’s good to expose the components that make up this solution. This example should work for Viya 3.5 and beyond.