When using an ODBC data source for a Planning Analytics Turbo Integrator (TI) process, native TI parameters can be utilized within the SQL query. Planning Analytics users have the power to be more specific and efficient with a query, potentially increasing performance by having less unnecessary data that needs to be read throughout the process, as well as more efficient logic to filter data in the Metadata and Data tabs, and less long-term model maintenance.
Before incorporating a parameter into the query, the variables which are expected of the query must be established either manually or with a non-dynamic version of the final query (recommended).
In the TI’s Prolog, the parameter can still be updated and manipulated, if necessary. The syntax for the dynamic parameter can be added to the query on the Data Source tab, or the DataSourceQuery function can be used in the Prolog to overwrite the query on the Data Source tab.
One common use of adding a parameter to a query is to specify a particular period (or range of periods) in the SQL statement’s WHERE clause to restrict data coming from the source to only the desired periods. In this example, developed using Planning Analytics Workspace (PAW), we will use the manually created parameter, pYear, to represent the input parameter that will be used to make this query dynamic.
The syntax for adding any string parameter to a SQL statement is: question marks around the full name of the parameter, enclosed with single quotes.
This example would then prompt the PA user at run-time, providing the ability to dynamically query the source for the year specified, instead of hard-coding the year, requiring long-term maintenance, or pulling too much data and having to filter it later in the process.
A numeric parameter has slightly different syntax by not enclosing the parameter in single quotes.
If you have additional questions or need any assistance, please contact QueBIT at info@quebit.com.
See more Planning Analytics technical tips at Ask QueBIT!