Pushing the Technical Frontier

I have a dilemma.

In the BI system that I’m working on I have the perfect application for the use of Expression Filters. It is almost as if someone specifically devised this problem in order to tempt me into using them, and I really am very tempted indeed.

Let me explain.

In the application which feeds data to the BI system users can define “display filters” that determine whether network alerts are displayed in particular windows or not. For example they might create a filter called “Failed Core Devices” for which the definition reads: “IS_CORE=’Y’ and FAILED>=1”. Network events which meet those criteria are displayed in that window. They might also have a filter called “Failed Cisco Devices” with a definition: “DEV_TYPE LIKE ‘CISCO’ AND FAILED >= 1”. So, a failed Cisco core device will actually appear in both windows.

The requirement is to tag which windows an event was displayed in at a particular time, and because there are no data elements for each event to tell you the display window(s) the only way to work that out is to evaluate the expression for each event.

This is exactly what an Oracle Expression Filter does, and although I would have to convert the definitions from Netcool ObjectSQL to Oracle SQL (LIKE in Netcool ObjectSQL is a regular expression, so “DEV_TYPE LIKE ‘CISCO'” has to be converted to “REGEXP_LIKE(DEV_TYPE,’CISCO’)”), they are an obvious fit for the problem.

My query to determine the filter names for the events would be reduced to something like:

  events e,
  filter_definitions f
      e.failed)) = 1

Which would obviously be pretty neat and tidy, considering the alternatives (think of how you might implement an expression such as “(COL1 NOT IN (1,3,4,5,78,900) AND COL2 IS NOT NULL) OR NOT REGEXP_LIKE(COL3,’^WHATEVER’)” by other means as simply as you would with an expression filter). It could be implemented as part of the ELT or as a filter or join in Business Objects (or your current query tool of choice).

However, what happens if I move on to another client and this method fails because of a new filter definition that references a new data element not currently included in the Expression Filter attribute set? Or an upgrade goes wrong? Or a patch breaks it? Or we discover a bug? Can I sleep at night if I’m leaving code behind that will have most reasonably competent Oracle practitioners reaching for Google to find out what EVALUATE is, and then has them reading documents for the next couple of hours? Even if I sit the support staff down and show them all how it works, that knowledge will have evaporated in a couple of months.

Documentation? Well I can certainly document how expression filters work, and how to detect problems and perform apparantly simple tasks like modifying the attribute set (I already did something similar for DBMS_Scheduler chains), but I’m not going to think of everything and even then do I want people to be following written instructions without understanding the architecture of expression filters? It’s pretty easy to get into a mess over this object oriented stuff.

The alternative? Currently unknown. The few options identified are prone to inaccuracy or imprecision (eg. they’ll only identify a single filter instead of the full set), and they tend to require continual maintenance of the ELT or the query tool or both whenever a filter is created, deleted or modified.

So to summarise: do I implement a solution that is a technical exact-match with potentially high future maintenance problems, or a poor technical solution with definite low-level ongoing maintenance problems.

At the moment, I have no idea. Input welcome.


8 thoughts on “Pushing the Technical Frontier

  1. I’m glad you brought this up, for a couple of reasons.

    First, I also struggle with this issue on any development that I partake in. It is hard to find the right balance because you don’t know what the future will hold. I believe you have to consider that some of the concerns you have about possibilities in the future (requirements, maintainability, competent personnel) at this point in time are all unrealized, and some of them are completely outside of your control.

    The issue that would concern me the most, because it’s within my control, is maintainability. The fear of high maintainability is a tough one to overcome but like I mentioned it is unrealized at this point. Chances are that once you become more comfortable with the new technology you’ll quickly realize what the limitations and road blocks are. This would allow you to, hopefully, go back and re-factor any code to overcome the challenges.

    Competent personnel is a concern that is with your company and anyone involved in long term planning and not a developer level issue in my opinion. If you understand a technology and it matches the business need, I think it is probably worth serious consideration for implementation.

    Requirements are subject to change all the time and the only thing you can do is try and do the best you can with what you know today and hope the customers don’t have a major shift in the future. If that is the case then they’ll have to consider the cost, and chances are no matter what implementation you choose something is going to have to change.

    The second reason I’m glad you brought this up is that I will be working on a system in a near future that needs to implement a business process / work flow. Unfortunately Oracle removed the Workspace component from 11g and integrated it with another, paid, product (BPEL I think?). I thought about using Rules Manager / Expression Filter to build a work flow module but I haven’t had time to sink my teeth into it. I’d be interested to read a blog series on these features and practical implementations.

    • The licensing issue must be frustrating. I wonder how often these things happen? There ought tobe a case for getting a steep discount in your case.

      I might write-up the implementation of the expression filter code. It probably ought to be more widely appreciated and I wish I’d known of it a few years ago for a complex subscriber categorisation exercise (Heitor knows what I mean) and for generating marketing cohorts.

      The Rules Manager is a definite step up from what I need but I’ll probably look into what it might offer.

      Thanks for the thoughts.

  2. I would use expression filters…

    Have you tested the performance for this? I’ve read that it is a little slow in some situations.

    See you,


    • There’s definite doubts. Evaluating the SQL for every row has to be a prerformance hit, and the generated functions are not expicitly tagged as parallel enable.

  3. I would tend to favour using an Oracle-supplied feature rather than building a complex custom-built feature. Just because Expression Filters might be poorly understood generally, there is at least Oracle documentation of the feature, so a future maintainer can look it up.

    It’s the same reason I try to use (good) new features like named parameters, ANSI joins, global temporary tables, etc. even at places where they’re not accustomed to them; dragging them kicking and screaming into the 20th century as it were :)

    • I was right with you until I read Centinul’s experience of using an Oracle-supplied feature that then becomes an added-cost option. Oh well, I’d do it anyway.

  4. David,

    Like Centinul, I am quite glad that you have raised this “issue”. The problem is, like most of the things in Oracle, I am afraid the answer is “it depends”. MODEL clause comes to mind immediately here. I am not yet able to decide whether it is a good alternative to procedural approach (Rob has discussed something similar in Choosing between sql and plsql and Plan schedule with sql modelclause)
    Nowadays, most of the times, I do not tend to think much about whether my collegues would be able to learn the new feature and maintain it properly. I rather try to push them to learn. I firmly believe as an IT professional, one should never stop learning.
    With Oracle, my biggest dilemma is whether the cost-based optimizer has been enhanced to deal with the usage of new features implementation. I don’t want to introduce a “cool and shiny” new feature and then deal with “My Oracle Support” in order to make sure it performs well.
    Your test case for DIY workflow solution was wonderful. I wish I could get opportunity to study your complete solution (and once again, shame on oracle for removing a fantastic product like workflow in favour of BPEL :( )

    • Yes the CBO concern is a real one for sure. I’d guess that “Evaluate(…)=1” acts as a filter applied after a cartesian product — does the CBO get the cardinality estimate right? OPT_ESTIMATE might help but I don’t know at the moment. Every row in EVENTS (see post) relates to 1 .. n rows in filter_definitions. In the case of my data that’s between 0 and 3-ish, but in other applications it could be any conceivable range.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s