DataDriving Applications Get in the Drivers Seat

1
Data-Driving ApplicationsGet in the Drivers
Seat!

• Dave Reid
• Principal IT Applications Analyst
• Symantec Corporation
• http//www.symantec.com
• Rob Wood
• Senior Principal Consultant
• MDB Technology Solutions
• http//www.mdbts.com

2
Agenda

• What is data-driving?
• Why data-drive?
• Toolbox
• Examples
• Excuses
• QA

3
What is Data-Driving?

• It is NOT a new Ford SUV!
• It is a coding methodology
• It removes program logic and hard-coded values
  from source code and stores them in the database.
• You control your applications behavior using
  data. You then edit the data instead of the
  code.
• Allows you to turn code changes into functional
  setups.

4
Why use Data-Driving?

• Think giant remote control
• Each data-driven element is a button
• More buttons means more control
• Create custom Lists of Values
• Eliminate hard coding
• Significantly reduce code branching and increase
  logic control
• Increase code compression and re-use

5
So What Does It Really Mean?

• Simpler code
• Dramatically fewer lines to read and maintain
• Say goodbye to long, ugly IF, ELSIF statements
• Bug fixes or small enhancements become simple
  functional changes
• Features can be turned on or off dynamically
• Give customers what they want more quickly
• Happier customers.

6
The Toolbox

• Data model and supporting forms and utilities
• Foundation of your future development work
• Toolbox consists of
• Tables to store the lookup data
• Maintenance forms
• Utilities
• Analysis considerations
• Data striping by application or operating unit
• Access control responsibilities

7
Toolbox 1st Design Concept

• Many custom, single-purpose tables
• Easily understood and built
• Easily queried
• Simply add columns to add additional, related
  data points(e.g. for a list of U.S. states, add
  a column for the full state name)
• A maintenance nightmare!
• Table proliferation
• Editing by SQL or build separate maintenance forms

8
Toolbox 2nd Design Concept

• Using Oracles Quickcodes form
• Multi-purpose, generic tables
• Tables and form are prebuilt
• Descriptive Flexfield for extendibility
• Already installed and available
• Not as useful as it seems
• Limited flexibility
• Cannot alter the data model
• Data not truly under your control
• At risk during patching
• Possible support issues

9
Toolbox 3rd Design Concept

• Custom multi-purpose tables with a maintenance
  form
• Complete control over data and the data model
• Add multiple generic columns for extra data
  elements
• Maintenance form is easy to build
• Still not a very robust solution
• Extra columns are not as useful as a Descriptive
  Flexfield
• Needs affectivity dates and enabled flags
• Somewhat like re-inventing the wheel

10
Symantecs Data-Driving Toolbox

• Custom tables as in the 3rd Design Concept, but
  using Oracles tables as a model
• Lots of good ideas
• Affectivity dates and Enabled flags
• Descriptive Flexfield
• Form for editing
• Custom extensions
• Application striping
• Org striping
• Data Security
• PL/SQL functions
• Application-striped, org-striped, active-only
  views

11
Symantecs Toolbox Details

• What is an Application-striped, org-striped,
  active-only view?
• CREATE OR REPLACE VIEW symom_active_lookup_values
  AS SELECT FROM symfnd_lookup_values_all
  WHERE enabled_flag 'Y' AND SYSDATE
  BETWEEN start_date_active
  AND NVL (end_date_active,
  SYSDATE 1) AND Oracle's
  org-striping clause AND application_short_n
  ame 'SYMOM'

12
Symantecs Toolbox - Screenshot
13
Examples
14
Example 1 Data Substitution

• Original code
• scrub_str (l_cust_name, '.') scrub_str
  (l_cust_name, '(P)')scrub_str (l_cust_name,
  'P') scrub_str (l_cust_name,
  '') / DLR 4/29 - Removed per Tracker ID
  792 scrub_str (l_cust_name, ' AND ', '
  ')/scrub_str (l_cust_name, 'CORPORATION',
  'CORP') scrub_str (l_cust_name,
  'INCORPORATED', 'INC') scrub_str (l_cust_name,
  'COMPANY', 'CO')scrub_str (l_cust_name,
  'LIMITED', 'LTD')scrub_str (l_cust_name,
  'INTERNATIONAL', 'INTL')scrub_str (l_cust_name,
  'DUPLICATE')

15
Example 1 (contd)

• Data-driven version
• CURSOR scrub_str_cur (p_string_type IN VARCHAR2)
  IS SELECT lookup_code search_str,
  meaning replace_str
  FROM symom_active_lookup_values WHERE
  lookup_type 'SCRUB_' p_string_type FOR
  scrub_str_rec IN scrub_str_cur ('CUSTOMER') LOOP
  scrub_str (l_cust_name,
  scrub_string_rec.search_str,
  scrub_string_rec.replace_str) END LOOP

