SAS Professional Services

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月 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.

3月 092017
 

SAS® Federation Server provides a central, virtual environment for administering and securing access to your data. It also allows you to combine data from multiple sources without moving or copying the data. SAS Federation Server Manager, a web-based application, is used to administer SAS Federation Server(s).

Data privacy is a major concern for organizations and one of the features of SAS Federation Server is it allows you to effectively and efficiently control access to your data, so you can limit who is able to view sensitive data such as credit card numbers, personal identification numbers, names, etc. In this three-part blog series, I will explore the topic of controlling data access using SAS Federation Server. The series covers the following topics:

SAS Metadata Server is used to perform authentication for users and groups in SAS Federation Server and SAS Federation Server Manager is used to help control access to the data. SAS Metadata Server is used to perform authentication for users and groups in SAS Federation Server and SAS Federation Server Manager is used to help control access to the data.  Note: Permissions applied for a particular data source cannot be bypassed with SAS Federation Server security. If permissions are denied at the source data, for example on a table, then users will always be denied access to that table, no matter what permissions are set in SAS Federation Server.

In this post, I will build on the examples from my previous articles and demonstrate how you can use data masking to conceal actual data values from users, but still allow them access for analysis and reporting purposes.

In previous posts, I gave the Finance Users group access to the SALARY table. Linda is a member of the Finance Users group, so currently she has access to the SALARY table.

However, I want to restrict her access. She needs access to the Salary info for analytic purposes, but does not need to know the identifying data of IDNUM, so I can hide that column from her. She does need the JOBCODE information for her analytics; however, she does not need to know the actual JOBCODE information associated with the record, so that data can be masked to prevent her from viewing that identifying information.

First, I create a FedSQL View of the SALARY table. FedSQL is the implementation of SQL that SAS Federation Server uses to access relational data.  For the view, I set the Security to Use the definer’s privileges when accessed since I will eventually deny Linda the rights to view the underlying table to the view.

Here is the default code for the view:

I change the code to the following to remove the IDNUM column from the view and mask the JOBCODE column, so Linda will not know what is the real JOBCODE associated with the Salary.

There are several data masking functions available for use. In this instance, I use the TRANC function to mask the JOBCODE field using transliterated values by replacing the first three characters with other values.  Refer to the Data Masking section of the SAS Federation Server Manager 4.2: User’s Guide for more information on the different data masking functions.

Now that I have created the FedSQL view, I then need to grant Linda authorization to it.

Next, I need to deny Linda authorization to the SALARY table, so she won’t be able to access the original table.

Linda is only able to view the SALARY_VIEW with the IDNUM column removed and the JOBCODE information masked.

Linda is denied access to the SALARY table.

However, Kate another member of the Finance team is able to view the full SALARY table with the IDNUM column and the real information (non-masked) in the JOBCODE column.

In this blog entry, I covered the third part of this series on controlling data access to SAS Federation Server 4.2.  Other blogs in the series include

For more information on SAS Federation Server visit the:

Securing sensitive data using SAS Federation Server data masking was published on SAS Users.

3月 092017
 

Several months ago, I posted a blog about calculating moving averages for a measure in the Visual Analytics Designer. Soon after that, I was asked about calculating not only the average, but also the standard deviation over a period of months, when the data might consist of one or more repeated values of a measure for each month of a series of N months.  For the example of N=20 months, we might want to view the average and standard deviation over the last n months, where n is any number between 3 and 20.

The example report shown below allows the user to type in a number, n, between 3 and 20, to display a report consisting of the amount values for past n months, the amount values for Current Month Amt-Previous, the Avg over the last n months, the Standard Deviation over the last n months, and the absolute value of the (Current Month Amt – Previous Month Amt), divided by the Standard Deviation over the last n months. A Display rule is applied to the final Abs column, showing Green for a value less than 1 and red for a value greater than or equal to 1.

The data used in this example had multiple Amount values for each month, so we first used the Visual Data Builder to create a SUM aggregation for Amount for each unique Date value.  This step gives more flexibility in using the amount value for aggregations in the designer.

When the modified data source is initially added to the report, it contains only the Category data item Month, with a format of MMYYYY, and the measure Amount Sum for Month.

The data will be displayed in a list table. The first columns added to the table will be Month, displayed with a MMYYYY format, and Amount Sum for Month.

Specify the properties for the list table as below:

Since we want to display the last n months, we create a new calculated data item, Numeric Date, calculated as below, using the TREATAS operator on the Month data item:

Then we create the Current Month Amt-Previous aggregated measure using the RelativePeriod date operator:

Next, create the Avg over all displayed months aggregated measure as below:

Then, create the Std.Dev. over all displayed months aggregated measure as shown below:

Create the Abs (Current-Previous/StdDev) as shown below:

Create a numeric parameter, Number of Months, as shown, with minimum value of 3 (smallest value that a standard deviation will make sense) and maximum value of 20 (the number of months in our data). You can let the default (Current value) value be any value that you choose:

For the List Table, create a Rank, as shown below. Note that we are creating the rank on the Numeric Date (not the Month data item), and rather than a specific value for count, we are going to use the value of the parameter, Number of Months.

Create a text input object that enables the user to type in a ‘number of months’ between 3 and 20.

Associate the Parameter with the Text input object:

If you wish, you can add display rules to sound an alarm whenever there is an alarming month-to-month difference in comparison to the standard deviation for the months.

So the final result of all of the above is this report, which points out month-to-month differences, which might deserve further concern or investigation. Note that the Numeric Date value is included below just to enable you to see what those values look like—you likely would not want to include that calculated data item in your report.

Calculating standard deviation of a measure in Visual Analytics Designer was published on SAS Users.

2月 142017
 

In this post I wanted to shed some light on a visualization you may not be using enough: the Word Cloud. Word association exercises can often be a fun way to pass the time with friends, or it can trigger immediate action – just think of your email inbox and seeing an email from a particular person: your boss, wife, husband or child. The same can be true for information for your organization. A single word can quickly, efficiently and effectively communicate the performance of a company’s metric, hence the value of using a word cloud visualization in your report.

Let’s look at some examples. Here I am using the Insight Toy data and looking at the performance of Products based on customer orders.

As the word cloud in SAS Visual Analytics 7.3 Designer has a maximum row return of 100, I have used the Rank feature to look at the top 25 Products and the bottom 25 Products. I also created a filtered interaction between the word clouds and their respective list tables below to show a bit more detail around the next level in the hierarchy after Product Make.

Notice how impactful these Product names are compared to when using their corresponding SKUs. Be sure to pick a meaningful category to represent your data in the word cloud.

This type of visualization could lead to a great comparison report, comparing what the top and bottom Products were for the same month in the previous year.

What if your data doesn’t have the appropriate column to display on a word cloud? No problem. In this next example, I took the value of Sales Rep Rating and created a new Calculated Data Item to represent values less than or equal to 25% to be Poor, inclusively between 26% and 50% to be Average and everything else to be Above Average.

Using a word cloud for this new category data item allows you to quickly move through the different states and compare the Sales Rep Performance frequency. You could also use this new category to compare each performance group’s Order Totals.

Here is California’s Sales Rep Performance:

And here is Maryland’s Sales Rep Performance:


These are two ideas for you to think about how you might include the word cloud visualization into your reports to help quickly and effectively represent the status of a company’s metric beyond the standard text analytics usage.

tags: SAS Professional Services, SAS Visual Analytics

Visualization Spotlight: Visual Analytics Designer 7.3 Word Cloud was published on SAS Users.

2月 102017
 

Small matters matter. Imagine saving (or spending wisely) just 1 second of your time every hour. One measly second! During your lifespan you would save or spend wisely (1 sec-an-hour * 24 hours-a-day * 365 days-a-year x 100 years) / (3600 seconds-an-hour * 24 hours-a-day) = 10 days, a whole two week vacation!

While truncation vs rounding may seem to be insignificant in a given instance, the cumulative effect of either could be truly enormous, whether it’s truncation vs rounding of decimal numbers or of the SAS time values presented below.

From my prior post Truncating decimal numbers in SAS without rounding, we know that SAS formats such as w.d, DOLLARw.d, and COMMAw.d do not truncate decimal numbers, but rather round them.

However, SAS time value formats are somewhat different. Let’s take a look.

Suppose we have a SAS time value of '09:35:57't. As a reminder, a SAS time value is a value representing the number of seconds since midnight of the current day. SAS time values are between 0 and 86400.

TIMEw.d Format

Let’s apply the TIMEw.d format to our time value and see what it does.

If you run the following SAS code:

data _null_;
	t = '09:35:57't;
	put t= time5.;
	put t= time2.;
run;

you will get in the SAS log:

t=9:35
t=9

which means that this format does truncate both seconds and minutes. Conversely, if rounding were taking place we would have gotten:

t=9:36
t=10

HHMMw.d Format

Let’s run the same SAS code with HHMMw.d format:

data _null_;
	t = '09:35:57't;
	put t= hhmm5.;
	put t= hhmm2.;
run;

SAS log will show:

t=9:36
t=9

What does that mean? It means that HHMMw.d format rounds seconds (in case of truncating I would expect to get t=9:35), but truncates minutes (in case of rounding I would expect to get t=10, as 35 minutes are closer to 10 than to 9). A bit inconsistent, at least for our purposes.

Truncating SAS time values

This little research above shows that out of the two formats, TIMEw.d and HHMMw.d, it is perfectly fine to use the TIMEw.d format for the purpose of SAS time value truncation, for both minutes and seconds.

Regardless of the format used, you can also truncate your time value computationally, before applying a format, by subtracting from that value a remainder of division of that value by 60 (for seconds truncation) or by 3600 (for minutes truncation). For example, the following code:

data _null_;
	t = '09:35:57't;
	t_m = t - mod(t,60);
	t_h = t - mod(t,3600);
	put t= hhmm5.;
	put t_m= hhmm5.;
	put t_h= hhmm5.;
run;

produces the following SAS log:

t=9:36
t_m=9:35
t_h=9:00

Rounding SAS time values

Now that we’ve learned both the computational method and the TIMEw.d format method of truncation, how do we go about rounding? As long as the format behavior is consistent we can use its truncating functionality to convert it into the rounding functionality. In order to do that we just need to increase the original time value by 60 (seconds) for seconds rounding, and by 3600 (seconds) for minutes rounding. Truncation of that new value is equivalent to rounding of the original value.

Let’s run the following SAS code:

data _null_;
	t = '09:35:57't;
	t_m = t + 60;
	t_h = t + 3600;
	put t_m= time5.;
	put t_h= time2.;
run;

SAS log will show:

t_m=9:36
t_h=10

which means that our original time value '09:35:57't was rounded in both cases – seconds rounding and minutes rounding.

Now you know how to truncate and how to round SAS time values. And don’t forget about your lifetime 2-week vacation opportunity by saving a second every hour; or make it 2 seconds per hour and enjoy the full month off.

tags: SAS Professional Services, SAS Programmers, tips & techniques

Truncating vs rounding SAS time values was published on SAS Users.

2月 102017
 

Since the SAS 9.4 M2 release in December 2014, there have been several refinements and updates to the middle tier that are of interest to installers and administrators. In this blog, I’m going to summarize them for you. What I’m describing here is available in the newest SAS release (9.4 M4). I’ll describe them at a high level, and refer you to the documentation for details and how to implement some of these changes.

Security enhancements

Preserve your TLS Customizations:
For security purposes, many of you will manually add TLS configurations, either to the SAS Web Server, the SAS Web Application Server, or both. In addition, you may prefer to use your own reverse proxy server (such as IIS), either instead of, or in addition to, the SAS Web Server. Before the 9.4 M4 release, when upgrading or applying maintenance, you had to undo these custom configurations, perform the upgrade, and then apply the custom configurations again. Now, the upgrade will preserve them, making the process much easier. See Middle-Tier Security in the Middle Tier Administration Guide, Fourth Edition for full details.

Newer versions of OpenSSL are now provided (see doc for specific version numbers):
A Java upgrade enables enforcement of TLSv2. TLS is now considered the security standard for https connections, (SSL is obsolete) and this can be enforced with configurations to the SAS Web Server and the SAS Web Application Server. The new version of Java SAS is using (Ver 1.7+) now allows for this. One important thing to be aware of is that certificates are completely independent of which protocol you are using, and therefore any certificates you may have been using with SSL should work equally with newer TLS protocols.

Management of the trusted CA (Certificate Authority) bundle:
SAS now has a trusted CA bundle, that can be managed by the SAS Deployment Manager, in a new location:  SASHome/SASSecurityCertificateFramework/1.1/cacerts/. The CA certificates can be root certificates, intermediate certificates, or both. Here’s what the menu item looks like:

Middle Tier Changes and Upgrades in SAS 9.4 M4

Previously it was necessary to manually add your root/intermediate certificates to the Java truststore “cacerts,” located inside the JRE; now it’s done through the new interface. If you are on Windows, you must also add trusted CAs to the Windows store (as before), which will make them available to any browsers running there. This is documented at http://www.sqlservermart.com/HowTo/Windows_Import_Certificate.aspx and elsewhere online.

