In-Database Nirvana – The Five Step Process to run business rules without moving data

The phrase “business rules” is often loosely used. It can refer to things like constraints in a query, a data mapping, a data quality constraint, a data transformation, or a model. Business rules also reflect an enforced policy, a regulatory requirement and business constraints on model scores that trigger analytically-driven outcomes.

There is a growing trend to not move data because it’s getting bigger by the day. And with needs to appropriately secure that data while improving response times, as well as taking advantage of existing infrastructure, there is a compelling argument for running rules in the database rather than extracting data to employ the same and necessary logic.

The current life cycle of building and deploying analytical models needs to be short to ensure objectives are met before the models become stale and obsolete. Couple that with changing and increasingly critical business requirements, and it’s clear that organizations today must be able to quickly adapt to external environment dynamics. These needs are met by the well-orchestrated application of business rules, and therefore, how or where we process business rules does indeed matter.

Using SAS, defined business rules can be pushed down into the database, to execute in situ, and without moving the data. It’s easy really. In fact, it’s one of the easiest “deployments to a different execution environment” I have ever done. You may be thinking, “easy” is relative – so here are the five steps you need to do this – and you be the judge. Of course there is a caveat, I’m assuming that the correct access engines and code accelerators have been installed/configured and tested. Teradata, Greenplum and Hadoop are all currently supported.

The Five Steps:

Step 1: Set system options. System options must be set to DS2ACCEL=ANY. By default they are set to DS2ACCCEL=NONE. Set this option in SAS Data Integration Studio, or define globally in the sasv9 configuration file - at whatever level you want. The associated settings are illustrated here for SAS Data Integration Studio:

Put Options DS2ACCEL=ANY; in the Precode section of the Precode and Postcode tab shown below on the Business Rules transform:

Step 2: Identify your database (Teradata or Greenplum) or Hadoop instance and your input tables.

Step 3: Develop your rules. SAS Decision Manager includes SAS Business Rules Manager, and the same process applies for business rule development and testing as would if you were testing against a SAS dataset. A vocabulary can be created from any table that is accessible from a SAS access engine (a long list indeed). You can even use machine learning techniques to generate business rules from data, to augment what you already defined. Within this environment, you will create, test and publish your complete rule flows. If testing the business rules running in-database or in the Hadoop table, set the system option as described in Step 1 - in the preprocessing section using the test setup wizard.

Step 4: Validate in-database rule execution. To answer “How do I really know where my rules ran?” examine the log notes:

In the Data Integration Studio log, you should look for these three important lines:

XOGTEST: SASEP was found on connection MYCON.

NOTE: Running THREAD program in-database

NOTE: Running DATA program in-database

Step 5: Deploy to the database. Once you’ve validated that business rules are successfully executing in the database (Step 4), the entire code thread will run in-database. You may want to redirect some of your Business Rules Manager transform output tables to the database rather than having them written back to SAS datasets. The job can then be scheduled for production.

That’s it. Just five steps. You’ve now eliminated data transfer time, which can be substantial, and are running business rules in the optimized compute environment of the database. This is a collaborative effort between business and IT that can save a great deal of manual time. The business side of the organization can own, test and manages the business rules in this non-coding environment - eliminating the cumbersome and time consuming necessity of IT having to write code for deployment. Now IT can proceed with their production implementation processes using the same logic (that’s been generated behind the business user interface). The overall execution run-time savings can be significant and while they’ll vary from environment to environment, putting business rules into action sooner will make your organization more responsive, agile and accurate.

In-Database Nirvana – The Five Step Process to run business rules without moving data was published on SAS Users.