Tech

5月 052017
 

SAS Visual Analytics 7.4 comes with a chock-full of new features. Report and section linking come with an added benefit. If you set up linking from one section to another section in the same report, or from one report to another report, you have the option to configure linking such that any filter prompt in the linked target location is brushed or highlighted by the values that are selected in the linked report object. And the visual report objects in that target location are filtered to reflect the context that was passed from the source location.

In SAS Visual Analytics 7.3, when you took a report link from the subscribed report to a target report with a filter prompt (or a target section in the current report) with a filter prompt, the target filter prompt was filtered by the selection made in the source report or source section. Now, with SAS Visual Analytics 7.4, if a selection is made in the source report, and a report link (or a section link) is taken to the target report (or target section), the target filter prompt is brushed. Users benefit from the flexibility to choose filter options from that filter prompt in the target location and modify that filter prompt selection as needed. Note that in both the source and target locations, common data sources should be used. If the data item is different, you are asked to map it.

To illustrate this new linking feature in SAS Visual Analytics 7.4, I created a source report and a target report. The source report has a Button Bar that filters the report objects in the source report. The target report contains the target Button Bar that receives the filtering selection made in the source report and displays the applicable button.

To illustrate the new linking enhancement, let’s take a look at the default scenario and the configured scenario where the values in the target report filter prompt are brushed or highlighted. Here are the two reports – the source and the target reports.

Source Report with Linking

Target Report

Default behavior for report and section linking

In this example, let’s take a quick look at how linking worked in SAS Visual Analytics 7.3 (and it still works the same way in SAS Visual Analytics 7.4 by default). In the following source report, I have a Button Bar in the filter prompt.

Choosing Orion Germany in the Button Bar

When I choose Orion Germany in the Button Bar, the report objects are filtered to show the filtered results.

Report Objects Filtered by Orion Germany in the Source Report

When I take a link from the Orion Germany tile in the Treemap to the target report, the Button Bar in the target report is filtered to show Orion Germany (this is the default behavior for linking) in the target report.

Target Report With Orion Germany in the Button Bar

But what if I want my users to take a report link from the source report, and be able to choose from the filter choices in the Button Bar within this target report?

SAS Visual Analytics 7.4 to the rescue!

Here’s an example of what I did with the report linking in SAS Visual Analytics 7.4 by allowing the filtering choices to be retained in the target filter prompt.

I chose Orion France in the Button Bar within the Source Report:

Choosing Orion France in the Source Report

Then, I took a report link from the Orion France tile in the Tilemap to the target report:

Target Report with Orion France Highlighted in the Button Bar

Notice how the Button Bar in the target report is brushed by Orion France, and I still have a choice of selecting a different Orion country in the Button Bar.

Design the Link for the Prompt Filters in the Target Report

It’s simple to make this happen.

1.  In the source report, where I had previously created report linking, I selected the Treemap and I chose to edit the report link by going to Interactions tab.

2.  I clicked the icon for editing this report link.

3.  In the Edit Report Link dialog, I selected the checkbox for Set the value for controls in the target report prompt bar and clicked OK. And I saved my source report. That’s it!

Note: This option sets only values on the controls that use the same data item as the source object or on data items that filter the source object. The source and target of report link should be based on the same data source. If you have multiple data sources, you are prompted to map the report link.

Linking to target reports and sections in SAS Visual Analytics 7.4 was published on SAS Users.

4月 282017
 

ETL automationWhen developing SAS applications, you can feed database tables into your application by using the libname access engine either by directly referring a database table, or via SAS or database views that themselves refer to one or more of the database tables.

However, such on-the-fly data access may not be efficient enough, especially for interactive SAS applications when system response time is critical. In case of distributed systems where a database might reside on one server, and the SAS Application server – on a different physical server, user wait time caused by delays in data access across networks and databases might become intolerable. In such cases, it makes perfect sense to extract database tables and load them into SAS tables in advance, preferably placing them on (or close to) the SAS Application server. That will ensure higher operational efficiency (responsiveness) of the interactive SAS application.

SAS Data Integration Studio provides a powerful visual design tool for building, implementing, and managing data integration processes across different data sources, applications, and platforms.

However, in case of multiple tables, loading them one by one using even such a powerful tool might become a bit tedious. In an automation paradigm, being “visual” is not necessarily a good thing; when we automate it implies that we want to get things done without visualizing or even seeing them.

Here is a SAS coding approach, which I recently used for a customer project, to automate the repetitive process.

SAS code example of loading multiple Oracle tables into SAS tables

There were several Oracle tables that needed to be extracted and loaded into SAS data tables on a different physical server. The Oracle tables contained multi-year data, but we were only interested in the latest year’s data. That is those tables that have datetime stamp EVENT_DT column needed to be subset to the latest rolling 365 days.

Step 1. Creating a driver table

In order to make this process data-driven, let’s first create a driver table containing a list of the table names that needed to be extracted and loaded:

/* --------------------------------------------------- */
/* create table list to extract & load,                */
/* dt_flag indicates whether EVENT_DT variable present */
/* --------------------------------------------------- */
 
libname parmdl '/sas/data/parmdata';
 
data parmdl.tablelist;
        length tname $8 dt_flag $1;
        input tname dt_flag;
        datalines;
ADDRESS N
ACCOUNT Y
BENEFIT Y
EXCLUSN N
FINANCE Y
HOUSING Y
;

This program runs just once to create the driver table parmdl.tablelist.

Step 2. Loading multiple tables

The following data-driven program runs every time when you need to reload Oracle tables into SAS:

/* source ORACLE library */
libname oralib oracle path="xxx" schema="yyy" user="uuu"
 	PASSWORD="{SAS002}D2DF612A161F7F874C4EF97F" access=readonly;
 
/* target SAS library */
libname sasdl '/sas/data/appdata';
 
/* driver table SAS library */
libname parmdl '/sas/data/parmdata';
 
options symbolgen mprint mlogic;
%let cutoff_date = %eval(%sysfunc(today()) - 365);
 
