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
