Lookup Tables in SAS Intelligent Decisioning 5.4 (SAS Viya 3.5)
Deploying, implementing, and supporting software requires product knowledge "beyond the demo." So let's take a deeper look at SAS Intelligent Decisioning (SID) Lookup Tables.
In theory lookup tables are simple. They just hold reference data to be joined into transactions, summary tables, etc.. In SID, however, they have an unique implementation and unique considerations. For example they manifest differently in each of the different publishing destinations -- CAS, Teradata, Hadoop, and MAS. So, let's dive in and see what we need to know to utilize SID lookup tables in our decisioning environment optimally. .
SID Lookup Tables are Static
When creating SID Lookup Tables, you do not simply point to a CAS table. You create it manually (or import it) in the SID User Interface. So, if you want your lookup data to change, you'll have to go into the SID interface and change it. (We'll hint at possible hacks to get around this later.)
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
SID Lookup Tables can be "In-Line" or "Shared"
Depending on the lookupStaticBinding setting, SID lookup tables will either be coded into the published Rule Set/Decision (In-Line), or they will be treated as shared components (Shared).
Basically, when lookupStaticBinding is set to yes, the lookup table is hard-coded into the published rule set (or decision). Thus, any subsequent changes to the lookup won't be reflected in the rule set. When it is set to no, rule sets are published referencing the lookup table as a shared component. So the rule set will use the latest version of the lookup at execution time.
Bottom Line:
- When lookupStaticBinding=No ("Shared"), changes to the lookup table will be reflected in previously published Rule Sets.
- When lookupStaticBinding=Yes ("In-Line"), changes to the lookup table will not be reflected in previously published Rule Sets.
NB: Changing the lookupStaticBinding value does not affect previously published rule sets. So changing the lookupStaticBinding to no will not change the behavior of rule sets published when it was set to yes and visa-versa. .
Publishing Destination Considerations -- Shared Lookups
This is where the story ends for in-line lookups. For in-line lookups (lookupStaticBinding=Yes), the lookup is coded into the rule set that references it. Thus, in-line lookups work the same across all publishing destinations, CAS, MAS, Teradata, and Hadoop. They just become part of the published code.
Shared lookups (lookupStaticBinding=No), however, have special considerations for the different publishing destinations .
CAS shared Lookups -- CAS Formats
CAS shared lookups are deployed as CAS Formats. If you're not familiar with SAS/CAS formats being used as lookups, see here. Basically PROC FORMAT is used to load a table's contents into a Format with one variable being assigned as a "lookup" value and another field being assigned as a "return" value. When the format is applied, it can translate the data from one "format" to another format. In the above example, a formatted "0" becomes "Nooooo!!!!!" and a formatted "1" becomes "Do it!!!!"
.
Since CAS shared lookups manifest as formats, you'll need to make sure your Viya configuration is equipped to support them. Specifically check the sas.referencedata.casformats settings as well as the casstartup_usermods.lua file.
Also, since any CAS process with the proper authorization can alter the formats published by SID, you could theoretically make SID lookups more dynamic by updating the CAS formats regularly outside of SID or even possibly within published decisions using custom code nodes. If you've done this, please comment. Be aware that using any process outside of SID to modify the CAS formats derived from SID lookup tables will make the lookup table data within the SID user interface outdated. The CAS formats and the data shown on the SID lookup table screens would be out of sync. .
In-Database Shared Lookups -- Not Supported
In SID 5.4, all in-database published rule sets must use in-line lookup tables. Shared lookups are not supported. In-database published rule sets/decisions with shared lookups simply fail during execution. .
MAS Shared Lookups -- MAS Code Modules
MAS shared lookups are deployed as MAS code modules. The image below shows the MAS Module for the sample bidCommands lookup table. Examining the source code, we see that the lookup rules are placed into a hash. While SID handles the publication of these modules for you, if you'd like to understand more about DS2 in the MAS, see here.
As with the shared CAS formats, you could introduce a process outside of SID to modify the module using a PUT to replace the DS2. However, the same considerations apply here that apply to the shared formats, most notably that any "outside" process would cause synchronization issues between The SID interface and the lookup table shared modules. .
Further Considerations
SID Data Query Files as an Alternative to SID Lookups
If the static nature or some other aspect of SID lookup tables does not suit your requirements, v5.4 now supports custom SQL through Data Query Files. Note that this feature is only available to MAS-published decisions. You can find documentation on this new feature here as well as MAS usage and configuration information here. .