/* --------------------------------------------------- */
/* loop through table list to extract & load           */
/* --------------------------------------------------- */
%macro ETL;
   %let dsid = %sysfunc(open(parmdl.tablelist));
   %syscall set(dsid);
   %do %while(%sysfunc(fetch(&dsid)) eq 0);
      data sasdl.&tname;
         set oralib.&tname;
         %if &dt_flag eq Y %then %str(where datepart(EVENT_DT) ge &cutoff_date;);
      run;
   %end;
   %let rc = %sysfunc(close(&dsid));
%mend ETL;
%ETL

In this code, we use a similar coding technique to that described in my earlier blog post Modifying variable attributes in all datasets of a SAS library. We loop through the initially created table parmdl.tablelist. in a macro %do-loop, and for each observation containing a single table name, implement a data step extracting one table at a time.

If macro variable &dt_flag=Y, then we generate and insert a where statement sub-setting the extracted table to the latest year’s data.

Note a very useful feature here %syscall set(dsid) that links SAS data set variables to macro variables that have the same name and data type. Since we have not defined those macro variables, in essence, it creates a namesake macro variable for each SAS data set variable.

Implementing data-driven load in SAS Data Integration Studio

The above SAS code is run on the SAS Application server – the same server where the SAS target library resides. To fully automate the ETL process, this code may be embedded into a SAS job in SAS Data Integration Studio and then scheduled via either Platform Suite for SAS or your operating system scheduler. Alternately, it can be run in batch mode or via script that that in turn can be scheduled by the operating system scheduler.

In SAS DI Studio:

1.  Create a New job and give it a meaningful name (e.g. Loading Oracle Tables into SAS Tables.)

2.  Drag & drop User Written Code transformation onto the SAS Job canvas and give it a meaningful name (e.g. ETL macro):

SAS Data Integration Studio Job

3.  Go to the Properties of this transformation and click on the Code tab, then paste your SAS code in the text entry area:

SAS DI job properties

4.  Click OK and Save your SAS Job.

Note, that we do not add Source and Target tables to the Job canvas, only User Written Code transformation. Also note, that the %ETL macro has no direct references to the database table names being extracted and loaded into SAS datasets. Instead, it receives the names of the tables to be loaded from the PARMDL.TABLELIST dataset. This makes it extremely flexible to the task of processing a completely different set of tables; all you need to do is update the table list in the driver table. No change to your SAS DI job is needed.

Now it's your turn

What is your experience with automating ETL processes? Please feel free to share it here in the Comments section.

 

Automating the loading of multiple database tables into SAS tables was published on SAS Users.

4月 262017
 

Oracle databases from SAS ViyaSAS Data Connector to Oracle lets you easily load data from your Oracle DB into SAS Viya for advanced analytics. SAS/ACCESS Interface to Oracle (on SAS Viya) provides the required SAS Data Connector to Oracle, that should be deployed to your CAS environment. Once the below described configuration steps for SAS Data Connector to Oracle are completed, SAS Studio or Visual Data Builder will be able to directly access Oracle tables and load them into the CAS engine.

SAS Data Connector to Oracle requires Oracle client components (release 12c or later) to be installed and configured and configurations deployed to your CAS server. Here is a guide that walks you through the process of installing the Oracle client on a Linux server and configuring SAS Data Connector to Oracle on SAS Viya 3.1 (or 3.2):

Step 1: Get the Oracle Instant client software (release 12.c or later)

To find the oracle client software package open a web browser and navigate to Oracle support at:
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Download the following two packages to your CAS controller server:

  • oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
  • oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm (optional for testing only)

Step 2: Install and configure Oracle instant client

On your CAS controller server, execute the following commands to install the Oracle instant client and SQLPlus utilities.

rpm –ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
rpm –ivh oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm

The Oracle client should be installed to /usr/lib/oracle/12.1/client64.
To configure the Oracle client, create a file called tnsnames.ora, for example, in the /etc/ directory. Paste the following lines with the appropriate connection parameters of your Oracle DB into the tnsnames.ora file. (Replace "your_tnsname", "your_oracle_host", "your_oracle_port" and "your_oracle_db_service_name" with parameters according to your Oracle DB implementation)

your_tnsname =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_oracle_host)(PORT = your_oracle_port ))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =your_oracle_db_service_name)
    )
  )

Next you need to set environment variables for the Oracle client to work:

LD_LIBRARY_PATH: Specifies the directory of your Oracle instant client libs
PATH: Must include your Oracle instant client bin directory
TNS_ADMIN: Directory of your tnsnames.ora file
ORACLE_HOME: Location of your Oracle instant client installation

In a console window on your CAS controller Linux server, issue the following commands to set environment variables for the Oracle client: (replace the directory locations if needed)

export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
export PATH=/usr/lib/oracle/12.1/client64/bin:$PATH 
export TNS_ADMIN=/etc 
export ORACLE_HOME=/usr/lib/oracle/12.1/client64

If you installed the SQLPlus package from Oracle, you can test connectivity with the following command: (replace "your_oracle_user" and "your_tnsname" with a valid oracle user and the tnsname configured previously)

sqlplus your_oracle_user@your_tnsname

When prompted for a password, use your Oracle DB password to log on.
You should see a “SQL>” prompt and you can issue SQL queries against the Oracle DB tables to verify your DB connection. This test indicates if the Oracle instant client is successfully installed and configured.

Step 3: Configure SAS Data Connector to Oracle on SAS Viya

Next you need to configure the CAS server to use the instant client.

The recommended way is to edit the vars.yml file in your Ansible playbook and deploy the required changes to your SAS Viya cluster.

Locate the vars.yml file on your cluster deployment and change the CAS_SETTINGS section to reflect the correct environment variables needed for CAS to use the Oracle instant client:
To do so, uncomment the lines for ORACLE_HOME and LD_LIBRARY_PATH and insert the respective path for your Oracle instant client installation as shown below.

CAS Specific ####
# Anything in this list will end up in the cas.settings file
CAS_SETTINGS:
1: ORACLE_HOME=/usr/lib/oracle/12.1/client64
#3: ODBCHOME=ODBC home directory
#4: JAVA_HOME=/usr/lib/jvm/jre-1.8.0
5:LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

