SAS SpeedyStore gives SAS Customer a top modern super fast and cost efficient data platform as a part of SAS Viya. It is tightly integrated with CAS and Visual Analytics. Data doesn't need to be lifted into CAS anymore, the CAS tables behave as views pointing to the underlaying table in SpeedyStore. All operations within Visual Analytics generate CAS Actions that are executed in CAS. With SAS SpeedyStore the CAS Actions are translated to SQL and sent down to SpeedyStore for execution. Even SAS functions, formats and calculations are executed in-database using the SAS embedded process.
As the CAS tables work as views, it is also possible to let them point down to a view in SpeedyStore. This opens up for a lot of different use cases like implementing Row Level Security within SpeedyStore, Reporting on JSON data and on execution calculation of joins, calculations and aggregations. One way of doing this is using User Defined Functions in SpeedyStore.
With User Defined Functions you can code your own functions and use them on the data at run time, meaning when you open / click on a VA report. In this way you can integrate data and do advanced calculations in an easy and consistent way. You define the functions once and use it across all your data. The number of use cases are close to unlimited, but some typical ones are:
Making SpeedyStore system information available in Visual Analytics
Doing advanced multipass calculations on the data, meaning functions looping the data
Define and use consistent business calculations
Enrich with data from other tables
Create custom grouping of data
Here I will show two examples into the same report:
Retrieving system information about the Singlestore cluster
A function to decide if a number is a prime or not
They are both created using a UDF in a SpeedyStore view and loading the view into CAS as illustrated below.
How the User Defined Functions are connected to the VA report
The code for the User Defined Function and the view for example 1 Environment variables is shown below. Here I collect three environment variables into a text string. You can of cause choose your own variables or query the information_schema tables to get the information you need.
/* Define the function getVer() */ DELIMITER //
CREATE OR REPLACE FUNCTION getVer() RETURNS VARCHAR(255) AS
DECLARE
var1 VARCHAR(255);
var2 VARCHAR(255);
var3 VARCHAR(255);
res VARCHAR(255);
BEGIN
var1 = @@memsql_version;
var2 = @@version_compile_os;
var3 = @@collation_server;
res = CONCAT("Version: ",var1, "; OS: ", var2,"; Collation: ", var3);
RETURN res;
END //
DELIMITER ;
/* Make the information available in the view v_SinglestoreVersion */
create view v_SinglestoreVersion as select getver() as info;
The code below create the is_prime() function. It shows how to make a function with a loop. At the bottom the function is used in view v_class_prime to creating a flag indicating if the age column in the class table is a prime or not.
/* define function for classifying if a number is prime or not */
DELIMITER //
CREATE FUNCTION is_prime(n BIGINT NOT NULL) returns BIGINT AS
BEGIN
IF n <= 1 THEN
RETURN FALSE;
END IF;
FOR i IN 2 .. (n-1) LOOP
EXIT WHEN i * i > n;
IF n % i != 0 THEN
CONTINUE;
END IF;
RETURN FALSE;
END LOOP;
RETURN TRUE;
END //
DELIMITER ;
/* make a view using the is_prime() function on the class table */
create view v_class_prime as
select *, is_prime(Age) as f_age_prime from class;
The views must be "loaded" to CAS. We use the standard method of loading data (proc casutil), but the data is not loaded to CAS. It is only created a view pointing down into the SpeedyStore view. I recommend to always use the droptable within the same proc with the quiet option on making the code work for both initial load and reload.
CAS;
proc casutil;
droptable casdata="v_class_prime" incaslib="CSASDP" quiet;
load casdata="v_class_prime" incaslib="CSASDP" outcaslib="CSASDP" casout="v_class_prime" promote;
quit;
proc casutil;
droptable casdata="v_SinglestoreVersion" incaslib="CSASDP" quiet;
load casdata="v_SinglestoreVersion" incaslib="CSASDP" outcaslib="CSASDP" casout="v_SinglestoreVersion" promote;
quit;
Now, the data is available in CAS and we can use it to build our report. The target report looks like this.
Example report
I add in my two datasets v_class_prime and v_Singlestoreversion to my report. Let us see how to make the blue text box with the information about SpeedyStore first. First I define a dynamic parameter called "Singlestore" and connect it to the "info" column in v_Singlestoreversion as shown below.
Steps to create the parameter with the SpeedyStore content
When I create a text box and reference the "Singlestore" parameter by using {} brackets.
Calling the dynamic parameter in a text box
Using the prime flag is easy. My new column f_age_prime is made available in the dataset. I move it into a category variable and use it as a normal column to create my table and my two graphs.
The calculated column is used a normal column in Visual Analytics
Utilizing User Defined Functions in SpeedyStore brings a lot of new possibilities into how to create Visual Analytics reports and also how to define business calculations once across tables. I recommend you to take a look at the Singlestore documentation as they provide some great examples:
https://docs.singlestore.com/db/v9.0/reference/sql-reference/procedural-sql-reference/create-function-udf/
Hopefully this can help you to create just the report you want for Christmas🎄
... View more
