WhereScape RED Builds data warehouses, fast.

WhereScape RED has a full time team of developers continually adding new features, adding support for new versions of database platforms and fixing bugs.  A primary input for deciding what will get worked on is the Forum – so if you have any suggestions / bugs to report then please use the Forum.

Recently there have been a lot of additions to WhereScape RED and I thought I would highlight some of the majors added through to 5.6.3.1 (current beta):

·         Support added for Oracle 11 as a data warehouse platform.

·         Beta support for SQL Server 2008 (including Analysis Services) as a data warehouse platform.

·         Check in / out support extended - different objects (tables, views, indexes and procs) can now be checked out and (in), not just procedures.   In addition multiple items can be checked out (and in) in one go.

·         Native ODBC works on all data warehouse platforms (including DB2) - this is the fastest database to database method for importing data into the data warehouse.

·         Export objects - provide the ability to export data from the data warehouse to files and manage it as part of the data warehouse scheduling.

·         Retrofit objects - added to simplify the process of importing existing data warehouses or pre-defined data warehouse schemas into the WhereScape RED Meta repository.

·         Browsing the data warehouse objects (in the source or right hand pane) is easier now in large data warehouses because you can filter objects by project.

·         Analysis Services 2005+ - there have been several fixes and additions to cube support including fixes to processing, support for attribute relationships, and drill through actions.

·         There have been numerous developer usability tweaks such as right click view code for a table, right click update / custom for a table, and right click start job for a held job,

The relase notes for each version contain a complete list of changes, but these are the highlights.

Sometimes I need to have more control and flexibility over a job than just scheduling it at a given time every day.

The WhereScape RED scheduler job frequency of “custom” provides you additional options to schedule your job:

i) Only on certain days of the week which is handy if you don’t want a daily job to run over the weekend

ii) Only for a specific time interval which is often required to check data warehouse source systems for new data every five minutes or every hour.

Tip:  If you are scheduling a custom job to run frequently and you don’t need to keep its history, you can specify a “Logs retained” value that will stop job output being cluttered with hundreds of repeated entries.

There is also the very useful functionality within the WhereScape RED scheduler to add single or multiple parent jobs to a job.  This is great for scenarios such as only running the daily sales job once the build  reference data job has completed successfully, or  only running the building consolidated fact table job once all of the different sales by region jobs have completed successfully.  You can even specify a maximum time interval to wait for the parent job to complete.

All very useful stuff indeed, but occasionally, even using all the WhereScape RED scheduler options above still does not give you the scheduling options you need.

But don’t worry, there are still a couple more clever procedures that will help you, Ws_Job_Release and Ws_Job_Create.

Ws_Job_Release allows the automated release of an on hold or waiting job.  When would I use this you may ask ?  As an example I have used this before in a daily job  that checks a manually maintained WhereScape RED parameter (using WsParameterRead) just before it finishes to see if I want to automatically catch up with the source systems I am extracting data from. If the parameter value is yes, then I release another  daily job.  This enables multiple daily jobs to run one after the other in the minimum time without having to schedule additional job runs and guessing how long each would take.

Ws_Job_Create allows the automated creating of a new job from an existing job that is in either an on hold or waiting state.  When would I use this as well you may ask ?  As an example I have used this before when I have a polling job that has a procedure to release the daily job (using Ws_Job_Release) once a trigger row of data is found. I only want to start polling at midnight though.  Within the polling job procedure if my trigger data row is not present I release another copy of the polling job (using Ws_Job_Release). If my trigger row is present I release the daily job (using Ws_Job_Release) AND create a new version of the polling job using Ws_Job_Create to run the following evening. 

Ws_Job_Release and Ws_Job_Create are just two of many job related procedures that are available within WhereScape RED.

Combining WhereScape RED scheduler functionally, additional job related procedures with the use of WhereScape RED parameters   will always enble you to get your data warehouse running exactly as you need it to.

Thanks to Aaron Parvin of WhereScape for historic job scheduling help.