Run the ansible-playbook to deploy the changes to your CAS server.
After ansible finished the update, your cas.settings file should contain the following lines:

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

Now you are ready to use SAS/ACCESS Interface to Oracle in SAS Viya.

Step 4: Test SAS/ACCESS Interface to Oracle in SAS Studio

Log on to SAS Studio to load data from your Oracle DB into CAS.
Execute the following SAS Code example in SAS Studio to connect to your Oracle DB and load data into CAS. Change the parameters starting with "your_" in the SAS code below according to your Oracle DB implementation.

/************************************************************************/
/*  Start a CAS session   */
/************************************************************************/
cas;
/************************************************************************/
/*  Create a Caslib for the Oracle connection   */
/************************************************************************/
caslib ORACLE datasource=(                                           
    srctype="oracle",
    uid="your_oracle_user_ID",
    pwd="your_oracle_password",
    path="//your_db_hostname:your_db_port/your_oracle_service_name",
    schema="your_oracle_schema_name" );
 
/************************************************************************/
/*  Load a table from Oracle into CAS   */
/************************************************************************/
proc casutil;
   list files incaslib="ORACLE"; 
   load casdata="your_oracle_table_name" incaslib="ORACLE" outcaslib="casuser" casout="your_cas_table_name";                   
   list tables incaslib="casuser";
quit;
/************************************************************************/
/*  Assign caslib to SAS Studio   */
/************************************************************************/
 caslib _all_ assign;

The previous example is a simple SAS program to test access to Oracle and load data from an Oracle table into CAS memory. As a result, the program loads a table in your CAS library with data from your Oracle database.

How to configure Oracle client for successful access to Oracle databases from SAS Viya was published on SAS Users.

4月 212017
 

send an email that embeds a graphWhen using the SAS® system to email graphics output, a common request is to use SAS to send an email in which the graphics output is embedded in the body of the email. This functionality is not available until the second maintenance release for SAS® 9.4 (TS1M2). If you are using a version of SAS earlier than SAS 9.4 TS1M2, your best option is to create graphics output in a format such as RTF or PDF, and then attach the RTF or PDF file to your email.

Using the INLINED Option to Embed Graphics

If you are running SAS 9.4 TS1M2 or later, you can embed graphics output in an email. To do this, use the INLINED suboption with the ATTACH option in a SAS FILENAME statement that uses the EMAIL engine. Here is an example:

filename sendmail email to=("first.last@company.com")          from=("first.last@company.com")
    attach=("c:\temp\email.png" inlined='sgplot')
    type='text/html' subject="Emailing graphics output";

 

Then, later in your code, reference the value specified for the INLINED option in DATA step code that creates custom HTML output, as shown below:

                data _null_;  
        file sendmail;  
  put '<html>';
  put '<body>';
  put '<img src=cid:sgplot>';
  put '</body>';
  put '</html>';
run;

With this technique, although the graph is sent as an attachment, the attachment is hidden. When the email recipient opens the email, the attached graph is automatically displayed in the email (so that it looks like the graph is embedded in the body of the email).

Note: When using SAS to email graphics output, you must first set the EMAILSYS system option to SMTP and the EMAILHOST system option to the name of the SMTP email server at your site.

Embedding Multiple Graphs

You can also send multiple graphs in a single email using a SAS FILENAME statement as shown here:

            filename sendmail email to=("first.last@company.com") from=("first.last@company.com")
    attach=("c:\temp\email1.png" inlined='sgplot1'  "c:\temp\email2.png" inlined='sgplot2')
    type='text/html' subject="Emailing graphics output";

Then, create custom HTML output using DATA step code similar to the following:

     Data _null_;  
       file sendmail;  
 put '<html>';
 put '<body>';
 put '<img src=cid:sgplot1>';
 put '<img src=cid:sgplot2>';
 put '</body>';
 put '</html>';
    run;

Embedding a Graph and a PROC PRINT Table

This example shows how to embed a graph and PRINT procedure table in one email. Let us assume that you have a graph named sgplot.png stored in C:\Temp. You want to send an email using SAS that displays the SGPLOT graph in the body of the email directly before a table created with PROC PRINT. The following sample code demonstrates how to do this using a TITLE statement with PROC PRINT:

filename sendmail email  to=("first.last@company.com") from=("first.last@company.com")
     attach=("c:\temp\sgplot.png" inlined='sgplot') 
     type='text/html' subject="Email test of GRAPH output";
      ods _all_ close; 
ods html file=sendmail; 
title1 '<img src=cid:sgplot>';
proc print data=sashelp.class; 
run;
ods html close; 
ods listing; 
filename sendmail clear;

Embedding a Graph (Complete Program)

Here is a complete sample program that demonstrates embedding graphics in an email using graphics output created with the SGPLOT procedure:

%let workdir=%trim(%sysfunc(pathname(work)));
ods _ALL_ close; 
ods listing gpath="&workdir";
ods graphics / reset=index outputfmt=PNG imagename='email';  
title1 'Graph output emailed using SAS';
proc sgplot data=sashelp.cars; 
  bubble x=horsepower y=mpg_city size=cylinders;
run;
filename sendmail email to=("first.last@company.com") from=("first.last@company.com")
     attach=("&workdir./email.png" inlined='sgplot')
     type='text/html' subject="Emailing graphics output";
      data _null_;
 file sendmail;  
 put '<html>';
 put '<body>';
 put '<img src=cid:sgplot>';
 put '</body>';
 put '</html>';
run; 
      filename sendmail clear;

In conclusion, if you are running SAS 9.4 TS1M2 or later, using the INLINED option in a FILENAME statement is an excellent option when emailing graphics output.  Note that you can use this technique to email any graphics file in PNG, GIF, or JPEG format created with the SAS SG procedures, ODS Graphics, or SAS/GRAPH® procedures (such as GPLOT and GCHART).  You can also use this technique to email graphics files created with software other than SAS.

Use SAS to send an email that embeds a graph in the body of the email was published on SAS Users.

4月 182017
 