Security Support for SAS Web Applications – white list external sites, and HTTP request methods:
For added security, web sites hosting SAS web applications can now maintain a white list of external URLs that are allowed to connect in. This provides protection against Cross Site Request Forgeries, and other vulnerabilities. This is what the prompt looks like in the SDW:

Middle Tier Changes and Upgrades in SAS 9.4 M4

HTTP request methods can also be specified as allowed/not allowed. The list of URLs can be specified during installation in the SDW (shown above), or using the SAS Management Console. You can disable whitelist checking entirely, and you can add a “blacklist” or specific sites to always block. You can also block based on request method–ie, GET, POST, PUT, etc. See the Middle Tier Administration Guide for details.

Forward Proxy Configuration:
You can now set up SAS web applications to forward external URL requests through a proxy–here it’s called a forward proxy server. Many organizations do this behind their firewalls. See details for how to set this up in the administration guide.

Other miscellaneous changes:
As an administrator you can now force users to Log Off using SAS Web Administration Console.    You can also send emails to one or more users from the same window.  This is what the menu looks like:

Middle Tier Changes and Upgrades in SAS 9.4 M4

Faster start-up time for the SAS Web Application Server

JMS Broker (ActiveMQ) now uses Version 5.12.2 (fixed bugs).

SAS Web Server now uses version 5.5.2 and includes an updated mod_proxy_connect module for TLS tunneling.

References

SAS 9.4 Intelligence Platform: Middle Tier Administration Guide, Fourth Edition

Encryption in SAS 9.4, Sixth Edition

 

tags: SAS 9.4, SAS Administrators, SAS Professional Services, security

Middle Tier Changes and Upgrades in SAS 9.4 M4 was published on SAS Users.

2月 012017
 

SAS® Federation Server provides a central, virtual environment for administering and securing access to your data. It also allows you to combine data from multiple sources without moving or copying the data. SAS Federation Server Manager, a web-based application, is used to administer SAS Federation Server(s).

Data privacy is a major concern for organizations and one of the features of SAS Federation Server is it allows you to effectively and efficiently control access to your data, so you can limit who is able to view sensitive data such as credit card numbers, personal identification numbers, names, etc. In this three-part series, I will explore the topic of controlling data access using SAS Federation Server.

The series covers the following topics:

SAS Metadata Server is used to perform authentication for users and groups in SAS Federation Server and SAS Federation Server Manager is used to help control access to the data. Note: Permissions applied for particular data source cannot be bypassed with SAS Federation Server security. If permissions are denied at the source data, for example on a table, then users will always be denied access to that table, no matter what permissions are set in SAS Federation Server.

In this blog post, I build on the example in my previous post and demonstrate how you can use SAS Federation Server Manager to control access to columns and rows in tables and views.

Previously, I gave the Finance Users group access to the SALARY table. Robert is a member of the Finance Users group, so he has access to the SALARY table; however, I want to restrict his access to the IDNUM column on the table. To do this, first I view the SALARY table Authorizations in Federation Server Manager, then I select the arrow to the right of the table name to view its columns.

Next, I select the IDNUM column. I then add the user Robert and set his SELECT permission to Deny for the column.

Note: There are 5 columns on the SALARY table.
Since he was denied access to the IDNUM column, Robert is only able to view 4 out of 5 columns.

Susan is also a member of the Finance Users group, so she has access to the SALARY table; however, I want to restrict her access to only rows where the JOBCODE starts with a “Q.” To do this, first I view the SALARY table Authorizations in Federation Server Manager.

Next, I select the Row Authorizations tab and select New Filter. I use the SQL Clause Builder to build my condition of JOBCODE LIKE Q%.

Next, I select the Users and Groups tab and add Susan to restrict her access to the filter I just created.

Finally, I select OK to save the changes I made to Row Authorizations.

Susan is now only able to view the rows of the SALARY table where the JOBCODE begins with “Q.”

In this blog entry, I covered the second part of this series on Securing sensitive data using SAS Federation Server at the row and column level:

Part 1: Securing sensitive data using SAS Federation Server at the data source level
Part 2: Securing sensitive data using SAS Federation Server at the row and column level
Part 3: Securing sensitive data using SAS Federation Server data masking

More information on SAS Federation Server:

tags: SAS Administrators, SAS Federation Server, SAS Professional Services

Securing sensitive data using SAS Federation Server at the row and column level was published on SAS Users.