16
Example 1 (contd)

• Supporting data

17
Example 2 Logic Control

• Original code
• IF l_database_sid 'PROD' THEN apply_hold
  (l_order_number, c_hold_type)END IF
• IF l_org_id 7 THEN l_email_address
  'us_orders_at_symantec.com' ELSIF l_org_id 8
  THEN l_email_address 'emea_orders_at_symantec.c
  om'ELSIF l_org_id 9 THEN

18
Example 2 (contd)

• Wrong solution
• IF l_database_sid lookup_value ('SYMOM',
  'ORDER_CHECKER',
  'HOLD_SID') THEN
  apply_hold (l_order_number, c_hold_type)END IF
• IF l_org_id 7 THEN l_email_address
  lookup_value ('SYMOM',
  'ORDER_CHECKER',
  'US_EMAIL')ELSIF l_org_id 8 THEN
  l_email_address lookup_value
  (,'EMEA_EMAIL')

19
Example 2 (contd)

• Complete solution
• IF lookup_value ('SYMOM',
  'ORDER_CHECK_FLAGS',
  'HOLD_FLAG') 'Y' THEN apply_hold
  (l_order_number, c_hold_type)END IF
• l_email_address lookup_value ('SYMOM',
  'ORDER_CHECKER',
  'EMAIL_ADDRESS')

20
Example 2 (contd)

• Supporting data

21
Example 3 Code Compression

• Custom.pll
• Do you create multiple similar types of
  customizations using the custom library?
• Setting fields as required
• Disabling fields
• Setting default values, etc
• How often do your users submit change requests
  for these minor customizations?
• Do they sit with all the other low priority
  change requests and never get done?

22
Example 3 (contd)

• Sample Requirements
• Make some fields required.
• FIRST_NAME on the Customer Standard form
• POSTAL_CODE on the Customer Standard form

23
Example 3 (contd)

• Brute force solution
• IF form_name 'ARXCUDCI' THEN IF event
  'WHEN-NEW-FORM-INSTANCE' THEN
  APP_ITEM_PROPERTY.SET_PROPERTY (
  'CONTACT.FIRST_NAME', REQUIRED,
  PROPERTY_ON) APP_ITEM_PROPERTY.SET_PROPERT
  Y ( 'ADDRESS.POSTAL_CODE',
  REQUIRED, PROPERTY_ON) END IFEND
  IF

24
Example 3 (contd)

• Data-driven solution
• CURSOR req_fields_cur (p_form_name IN VARCHAR2)
  IS SELECT lookup_code field_name FROM
  symom_active_lookup_values WHERE lookup_type
  p_form_name _REQ_FIELDSIF event
  'WHEN-NEW-FORM-INSTANCE' THEN FOR
  req_field_rec IN req_fields_cur (form_name) LOOP
  APP_ITEM_PROPERTY.SET_PROPERTY (
  req_field_rec.field_name, REQUIRED,
  PROPERTY_ON) END LOOPEND IF

25
Example 3 (contd)

• Supporting data

26
Example 3 (contd)

• Supporting data
• Imagine your happy customers.

27
More Examples

• Special Menu items
• Dynamically add reports or forms to the menu
• Submit concurrent requests for each report
• Gather parameters from the current record
• Dynamic SQL (DBMS_SQL)
• Data-driven table names plus WHERE and ORDER BY
  conditions
• API parameters
• Dynamically select parameters to send to an
  Oracle API
• Gather values from the current record
• There is no end to the possibilities

28
I like the concept, but

• These hard-coded strings will never change.
• They will change your business will change over
  time.
• Being prepared for something that does not happen
  is never a bad idea.
• It will be too slow doing all these lookups.
• Actually thats not true. Symantec rolled a very
  heavily data-driven single global instance with
  no performance issues.
• The table is relatively small and the key columns
  are indexed.

29
Excuses (contd)

• It will take much longer to code.
• No it wont. Once you have your toolbox, it
  takes no time at all.
• It is a trade-off. Any additional time would be
  for your initial data setup, which could save
  days of maintenance work in the future.
• But I like doing repetitive maintenance. I
  dont want to make my life easier.
• Theres just no helping you. ?
• Any more excuses?

30
So

• Data-drive wherever you can.
• If you are about to implement a solution or fix a
  bug, think of how you could use data-driven
  techniques.
• Remember, how many buttons do you want on the
  remote control for this piece of code?
• There is no hard and fast way to data-drive. Be
  creative.

31
QA

• Questions?

32
Contact Information

• Any questions please contact
• Rob Wood
• Senior Principal Consultant
• MDB Technology Solutions
• E-Mail rwood_at_mdbts.com
• Toll Free (888) 484-4MDB
• http//www.mdbts.com