Menu Request Demo

Parameters Limit the Need for Custom Code

Date:
18 October 2007
Author:
Aaron Parvin

 

Parameters are a means of passing information between two or more procedures and between the RED environment and procedures. WhereScape Red supplies two functions to allow procedures to read and write parameters. These functions are WsParameterRead and WsParameterWrite. Using these functions a procedure can load and use the contents of a parameter, or modify an existing parameter, or add a new parameter.

Importantly the use of these parameter functions is not limited to custom procedures.

When creating a data warehouse using WhereScape Red a major benefit is the ability to keep custom code to a minimum. Parameters are an important aspect of the RED environment that facilitates the use of standard RED generated code.

The following is a list of some of the areas that these functions can be used in.

• Load table objects. Use $P$ in the ‘where clause’, ‘remote view’, ‘load override statement’ and transformations.

 

• Stage table objects. In the ‘where clause’ and ‘column transformations’.

 

• Fact table objects. In the ‘where clause’ and ‘column transformations’.

If performance issues dictate that in some situations the use of the parameter functions is not an option. You can also use the parameter table itself as a data source table.

This can be done through WhereScape RED generated views or loading the parameter data required directly from the metadata table that stores the parameter values. The parameter values are stored in a table called ‘dss_parameter’. The resulting load table or view can then be used to assist in filtering data sets via the use of joins during stage processing to other tables containing source data.

 

Examples of function use:

In The Load Table Where Clause AND TO_CHAR(AL3.CONN_ACTIVE_DATE,'YYYYMM') IN ('$PCURRENT_CAL_MONTH$' , '$PPREVIOUS_CAL_MONTH$' , '$PPRIOR_CAL_MONTH$')

 

In The Stage/Fact Table Where Clause

 

Where WsParameterRead('QTR_RE_SUBMISSION') = 'Y';

 

In the Stage/Fact Table Column Transformation

 

'NZLQ'

|| substr(WsParameterRead('PREVIOUS_CAL_MONTH'),5,2)

|| substr(WsParameterRead('PREVIOUS_CAL_MONTH'),3,2)

 

Examples of dss_parameter use as a source table:

 

Consider the use of a load table sourced from a parameter that contains a variable piece of data that must be attributed to each row of your source data. Each source row should be associated with this data, but the content can change from one load to the next. This may be dependant on the month/ week /day of processing or something else entirely.

Using a load table that loads the parameter required from dss_parameter you can then join this table to your source data during staging without the need for any complexity involved in obtaining the variable data needed across multiple stage table processes. Any complexity can be handled once through a custom procedure that maintains the parameter.

 

CATEGORIES:

Comments 2

Leave a Comment

  • By Tom Kelly 12 September 2016
    Hi Yiyi. I have written a solution article on how to do this. You can find it here: https://support.wherescape.com/support/solutions/articles/4000088194-calling-wsparameterread-inside-a-where-clause
  • By Yiyi Li 12 September 2016
    Great articles. Thank you. I am wondering how do you use the parameter in Fact table objects - the ‘where clause’? Apparently not like stage table, there is no option for selecting parameter before entering ‘where clause’.