SAS Viya 3.5: SAS Intelligent Decisioning – Data Query Configuration

[#1]

In SAS Intelligent Decisioning on SAS Viya 3.5, you can use Data Queries as part of your decision flow.  A data query allows you to use SQL to return records from a database or SAS data set.  Any decisions that involve a data query file can only be published to the SAS Micro Analytic Service (MAS).  However, to query a database or SAS data set, you first need to configure MAS in order to interact with the database or SAS data set. The MAS documentation lists its supported database drivers.

There are four main steps for configuring MAS to use data query files in SAS Intelligent Decisioning.  Note:  You must have administrative privileges to configure MAS.

1. Edit the microanalyticservice.conf file located at: /opt/sas/viya/config/etc/sysconfig.  Note: You may need to create this file if no other configuration has previously been set for MAS.  Enter the environment variable(s) for the third-party database.  This typically includes the path of where the database is installed.

   Select any image to see a larger version.
   Mobile users: To view the images, select the "Full" version at the bottom of the page.

2. In SAS Environment Manager, enter the connection string for the third-party database in the microanalyticserviceservice.properties.connectionstring property for the Micro Analytic Score service.
3. Restart the MAS service by entering the following statement: systemctl restart sas-viya-microanalyticservice-default
4. In SAS Intelligent Decisioning, create a Custom Code Data Query file and validate the SQL statement to ensure the configuration is correct and you can communicate with the third-party database. For more information on creating a data query file, refer to the documentation. If the connection is successful (and your SQL is correct), you will receive the message below when you validate your query.

In this article, I review the necessary configuration to create data queries for Oracle, Postgres, SQL Server and SAS data sets.

Oracle

Based on where the Oracle driver is installed on my SAS Viya 3.5 image, I entered the following environment variables in the microanalyticservice.conf file:

In SAS Environment Manager, I edit the sas.microanalyticserviceservice.properties section and for the connectionstring property I enter the following:

Note: Oracle does not use catalogs, so I am able to specify a catalog name of my choice to help distinguish this connection when I write my SQL in a data query.

Next, I restart the MAS service. After waiting a few minutes for the service to be fully up and running, I validate a data query in SAS Intelligent Decisioning for one of the tables in my Oracle database. In my query I refer to the custom catalog name myora when referencing the table since my connection string has the schema associated with it.

Note: Oracle is case-sensitive, so I need to make sure I use double-quotes around the table and column names to ensure the correct casing is passed when they query is performed.

For more information, refer to the documentation for Oracle configuration.

Postgres

Based on where the Postgres driver is installed on my SAS Viya 3.5 image, I entered the following environment variables in the microanalyticservice.conf file:

In SAS Environment Manager, I edit the sas.microanalyticserviceservice.properties section and for the connectionstring property I enter the following:

Next, I restart the MAS service. After waiting a few minutes for the service to be fully up and running, I validate a data query in SAS Intelligent Decisioning for one of the tables in my Postges database. In my query I refer to the custom catalog name mypg when referencing the table since my connection string has the schema associated with it.

For more information, refer to the documentation for Postgres configuration.

SQL Server

Based on where the ODBC information is located on my SAS Viya 3.5 image, I entered the following environment variables in the microanalyticservice.conf file:

In SAS Environment Manager, I edit the sas.microanalyticserviceservice.properties section and for the connectionstring property I enter the following:

Next, I restart the MAS service. After waiting a few minutes for the service to be fully up and running, I validate a data query in SAS Intelligent Decisioning for one of the tables in my SQL Server database. In my query I refer to the SQL Server schema name dbo since I can't use the catalog option when defining my SQL Server ODBC connection string.

For more information, refer to the documentation for ODBC configuration.

SAS Data Set

SAS Data Sets do not require any environment variables to be set in the microanalyticservice.conf file, so I can skip that step.

In SAS Environment Manager, I edit the sas.microanalyticserviceservice.properties section and for the connectionstring property I enter the following:

Note: The locktable=share option prevents the exclusive locking of the table when running the query.

Next, I restart the MAS service. After waiting a few minutes for the service to be fully up and running, I validate a data query in SAS Intelligent Decisioning for one of the tables in my SAS Data Set. In my query I refer to the custom catalog name mysas when referencing the SAS data set.

For more information, refer to the documentation for SAS Data Set configuration.

Summary

In conclusion, putting all the examples together my connectionstring in SAS Environment Manger for MAS would be:

Note: For some reason on my image I had to put the ODBC conopts information for SQL Server first to get it work, but I haven't found any documentation that states that is necessary, so may just be a quirk on my system.

I restart the MAS service and re-validate the data queries for Oracle, Postgres, SQL Server, and SAS Data Set to confirm everything is configured correctly.

For more information on data queries, refer to the documentation on data queries and the documentation for configuring its support.

Acknowledgements

Special thanks to my colleague, Nicolas Robert, for his help in figuring out the appropriate configuration settings and testing the various scenarios. I also would like to thank Prasenjit Sen, Stephen Vincent and Joey Vail for their input.