Analytics SAQL Developer Guide

Overview
Quick Start
Examples
SAQL Reference
Query Performance
Speed Up Queries with Dataflow Transformations
Limit Multivalue Fields
Use Group and Filter Pre-projection
Remove Redundant Projections
Check for Redundant Filters
Limit the Use of unique()
arimax Show Parameters Query Tool
PDF

Newer Version Available

This content describes an older version of this product. View Latest

arimax Show Parameters Query Tool

The showParams parameter is provided for you as a query developer tool. It allows you to run an arimax query and review the model parameters and Bayesian Information Criteria (BIC) to understand the data better and improve query efficiency.

When running an arimax query with the showParams specified, no statements after the arimax statement are allowed. During development, splitting your query can make testing easier.

The query result isn't the queried data, it’s debugging information that shows the model parameters and the Bayesian Information Criteria (BIC) value. Use the result with the lowest BIC in your final query.

Description

Valid values are top and multi.

Specifying top returns the final Arimax model and BIC for each partition and each forecasted measure. For example, the results for showParams="top" are:

Measure ArimaOrder SeasonalOrder BIC
Revenue (1,1,1) (1,2,0,4) 123.45

Specifying multi returns the top 3 Arimax models and BIC for each partition and each forecasted measure. A lower BIC score denotes a better model fit. For example, the results for showParams="multi" are:

Measure ArimaOrder SeasonalOrder BIC
Revenue (2,1,1) (1,1,0,4) 100.248204
Revenue (2,1,1) (2,0,1,12) 107.434348
Revenue (2,1,1) (0,1,2,4) 112.206876

If both arimaxOrder and seasonalOrder are defined in the query, only the specified Arimax model and BIC are returned.

It's best practice to always provide a dateType with the dateCols parameter to ensure more accurate predictions and better query performance. Omitting dateType results in a long-running query.

Warning

Syntax - No Partition

Syntax examples for show parameters queries with no partition.

Single Forecast Measure, showParams="top"

The query uses showParams="top" and a single forecast measure. The results return the lowest BIC value.

1q = arimax q generate Revenue as fRevenue
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), showParams="top");

Query results are:

Measure ArimaOrder SeasonalOrder BIC
Revenue (1,1,0) (0,1,1,3) 459.50558

Single Forecast Measure, showParams="multi"

The query uses showParams="multi" and a single forecast measure. The results return the top 3 parameter combinations with the lowest BIC.

1q = arimax q generate Revenue as fRevenue
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), showParams="multi");

Query results are:

Measure ArimaOrder SeasonalOrder BIC
Revenue (1,1,0) (0,1,1,3) 459.50558
Revenue (0,1,0) (0,1,1,3) 478.15679
Revenue (2,1,0) (0,1,1,3) 512.45935

Multiple Forecast Measures, showParams="top"

The query uses showParams="top" and a multiple forecast measures. The results return the lowest BIC value for each measure.

1q = arimax q generate Revenue as fRevenue, ShippingCost as fShippingCost
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), showParams="top");

Query results are:

Measure ArimaOrder SeasonalOrder BIC
Revenue (1,1,0) (0,1,1,3) 459.50558
Shipping Cost (2,0,1) (0,1,1,3) 1017.855

Multiple Forecast Measures, showParams="multi"

The query uses showParams="multi" and multiple forecast measures. The results return the top 3 parameter combinations with the lowest BIC for each measure.

1q = arimax q generate Revenue as fRevenue, ShippingCost as fShippingCost
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), showParams="multi");

Query results are:

Measure ArimaOrder SeasonalOrder BIC
Revenue (1,1,0) (0,1,1,3) 459.50558
Revenue (0,1,0) (0,1,1,3) 478.15679
Revenue (2,1,0) (0,1,1,3) 512.45935
Shipping Cost (2,0,1) (0,1,1,3) 1017.855
Shipping Cost (2,0,0) (0,1,1,3) 1025.45699
Shipping Cost (2,0,2) (0,1,1,3) 1131.45972

Syntax - With Partition

Syntax examples for show parameters queries with a partition.

Single Forecast Measure, showParams="top"

The query uses showParams="top", a single forecast measure, and a partition. The results return the lowest BIC value for the measure in each partition.

1q = arimax q generate Revenue as fRevenue
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), partition='Region', showParams="top");

Query results are:

Partition Measure ArimaOrder SeasonalOrder BIC
AMER Revenue (1,1,0) (0,1,1,3) 53.78475
APAC Revenue (0,1,0) (0,1,1,3) 56.0938
EU Revenue (2,1,2) (0,1,1,3) 58.3398

Single Forecast Measure, showParams="multi"

The query uses showParams="multi", a single forecast measure, and a partition. The results return the top 3 parameter combinations with the lowest BIC value for the measure in each partition.

1q = arimax q generate Revenue as fRevenue
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), partition='Region', showParams="multi");

Query results are:

