When first starting to use WhereScape RED many years ago, I briefly looked at parameter functionality and thought “I am sure that will come in handy some day”.
Now the RED parameter is a fundamental building block in the daily operation of all the warehouses I have built.
A parameter in RED can be called anything you like and can contain a static value that never changes after the implementation of the warehouse or more commonly regularly incremented/updated values.
A set of very useful built in RED functions allow parameters to be read and updated at any time which are called WsParameterRead and WsParameterWrite.
Examples of how I have used each of these in SQL Server are:
– find out the value of the parameter ‘DAILY_RUN_IN_PROGRESS_FLAG’ SELECT @v_run_in_progress_flag = dbo.WsParameterReadF(’DAILY_RUN_IN_PROGRESS_FLAG’)
– set the value of parameter ‘DAILY_RUN_IN_PROGRESS_FLAG’ to ‘Y’ with comments EXEC @v_return_code = WsParameterWrite DAILY_RUN_IN_PROGRESS_FLAG,’Y',’Place our paramter update comments here’
There are there also two other “special” ways to access your RED parameters in addition to the functions above which are:
1. Load table definitions
A RED parameter can be used in the WHERE clause of a load table by simply wrapping “$P” and “$” around the parameter you wish to use (you must add single quotes as shown in example for non numeric comparisons) in the source mapping tab in the load table properties screen e.g.
WHERE DATE_ADD(’1970-01-01′, INTERVAL Load_Table_Name.Load_Table_Date_In_Seconds_Column) SECOND) >= ‘$PLAST_RUN_DATE_GMT$’
AND DATE_ADD(’1970-01-01′, INTERVAL Load_Table_Name.Load_Table_Date_In_Seconds_Column) SECOND) <= PCURRENT_RUN_DATE_GMT$’
At run time the RED parameter value will be substituted and I recommend storing date parameters in a format that works (without transformation) with the database you are using.
In the SQL Server example above I set the parameter to the format “YYYY-MM-DD HH24:MM:SS” e.g. 2008-02-27 10:59:56
2. Staging table definitions
When generating RED procedures a popup screen appears asking if you need to use any parameters in the SQL statement in the procedure.
By selecting parameters you want to use, additional code is automatically added to the beginning of the generated procedure setting the parameter values into procedure local variables:
SELECT @v_current_run_date = dbo.WsParameterReadF(’CURRENT_RUN_DATE’)
SELECT @v_last_run_date = dbo.WsParameterReadF(’LAST_RUN_DATE’)
SELECT @v_unknown_date = dbo.WsParameterReadF(’UNKNOWN_DATE’)
These local variables can then be referenced in the where clause of the main SQL statement using the variable name generated in RED:
WHERE DATEADD(hh,2,stage_vehicle.StartDate + stage_vehicle.StartTime) >= last_run_date
AND stage_vehicle.StartDate + stage_vehicle.StartTime <= @v_current_run_date
Staging table column definitions can also use the local variable names for the parameters:
ISNULL(stage_Vehicle.StartDate,CONVERT(DATETIME,@v_unknown_date,103))StartDate
Using RED parameters in the WHERE clauses of load and staging tables is a very effective way of restricting large volumes of transactional data.
I can highly recommend the checking/setting of parameters at the start and end of a scheduled job to easily manage incremental/delta data loads as part of a well designed data warehouse.
