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:

select
  e.event_id,
  f.filter_name
from
  events e,
  filter_definitions f
where
  Evaluate(
    f.filter_condition,
    get_filters.getVarChar(
      e.dev_type,
      e.is_core,
      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.

Data Warehouse Architect Position Available — Dayton OH

As I was mentioning earlier, I am leaving my current client and moving on to new pastures. Apparantly I have been with them fulltime for five years, though when I started it was just for a quick two weeks to get them up to speed on some issues — time flies, it seems.

Here are the highlights of the requirements for my replacement:

  • In-depth knowledge and prior experience of Oracle Data Warehousing concepts and features — partitioning, parallelism, materialized views and query rewrite, bitmap indexing, data segment compression.
  • Strong SQL and PL/SQL development and tuning experience.
  • 9i and 10g experience.
  • Instance tuning for data warehouse environments.
  • Knowledge of ETL tools (Informatica preferred).
  • Knowledge of BI tools (Business Objects v6 and/or XI preferred).
  • Prior experience of dimensional and 3NF modeling.
  • Experience in supply chain management & accounting & finance preferred.
  • Incumbent will be responsible for full life-cycle development, from requirement analysis through schema design and strong input into ETL and BI configuration.
  • Must be U.S. citizen and be able to obtain a Department of Defense Security clearance.

Here is a link to apply for the job or to get more details, or you can email me directly at daaguard-jobs@yahoo.com.

Data Warehouse Architecture Decisions for Ad hoc and Predefined Reports

First, a disclaimer: the techniques herein represent “blue-sky” thought on an abstract problem. I have not implemented these ideas, although I am very willing to do so given the chance and if nobody can postulate a fatal objection to them. Or a near-fatal one. If you disagree with any of these ideas then you are not contradicting my experience and I’m not going to get all defensive over the matter.

And so to the substance … Continue reading

Port Conflict Between Informatica 7 and Business Objects XI

When my Informatica v7.1.3 Repository Service refused to start this morning, I instantly thought of my recent install of Business Objects XI, with it’s myriad of services and required firewall rules. As the Windows Event Viewer revealed, the service had refused to start because of an “(356|1840) Error in opening Server Port [5001] to listen for client connections“.

AVG 7.1 to the rescue — a scan through the firewall log showed:

Date and time:      2006-08-07 11:51:22
Action:             Ask
Application:        D:\Program Files\Business Objects\Tomcat\bin\tomcat5.exe
Protocol:           TCP
Direction:          In
Local address:      localhost:5001
Remote address:     0.0.0.0:0

There we go, Tomcat is using 5001.

Having not the foggiest notion of how to change Tomcat to a different port, nor what else might be required to prevent a meltdown of the entire Business Objects installation, I used the more familiar Informatica Repository Server Setup to change that component to port 5002, and he service then started right up.

Follow that with a reconfiguration of the Informatica Server service to look for the Repository service on the new port, and a similar process for the client tools through the PoweCenter Repository Manager, and I’m up and running again.

And you thought I was just some sort of Oracle geek … sheesh!

Business Objects XI Install

I just finished an install process so lengthy that I have forgotten why I started it in the first place — to whit, Business Objects XI Release 2 with Performance Management. I’d guess that properly undertaken the install would be a job of around three-to-four hours, but since I went camping for the weekend half way through, it actually took me around seventy.

I have to say that in terms of complexity this is one behemoth of an application. By my count it has installed twenty-one windows services and required the creation of around thirty-three new firewall rules. It left four warnings and fifty-nine errors in the Windows Application Event Log, most of which I’m afraid to even view. Those figures may be inaccurate as I lost the will to live several times during the counting, and doubtless when I actually start to use the applications there will be another host of firewall rules to create.

I encountered some interesting errors — “Product: BusinessObjects Enterprise XI Release 2 — Error 1904. Module D:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\importent6.dll failed to register. HRESULT -1073741819. Contact your support personnel.“. Huh.

I recall that “back in the day” I changed employer on the basis of my promised ability to install Business Objects and get the first usable reports out of an OLTP system before lunchtime on my first day — a task that an Oracle consultant stated to be beyond the wit of man even with a generous allowance of one month. That turned out to be a bold claim that delayed my lunch until 12:30, but mostly through having to sign HR forms until around 10am. That was also with Business Objects v3, as I recall — in any rate it was a product that installed a darned sight more quickly and simply than this latest montrosity, and without more than a single Windows service.

It seems that nothing gets more simple in the world of technology, and that pretty soon Business Objects may have to follow Oracle’s lead and create an “Express Edition” that can be installed with less than a hundred or so mouse clicks. Or maybe Oracle will just buy them up and make ‘em do it.

At any rate, I’m beginning to think that I ought to make it a practice to read the documentation on new products before tackling them, so that I can judge how much of my personal information I am going to forget in the process of remembering the new stuff . In fact prior to that it seems that one could just weigh the documentation and base one’s decision on that.