We’re smarter together. Learn from this collection of community knowledge and add your expertise.

SAS Visual Scenario Designer 6.3 M1 Windows: Overview & Transformation Window

by SAS Employee BenDavidson on ‎11-16-2015 10:34 AM - edited on ‎02-17-2016 03:43 PM by SAS Employee kimy_sas (451 Views)

Overview

If you read the recent What is SAS Visual Scenario Designer? article, then you have a general understanding of the benefits and functionality of SAS Visual Scenario Designer. Because creating a window is one of the first actions that you'll do, it helps to understand how each window type in the upcoming release of SAS Visual Scenario Designer 6.3 M1 analyzes the raw data from the SAS LASR Analytics Server. This article, part one in a five-part series, introduces you to the four window types, and explains how the transformation window works.  

 

A window in SAS Visual Scenario Designer takes raw input data and applies filter conditions to it, to create what's known as detail data. Depending on the options that the user selects, a SAS Visual Scenario Designer window then aggregates this detail data.

 

Knowing how each window analyzes your raw or detail data enables you to understand why you might choose a particular type. 

 

The Four Window Types

In SAS Visual Scenario Designer 6.3 M1, a window can analyze your data in four different ways:

 

  • A transformation window applies any specified filter conditions to your raw data and outputs detail data. This window type does not aggregate data. It can be useful for writing a scenario directly against an input table or for reducing data volumes within the system by dropping columns or rows.
  • An aggregation window aggregates raw or detail data by one or more group-by columns, and outputs one row per unique group-by value for all of the records in the window. It can be useful for calculating statistics (such as averages) across the entire table.
  • A date-based aggregation window aggregates raw or detail data by one or more group-by columns, and outputs one row per unique group-by value. The difference between a non-date based aggregation window and a date-based aggregation window is that the latter assigns a lookback to each group-by column. This lookback only considers the data within the most recently specified lookback period, looking backward from the end date value.
  • A transaction window aggregates raw or detail data by one or more group-by columns, and outputs one row per transaction. Similar to the date-based aggregation window, each transaction has a lookback. The difference is that each lookback only considers the data from the date of the individual transaction. 

Each of these approaches can yield different results, so it's important to understand how each one differs, so that you can choose the one that best suits your needs.

 

The Raw Data Each Window Will Analyze

Before we discuss the first type, the transformation window, here's the hypothetical raw data that we'll be using in the examples presented throughout these articles:

 

Our hypothetical raw data is the following ATM transactions that occur between 4/15/2014 8PM and 5/13/2014 4PM. (Due to space considerations in the animated diagrams that appear in later articles, minutes or seconds values for the transaction datetimes are not provided.)

 

Figure 1: Raw Data

Transaction ID

Transaction Datetime

Member ID

Deposit Amount

1

4/15/2014 8PM

2

$5,000

2

5/1/2014 8AM

2

$5,000

3

5/1/2014 12PM

2

$4,000

4

5/1/2014 11PM

3

$8,000

5

5/2/2014 1PM

2

$5,000

6

5/3/2014 9AM

1

$4,000

7

5/3/2014 5PM

1

$2,000

8

5/3/2014 10PM

3

$3,000

9

5/4/2014 11AM

2

$4,000

10

5/4/2014 9PM

3

$2,000

11

5/5/2014 11AM

1

$3,000

12

5/6/2014 2PM

1

$3,000

13

5/7/2014 10PM

1

$2,500

14

5/8/2014 1PM

2

$1,000

15

5/10/2014 10AM

2

$2,000

16

5/10/2014 5PM

2

$4,000

17

5/10/2014 8PM

4

$12,000

18

5/12/2014 8AM

2

$5,000

19

5/13/2014 4PM

1

$2,000

 

The Transformation Window

A transformation window analyzes your raw data according to filter criteria that you define. This detail data has not been aggregated (for more information, see the Aggregation Window article), and is at the same level as the raw data. The resulting output contains those values in the raw data that meet the filter criteria. A transformation window produces one output record per input record that meets the filter criteria. In this example, your filter criteria filters out any transactions that are lower than $3,500 so that you are left with only high-value transactions in your window:

 

Figure 2: Detail Data from a Transformation Window

Transaction ID

Transaction Datetime

Member ID

Deposit Amount

1

4/15/2014 8PM

2

$5,000

2

5/1/2014 8AM

2

$5,000

3

5/1/2014 12PM

2

$4,000

4

5/1/2014 11PM

3

$8,000

5

5/2/2014 1PM

2

$5,000

6

5/3/2014 9AM

1

$4,000

9

5/4/2014 11AM

2

$4,000

16

5/10/2014 5PM

2

$4,000

17

5/10/2014 8PM

4

$12,000

18

5/12/2014 8AM

2

$5,000

 

Why use a transformation window? You might do so to obtain a subset of data from a larger data set, such as retaining only records that are ATM deposits (rather than withdrawals). Doing so speeds up the processing time for any dependent windows because the data being passed to them is reduced.

 

The transformation window has two other typical use cases:

 

  • Dropping or renaming columns from the input table. Again, this can be used to reduce the data that is sent to downstream windows by only keeping what is required for the scenarios.
  • Creating calculated columns that do not require aggregation, and that instead operate on individual rows within the input data. An example of this is producing a new field that is the maximum of 0 and the deposit amount to remove negative values.

This is article #1 in a five-part series. See the links below for the additional articles: 

 

Part 2: Aggregation Window
Part 3: Date-based Aggregation Window

Part 4: Transaction Window

Part 5: Knowing Which Window Type to Choose

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.