In addition to his day job as Chief Technology Officer at SAS, Oliver Schabenberger is a committed lifelong learner. During his opening remarks for the SAS Technology Connection at SAS Global Forum 2017, Schabenberger confessed to having a persistent nervous curiosity, and insisted that he’s “learning every day.” And, he encouraged attendees to do the same, officially proclaiming lifelong learning as a primary theme of the conference and announcing a social media campaign to explore the issue with attendees.

This theme of lifelong learning served a backdrop – figuratively at first, literally once the conference began! – when Schabenberger, R&D Vice President Oita Coleman and Senior R&D Project Manager Lisa Morton sat down earlier this year to determine the focus for the Catalyst Café at SAS Global Forum 2017.

A centerpiece of SAS Global Forum’s Quad area, the Catalyst Café is an interactive space for attendees to try out new SAS technology and provide SAS R&D with insight to help guide future software development. At its core, the Catalyst Café is an incubator for innovation, making it the perfect place to highlight the power of learning.

After consulting with SAS Social Media Manager Kirsten Hamstra and her team, Schabenberger, Coleman and Morton decided to explore the theme by asking three questions related to lifelong learning, one a day during each day of the conference. Attendees, and others following the conference via social media channels, would respond using the hashtag #lifelearner. Morton then visualized the responses on a 13-foot-long by 8-foot-high wall, appropriately titled the Social Listening Mural, for all to enjoy during the event.

Questions for a #lifelearner

The opening day of the conference brought this question:

Day two featured this question:

Finally, day three, and this question:

"Committed to lifelong learning"

Hamstra said the response from the SAS community was overwhelming, with hundreds of individuals contributing.

Morton working on the Social Listening Mural at the SAS Global Forum Catalyst Café

“It was so interesting to see what people shared as their first jobs,” said Morton. “One started out as a bus boy and ended up a CEO, another went from stocking shelves to analytical consulting, and a couple said they immediately started their analytical careers by becoming data analysts right out of school.”

The “what do you want to learn next?” question brought some interesting responses as well. While many respondents cited topics you’d expect from a technically-inclined crowd – things like SAS Viya, the Go Programming Language and SASPy – others said they wanted to learn Italian, how to design websites or teach kids how to play soccer.

Morton said the connections that were made during the process was fascinating and made the creation of the mural so simple and inspiring. “The project showed me how incredibly diverse our SAS users are and what a wide variety of backgrounds and interests they have.”

In the end, Morton said she learned one thing for sure about SAS users: “It’s clear our users are just as committed to lifelong learning as we are here at SAS!”

My guess is that wherever you’ll find Schabenberger at this moment – writing code in his office, behind a book at the campus library, or discussing AI with Dr. Goodnight – he’s nodding in agreement.

The final product

Nurturing the #lifelearner in all of us was published on SAS Users.

4月 172017
 

In this post, I will explain how LASR Servers (both Distributed and Non-distributed) are started from the SAS Visual Analytics Administrator application. We will also look at one particular issue and I will provide you with more details to understand the situation and two strategies to address this issue.

When you start a LASR Server from the Visual Analytics Administrator application, the system processes your request in slightly different ways depending on whether you are starting a distributed or non-distributed LASR Server.

What happens in the background for a distributed LASR Server?

1.     The administrator starts a LASR Server from the Visual Analytics Administrator application.

2.     The Visual Analytics Administrator application generates the required code and sends it to the JES to be executed on the SAS Visual Analytics Root Node.

3.     The JES invokes the SAS Object Spawner on the Visual Analytics Root Node.

4.     The SAS Object Spawner initializes a SAS Workspace Server session.

5.     A client connection is established between the JES and the SAS Workspace Server session.
The JES sends the distributed LASR Server startup code generated in step 2, and the SAS Workspace Server session executes it.

6.     As soon as the distributed LASR Server startup code is executed, a distributed LASR Server instance is created. This instance is independent from the SAS Workspace Server session.
After the distributed LASR Server startup code executes, the SAS Workspace Server session, and the client connection drop down. But the LASR Server instance is up and persistent until the administrator stops it.

What happens for a non-distributed LASR Server?

1.     The administrator starts a LASR Server from the Visual Analytics Administrator application.

2.     The Visual Analytics Administrator application generates the required code and sends it to the JES to be executed on the SAS Visual Analytics Root Node.

3.     The JES invokes the SAS Object Spawner on the Visual Analytics Root Node.

4.     The SAS Object Spawner initializes a SAS Workspace Server session.

5.     A client connection is established between the JES and the SAS Workspace Server session.
The JES sends the non-distributed LASR Server startup code generated on step 2, and the SAS Workspace Server session executes it.

6.     As soon as the non-distributed LASR Server startup code is executed, a non-distributed LASR Server instance is created. This instance is dependent on the SAS Workspace Server session.
After the distributed LASR Server startup code executes, the SAS Workspace Server session, and the client connection become persistent until the administrator stops the non-distributed LASR Server.
The SAS Workspace Server session and the client connection are required for the non-distributed LASR Server to be up and persistent.

Potential non-distributed LASR Server issue

The client connection between the JES and the SAS Workspace Server session in non-distributed deployment is crucial. This connection has to be persistent during the non-distributed LASR Server lifetime. As soon as the JES or SAS Workspace Server session goes down, the client connection is broken and the non-distributed LASR Server will go down as well. And, its data become unavailable.

If administrators manage their SAS non-distributed LASR Servers from the SAS Visual Analytics Administrator web application, this will create a dependency between the non-distributed LASR Servers and the middle tier - more specifically with the SASServer1_1 instance (the sas.wip.soapservices application). This dependency does not exist for distributed LASR Servers.

When the dependency exists, each time the middle tier or the SASServer1_1 instance is down, all non-distributed LASR Servers that were managed using the SAS Visual Analytics Administrator will go down automatically because the client connection to the non-distributed LASR Server is broken and its associated SAS Workspace Server session will go down.

This issue will also appear if the network connection between the Middle Tier and the SAS Visual Analytics Root Node is broken. This will break the client connection and terminate the SAS Workspace Server session(s) and their associated non-distributed LASR Server(s).

How can we prevent the issue?