Partition Measure ArimaOrder SeasonalOrder BIC
AMER Revenue (1,1,0) (0,1,1,3) 53.78475
AMER Revenue (1,1,1) (0,1,1,3) 57.15645
AMER Revenue (0,1,0) (0,1,1,3) 62.14569
APAC Revenue (0,1,0) (0,1,1,3) 56.0938
APAC Revenue (0,1,1) (0,1,1,3) 57.19587
APAC Revenue (1,1,0) (0,1,1,3) 58.45987
EU Revenue (2,1,2) (0,1,1,3) 58.3398
EU Revenue (1,1,1) (0,1,1,3) 64.01597
EU Revenue (2,1,0) (0,1,1,3) 71.48946

Multiple Forecast Measures, showParams="top"

The query uses showParams="top", multiple forecast measures, and a partition. The results return the lowest BIC value for each measure in each partition.

1q = arimax q generate Revenue as fRevenue, ShippingCost as fShippingCost
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), partition='Region', showParams="top");

Query results are:

Partition Measure ArimaOrder SeasonalOrder BIC
AMER Revenue (1,1,0) (0,1,1,3) 53.78475
AMER Shipping Cost (0,1,0) (0,1,1,3) 122.04503
APAC Revenue (0,1,0) (0,1,1,3) 56.0938
APAC Shipping Cost (2,1,0) (0,1,1,3) 138.1605
EU Revenue (2,1,2) (0,1,1,3) 58.3398
EU Shipping Cost (2,1,1) (0,1,1,3) 130.74353

Multiple Forecast Measures, showParams="multi"

The query uses showParams="multi", multiple forecast measures, and a partition. The results return the top 3 parameter combinations with the lowest BIC value for each measure in each partition.

1q = arimax q generate Revenue as fRevenue, ShippingCost as fShippingCost
2with (length=10, order='ldx', predictionInterval=[80,95],
3seasonalOrder=(0,1,1,3), partition='Region', showParams="multi");

Query results are:

Partition Measure ArimaOrder SeasonalOrder BIC
AMER Revenue (1,1,0) (0,1,1,3) 53.78475
AMER Revenue (1,1,1) (0,1,1,3) 57.15645
AMER Revenue (0,1,0) (0,1,1,3) 62.14569
AMER Shipping Cost (0,1,0) (0,1,1,3) 122.04503
AMER Shipping Cost (2,0,1) (0,1,1,3) 127.48979
AMER Shipping Cost (0,1,1) (0,1,1,3) 139.48975
APAC Revenue (0,1,0) (0,1,1,3) 56.0938
APAC Revenue (0,1,1) (0,1,1,3) 57.19587
APAC Revenue (1,1,0) (0,1,1,3) 58.459878
APAC Shipping Cost (2,1,0) (0,1,1,3) 138.1605
APAC Shipping Cost (1,1,1) (0,1,1,3) 141.1567
APAC Shipping Cost (0,1,0) (0,1,1,3) 143.15756
EU Revenue (2,1,2) (0,1,1,3) 58.3398
EU Revenue (1,1,1) (0,1,1,3) 64.01597
EU Revenue (2,1,0) (0,1,1,3) 71.48946
EU Shipping Cost (2,1,1) (0,1,1,3) 130.74353
EU Shipping Cost (1,0,1) (0,1,1,3) 137.1567
EU Shipping Cost (0,1,2) (0,1,1,3) 146.49865

Use Show Parameters with No Seasonality to Model BIC Values

To understand your data, run the arimax query without seasonalOrder and with showParams="multi".
1q = load "em/coffee";
2q = filter q by Type in ["Hot Coffee", "Bulk Coffee"];
3q = group q by ('ClosedDate_Year', 'ClosedDate_Quarter', "Type");
4q = foreach q generate 'ClosedDate_Year', 'ClosedDate_Quarter', 'Type', sum(Weight) as sum_Weight;
5q = arimax q generate 'sum_Weight' as fSumWeight,
6with (length=4, showParams="multi", partition='Type', ignoreLast=true,
7dateCols=('ClosedDate_Year', 'ClosedDate_Quarter', "Y-Q"), arimaOrder=(2,1,2));"

The results show multiple seasonalOrder values with the lowest BICs for each forecast measure. You can select the seasonalOrder that works best for your final query.

Partition Measure ArimaOrder SeasonalOrder BIC
Cold Coffee sum_Weight (2,1,2) (2,0,2,4) 207.8193821
Cold Coffee sum_Weight (2,1,2) (0,1,0,4) 270.4199107
Cold Coffee sum_Weight (2,1,2) (1,1,0,4) 283.68998334
Hot Coffee sum_Weight (2,1,2) (0,1,0,4) 329.0235125
Hot Coffee sum_Weight (2,1,2) (0,1,2,4) 320.3612713
Hot Coffee sum_Weight (2,1,2) (1,1,0,4) 365.6449188