I can think of at least two techniques you can use to prevent the dependency of your non-distributed LASR Servers upon your middle-tier:

  • Start your non-distributed LASR Servers from a SAS program.
  • Configure your LASR Server to start using the AutoLoad facility.

Start your non-distributed LASR Server from a SAS program

Here is a sample program that you might use to start a non-distributed LASR Server:

/* Start the LASR Analytic Server by defining the LASR library*/
libname LASRLIB SASIOLA 
        startserver  host="sasserver01.race.sas.com" port=10013
        signer="sasserver05.race.sas.com:7980/SASLASRAuthorization";
 
/* Keep the SAS session up until SERVERTERM received */
proc vasmp;
   serverwait  port=10013;
quit;

 

This program could be scheduled or set as a service under UNIX or Windows to be executed as soon as SAS Visual Analytics is started/restarted. Whichever strategy you use, you will want to have the code run after these services are up and available:

The SAS Metadata Server.
The Object Spawner on the SAS Visual Analytics Root Node.
The SASLASRAuthaurization service on the Middle Tier:

Start the non-distributed LASR Server Using the AutoLoad capability

AutoLoad runs as a periodic scheduled task. In the standard configuration, a new run of AutoLoad is started every 15 minutes.
AutoLoad periodically scans the contents of a designated host directory, which is referred to as the AutoLoad data directory or drop zone.
AutoLoad will automatically start the associated SAS LASR Server, if it is not yet started.

If you do not already have a LASR library configured with AutoLoad, you will need to use the SAS Deployment Manager application to configure an AutoLoad directory.

In Administration Tasks, choose Configure an AutoLoad Directory for SAS Visual Analytics. And follow the instructions.

Once you have AutoLoad setup, you must make sure certain LASR Library Extended Attributes are set correctly to ensure that the AutoLoad process will automatically start your LASR Server. You will want to set/verify the following attributes for the LASR library:

  • To enable the LASR Server AutoStart only:

  • VA.AutoLoad.AutoStart: YES to indicate that you want the associated LASR Server to be started if it is not already running.
  • VA.AutoLoad.Enabled: YES to enable the AutoLoad process for the Library.
  • To enable the LASR table(s) AutoLoad (optional):

  • VA.Default.MetadataFolder: to relocate the table(s) metadata registration against a specific metadata folder.
  • VA.Default.Location: to specify the location of the table directory at OS level.
  • VA.Default.Sync.*: to manage the table(s) from the VA.Default.Location.

On your SAS Visual Analytics machine, you have to:

  • Set the appropriate security against the [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef] to be sure that the appropriate LASR Server administrator will be allowed to manage it.
  • Set the AutoLoad process frequency (default is 15 minutes).
    “TIME_INTERVAL_MINUTES=[Your-Value]” in the [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef]/schedule.[sh|bat] file.
    Note: Reference the SAS Visual Analytics Administration Guide for more about  the timing of AutoLoad.
  • Schedule the AutoLoad process using the LASR Server administrator account responsible to manage this particular LASR Server by executing the [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef]/schedule.[sh|bat] script.

Validate the AutoLoad setup by stopping the SAS LASR Server from SAS Visual Analytics Administrator application, or using SAS code. Wait for the “TIME_INTERVAL_MINUTES=[Your-Value]“, and look at the LASR Server status in SAS Visual Analytics Administrator application, and the AutoLoad logs in [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef]/Logs (one log file each time the AutoLoad process is executed).

Whichever technique is used, the non-distributed LASR server will no longer be dependent on the middle-tier.

I hope this article has been helpful to you.

How LASR servers are started from SAS Visual Analytics Administrator was published on SAS Users.

4月 172017
 

In this post, I will explain how LASR Servers (both Distributed and Non-distributed) are started from the SAS Visual Analytics Administrator application. We will also look at one particular issue and I will provide you with more details to understand the situation and two strategies to address this issue.

When you start a LASR Server from the Visual Analytics Administrator application, the system processes your request in slightly different ways depending on whether you are starting a distributed or non-distributed LASR Server.

What happens in the background for a distributed LASR Server?

1.     The administrator starts a LASR Server from the Visual Analytics Administrator application.

2.     The Visual Analytics Administrator application generates the required code and sends it to the JES to be executed on the SAS Visual Analytics Root Node.

3.     The JES invokes the SAS Object Spawner on the Visual Analytics Root Node.

4.     The SAS Object Spawner initializes a SAS Workspace Server session.

5.     A client connection is established between the JES and the SAS Workspace Server session.
The JES sends the distributed LASR Server startup code generated in step 2, and the SAS Workspace Server session executes it.

6.     As soon as the distributed LASR Server startup code is executed, a distributed LASR Server instance is created. This instance is independent from the SAS Workspace Server session.
After the distributed LASR Server startup code executes, the SAS Workspace Server session, and the client connection drop down. But the LASR Server instance is up and persistent until the administrator stops it.

What happens for a non-distributed LASR Server?

1.     The administrator starts a LASR Server from the Visual Analytics Administrator application.

2.     The Visual Analytics Administrator application generates the required code and sends it to the JES to be executed on the SAS Visual Analytics Root Node.

3.     The JES invokes the SAS Object Spawner on the Visual Analytics Root Node.

4.     The SAS Object Spawner initializes a SAS Workspace Server session.

5.     A client connection is established between the JES and the SAS Workspace Server session.
The JES sends the non-distributed LASR Server startup code generated on step 2, and the SAS Workspace Server session executes it.

6.     As soon as the non-distributed LASR Server startup code is executed, a non-distributed LASR Server instance is created. This instance is dependent on the SAS Workspace Server session.
After the distributed LASR Server startup code executes, the SAS Workspace Server session, and the client connection become persistent until the administrator stops the non-distributed LASR Server.
The SAS Workspace Server session and the client connection are required for the non-distributed LASR Server to be up and persistent.

Potential non-distributed LASR Server issue

The client connection between the JES and the SAS Workspace Server session in non-distributed deployment is crucial. This connection has to be persistent during the non-distributed LASR Server lifetime. As soon as the JES or SAS Workspace Server session goes down, the client connection is broken and the non-distributed LASR Server will go down as well. And, its data become unavailable.

If administrators manage their SAS non-distributed LASR Servers from the SAS Visual Analytics Administrator web application, this will create a dependency between the non-distributed LASR Servers and the middle tier - more specifically with the SASServer1_1 instance (the sas.wip.soapservices application). This dependency does not exist for distributed LASR Servers.

When the dependency exists, each time the middle tier or the SASServer1_1 instance is down, all non-distributed LASR Servers that were managed using the SAS Visual Analytics Administrator will go down automatically because the client connection to the non-distributed LASR Server is broken and its associated SAS Workspace Server session will go down.

This issue will also appear if the network connection between the Middle Tier and the SAS Visual Analytics Root Node is broken. This will break the client connection and terminate the SAS Workspace Server session(s) and their associated non-distributed LASR Server(s).

How can we prevent the issue?

I can think of at least two techniques you can use to prevent the dependency of your non-distributed LASR Servers upon your middle-tier:

  • Start your non-distributed LASR Servers from a SAS program.
  • Configure your LASR Server to start using the AutoLoad facility.

Start your non-distributed LASR Server from a SAS program

Here is a sample program that you might use to start a non-distributed LASR Server:

/* Start the LASR Analytic Server by defining the LASR library*/
libname LASRLIB SASIOLA 
        startserver  host="sasserver01.race.sas.com" port=10013
        signer="sasserver05.race.sas.com:7980/SASLASRAuthorization";
 
/* Keep the SAS session up until SERVERTERM received */
proc vasmp;
   serverwait  port=10013;
quit;

 

This program could be scheduled or set as a service under UNIX or Windows to be executed as soon as SAS Visual Analytics is started/restarted. Whichever strategy you use, you will want to have the code run after these services are up and available:

The SAS Metadata Server.
The Object Spawner on the SAS Visual Analytics Root Node.
The SASLASRAuthaurization service on the Middle Tier:

Start the non-distributed LASR Server Using the AutoLoad capability

AutoLoad runs as a periodic scheduled task. In the standard configuration, a new run of AutoLoad is started every 15 minutes.
AutoLoad periodically scans the contents of a designated host directory, which is referred to as the AutoLoad data directory or drop zone.
AutoLoad will automatically start the associated SAS LASR Server, if it is not yet started.

If you do not already have a LASR library configured with AutoLoad, you will need to use the SAS Deployment Manager application to configure an AutoLoad directory.

In Administration Tasks, choose Configure an AutoLoad Directory for SAS Visual Analytics. And follow the instructions.

Once you have AutoLoad setup, you must make sure certain LASR Library Extended Attributes are set correctly to ensure that the AutoLoad process will automatically start your LASR Server. You will want to set/verify the following attributes for the LASR library:

  • To enable the LASR Server AutoStart only:

  • VA.AutoLoad.AutoStart: YES to indicate that you want the associated LASR Server to be started if it is not already running.
  • VA.AutoLoad.Enabled: YES to enable the AutoLoad process for the Library.
  • To enable the LASR table(s) AutoLoad (optional):

  • VA.Default.MetadataFolder: to relocate the table(s) metadata registration against a specific metadata folder.
  • VA.Default.Location: to specify the location of the table directory at OS level.
  • VA.Default.Sync.*: to manage the table(s) from the VA.Default.Location.

On your SAS Visual Analytics machine, you have to:

  • Set the appropriate security against the [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef] to be sure that the appropriate LASR Server administrator will be allowed to manage it.
  • Set the AutoLoad process frequency (default is 15 minutes).
    “TIME_INTERVAL_MINUTES=[Your-Value]” in the [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef]/schedule.[sh|bat] file.
    Note: Reference the SAS Visual Analytics Administration Guide for more about  the timing of AutoLoad.
  • Schedule the AutoLoad process using the LASR Server administrator account responsible to manage this particular LASR Server by executing the [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef]/schedule.[sh|bat] script.

Validate the AutoLoad setup by stopping the SAS LASR Server from SAS Visual Analytics Administrator application, or using SAS code. Wait for the “TIME_INTERVAL_MINUTES=[Your-Value]“, and look at the LASR Server status in SAS Visual Analytics Administrator application, and the AutoLoad logs in [SAS-Configuration-Directory]/Levn/Applications/AutoLoad/[Your-LASR-Server-Library-LibRef]/Logs (one log file each time the AutoLoad process is executed).

Whichever technique is used, the non-distributed LASR server will no longer be dependent on the middle-tier.

I hope this article has been helpful to you.

How LASR servers are started from SAS Visual Analytics Administrator was published on SAS Users.

4月 132017
 
The Penitent Magdalene

Titian (Tiziano Vecellio) (Italian, about 1487 - 1576) The Penitent Magdalene, 1555 - 1565, Oil on canvas 108.3 × 94.3 cm (42 5/8 × 37 1/8 in.) The J. Paul Getty Museum, Los Angeles; Digital image courtesy of the Getty's Open Content Program.

Even if you are a traditional SAS programmer and have nothing to do with cybersecurity, you still probably have to deal with this issue in your day-to-day work.

The world has changed, and what you do as a SAS programmer is not just between you and your computer anymore. However, I have found that many of us are still careless, negligent or reckless enough to be dangerous.

Would you scotch-tape your house key to the front door next to the lock and go on vacation? Does anybody do that? Still, some of us have no problem explicitly embedding passwords in our code.

That single deadly sin, the thing that SAS programmers (or any programmers) must not do under any circumstances, is placing unmasked passwords into their code. I must confess, I too have sinned, but I have seen the light and hope you will too.

Password usage examples

Even if SAS syntax calls for a password, never type it or paste it into your SAS programs. Ever.

If you connect to a database using a SAS/ACCESS LIBNAME statement, your libname statement might look like:

libname mydblib oracle path=airdb_remote schema=hrdept
	user=myusr1 password=mypwd1;

If you specify the LIBNAME statement options for the metadata engine to connect to the metadata server, it may look like:

libname myeng meta library=mylib
	repname=temp metaserver='a123.us.company.com' port=8561 
 		user=idxyz pw=abcdefg;

If you use LIBNAME statement options for the metadata engine to connect to a database, it may look like:

libname oralib meta library=oralib dbuser=orauser dbpassword=orapw;

In all of the above examples, some password is “required” to be embedded in the SAS code. But it does not mean you should put it there “as is,” unmasked. SAS program code is usually saved as a text file, which is stored on your laptop or somewhere on a server. Anyone who can get access to such a file would immediately get access to those passwords, which are key to accessing databases that might contain sensitive information. It is your obligation and duty to protect this sensitive data.

Hiding passwords in a macro variable or a macro?

I’ve seen some shrewd SAS programmers who do not openly put passwords in their SAS code. Instead of placing the passwords directly where the SAS syntax calls for, they assign them to a macro variable in AUTOEXEC.SAS, an external SAS macro file, a compiled macro or some other SAS program file included in their code, and then use a macro reference, e.g.

/* in autoexec.sas or external macro */
%let opw = mypwd1;
 
/* or */
 
%macro opw;
	mypwd1
%mend opw;
/* in SAS program */
libname mydblib oracle user=myusr1 password=&opw
        path=airdb_remote schema=hrdept;
 
/* or */
 
libname mydblib oracle user=myusr1 password=%opw
        path=airdb_remote schema=hrdept;

Clever! But it’s no more secure than leaving your house key under the door mat. In fact it is much less secure. One who wants to look up your password does not even need to look under the door mat, oh, I mean look at your program file where the password is assigned to a macro variable or a macro. For a macro variable, a simple %put &opw; statement will print the password’s actual value in the SAS log. In case of a macro, one can use %put %opw; with the same result.

In other words, hiding the passwords do not actually protect them.

What to do

What should you do instead of openly placing or concealing those required passwords into your SAS code? The answer is short: encrypt passwords.

SAS software provides a powerful procedure for encrypting passwords that renders them practically unusable outside of the SAS system.

This is PROC PWENCODE, and it is very easy to use. In its simplest form, in order to encrypt (encode) your password abc123 you would need to submit just the following two lines of code:

proc pwencode in="abc123";
run;

The encrypted password is printed in the SAS log:

1 proc pwencode in=XXXXXXXX;
2 run;

{SAS002}3CD4EA1E5C9B75D91A73A37F

Now, you can use this password {SAS002}3CD4EA1E5C9B75D91A73A37F in your SAS programs. The SAS System will seamlessly take care of decrypting the password during compilation.

The above code examples can be re-written as follows:

libname mydblib oracle path=airdb_remote schema=hrdept
	user=myusr1 password="{SAS002}9746E819255A1D2F154A26B7";
 
libname myeng meta library=mylib
	repname=temp metaserver='a123.us.company.com' port=8561 
 		user=idxyz pw="{SAS002}9FFC53315A1596D92F13B4CA";
 
libname oralib meta library=oralib dbuser=orauser
dbpassword="{SAS002}9FFC53315A1596D92F13B4CA";

Encryption methods

The {SAS002} prefix indicates encoding method. This SAS-proprietary encryption method which uses 32-bit key encryption is the default, so you don’t have to specify it in the PROC PWENCODE.

There are other, stronger encryption methods supported in SAS/SECURE:

{SAS003} – uses a 256-bit key plus 16-bit salt to encode passwords,

{SAS004} – uses a 256-bit key plus 64-bit salt to encode passwords.

If you want to encode your password with one of these stronger encryption methods you must specify it in PROC PWENCODE:

proc pwencode in="abc123" method=SAS003;
run;

SAS Log: {SAS003}50374C8380F6CDB3C91281FF2EF57DED10E6

proc pwencode in="abc123" method=SAS004;
run;

SAS Log: {SAS004}1630D14353923B5940F3B0C91F83430E27DA19164FC003A1

Beyond encryption

There are other methods of obscuring passwords to protect access to sensitive information that are available in the SAS Business Intelligence Platform. These are the AUTHDOMAIN= SAS/ACCESS option supported in LIBNAME statements, as well as PROC SQL CONNECT statements, SAS Token Authentication, and Integrated Windows Authentication. For more details, see Five strategies to eliminate passwords from your SAS programs.

Conclusion

Never place unencrypted password into your SAS program. Encrypt it!

Place this sticker in front of you until you memorize it by heart.

PROC PWENCODE sticker

 

One deadly sin SAS programmers should stop committing was published on SAS Users.

4月 112017
 

You may be wondering if you need something special to gain access to the Schedule Chart object. Don’t worry, you don’t, you just need to unhide this visualization if it isn’t already. You can do this from the Objects’ drop down menu. There are several other objects available to you if you’d like to show those as well. Simply check the ones you want to include in the list and then click ok.

The VA 7.3 Schedule Chart is similar to the traditional Gantt chart in that it serves to illustrate the start and finish duration of a category data item. You must provide a category data item and two date or datetime data items representing the start and end dates. You can also add a group by category, lattice by columns and/or rows.   Here is a simple example that visualizes my local school district’s 2016-2017 Traditional School Calendar.

Schedule charts can be used to visualize a variety of data such as:

  • Calendar Events
  • Project Tracking
  • Campaign/Promotional Runs
  • Floor Service Coverage

Essentially, any category which can be associated with a start and end date can use this visualization.

Here are some examples of using the Schedule Chart to look at project tracking data. Our team uses a similar visualization; however, I have modified real names and gave it a Star Wars theme for a bit of fun.

Example 1

In this example, the Project name is assigned as the main category. Here are a few takeaways:

  • The schedule chart gives a great bird’s eye view of a lot of data. This particular data has over 350 projects spanning a team of 19 individual members.
  • The schedule chart automatically includes the least and greatest date value. You can override the X Axis in the Properties tab by assigning a fixed minimum and maximum.

In this next screenshot, I have selected the manager Obi-Wan Kenobi to filter the Schedule Chart. Therefore, by adding section filters to this report, you can see how spotting coverage of Projects and Project Types are easy. And, with some Mock Combat planned later in the year, the Jedis might want to up their training.

Example 2

This example uses the same Schedule Chart role assignments as before, but different section prompt filters. Here, this report shows how an individual team member can use the Schedule Chart to visualize several things:

  • A list of his/her assigned projects.
  • The planned duration of each project.
  • How the projects are spread throughout the year.

If you chose to look at a particular Project Type, then this visualization would help list the Project names and how they span across the year.

Example 3

The next example moves away from the traditional use of putting the Project as the main category data item and instead places the Team Lead on the Y Axis. This now allows us to see how busy each Team member is and with which Project Type. By the way, did you notice the neat way the Team names are sorted? I used a custom sort!

Here are just a few more things you can do to enhance this visualization: you can adjust the transparency so you can see overlapping projects and you can easily add reference lines to the X Axis. In this example, I’ve added the reference lines for Q1 through Q4. I’ve also selected a manager from the report section filters and added an additional data item for the Label.

Example 4

In this example, I wanted to demonstrate the use of the Lattice rows and I also applied a Display Rule for the Project Type. This is a good way if you want to view overlapping information and the transparency property isn’t distinct enough.

 

Schedule Chart Limitations

If you want a bar representation on the Schedule Chart to appear then the data must have a start and end date for every row of data. If either is missing, then the category name will appear on the Y Axis but no bar will be displayed on the visualization.   Also, you cannot create an interaction from or to the Schedule Chart object. That means you cannot create a filter or brush with another object in the report area. The Schedule Chart will be filtered by either report or section prompts.   I hope you can include the Schedule Chart into your reports, it is one of my favorite visuals.

SAS Visual Analytics Designer 7.3 Schedule Chart was published on SAS Users.

4月 102017
 

You can expand on the functionality of SAS Visual Data Builder in SAS Visual Analytics by editing the query code, adding code for pre- and post-processing, or even writing your own query.   You can process single tables or join multiple tables, writing the output to a LASR  library, a SAS library, or a DBMS library.  But you can also easily schedule your queries, right from the Visual Data Builder interface.

Here’s how.

When a query is open in the workspace of Visual Data Builder, you can schedule the query from the application by clicking the Schedule (clock) icon.

The scheduling server used is determined by the SAS Visual Data Builder Scheduling preferences setting, shown below.

By default, the Visual Analytics deployment includes the Operating System Services scheduling server, so it appears automatically as the default.

The Server Manager plug-in to SAS Management Console identifies the scheduling servers that are included in your deployment. You can specify a different scheduling server, such as Platform Suite for SAS server, if your deployment includes it.

Note:  The Distributed In-process scheduling server is not supported.

Any scheduling preferences that you change are used the next time you create and schedule a query. If you need to change the settings for a query that is already scheduled, you can use SAS Management Console Schedule Manager to redeploy the deployed job for the query.

When you schedule a query, the SAS statements are saved in a file in the default deployment directory path: SAS-config-dir/Lev1/SASApp/SASEnvironment/SASCode/Jobs.

In the examples in this blog, the SAS-config-dir is /opt/sasinside/vaconfig.
The metadata name of the directory is Batch Jobs.
The default SAS Application Server name associated with the directory is SASApp.

If you are working in a VA environment where multiple application servers are defined, you should be aware of the following SAS Notes at the links below, relating to the application’s choice of application servers for scheduling.

SAS Note 58186SAS® Visual Data Builder might use the wrong application server for scheduling
SAS Note 52977SAS® Visual Data Builder requires the default SAS® Application Server and the default scheduling servers to be located on the same physical machine

To schedule a query, open the query and select the Schedule (clock) icon. (The clock is grayed out if you have not saved the query.)

You can schedule the query to run immediately (Run now) or at a specified time event.  To define a time event, select the Select one or more triggers for this query button and click New Time Event.  Grouping events are not supported for the default server, but may be supported for other scheduling servers, such as Platform Suite.

You can schedule for One time only, or More than once, running Hourly, Daily, Weekly, Monthly, or Yearly.  The appearance of the interface and scheduling parameters change with your specification.

In this example, a One time only event is specified.

 

The time event specification gets recorded in the Trigger list on the Schedule page, and is selected in the Used column.

After you click OK in the Schedule window, you will get the confirmation below.

After the time event has passed, you can verify that the table has been loaded on the LASR Tables tab of the Visual Analytics Administrator.

When you schedule, the Visual Data Builder:

  • creates a job that executes the query.
  • creates a deployed job from the job.
  • places the job into a new deployed flow.
  • schedules the flow on a scheduling server.

The files are named according to vdb_query_id_timestamp.
In this example the files are named vdb_CustomerInfoData_1490112883364_timestamp.
When the query executes at the scheduled time, the SAS code that is written to the /opt/sasinside/vaconfig/Lev1/SASApp/SASEnvironment/SASCode/Jobs directory.  The query is run with the user ID that scheduled it.

If you right-click on Server Manager in SAS Management Console and view Deployment Directories, you will see that this is the Deployment directory (Batch Jobs) for SASApp.

In the /opt/sasinside/vaconfig/Lev1/SASApp/BatchServer/Logs directory, you can view the SAS Log.

The scheduling server script and log are in /opt/sasinside/vaconfig/Lev1/SchedulingServer/Ahmed/vdb_CustomerInfoData_14900112883364

Observe that the script was written to this location at the time the job was scheduled, rather than at execution time.

If you edit a data query that is already scheduled, you must click the schedule icon again so that the SAS statements for the data query are regenerated and saved.

If you edit the query again and specify additional time events, each event appears in the trigger list, and you can check which time event is to be used for scheduling.

If scheduling a query according to time events, you should also be aware of this Usage note:

Usage Note 55880: Scheduled SAS® Visual Data Builder queries are executed based on the time zone of the scheduling server 

And to add to the fun, also keep in mind that if your deployment includes SAS Data Integration Studio, you can also export a query as a Job and then perform the deployment steps using DI Studio.

Just right-click on the query in the SAS folder panel in Visual Data Builder and Select Export as a Job!

Easy Scheduling in Visual Data Builder - SAS Visual Analytics 7.3 was published on SAS Users.