Record Linkage

The big switch

2012-01-21T12:59:00.000-08:00

Well I am in the process of switching over to Mac. I know I know, but after the iPod, the iPad, and then seeing the new Mac Book Air, I couldn't resist. So I've started coding using the Xcode environment and getting used to the differences. Lets see how far this goes...

Scientology Video Channel

2008-03-16T12:03:00.000-07:00

Check out the new Scientology Video Channel at www.scientology.org. This new site has 82 videos, nearly three hours of audiovisual presentations on Scientology. It answers any questions one might have on What is Scientology, What are the beliefs of Scientology and what it is doing around the world.

ISAL Rates and Country Listing

2008-01-06T18:03:00.000-08:00

I always have a hard time finding this when searching the USPS website. So here is a link to the ISAL rates and country listing:

293 International Surface Air Lift (ISAL) Service

Record Linkage Resources

2007-12-23T22:09:00.000-08:00

Download whitepaper on "Approximate Duplicate Detection" which opens with the following:

Detecting database records that are approximate duplicates, but not exact duplicates, is an important task. Databases may contain duplicate records concerning the same real-world entity because of data entry errors, unstandardized abbreviations, or differences in the detailed schemas of records from multiple
databases – such as what happens in data warehousing where records from multiple data sources are integrated into a single source of information – among other reasons. In this paper we review a system to detect approximate duplicate records in a database and provide properties that a pair-wise record matching algorithm must have in order to have a successful duplicate detection system.

Download paper on probabilistic record linkage and an automated procedure to minimize the undecided-matched pair problem. Here is an introduction to this paper:

Probabilistic record linkage allows the assembling of information from different data sources. In this article, we present a procedure in case a one-to-one relationship between records in different files is expected but is not found only by applying the probabilistic record linkage methodology. Our data were births and infant deaths from the 1998-birth cohort whose mother’s place of residence was the City of São Paulo at the time of birth. Our assumption was that pairs for which a one-to-one relationship was obtained, and a best-link was found with the highest combined weight would be considered as uniquevocally matched pairs or gold-standard and should then provide information in order to decide about pairs in which such a relationship could not be established. For example, we observed that the for the unequivocally matched pairs a clear and expected relationship between differences in dates of death and birth registration could be assessed. As a result, such a relationship was used to help solving the remaining pairs for which a one-to-one relationship could not be found. Indeed, we reduced the number of non-uniquely matched records and even though we could not establish a one-to-one relationship for every single death we reduced the number of uncertain. We suggest that future research using record linkage should use combined strategies from results from first record linkage runs before a full clerical review (the standard procedure under uncertainty) in order to most efficiently (and less costly), retrieve record matches.

Applications of Record Linkage Techniques

Here is an excerpt:

Record linkage is simply the bringing together of information from two or more records that is believed to relate to the same entity, e.g., the same individual, the same family or the same business. This may entail the linking of records within a single computer file to identify duplicate records. Alternatively, record linkage may entail the linking of records across two or more files. The challenge lies in bringing together the records for the same individual entities. Such a linkage is known as an exact match. The task is easiest when 1) the files have nearly unique identification numbers (e.g., social security numbers), 2) information is recorded in standardized formats and 3) the files are small. In the absence of nearly unique identifiers, names, addresses, dates-of-birth or other indirect identifiers are frequently used in the matching process.

record linkage description

2007-12-23T21:41:00.000-08:00

Here is a good description of "Record Linkage" from the University of Manitoba website:

Record linkage is simply the integration of information from two independent sources. Records from the two sources that are believed to relate to the same individual are matched in such a way that they may then be treated as a single record for that individual. Records brought together in this way are said to be linked. The principles of record linkage may be applied to any field in which it is necessary to bring together information recorded about persons in different places or at different times.

What is the Perfect Computer?

2007-12-20T11:42:00.000-08:00

As a computer programmer, I was intrigued by the opening chapters of Dianetics, the Modern Science of Mental Health, by L. Ron Hubbard claiming that man how the "Analytical Mind" is in fact a perfect computer. It makes calculations based on the data it has. It is ALWAYS RIGHT.

But like a computer program with unfortunate bugs, the calculation may come out with a wrong conclusion, but based on data it has, the program is still "right".

There are two parts to one's mind. The Analytical and the Reactive Mind. Dianetics says that the Analytical Mind IS the Perfect Computer.

"What would you want in a computing machine? The action of the analytical mind—or analyzer—is everything anyone could want from the best computer available. It can and does do all the tricks of a computer. And over and above that, it directs the building of computers. And it is as thoroughly right as any computer ever was. The analytical mind is not just a good computer, it is a perfect computer. It never makes a mistake. It cannot err in any way so long as a human being is reasonably intact (unless something has carried away a piece of his mental equipment).
"The analytical mind is incapable of error, and it is so certain that it is incapable of error that it works out everything on the basis that it cannot make an error. If a person says, “I cannot add,” he either means that he has never been taught to add or that he has an aberration about adding. It does not mean that there is anything wrong with the analytical mind."
- L. Ron Hubbard, Dianetics, The Modern Science of Mental Health

If this were true, how is it possible that people make mistakes, wrong decisions and don't live "perfect" lives? Or more to the point, how does one prevent writing buggy computer software? The answer lies in Dianetics and specifically in the chapter on the Reactive Mind. Dianetics should be required reading for any software developer. It would not only result in less buggy software being developed, it would make for a world of less stressed out programmers!

Anyone interested in Dianetics can get a copy of this book here.

Format your Code for Blogger

2007-10-30T17:18:00.000-07:00

Finally, I found a solution of how to easily format my Source Code examples for Blogger. I've seen many examples of how other blog services handle this, such as Wordpress, but I don't use Wordpress...

Here is how to easily do this, until Blogger comes up with an easier way:

Copy your source code example and paste it into this page:

Source Code Formatter for Blogger

Click on the "Format Code" button and copy the results.

Switch to "Edit Html" view in your Blogger Edit Post page and paste in the formatted code example.

Here is what a sample PL/SQL example would looks like:

BEGIN
   FOR cur IN (select id from test)
   LOOP
      UPDATE table SET column = "DONE" WHERE id = cur.id where;
      COMMIT;
   END LOOP;
END:
/

Thanks to Greg Houston for this simple solution.

How to Use JList - for Dummies

2007-10-26T10:44:00.000-07:00

I've built a few Java programs and am still not proficient with my tools, so I am laying out a series of simple instructions for learning and using the basic Java Swing tools. This will reference the websites where I go for direction as well as examples that I've found useful.

The first place I always start is the Java tutorials on using Swing:

How to Use Lists

I ran into a situation where I had to remove multiple elements from a JList. Here is the exact trick on how to do that. It worked perfectly:

Remove Multiple Items from a List

Percentage of Americans who do not fill out a Change of Address with USPS

2007-10-25T12:12:00.000-07:00

Here is an excerpt giving an estimate of how many Americans move each year that do not fill out a change of address request with the post office. These address changes would not be returned with NCOA and would require other means of address recovery such as a "Deluxe" NCOA service that utilizes other address change databases such as magazine subscription lists, utility company databases and even the "Pizza Hut" database they maintain of deliveries they've made and address changes they've maintained.

One of the fallacies surrounding consumer list compilation is that the U. S. Postal Service’s National Change of Address file is the answer to this problem. The fact is, however, that NCOA, while helpful, falls far short of the accuracy that most of us would prefer. An estimated 30 percent of the people who move in the United States never file a change-of-address form with the postal service. And even when they do, it is sometimes weeks or even months before that information is made available to list compilers or the direct marketers who use the lists.

Competent list managers have created routines that address this problem. For example, if there is a change of address on a current, reliable source (such as new billing addresses for a charge account or new utility hookups) the new address might be placed in the consumer record. This type of effort can significantly improve address quality on a mailing list – but it cannot ever be perfect.

Jlist or Jtable - I need a multi-column display

2007-10-24T16:37:00.000-07:00

I'm gradually coming to grips with programming user interfaces in Java. Read a bit on the MVC (Model View Control) concept and while I see its value, it is a bit too steep a gradient at this point. I am starting off getting the basics of the Swing GUI and have been using Jgoodies with the Jigloo Windows builder. Even that is a bit steep as I want to know how to build these from the ground up and using the drag and drop tools resulted in skipping some of the basics that left me hanging, when trying to modify and customize what Jigloo provide.d

What has helped learn this the fastest has been to work out some simple application that will be useful and then laying out a UML diagram including Use Case for the requirements, Activity Diagrams and Class Diagrams. A programmer is lost in the woods without these design tools. Then I've taken one element at a time, the JDBC database access, pulling up a list and displaying this, etc.

I am up to a point where I need to display a simple list of entries from a database. The list should have 3 columns. I've only been able to get this to work with one column so far. So what I am looking into is whether it would be better to just use the JTable or figure out a "multi-column" JList.

Here are some resources to figure this out:

Code Guru
Java Sun
Dream In Code
Jlist Tutorial

One simple method of doing this would be to select your multi-column data from your data source and return this resultset to a String array.

Refresh a JTable from a JDBC connection

2007-10-24T16:34:00.000-07:00

I have a Swing application that uses a JTable and JScrollPane to display data from an Access database via a JDBC connection.

Even though I had properly coded the SQL INSERT statement and the data was loading into my database, the JTable was not refreshing with the new record. I tried the .revalidate() and the .repaint() methods to no avail.

Finally, after reading Ben Hendry's Mastering JTable I was able to sort this out.

In my JButton ActionListener I added the TableModel.addRow method and inserted the newly inserted records into the JTable as well. Followed by a .revalidate() and the data properly displayed without even a flicker in my application.

Moral of the story - always use Model-View-Control (MVC) architecture and separate your view from your data and controls.

Switzerland Language Assignment by Postal Code

2007-08-04T17:59:00.000-07:00

Map of Switzerland showing language areas.

Language assignment by postal code:

Postal code	Area	Language
1000	Lausanne	French
2000	Neuchatel	French and German
3000	Bern	French and German
4000	Basel	German
5000	Aarau	German
6000	Luzern	German
6500..6999	Ticino	Italian
7000	Chur	German
8000	Zurich	German
9000	St. Gallen	German

Language Assignment By Postal Code

2007-07-26T20:23:00.000-07:00

Every so often I run across a need to assign languages by postal code. Currently I need to assign languages by the Belgium postal codes. The following table shows the prominent language for each postal code of Belgium:

Capital	Language	Postal codes
Antwerp	DUTCH	2000-2999
Brussels	BOTH	1000-1299
Ghent	DUTCH	9000-9999
Leuven	DUTCH	1500-1999, 3000-3499
Mons	FRENCH	6000-6599, 7000-7999
Liege	FRENCH	4000-4999
Hasselt	DUTCH	3500-3999
Arlon	FRENCH	6600-6900
Namur	FRENCH	5000-5999
Wavre	FRENCH	1300-1499
Brugge	DUTCH	8000-8999

Here is a map showing the language breakdown of Belgium:

Here is a language assignment by postal code for Switzerland.

Salutations Used in Direct Mail

2007-07-23T23:30:00.000-07:00

I am working on a direct mail project that requires personalized letters addressed to all manner of people from around the world - from members of Congress to Doctors, lawyers, members of parliament, etc. and these letters are being translated into multiple languages. I am searching for an already existing table of translated titles of address or salutations.

The following pages include fairly complete lists of salutations but I am hunting around for more complete and then translated tables like this. If anyone out there knows of such a table, please let me know.

Table with Forms of Address
Official Forms of Address

Char_Instr - Another Matching Algorithm

2007-06-17T00:06:00.001-07:00

I've using Soundex, Double Metaphone, Levenshtein Distance and other string matching algorithms, but somethings it is the simple things that work best.

Here is a function I wrote in PL/SQL utilizing the INSTR() function to traverse through one string, checking each character as to whether or not it exists in the another string.

This has been useful in checking for duplicates in mailing lists as well as identifying potential duplicates that should be reviewed. The function returns the number of characters from the second string that are the same as the first string.

I've found the use of the Soundex to be too loose and the Levenshtein Distance algorithm to be lacking in its handling of transposed characters. Use of this function will give you a count of how many chars are the same regardless of position in the string.

/*==========================================================
Function: char_instr (str1, str2)

Purpose: To check how many characters from one string
exist in another string. The return value is the number
of characters that exist in the in both strings.
==========================================================*/

FUNCTION char_instr
(
str1 IN VARCHAR2,
str2 IN VARCHAR2
)
RETURN NUMBER
AS
v_exists NUMBER := 0;
v_occurs NUMBER := 0;
v_length NUMBER;
BEGIN
/*==========================================================
Get the length of the str2
==========================================================*/
v_length := LENGTH(str2);
/*==========================================================
Loop through str2 and check if char exists in str1
=========================================================*/
IF( v_length > 0 ) THEN
FOR x IN 1..v_length LOOP
v_exists := INSTR(UPPER(str1), SUBSTR(UPPER(str2), x, 1));
IF(v_exists>0) THEN
v_occurs := v_occurs + 1;
END IF;
END LOOP;
END IF;
RETURN v_occurs;
END char_instr;
*

SQL Cheat Sheet

2007-06-14T20:53:00.000-07:00

I've put together this page to give quick links to all the Oracle SQL tricks that I find myself continuing to search for on the net. This is a quick reference guide and will be expanded regularly.

Oracle SQL Cheat Sheet

SQL Introduction and Definitions

INSTR()

Create a Delimited Output in SQL Plus

How to Rename a Column in Oracle SQL

How to Delete a Column from a Table

Use of Variables in SQL Plus or PL/SQL

Format Query Results to Display Currency

Display Seconds Past Midnight

Select Only Top Results in a Query

Use of Wildcards in Oracle SQL

What is NVL and how does it work?

How to Use Decode in Oracle SQL

How to Round Off a Number in Oracle SQL

Debug JDBC Connectivity with Oracle Thin Client

Oracle String Matching Algorithm - CHAR_INSTR

If you have any other tips or suggested entries, let me know!

Nice Bike Tom!

2007-06-02T00:56:00.000-07:00

Check out these shots of Tom Cruise's new bike. I hear it has a Ducati engine and was custom built for him, sized to fit.

Standard Mail $.02-$.03 rate reduction proposed by PRC

2007-05-29T10:04:00.000-07:00

NOTE: THIS IS ONLY PROPOSED AT THIS POINT - NOT YET APPROVED

This morning I received the following pleasant news update from the DMA regarding a temporary rate reduction proposed on all Standard USPS mail:

The Postal Regulatory Commission May 25 offered interim rate relief for Standard Regular flats and catalog mailers in response to the U.S. Postal Service Governors’ request to reconsider the recommended rates in March.

The PRC’s decision grants a temporary rate reduction of 3 cents for all Standard Mail regular flats and 2 cents for Standard Regular nonprofit flats. The temporary transitional rate relief for Standard Regular flats mailers would end Sept. 29.

click to read full story...

They would not require any software change be made but simply a blanket 3 cents per Standard Mail Flat and 2 cents per Non-Profit Standard Mail Flat to be deducted from the overall postage statement.

Mailers concerned should contact their USPS postal reps and support this PRC recommendation.

How to Round Up in Oracle SQL

2007-05-24T10:53:00.000-07:00

Recently I needed to provide a report that had numbers rounded to the nearest thousand. I found the ROUND command but this appeared to be only rounding off decimal points to a specified number of spaces. The syntax for this is:

SELECT ROUND(number, decimals)
FROM table;

If you use 2 for the decimals your result would look like this:

SELECT ROUND(2.123,2) FROM DUAL;
Result: 2.12
SELECT ROUND(2.129,2) FROM DUAL;
Result: 2.13

However, I needed something that would round to the nearest thousandth. So I tried a negative number for the decimals like this and found it did exactly what I wanted:

SELECT ROUND(10234,-3) FROM DUAL;
Result: 10000
SELECT ROUND(10567,-3) FROM DUAL;
Result: 11000

While this is a basic Oracle SQL function, it is not clear that you can do this, hence this post.

USPS Rate Change - What do you think?

2007-05-17T10:08:00.000-07:00

May 14th, 2006 will go down in history as the most confusing day in mailers history. Unfortunately many people did not install the new presorting software on development machines and get them fully tested before the big date and tried to frantically switch over on May 14th. The tech support team at Firstlogic was overwhelmed with calls - specifically in their Presort support dept. Complaints regarding the post office rejecting their mail, confusing new requirements and general upset due to the increase in postage as a result of the new rate case. Mailers are experiencing anywhere upwards of $.10 per piece increase - the highest increase in standard mail that I can remember.

I saw a post on the DM News website that the USPS claimed the new rate case implementation was going smooth - however it is quite different on the ground in speaking to mailers and software support technicians.

Per the below excerpt from DM News page, it looks as though the Postal Regulation Committee is considering reverting the Standard Flat Mail rate change due to the "rate shock" occurring:

The Governors of the USPS asked that the rates for this category of mail be reconsidered because the price increases recommended by the PRC may impose an unnecessary degree of “rate shock” on the catalog industry and small businesses particularly. The recommended increase for some catalog mailers is as much as 40 percent, which is more than double what the Postal Service had proposed.

Here is a poll to find out your opinion of the new rate case. Please vote and let me know your comments on this.

&amp;amp;amp;amp;amp;amp;lt;a href="http://polls.blogflux.com/poll-11584.html"&amp;amp;amp;amp;gt;Take the poll&amp;amp;amp;lt;/a&amp;amp;amp;gt;&amp;amp;amp;lt;br /&amp;amp;amp;gt;&amp;amp;amp;lt;br /&amp;amp;amp;gt;&amp;amp;amp;lt;a href="http://polls.blogflux.com/"&amp;amp;amp;amp;gt;Free Poll by Blog Flux&amp;amp;amp;lt;/a&amp;amp;amp;gt;

Is anyone teaching us Morals anymore?

2007-04-24T11:56:00.000-07:00

Back in the day, religion had its place in society of instilling moral values in our youth. With the separation of Church and State and the resulting exclusion of any religious material from the classrooms, a void was unintentionally forged - or should I say a chasm.

In the last few years, we've heard more horrific stories of youth violence and their apparent lack of any moral sense. Look at the Columbine shootings or more recently the Virginia Tech rampage. Cho certainly had no sense of morals. Maybe this was the result of the psychiatric - mind-altering drugs he was on. He certainly would have been schooled in the generation where morals were stripped from our classrooms.

Who is teaching our children morals these days anyway?

There is a organization paving the way towards getting Moral values back into society. The Way to Happiness is an organization that promotes "Moral Values for a Modern World. This is a non-religious moral code that anyone can follow.

They have recently launched a campaign of Public Service Announcements that promote this moral code in terms anyone can understand.

Here is a link to a video where you can watch one of these PSAs promoting one of these precepts, Be Competent.

Why Global Data Integrity is Important

2007-04-03T22:27:00.000-07:00

Why Global Data Integrity is Important

Various Matching Algorithims

2007-03-24T13:37:00.000-07:00

Implement Phonetic ("Sounds-like") Name Searches with Double Metaphone Part VI: Other Methods & Additional Resources - The Code Project - String

I have only experimented with Levenshtein Distance and Soundex algorithms. This page gives a free download of the Double Metaphone algorithm implementations as well as several other samples that are very useful.

Oracle Thin Client JDBC

2007-03-19T16:41:00.000-07:00

I've had problems setting connections to an Oracle DB using the thin client JDBC. This post is to track how this was solved.

First you must have the oracle.jar file in your build path.

Next I used the following for the actual connection:

public static Connection getConnection() {

Connection con = null;
try{
Class.forName("oracle.jdbc.OracleDriver");
} catch (Exception e) {
System.out.println("Failed to load driver.");
return con;
}
try {
con = DriverManger.getConnection(
"jdbc:oracle:thin:username/pw@hostname:port:SID"
);
} catch (SQLException ee) {
ee.printStackTrace();
}
return con;
}

I also had imported java.sql.*;

The thing that was causing a problem with this before was that I did not have the host name, port or SID listed out. This was easily located in the tnsnames.ora file in the oracle installation folder.

Kelly Preston, Kirstie Alley and Priscilla Presley at CCHR Awards Banquet

2007-03-14T17:59:00.000-07:00

Citizens Commission on Human Rights acknowledged leaders in the field of human rights last month and awards were presented by Kelly Preston (John Travolta's wife), Priscilla Presley (Elvis Presley's wife) along with Kirstie Alley and Marisol Nichols.

Celebrities present human rights awards to individuals who risked their careers to warn the public about psychiatric drug risks, and urge parents to get informed.

LOS ANGELES: On Saturday, February 17th, at the annual awards banquet of the psychiatric watchdog Citizens Commission on Human Rights (CCHR), actors Kirstie Alley, Kelly Preston, Priscilla Presley, Marisol Nichols (24) and Anne Archer (Patriot Games, Fatal Attraction) awarded mental health industry whistleblowers who have risked their professional careers to warn the public about the dangers and fraudulent marketing of psychiatric drugs. Exemplifying the effort to inform parents and consumers, former pharmaceutical sales representative Gwen Olsen, and former Pennsylvania government investigator Allen Jones, were presented with CCHR’s annual Human Rights Awards for their courageous contributions to mental health reform.

Like Russell Crowe’s character in the film The Insider, Allen Jones reluctantly stepped forward as a whistleblower. Kirstie Alley presented Jones with his Human Rights Award for exposing several pharmaceutical companies who bribed Texas government officials to implement mental health treatment laws requiring the use of their brand name drugs.

Kelly Preston, Kirstie Alley and Priscilla Presley Colin and Marisol Friedberg with Anne Archer

(Click to enlarge)

Recently, a four-year-old Massachusetts girl died from a deadly cocktail of psychiatric drugs, calling into question the psychiatric practice of drugging small children with powerful anti-psychotic drugs. Last week, the story ran on the front page of The New York Times and was featured on Fox National News, drawing national attention to the culpability of the prescribing psychiatrist, and the growing controversy over psychiatrists drugging children and toddlers with powerful drugs.

Magnetic Card Reader Parsing of Track Data

2007-03-14T15:37:00.000-07:00

I've been working on developing an application that will read a driver's license and parse the information and populate a data entry screen. This is useful for rapidly collecting name and address information when you have drivers licenses available.

To work out the exact format of drivers license magnetic strips, the following links were very helpful:

Drivers license format
Magnetic Strip Encoding Standards
Mag Stripe Cards

Java Magnetic Card Parser
Exeeba AAMVA Drivers License Format
CA DL Format

Many states do not conform to the above standards but generally these will work.

Java Case or Switch Example

2007-02-28T11:46:00.000-08:00

How do you use the CASE function in Java?

The Switch or Case function in Java is useful for branching off to different actions based on a conditional expression. For example if you have a series of categories that can be selected and depending on which category was selected, you want the program to do a different action, the Case function is the best way to do this.

Here is the syntax

    switch( expression ) {
       case value1:
           statement1;
           break;
       case value2:
           statement2;
           break;
       case value3:
           statement3;
           break;
       ...
       case valuen:
           statement n;
           break;
       default:
           statements;
   }

A real example of this from the Sun website:

int month = 8;
switch (month) {
case 1: System.out.println("January"); break;
case 2: System.out.println("February"); break;
case 3: System.out.println("March"); break;
case 4: System.out.println("April"); break;
case 5: System.out.println("May"); break;
case 6: System.out.println("June"); break;
case 7: System.out.println("July"); break;
case 8: System.out.println("August"); break;
case 9: System.out.println("September"); break;
case 10: System.out.println("October"); break;
case 11: System.out.println("November"); break;
case 12: System.out.println("December"); break;
default: System.out.println("Invalid month.");break;
}

String Parsing in Java

2007-02-27T19:20:00.000-08:00

In Awk you can easily find the position of a delimiter and then take all characters up to that point by using the index() and substr() functions. But how does that work in Java? Is there an easy way to parse a String and carry out these type of routine actions?

Here you go:

subtring method:

int start = 1;
int end = 4;
String substr = "aString".substring(start, end); // Str

index method:

String string = "madam, i am Adam";
// First occurrence of a c
int index = string.indexOf('a'); // 1

Here is a live example I am working with where I need to parse the String into two variables as my webpage is passing me a parameter that is pipe-delimited:

String str = "YES|12345";

int pipe = str.indexOf('|'); //4

String firstPart = str.substring(1,pipe-1);
String lastPart = str.substring(pipe+1);

Oracle SQL DECODE

2007-02-27T14:40:00.000-08:00

The DECODE function in Oracle SQL is used for if-then-else statements. A very handy tool for powerful queries.

The syntax for the DECODE function is as follows:

DECODE(value, if1, then1, if2, then2, if3, then3, etc., else)

Here is a good example of this from the devx.com website:

Consider the following example in which the Viewable column can have values of 1, 2, and 3:

SELECT FirstName, Viewable
FROM employee;

Results:

FIRSTNAME Viewable
===========================
John 1
Tim 2
Julie 2
Stacy 1
Rahul 3
Leena 4
Amy 1
Bill 3
Teri 3

Now, we can use Decode to display different things in a report based on the values in Viewable.

SELECT Firstname,
Decode(Viewable, 1,'VISIBLE', 2,'INVISIBLE',
3,'UNKNOWN', 'OTHER')
FROM employee;

Results:

FIRSTNAME Viewable
===========================
John VISIBLE
Tim INVISIBLE
Julie INVISIBLE
Stacy VISIBLE
Rahul UNKNOWN
Leena OTHER
Amy VISIBLE
Bill UNKNOWN
Teri UNKNOWN

Decode checks the column values and interprets the provided values in pairs. This is how it works:

Switch viewable:

Case 1:
Result = VISIBLE
Case 2:
Result = INVISIBLE
Case 3:
Result = UNKNOWN
Default:
Result = OTHER
End Case

OR
====================

If Viewable = 1 Then
Result = VISIBLE
Elsif Viewable = 2 Then
Result = INVISIBLE
Elsif Viewable = 3 Then
Result = UNKNOWN
Else
Result = OTHER
End If

One can use Decode in Updates as well. For example, instead of writing 50 different updates to change state names, we can do it in one Update using Decode as follows:

Update employee
set homestate = decode(homestate,
'AL', 'Alabama',
'AK', 'Alaska',
'AS', 'American Samoa',
'AZ', 'Arizona',
. . . . . . . .
. . . . . . . .
'WA', 'Washington',
'WV', 'West Virginia',
'WI', 'Wisconsin',
'WY', 'Wyoming' , homestate)
where homecountry = 'UNITED STATES'

This will replace state abbreviations with state names. And it leaves the abbreviations alone in case it doesn't match any of the values, such as AL, AK, etc.

Most Oracle SQL users do not realize the power of this function. Hopefully this post will enlighten some on it.

Scientology Edinburgh

2007-02-25T17:55:00.000-08:00

A new website was just put up for the Church of Scientology in Scotland. The Volunteer Ministers are really needed there right now with the recent train wreck.

Google Directory - Computers > Data Formats > Markup Languages > HTML > Tutorials

2007-02-20T10:57:00.000-08:00

I figured its about time I learned HTML. Here is a great starting point with a plethora of tutorials, examples, etc.

Google Directory - Computers > Data Formats > Markup Languages > HTML > Tutorials

Submit Links Directory

Moving Data around HTML pages with JSP

2007-02-14T20:34:00.000-08:00

Ok, I admit I never programmed a web site before. But I've recently realized the potential in using an internal website to handle administrative tasks such as tracking jobs, etc. Previously I tried doing such with a Swing based approach as this is what I was familiar with, but after consulting with a fellow programmer I chose to set up a webpage with hibernate on top of a MySql db. So far so good, however, I am looking for a crash course in HTML and JSP to get me going.

An excellent guide for this is on IBMs site under JSP Best practices. I am going through this and hopefully it will answer all my questions...

Event Viewer not showing up

2007-02-14T19:57:00.000-08:00

What to do if the alerts in your Event Viewer do not show up? Try the refresh button in the Event Viewer. Usually this means there are too many events logged and your Event Viewer is jammed.

This happened to me after installing some new software that bunged up my machine.
Events were being logged like every minute or so and it shortly was overstuffed. I searched the net and found this suggestion in a TechRepublic forum and it worked. After refreshing I could see the events, delete the log if needed and carry on.

Java isDate Date Checker

2007-02-09T10:46:00.000-08:00

Here is an earlier post, showing what what proposed as one solution to the Java isDate() problem:

Below is a method that simulates the Visual Basic isDate() method and is configurable so you can set what you consider are the valid date formats.
I modifed this and added the "Jan|Feb|Mar|Apr..." and "JAN|FEB|MAR|APR|..." to the regex so that the method now validates the formats I needed to check.
I am now implementing this in my application.

See this code in my earlier blog post.

However, since that time, I worked out the below which has worked wonders and I am posting it here for any others who may need such a simple tool as to convert a text string to a Date object or check if a string is a valid Date, etc. Let me know if anyone found this useful:

First load the different type of date formats that you consider valid formats:

public static ArrayList loadFormats() {

ArrayList dateFormats = new ArrayList();
dateFormats.add(new SimpleDateFormat("yyyy-MM-dd"));
dateFormats.add(new SimpleDateFormat("yyyy MM dd"));
dateFormats.add(new SimpleDateFormat("yyyy.MM.dd"));
dateFormats.add(new SimpleDateFormat("yyyy-MMM-dd"));
dateFormats.add(new SimpleDateFormat("yyyy MMM dd"));
dateFormats.add(new SimpleDateFormat("yyyy.MMM.dd"));
dateFormats.add(new SimpleDateFormat("dd-MM-yyyy"));
dateFormats.add(new SimpleDateFormat("dd MM yyyy"));
dateFormats.add(new SimpleDateFormat("dd.MM.yyyy"));
dateFormats.add(new SimpleDateFormat("dd/MM/yyyy"));
dateFormats.add(new SimpleDateFormat("dd-MM-yy"));
dateFormats.add(new SimpleDateFormat("dd MM yy"));
dateFormats.add(new SimpleDateFormat("dd.MM.yy"));
dateFormats.add(new SimpleDateFormat("dd/MM/yy"));
dateFormats.add(new SimpleDateFormat("dd-MMM-yy"));
dateFormats.add(new SimpleDateFormat("dd MMM yy"));
dateFormats.add(new SimpleDateFormat("dd.MMM.yy"));
dateFormats.add(new SimpleDateFormat("dd/MMM/yy"));
dateFormats.add(new SimpleDateFormat("dd-MMM-yyyy"));
dateFormats.add(new SimpleDateFormat("dd MMM yyyy"));
dateFormats.add(new SimpleDateFormat("dd.MMM.yyyy"));
dateFormats.add(new SimpleDateFormat("dd/MMM/yyyy"));
dateFormats.add(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("yyyy MM dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("yyyy.MM.dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("yyyy/MM/dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("yyyy-MMM-dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("yyyy MMM dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("yyyy.MMM.dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("yyyy/MMM/dd hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd-MM-yyyy hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd MM yyyy hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd.MM.yyyy hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd/MM/yyyy hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd-MMM-yyyy hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd MMM yyyy hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd.MMM.yyyy hh:mm:ss"));
dateFormats.add(new SimpleDateFormat("dd/MMM/yyyy hh:mm:ss"));

return dateFormats;
}

Next create your isDate method:

public static boolean isDate(String date) {

// Load formats
ArrayList dateFormats = loadFormats();

boolean validDate = false;
Date myDate = null;

Object[] myDateFormats = dateFormats.toArray();
for (int i = 0; i SimpleDateFormat myFormat = (SimpleDateFormat) myDateFormats[i];
try {
myFormat.setLenient(false);
myDate = myFormat.parse(date);
validDate = true;
break;
}
catch (Exception e) {
validDate = false;
}
}
return validDate;
}

Here is a converter from String to Date to ensures it is a valid format:

public static Date convertStringToDate(String date) {

// Load formats
ArrayList dateFormats = loadFormats();

boolean validDate = false;
Date myDate = null;

Object[] myDateFormats = dateFormats.toArray();
for (int i = 0; i < myformat =" (SimpleDateFormat)" mydate =" myFormat.parse(date);" validdate =" true;" validdate =" false;">

There you go!

Java isDate() Function Anyone?

2007-02-09T10:42:00.000-08:00

From my earlier "pre-category" blog, this post was written when deep into the lack of a Java isDate() function:

New to Java, but learning fast and definitely experiencing the "Paradigm Shift", however I find it odd that there is no isDate() function like there is in Visual Basic.

As mentioned in an earlier post, I ran into a Java Date Conversion issue where the date format that I was expecting and turned out in the live data tests, to be a different format. This was after building over 500 test cases that test each of the business rules programmed into a Drools Rules (now Jboss) processor.

So obviously, I want to have all possible date formats accounted for so the solution is to have a function that loops through each of the possible date formats and check if the string parses into a valid Date object using that particular SimpleDateFormat.

I know this can be done using try-catch blocks, but I want to create a more elegant approach calling my custom isDate() function.

Checking aroung Javalobby and other places to see if anyone has already solved this, or if I need to create from scratch.

Jigloo Eclipse Windows Builder?

2007-02-09T10:31:00.000-08:00

Building GUIs for rich client apps in Eclipse is not the easiest thing in the world. I'm looking for windows builders comparable to the Net Beans Matisse GUI builder.

I've downloaded the Jigloo GUI builder which sounds pretty good but that is coming from company who made it.

What about WindowsBuilderPro? Both Jigloo and WindowsBuilder are commercial products that offer free trial editions. I've generally heard that WindowsBuilderPro is the better product.

I'll try both and see for myself...

On the install, Jigloo is very simple and is just like any other Eclipse plugins. Just copy to the features and plugins directories and your done. WindowBuilder on the other hand has you walk through a install wizard which took only a couple minutes to run through. (Make sure you don't have Eclipse running during the install).

WindowsBuilder has several example apps you can try. In 5 minutes of testing this builder I found it very user friendly. I liked the "quick view" feature where without even compiling you can get a feel for how your app will look during runtime. Now on to Jigloo.

After the few minutes I found it easy to create a basic app. Nothing difficult. I liked the two screen (code/app) view that shows the two-way changes so if you edit the GUI the code changes or you edit the code and the GUI changes.

So far they are both comparable products and I'll spend a bit more time using each on an actual application to see which is the better product. ...

After spending several hours using Jigloo and writing over 1,000 lines of Java using GUI parts from Jigloo I've come to the conclusion that the best use of a GUI builder is for the initial creation of your form and widgets. The size, arrangement of widgets and placement of your GUI can be very much sped up using Jigloo.

However, once you understand what a GUI builder is doing and have your basic form created, continued use of any GUI builder is more annoying than anything. I'd recommend using one to get started, get your basic form created and then drop kick it for any later modifications or additions.

Star UML Design Tool

2007-02-09T10:18:00.000-08:00

Ok, I downloaded the Star UML Design tool. Looks like an open source UML tool. Sounds promising. I'll give it a review in a bit.

As far as basic modeling tools go, this is the most easy to work with, simple to understand and fastest startup time. I've used this to generate a myriad of Use Case Diagrams, Activity Diagrams and Sequence Diagrams. Not reviewed yet is Star UML's Code Generation capabilities which look pretty straightforward.

One thing a bit confusing to a UML/MDA beginner is the "New Project By Approach" window where you have to select which UML approach you want to use. This basically means how the different types of diagrams will be organized. You have the option of selecting the 4+1 View Model, the Rational Approach, the UML Components Approach or the Default Approach.

I recommend just using the Default Approach unless you are familiar other standard UML approaches.

Eclipse Visual Editor vs. Jigloo

2007-02-09T10:14:00.000-08:00

Ok, I've been using the Eclipse Visual Editor for all of 15 minutes and I am sold. Up until now, I've been using Cloudgarden's Jigloo free version, yet after getting constantly annoyed by Jigloo's constant modifications to my code, prevention of using builders and the incessant warnings about how you are using the free version and that this can not be used for commercial use, I had it.

It is much easier to build with the Model View Control (MVC) architecture using the VE tool as opposed to the Jigloo builder.

Here are some comparisons and reviews of these GUI builder tools:

Jigloo Review
Comparison of Java GUI Builders
Comparison with Matisse
IBM Giving Away VE Source Code to Eclipse

Refresh a JTable from a JDBC connection

2007-02-09T10:13:00.000-08:00

Tom Cruise, Baby Suri, Photos Released

2007-02-09T10:10:00.000-08:00

As you can see, Suri is absolutely a beautiful child. Vanity Fair has a major feature on Tom Cruise, Katie Holmes and Suri Cruise. This article dispels the rumors that have been generated by the media over the last several months.

Marilyn Monroe - Death By Psychiatry?

2007-02-09T10:04:00.000-08:00

On the CCHR website there is a section devoted to artists. As I am an artist this was struck home to me. Artists and successful people are often found surrounded by "professionals" in the field of mental health.

I've heard many stories about what happened to Marilyn Monroe but this, from CCHR's website makes the most sense. Here is an excerpt:

"In 1960, Monroe saw psychiatrist Ralph Greenson, whose control over her was swift, severing all her close relationships. By 1962, she realized—too late—that she must "disconnect from Greenson." After spending six hours with him, she was found dead of a drug overdose. In the seven years prior to psychiatry’s influence, Monroe had made 23 movies. In the seven years of her psychiatric "care," she only made six films."

Check out the Citizen's Commission on Human Rights for more information on other famous artists whose lives have been destroyed by Psychiatry in the name of help.

Probabilistic Matching

2007-02-07T09:50:00.000-08:00

Probabilistic matching uses likelihood ratio theory to assign comparison outcomes to the correct, or more likely decision.

Probabilistic matching enables one to use match scores and percentages on a field by field comparison to determine a match. There are three categories output from probabilistic matching and are set by the user based on the overall probability percentage:

1) Match - that can be automatically merged
2) Candidate Match - requiring manual review
3) Non Match

Artificial Intelligence and Machine Learning utilize probabilistic matching.

Service Oriented Architecture (SOA)

2007-02-06T11:06:00.000-08:00

A service-oriented architecture is a collection of services that communicate with each other. The services are self-contained and do not depend on the context or state of the other service. They work within a distributed systems architecture.

Record Linkage

2007-02-06T10:55:00.000-08:00

Record linkage is the act of detecting and linking duplicate records within a database. It is also called match/merge, deduplication, match/consolidate, etc.

Some record linkage software tools include:
Febrl (Freely Extensible Biomedical Record Linkage) - Open Source
TAILOR
TheLinkKing
MatchIT - Commercial
Business Objects - Commercial
Group1 - Commercial

Fuzzy Matching Logic

2007-02-06T10:54:00.000-08:00

A method of matching where algorithms are used to compare character strings and detect recurring patterns within these strings. Common algorithms are Soundex, Levenshtein Distance and Double Metaphone which find similarities based on patterns and similarities. It is called a "fuzzy match" because the match is not exact, like if you were looking at the two records out of focus, they would look the same, but would be a bit fuzzy.

Enterprise Resource Planning (ERP)

2007-02-06T10:53:00.002-08:00

Software that integrates departments and functions across a company into one computer system. ERP runs off a single database, enabling various departments to share information and communicate with each other. It helps a manufacturer or other business manage the important parts of its business, including product planning, parts purchasing, maintaining inventories, interacting with suppliers, providing customer service, and tracking orders.

Customer Data Integration (CDI)

2007-02-06T10:53:00.001-08:00

Customer Data Integration (CDI) is the consolidation and management of customer data from all available sources. This customer data typically includes contact details, value data, and other information collected across multiple channels and through various interactions. The purpose of CDI is to keep source data systems and the central database in synch with each other to provide an accurate central view of each customer

Customer Relationship Management (CRM)

2007-02-06T10:51:00.000-08:00

The functions and programs a company uses to connect with its customers - typically divided into Operational CRM (call centers, sales force automation, supply chain management) and Analytic CRM (customer analysis, database marketing). It allows an individual approach to marketing to and servicing customers.

CDI - Glossary of Terms

2007-02-04T20:26:00.000-08:00

As I've mentioned before, a primary barrier to studying any subject is the misunderstood word. Per Hubbard's study technology site:

"To clear a word, one looks it up in a good dictionary.

"The first step is to look rapidly over the definitions to find the one which applies to the context in which the word was misunderstood. One reads the definition and uses it in sentences until one has a clear concept of that meaning of the word. This could require ten or more sentences.

"Then one clears each of the other definitions of that word, using each in sentences until one has a conceptual understanding of each definition.

"The next thing to do is to clear the derivation – which is the explanation of where the word came from originally. This will help gain a basic understanding of the word." ...
- L. Ron Hubbard

Therefore I've started putting together simple to understand definitions for the most common terms in the field of Customer Data Integration.

Customer Relationship Management (CRM)

Customer Data Integration (CDI)

Enterprise Resource Planning (ERP)

Fuzzy Matching Logic

Probabilistic Matching Logic

Record Linkage
Service Oriented Architecture (SOA)

Record Linkage Graphical User Interfaces

2007-01-29T10:11:00.000-08:00

Manual handling of duplicates in a database can be quite time consuming. It is important to find the right tool to help you speed this process. Or if you are building your own record linkage tool, it is always good to see what is out there, how GUIs are laid out and what the common features are.

The Link King:

The Link King’s graphical user interface (GUI) makes record linkage and unduplication easy for beginning and advanced users. The data linking neophyte will appreciate the easy-to-follow instructions. The Link King's artificial intelligence will assist in the selection of the most appropriate linkage/unduplication protocol.

Linkage Wiz:

LinkageWiz is a powerful data matching and record de-duplication software program used by businesses, government agencies, hospitals and other organisations in the USA, Canada, UK, Australia and France. It makes it easy to link records across multiple databases and to identify and remove duplicate records within databases

TAILOR -

TAILOR is extensible, and hence any proposed searching method, comparison function, decision model, or measurement tool can be easily plugged into the system. We have proposed three machine learning record linkage models that raise the limitations of the existing record linkage models. Our extensive experimental study, using both synthetic and real data, shows that the machine learning record linkage models outperform the probabilistic record linkage model with respect to the accuracy and the completeness metrics, the probabilistic record linkage model identifies a lesser percentage of possibly matched record pairs, both the clustering and the hybrid record linkage models are very useful, especially in the case of real applications where training sets are not available or are very expensive to obtain, and Jaro's algorithm performs better than the other comparison functions.

The following three screen snapshots are the basic screens of TAILOR graphical user interface. The first screen allows the user to either generate a synthetic experiment using DBGen, perform a real experiment on a database, or repeat a previous experiment knowing its data files. The user then uses the second screen in order to select a searching method and a comparison function and tune their required parameters. Finally, the third screen allows the user to select the decision model he would like to apply and outputs the values of the measures if the experiment is on synthetic data.

Download PDF for screenshots of TAILOR here.

MatchIT -

matchIT incorporates our proprietary matching algorithms to ensure phonetic, miskeyed and abbreviated variations of data are detected. Results can be verified using comprehensive data auditing functions, drilling down to suspect data, identifying data anomalies, and filtering garbage and salacious words.

Fuzzy Dupes 2007

Did you know that your contact database typically contains 3-10% duplicates ?

These duplicate records result in unnecessary costs when sending out printed catalogs, are aggravating to your customers, create problems in the controlling, etc. With classical methods you have no possibility to locate these duplicates in your database.

Machine Learning and the Hidden Markov Models (HMM)

2007-01-28T21:20:00.000-08:00

What is a Hidden Markov Model? (from Answers.com)

A hidden Markov model (HMM) is a statistical model where the system being modeled is assumed to be a Markov process with unknown parameters, and the challenge is to determine the hidden parameters from the observable parameters. The extracted model parameters can then be used to perform further analysis, for example for pattern recognition applications. A HMM can be considered as the simplest dynamic Bayesian network.

In a regular Markov model, the state is directly visible to the observer, and therefore the state transition probabilities are the only parameters. In a hidden Markov model, the state is not directly visible, but variables influenced by the state are visible. Each state has a probability distribution over the possible output tokens. Therefore the sequence of tokens generated by an HMM gives some information about the sequence of states.

Hidden Markov models are especially known for their application in temporal pattern recognition such as speech, handwriting, gesture recognition and bioinformatics.

State transitions in a hidden Markov model (example)
x — hidden states
y — observable outputs
a — transition probabilities
b — output probabilities

Resources:

Machine Learning Links

Record Linkage and List Quality

2007-01-28T20:41:00.000-08:00

What is Record Linkage:

Record linkage is the task of quickly and accurately identifying records corresponding to the same entity from one or more data sources. Record linkage is also known as data cleaning, entity reconciliation or identification and the merge/purge problem. This paper presents the “standard” probabilistic record linkage model and the associated algorithm. Recent work in information retrieval, federated database systems and data mining have proposed alternatives to key components of the standard algorithm. The impact of these alternatives on the standard approach are assessed. The key question is whether and how these new alternatives are better in terms of time, accuracy and degree of automation for a particular record linkage application.

Other names that mean the same thing: entity heterogeneity, entity identification, object isomerism, instance identification, merge/purge, entity reconciliation,
list washing, match/consolidate and data cleaning. I like the term "record linkage" and will refer to it as such from this point forward in this blog.

Seems clear that if you want to be thorough in your record linkage efforts you would implement a combination of deterministic and probabilistic matching methodologies. Using a straightforward name and address match such as the Firstlogic (now Business Objects) approach will usually be sufficient if you are a list vendor or mailhouse. But if you are at all serious about identity management you will step into the deep end and implement a probabilistic matching method. I've downloaded the Ferbl open source probablistic matching tool, but have yet to experiment with it.

But whatever method you use or software package you buy, the quality of your record linkage always ends up in how good you have configured your rules. This is not an off-the-shelf solution - it requires work.

I bet most organizations "record linkage problems" could have been avoided if enough forsight and initiative had been put on the original database systems, in establishing that unique key.

Well, I guess hindsight is in fact - 20/20.

One product that definitely contributes to an increased percentage of record linkage is the SSA-NAME3 product. As you'll see from their site, they have developed this name search tool which uses probabilistic matching but factors in how common or uncommon the name is. For example matching two Jose Garcia's in the city of Los Angeles is not the same as matching two Jose Garcia's in Iceland. They are probably the same person in Iceland and most likely not, in LA.

What is a Data Steward?

2007-01-28T20:11:00.000-08:00

Data Steward defined:

The person responsible for a data standard. In this role, a Data Steward is charged by his/her Management to develop and maintain the data standard and to counsel Service personnel on the proper use of the data. He/she must: have a thorough knowledge of the subject matter of the standard, provide accurate and current electronic copies of data relevant to the standard, and weigh the pros and cons of comments received during review of the standard. He/she is authorized to defend or modify the standard as necessary in order to ensure its proper use.

Claudio Imhoff's definition and description of duties:

Steward - from Old English for "keeper of the sty", a sty ward.

Data Steward - Person responsible for managing the data in a corporation in terms of integrated, consistent definitions, structures, calculations, derivations, and so on.

Corporations are demanding better and better sources of data. The explosive growth of data warehousing and sophistication of the access tools are proof that data is one of the most critical assets any company possesses. Data, in the form of information, must be delivered to decision-makers quickly, concisely and more importantly, accurately.

The data warehouse is an excellent mechanism for getting information into the hands of decision-makers. However, it is only as good as the data that goes into it. Problems occur when we attempt to acquire and deliver this information. A major effort must be made in defining, integrating and synchronizing the data coming from the myriad operational systems producing data throughout the corporation. Who should be responsible for this important task? The answer for a growing number of companies is a new business function called Data Stewardship.

What is Data Stewardship?

Data Stewardship has, as its main objective, the management of the corporation's data assets in order to improve their reusability, accessibility, and quality. It is the Data Stewards' responsibility to approve business naming standards, develop consistent data definitions, determine data aliases, develop standard calculations and derivations, document the business rules of the corporation, monitor the quality of the data in the data warehouse, define security requirements, and so forth (see Table 1 for a list of the data integration issues determined by Data Stewards).

This data about data, or meta data, developed by Data Stewards can then be used by the corporation's knowledge workers in their everyday analyses to determine what comparisons should be made, which trends are significant, that apples have indeed been compared to apples, etc.

Just as the demand for a data warehouse with good data has grown, the need for a Data Stewardship function has likewise grown. More and more companies are recognizing the critical role this function serves in the overall quest for high quality, available data. Such an integrated, corporate-wide view of the data provides the foundation for the shared data so critical in the data warehouse. ...

Data Stewards are responsible for the following:
* Standard Business Naming Standards
* Standard Entity Definitions
* Standard Attribute Definitions
* Business Rules Specification
* Standard Calculation and Summarization Definitions
* Entity and Attribute Aliases
* Data Quality Analyses
* Sources of Data for the Data Warehouse
* Data Security Specification
* Data Retention Criteria

Crash Course on Data Stewardship:

Data Stewardship programs are implemented to reduce information technology costs and improve the value companies gain from their data assets. Stewardship programs focus on improving data quality, reducing data duplication, formalizing accountability for data, and improving business and IT productivity. An effective Data Stewardship program will rapidly improve the ROI from data warehousing and business intelligence efforts

Data Governance - IT or Business?

2007-01-28T18:52:00.000-08:00

"Where does Data Governance fit into the Organization?"

This question appears to have two answers. "In Business" or "In IT". The answer I get most often when I ask the question is ... "In Business". Or "Business should own Data Governance".

I wish it was that simple.

What exactly does it mean for Data Governance to fit "In Business"?

This article seemed to take up the questions posed in my last post. The last paragraph sums it up and answers this question.

The best answer to the question "Where does Data Governance fit into the Organization?" is "It doesn't matter". Data Governance can be successful when managed by a business area or by an IT area.

The decision of who will manage the Data Governance program can be very important to the success of the program. However, it will not necessarily make or break a well-defined Data Governance program's likelihood of success. As long as the business areas and IT areas coordinate their efforts, use a Data Governance Council as a strategic resource, cooperate in strategic data management activities, and act in the best interests of the organization (data-wise), the placement of the management of the Data Governance program is not nearly the most important question that needs to be answered.

IT Departments Role in CDI

2007-01-24T10:29:00.000-08:00

Introducing Customer Data Integration into an organization can be a bit daunting. A recommendation to anyone doing this is Jill Dyche / Evan Levy's book: Customer Data Integration which definitely builds the needed foundation for anyone's understanding of this subject. However, there are still aspects that need to be understood and certain functions clearly delineated.

What is the role of IT in CDI (Customer Data Integration)?

First a definition of IT:

information technology
n. Abbr. IT
The development, installation, and implementation of computer systems and applications.
"IT." The American Heritage® Dictionary of the English Language, Fourth Edition. Houghton Mifflin Company, 2004. 24 Jan. 2007. http://dictionary.reference.com/browse/IT>

The development, installation and implementation of the CDI system is the responsibility of IT - Not the day-to-day upkeep of the data. As a Data Steward you must have the needed access to the data in order to uphold your responsibilities.

Tight coordination is needed between the Data Stewards and the IT Dept when the CDI project is in its early stages. Clear definitions should be established for the roles needed, responsibilities assigned and the needed functionality and access levels built into the CDI project.

Getting this established and clearly delineated at the onset is critical to a successful CDI implementation and will increase the mileage and speed of progress in achieving your goals with CDI.

Postalsoft Mailing Software: Compare Solutions

2007-01-19T17:46:00.000-08:00

Here is a good comparison of the Firstlogic mailing products. I thought I had the professional suite but in looking at these it seems I am only getting the Business Edition options. I better call my account rep...

Postalsoft Mailing Software: Compare Solutions

Oracle TO_CHAR function

2007-01-18T10:48:00.000-08:00

The oracle TO_CHAR function can display seconds past midnight with the following syntax:

SELECT TO_CHAR(sysdate, 'SSSSS') FROM dual;

Further Oracle SQL/PLSQL topics

Machine Learning and Artificial Intelligence

2007-01-15T11:04:00.000-08:00

Machine Learning is a topic that has come up a lot during my CDI (Customer Data Integration) research. Specifically when diving into the domain of Probabilistic Matching. From the Initiate website:

Probabilistic matching

Probabilistic matching uses likelihood ratio theory to assign comparison outcomes to the correct, or more likely decision. This method leverages statistical theory and data analysis and, thus, can establish more accurate links than deterministic systems between records that have more complex typographical errors and error patterns.

Typically, probabilistic systems assign a percentage (such as 75 percent) indicating the probability of a match. Because these systems pinpoint variation and nuances to a much finer degree than a deterministic approach, they are better suited for businesses that have complex data systems with multiple databases. Due to the size of these data systems, the potential for duplicates, human error and discrepancies is far greater, making a system designed to establish links between records with complex error patterns much more effective.

Probabilistic matching enables one to use match scores and percentages on a field by field comparison to determine a match. There are three categories output from probabilistic matching and are set by the user based on the overall probability percentage:

1) Match - that can be automatically merged
2) Candidate Match - requiring manual review
3) Non Match

The topic of Machine Learning and artificial intelligence enters in with the manual review process of the candidate matches. The idea is that computer can learn from the users decisions of what was manually determined to be a match or non-match and build these into its future decisions and probability scores.

If an expert system--brilliantly designed, engineered and implemented--cannot learn not to repeat its mistakes, it is not as intelligent as a worm or a sea anemone or a kitten.
-Oliver G. Selfridge, from The Gardens of Learning.

"Find a bug in a program, and fix it, and the program will work today. Show the program how to find and fix a bug, and the program will work forever."
- Oliver G. Selfridge, in AI's Greatest Trends and Controversies

Machine learning refers to a system capable of the autonomous acquisition and integration of knowledge. This capacity to learn from experience, analytical observation, and other means, results in a system that can continuously self-improve and thereby offer increased efficiency and effectiveness.

(read full article on Machine Learning here)

Companies like Purisma and Siperian offer machine learning techniques built into their software.

A primary goal of machine learning would be to reduce the amount of manual review needed to determine matches and continuously improve the software's ability to accurately detect and consolidate duplicate records.

An open source tool that can be used for probabilistic record matching is Febrl (Freely Extensible Biomedical Record Linkage). Written in Python anyone can download this from sourceforge.net and get your feet wet with probabilistic matching.

An SQL Introduction and Tutorial

2007-01-09T14:25:00.000-08:00

If you need to freshen up your basics on SQL or if you need to train a new employee on a gradient approach to SQL, the following is a suggested training line-up:

First, clear up each of the following terms:

SQL
SELECT
FROM
AS
WHERE
LIKE
IN
BETWEEN
AND
OR
INTO
GROUP BY
ORDER BY
HAVING
COUNT

Next, go through this online interactive tutorial. This has the student walk through real examples using an online SQL tool so you can practice what you learn online without having to go to another computer where your database is.

An Interactive SQL Tutorial

In learning any new subject it is extremely important to start with the key words and to have these cleared up first, before carrying on with the material. This is covered in Hubbard's, Study Technology.

Here are good basic definitions for each of these SQL terms:

SQL: SQL stands for Structured Query Language and is a computer language that allows you to ask questions (query) or interact with your database in a structured manner.
SELECT: The SELECT statement indicates that you wish to query and retrieve information from a database. The select_list specifies the type of information (or column names) to retrieve. The keyword ALL or the wildcard character asterisk (*) could be used to signify all columns in the table. Also, the keyword DISTINCT could be used to discard duplicate records and retrieve only the unique records for the specified columns.
INTO: The INTO keyword indicates that you are inserting records into another table. For example in Microsoft Access you can create a new table and insert all records from another table by using the following: SELECT * INTO newtablename FROM tablename
FROM: The FROM clause is required in any SELECT statement. The FROM clause specifies the specific tables to retrieve data from.
AS: The AS keyword is used in the SELECT or the FROM clause when you want to refer to a table or column as something else. This is very handy if you have long table or column names and you want to use a shorter name to refer to them. This is commonly used when you are joining multiple tables together and do not want to rewrite the full table name each time you are referring to that table. For example:

SELECT a.column, b.column
FROM table_name_a AS a, table_name_b AS b
WHERE a.ID = b.ID

This is also commonly used to have a nicer display of your results. For example if you have a column called "field17" and you want to display it as "COUNTRY" you can use the following:

SELECT field17 as COUNTRY
FROM table_name
WHERE: The WHERE clause limits the results to those records (or rows) that meet some particular conditions (optional).
LIKE: LIKE is another SQL keyword that is used in the WHERE clause. LIKE allows you to search based on a pattern rather than specifying exactly what is desired The syntax for it is as follows: SELECT * FROM table WHERE column LIKE 'abc%'
The wildcard '%' (in Microsoft SQL and Oracle SQL) or '*' (in Access SQL) indicates any character and can be used in any location within the quotes. This will return any records that match 'abc' as the first three letters and then anything else after that such as 'abc123', 'abcdef', etc.
IN: The IN keyword is used in the WHERE clause when you want to select all records that have specific values such as if you wanted all customers that have either blue, green or hazel eyes you would use the following:

SELECT *
FROM customers
WHERE eye_color IN ('blue', 'green', 'hazel')

You can not use wildcards with the IN keyword.
BETWEEN: If you want to specify a range of values you can use the BETWEEN keyword. This will give you anything between value a and b values. For example, if you wanted any zip codes that were between '90210' and '91420' you would use the following:

SELECT *
FROM address_list
WHERE zip_code BETWEEN '90210' AND '91420'
AND: The AND keyword is used when you want to add additional criteria to your WHERE clause or as part of the BETWEEN clause.
OR: The OR keyword is used when you want either one criteria or another, but not both. Be very careful when using the OR keyword as if you have AND and OR in the same WHERE clause your result will probably not be what you expect, unless you use parentheses (). For example, if you want all people who with blue or hazel eyes that live in California, you would use the following:

SELECT *
FROM address_list
WHERE (eye_color = 'blue' OR eye_color = 'hazel') AND state = 'CA'; If you don't use the parentheses your query will return people in California, people with blue eyes or people with hazel eyes. So remember your math class where you learned to group parts of the equation to avoid confusion (a * b) + y ...
GROUP BY: The GROUP BY clause specifies if you are grouping (or aggregating) any of the columns in your SELECT statement. For example if you want to display a count of all addresses by city you would use the GROUP BY clause to group the results by City. This following example will give you a breakdown by city and sort it by most to least:; SELECT city, COUNT(*); FROM table; GROUP BY city; ORDER BY COUNT(*) DESC
HAVING: The HAVING clause specifies the specific conditions to group by (optional). For example you may want to group your results by City but only display those Cities that have more than 10 matches. You would do this by saying "HAVING COUNT(*) > 10". HAVING always comes after the GROUP BY clause.
ORDER BY: The ORDER BY clause specifies whether to output the query result in ascending or descending order. This is often used to sort your results in alphabetical or numerical sequence.
COUNT: The COUNT keyword is used in the SELECT or HAVING clauses. This allows you to give a total count of something. This is also referred to as an "aggregate". This means you are grouping things together and displaying aggregated data on the group instead of listing each individual record. Other aggregate keywords are SUM, MIN, MAX, etc. COUNT gives you the count of records, SUM gives you the SUM of the values in a column, MIN gives you the minimum value, MAX gives you the maximum value. Here is an example of the COUNT usage:

SELECT COUNT(*)
FROM table_name
WHERE last_name = 'SMITH'

This will give you a count of all records with the last name of 'SMITH'. Notice the (*) after the keyword. This means you want to give a count of all records meeting the criteria in your WHERE clause. But you may also see COUNT(1) which also means give a total number of the records. The COUNT keyword is very useful in giving reports or breakdowns of various columns. For example if you want a breakdown of all records you have for each eye_color (if you have this column in your table) you could use the following:

SELECT eye_color, COUNT(1)
FROM table_name
GROUP BY eye_color
ORDER BY COUNT(1) desc

Notice the GROUP BY and ORDER BY keywords used as well. This will show you the count by eye_color and will sort it by the the highest number to the lowest.

Here is another simple introduction to SQL.

Filmed Interview with L. Ron Hubbard

2007-01-05T11:44:00.000-08:00

The Church of Scientology just released the only video interview with the founder of Scientology, L. Ron Hubbard. This answers all common questions on Scientology:

"What is Scientology?"
"What is the Mind?"
"How did L. Ron Hubbard come to develop Scientology?"
"How did he make these discoveries?"
"What is an auditor?"
"Why is Man on this planet and what is his purpose here?"
"Why is Man basically good?"

This site also has a running tally of how many of these DVDs are now in circulation - over 100,000 in the first week after its release!

CDI - Matching Engines

2007-01-05T10:30:00.000-08:00

Another tip from CDI expert Jill Dyche on the subject of matching engines and whether or not to base your CDI software decision entirely on the quality of the match engine:

Here are some other things to keep in mind when assessing matching engines:
Test with lots of real data; compare and benchmark results. Organizations testing CDI hubs should test with as much data as possible to get a more real-world test of the system, Levy said. DataFlux's Gidley also recommends profiling data sources to assess the accuracy and completeness of data. This can help with tuning the matching engine's business rules. For example, if 50% of the phone number fields in a database are empty, it might not be a good matching attribute.
Consider the interface. When a system isn't sure whether records match, it generally refers the matter to a human data steward to make the call. So the interface for data stewards is an important part of a tool decision, Gidley said.
Think globally and futuristically. Language differences, industry-specific requirements, and future infrastructure plans -- such as moving to a service-oriented architecture -- can also affect matching engine choices, according to Dyche.
Overall, Dyche said, the matching engine should be only a part of the overall CDI tool choice.
"Matching is one of many decisions to make," Dyche said. "When we see these vendor bake-offs and companies get down to [which CDI vendor] has the most accurate match, that's still only one component of the decision."
So, in some cases, the CDI tool with the most accurate matching engine might not be the final choice. Companies must consider the big picture, including data volumes, processing speed and functional requirements, Dyche said.

CDI - Project Research

2007-01-05T10:21:00.000-08:00

A great post about CDI project research and what you should ask yourself before diving too deep into the CDI pool covers the following:

What is the "need, pain or problem" that we're trying to solve?
What are our requirements, both from a business as well as a functional perspective?
What data sources have critical customer information in them?
What's the current system of record for customer data?
Who are the current de facto data owners in the company?
What are the ideal matching algorithms for our particular data?
What is the potential impact on existing technology architecture and systems?
How and when will data stewardship be addressed?

"Eight must-ask questions for CDI projects by Jill Dyche

ISAL Rate Calculator - Download it here

2007-01-04T19:44:00.000-08:00

If you are a mailer who has ever sent out ISAL (International Surface to Air Lift) mailings you know the postal form can be quite a pain to fill out. To simplify this for myself and for anyone else who is interested, I've made an Excel spreadsheet that contains the latest ISAL rates for each ISAL Zone as well as the calculations for the per piece and per pound costs. This is set up so all you have to do is enter the weight of the mailpiece (in ounces) and enter how many pieces are going to each ISAL Zone. Here is a view of this tool that you can download below and use:

Download the ISAL Rate Calculator here.

Just enter the weight and pieces for each ISAL Zone in yellow. If you get an ISAL Discount you can edit this cell as well. Your final ISAL cost will show up in the blue cell.

This can be used to help you fill in the USPS Postal Form for International mail (PS FORM 3650).

If you downloaded this ISAL Rate Calculator and it worked for you, let me know.

Here is the current link to the USPS ISAL Page where the latest rates for each ZONE can be found. Note there is a new ISAL Zone for Australia as of May 14th, 2007.

Matching Algorithms for CDI

2007-01-02T09:54:00.000-08:00

It generally understood that between 2%-5% of a customer database will contain undetected duplicates after a standard merge/merge routine is run. As stated in CRM Today, most merge-purge algorithms are 20 years old...

Most merge/purge processes were developed over 20 years ago and were never conceived to recognize the fluidity of movement, name change, and channels in which customers interact today. Even the most advanced de-duplication processes use character based logic and look up tables that are ill-equipped to assess the totality of a customers’ name and address permutations that accumulate through multiple customer interaction channels. These processes are easily deceived by minor variations in the name and address elements such as married/maiden, nick names, typos, and mis-keys. A typical file will contain 2 to 5% unidentified duplicate customers after a standard merge/purge process.

Instead of the common approach of only using one matching algorithm (I am guilty of this one) modern approaches are utilizing many algorithms to isolate duplicate records.

No single algorithm can efficiently and effectively power a matching technology due to the multiple culprits of customer identity and data quality error. Advanced solutions incorporate not one, but several advanced matching algorithms, each designed to group records into temporal data sets for the purpose of bringing visibility to distinct patterns of repetitious error. Once patterns of error are identified, the records can be referenced to consumer data sources, allowing for the remediation of the error and recognition of the true identity of the customer.

An integral part of any CDI product is its matching capability. Match rules will vary from organization to organization so using a standard template or built-in rules usually will not suffice if you want thorough duplicate detection and handling.

Firstlogic (now Business Objects) has a sophisticated match engine and beyond the standard match capabilities are "Extended Matching" which allows the user to set up custom rules for different match scenarios. I believe that simply using rule based matching you can solve 80% of your data consolidation issues.

Companies like Purisma or Siperian offer much more sophisticated match engines that utilize match clusters and larger "match footprints" which ease the process, but for the most part, if you know what you are trying to do, any rule based matching engine will do. And remember:

"The computer is no better than the organization that feeds it." - L. Ron Hubbard

The moral is first work out all your match rules and then see if you can solve your problems using your existing match engine software before looking elsewhere to solve these match related problems.

Awk Tab Delimiter (FS="\t")

2006-12-31T17:34:00.000-08:00

Quick guide to Awk Field Separators (FS):

Tab Delimited: FS="\t"
Comma Delimited: FS="," (Beware of CSV files. See earlier post on this.)
Pipe Delimited: FS="|"

CDI - Build or Buy?

2006-12-29T18:04:00.000-08:00

Organizations with a central HQ and regional offices, must have some form of CDI in order to accurately understand their customer base. Otherwise you are left with disparate sets of disrelated data, unlinked and unconsolidated, making analysis and effective marketing nearly impossible. So once CDI as a topic is understood, the next question becomes - Build or Buy? After travelling down the "build" path for 2 years I wish I had researched and implemented a commercial product just to avoid the headaches alone...

However, with all that water under the bridge, it would be nice to be able to simply "plug-in" the missing parts to make my CDI solution complete.

I recommend buying a solution outright, even though the cost may be overwhelming at first, it will pay off if your organization is doing any volume of mailing - and will probably pay itself back in postage saved alone.

Here are the key questions that should be asked of a CDI vendor if you are looking to buy:

* Since systems rarely share identifiers, does the solution score and match data taking advantage of all attributes that aid in the matching process using likelihood statistical theory for the highest levels of accuracy? State-of-the-art systems use "probabilistic algorithms" to compare attributes one by one and produce a weighted score for each pair of records. The higher the score, the more likely the records represent the same person. This method improves matching accuracy by using weights that are specific to an organization's data.

* Does the vendor provide analysis based on your own real-world data? Premier providers conduct frequency-based file analysis to provide weighting and thresholds specific to a customer's data. By using your own data, a better match is possible than by using generic criteria and arbitrary weights for each attribute. The data can be studied and tuned to recognize that a match on John Smith in the Bronx, NY, contains a more trusted matching value than a match on John Smith in Minnesota.

* Does the provider offer a way to capture and maintain a complete history of changes to attribute values? Such a method of "complete versioning" improves accuracy and has the ability to make the correct decisions on linking data even as it constantly changes due to marriage, divorce, new address, new phone number, etc.

* Does the solution enable you to establish the right accuracy and identification levels for your application and budget? Managing data quality is a business decision, so consider whether you need automated identification of results to ensure faster customer service, for example, or a solution that allows manual review of data to capture fraudulent customer activity, such as duplicate insurance claims.

* Can the solution overcome multiple identifications and data discrepancies across sources? Common obstacles to customer identity include transpositions, misspellings, nicknames, aliases, address inconsistencies and identity misrepresentations, all of which can occur when customer data flows into the company through multiple touchpoints. A strong CDI product should be able to keep data up to date and synchronized across the enterprise.

* Can you use the same matching technology in order to maintain all your relationships: for example, individuals, households and companies?
(read rest of article here)

How to create that Golden Master

2006-12-29T14:38:00.000-08:00

The concept of a centralized view or 360 degree view of a customer is often strived for but the "name, want and get" is not always figured out. Most companies will name what they are looking for, some fall short in getting the board to want the solution and many lose it on figuring out how to get it done.

VP of Siperian describes the need for assigning reliability factors to the different sources of data as some sources may have more accurate data:

In order to deliver a “golden” or master record for each customer and its various affiliations, a system must dynamically assess reliability across all data sources – based on user-defined parameters and intrinsic data properties – and ensure that only the most reliable content survives at the cell-level of the master record. For instance, if the call center begins collecting email addresses when confirming orders, this data attribute may be more reliable than the email addresses submitted by customers at the website. The ability to rapidly adjust the system to survive the call center email address over the website email address is a critical architectural component of any CDI system. Moreover, such cell-level survivorship for data reliability must be built into the core product architecture and should not be sacrificed as the customer hub scales to millions of customers. Ultimately, how well the end-users accept a customer data hub depends on sustaining high level of data reliability, even as the hub grows in volume or as new data sources are added.

In looking into different CDI solutions, you must look into how the data hub is structured, if they have configurable rules for loading and merging and if they have a robust Data Steward. Realize that data stewardship is the way all 'exceptions' get handled. If you do not separate out the confident updates from the questionable ones, you will have a poor foundation that will eventually lead to a polluted database.

A business cannot implement an operational customer hub in the absence of data stewardship ― as soon as data begins to flow through the supported business processes, exceptions and errors begin to flow as well. Therefore, any customer hub acting as a data integration platform must offer business capabilities to monitor and handle such exceptions either by business analysts and end-users or by anyone designated as a data steward.
...
Often managing such exceptions requires full user interfaces for complex data stewardship tasks such as investigating the history and lineage of certain master records in question. This may be the only way to ensure that the user acceptance and data hub reliability remains high. In other circumstances, an enterprise may choose to build a specific user interface against the programming interfaces of the master hub in order to suit its needs. In either case, an adaptive solution must deliver rules-based configurability with best-in-class data stewardship consoles as well as programming interfaces to handle all data exception and reliability needs.

I've worked on custom CDI projects before CDI was called CDI and can attest that the Data Stewardship interface will make or break your CDI project. This should not be underestimated.

The last point data export back to original sources. This is where you exchange your centralized view with the sources and provide them with your expanded view to assist them in better understanding your customers. If you have done a good job, they will love you, if you haven't you will be public enemy number one. They think their data is "better than yours", "why did you change my address", "you merged two customers into the same one!" and other rantings will be sent your way if you have not done a good job.

You should have a well thought out strategy including what data elements you will send back to sources and you should allow them to decide whether or not they want your data.

Often, certain data attributes (such as privacy preferences) need central control and exception handling whereas other attributes are best left under local management. In addition, security and access to data attributes in the hub will vary by individual roles within each organization and by organization at large. In fact, to support the broad range of business requirements across business lines, there may be multiple data governance regimes required for different data attributes, all within a single enterprise.

An adaptive approach must be based on a distributed architecture whereby multiple hubs can be deployed to integrate different data sources and support different processes, yet be able to share data across one another based on any number of hub-and-spoke or peer-to-peer governance regimes. This offers a line of business yet another dimension of flexibility to share some but not all data – each based on its own data reliability and governance policies. With full rules-based configurability and data stewardship interfaces, a broad range of data governance regimes can be supported.
In summary, data must be the central focus of any CDI project, and data reliability, stewardship and governance regime must never be an after-thought of a comprehensive CDI solution. Enterprises must closely review the details of a vendor’s offering in this area to reduce their risk of project failure and the total cost of CDI implementation.

Customer Data Integration (CDI) Can't Decipher It

2006-12-22T12:01:00.001-08:00

I'm drowning in the sea of Customer Data Integration solutions, Data Librarians, Data Stewards, Registry hubs, Persistant hubs or Hybirds. Above is a good review of the basics of the problem domain. Key players are the Oracle Customer Data Hub, Siperian, Purisma and possibly the recent Sun aquisition - SeeBeyond.

"Oracle leads in customer data integration. Oracle has assembled the broadest set of components, from data quality, to matching, to customer key management, to process integration, to ongoing monitoring and management."—Forrester Research

Customer knowledge is the lifeblood of the enterprise. And customer knowledge begins with a unified, reconciled source of high-quality customer data—provided only by the Oracle Customer Data Hub.

Internet seminar: Get A Single View of Your Customers

Internet seminar: Practical Guide to Customer Data Politics

Oracle Customer Data Hub is a fully integrated customer data management solution that centralizes, de-duplicates, and enriches your customer data, continuously synchronizing with all your data sources, to give you a single view of your customers. As new data comes in, reporting accuracy grows, analytics become more valuable, employee productivity increases, and day-to-day customer relationships improve.

Reading Jill Dyche's new book, Customer Data Integration is my life preserver. Talking to salespeople has led to further confusion and contradictory statements about each others products.

Gartner's findings of May 2006 on CDI may already be outdated with the fastly evolving market.

World's Oldest Computer - Antikythera Mechanism

2006-12-21T14:59:00.000-08:00

Ancient computer found off the coast of Antikythera, Greece is estimated to have been built over 2000 years ago. This makes it the world's oldest known computing device.

Above is a reconstruction of this device is believed to look like. There are over two thousand greek characters inscribed on this machine which helped archeologists decipher what it did and how it worked.

From a recent article in All Headlines News:

"...a device known as the Antikythera (an-tee-KITH-air-uh) mechanism, a device consisting of thirty bronze gears with approximately two-hundred-twenty-five teeth, was discovered off the Greek Island of Antikythera.
The Antikythera mechanism, which was made in Greece, is the earliest known computer, though research has shown that the mechanism was not unique for its time. Other instruments similar to the Antikythera have been mentioned in historical documents written in first-century B.C. by Cicero and Posidonius, the teacher of Cicero.
Scientists believe the Antikythera mechanism may have been used in the field of astronomy, showing the motion of the planets and computing eclipses of the sun and moon."

Here is a photo of what one of the pieces of this computer actually look like:

Other Antikythera links:

Reconstuction of Antikythera Mechanism
Antikythera Animation of gears
Pictures of mechanism
An Ancient Greek Computer
Oldest Known Computer

Oracle SQL Function - INSTR()

2006-12-18T19:31:00.000-08:00

I found it. The use of INSTR() simulates the AWK Function INDEX() and does a search of the first argument to see if the second argument exists and then returns the position where this is first found. Here is the syntax:

INSTR(str, chars[,s[,n]])
Find the 'n'th occurence of 'chars' in 'str'
Starting at position 's'
n and s default to 1

This was decribed in the following page which lists out all the Oracle SQL Functions and is a very useful link:

Oracle SQL Functions

To see a full listing of all Oracle SQL Functions you can run the following query:

SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD';

Looking for Index() function in Oracle SQL

2006-12-18T18:51:00.000-08:00

From my blog you probably can tell I have an infatuation with Awk. Well today I was trying to work out a problem in Oracle SQL where the index() function would have worked perfectly. I'm checking around to find if there is any built in function that searches an existing column for a string and returns the starting point if it exists. Oracle SQL does have the substr() function which I've found quite useful...

Download Free Awk Book

2006-12-06T12:18:00.000-08:00

Here is a link to download a free copy of the Gawk - Effective Programming Language book.

Awk Executables for Windows

2006-12-06T11:51:00.000-08:00

Here are some free downloads of AWK that you can install on a windows machine

AT&T Original Awk
Other Awk Downloads

What is AWK?

2006-12-06T11:28:00.001-08:00

Now that I am writing about AWK I thought I should start off with a descriptive definition of this language. (See full description here):

awk is a simple and elegant pattern scanning and processing language. It was created in late 70th of the last century. The name was composed from the initial letters of three original authors Alfred V. Aho, Brian W. Kernighan, and Peter J. Weinberger. It is commonly used as a command-line filter in pipes to reformat the output of other commands. It's the precursor and the main inspiration of Perl. Although originated in Unix it is available and widely used in Windows environment too. It takes two inputs: data file and command file. The command file can be absent and necessary commands can be passed as augments. As Ronald P. Loui aptly noted awk is very underappreciated language:

what language we use in our undergraduate AI programming class. That's understandable. We use GAWK. GAWK, Gnu's version of Aho, Weinberger, and Kernighan's old pattern scanning language isn't even viewed as a programming language by most people. Like PERL and TCL, most prefer to view it as a "scripting language." It has no objects; it is
not functional; it does no built-in logic programming. Their surprise turns to puzzlement when I confide that (a) while the students are allowed to use any language they want; (b) with a single exception, the best work consistently results from those working in GAWK. (footnote: The exception was a PASCAL programmer who is now an NSF graduate fellow getting a Ph.D. in mathematics at Harvard.) Programmers in C, C++, and LISP haven't
even been close (we have not seen work in PROLOG or JAVA).
The main advantage of AWK is that unlike Perl and other "scripting monsters" that it is very slim without feature creep so characteristic of Perl and thus it can be very efficiently used with pipes. Also it has rather simple, clean syntax and like much heavier TCL can be used with C for "dual-language" implementions.

How to parse a CSV file in AWK

2006-12-06T11:26:00.001-08:00

Okay, so I am not new to Awk - been using it for 3 years. One delimeter type I tend to stay away from is .csv files. I deal with a lot of address data so if you try to do the obvious thing and set the FS = "," you are going to run into a lot of incorrectly parsed fields as address data sometimes includes commas in the street address.

So when I am dealing with a .csv file it is going to have quotes around each field and then a comma between each field.

So then you might say, why not set up the Field Separator like this: FS = "/",/"" which translates into the delimiter looking like ",".

Great, but the first field $1 will be left with a quote in the beginning as awk was not programmed to understand anything differently.

So I am looking for a simple way to parse .csv files using awk.

Here is all I've found so far:

Handling CSV data and a complicated example.
Google Group on AWK CSV parsing
Another Thread related to AWK CSV parsing

From all of this the most workable solution so far is:

function setcsv(str, sep) {
gsub(/[\\"]"/, "\035", str)
while (match(str, /"[^"]*"/)) {
middle = substr(str, RSTART+1, RLENGTH-2)
gsub(sep,"\036", middle)
str = substr(str, 1, RSTART-1) middle substr(str, RSTART+RLENGTH)
}
if (index(str, "\"")) {
if ((getline) <= 0)
return 0
setcsv(str "\n" $0, sep)
}
else {
gsub("\035", "\"", str)
gsub(sep, "\034", str)
gsub("\036", sep, str)
$0 = str
return 1
}

}

Introduction to Awk

2006-12-06T11:10:00.001-08:00

I'll be posting a bit more on AWK as this is a language I use all the time and I am constantly referring back to my MKS Toolkit manual for that certain syntax or to see how exactly that index command works again. Below are some useful links of AWK manuals or tutorials to get started using AWK.

An Awk Primer

Getting Started with Awk

The GNU Awk User's Guide

Awk is your friend

I'll get more into the FS command and try to overcome some of the annoyances I've had with it in my next posts.

Scientology Disaster Response Team Asking for Volunteers

2006-12-05T00:38:00.001-08:00

Typhoon Durian struck the coast of the Philippines this past Thursday and already as many as 1,000 are presumed dead in the aftermath. The Scientology Disaster Response Team is activating any and all volunteers who wish to help aid those in need in the Philippines.

Here is a link to the Scientology Disaster Response website which gives directions on how to help whether this is through donations or volunteering yourself.

SQL Plus Delimited Output File

2006-12-01T01:39:00.000-08:00

Ad hoc queries anyone?

Something that has always been a pain is when you need a fast report generated from your latest SQL script. You probably just resort to executing the query in TOAD or another query tool, and then saving it off to a file. That is about 5 mouse clicks too many and you just wish you could click a button to run your script and dump out the results to a nicely formatted text file. Using SQL Plus and a batch script, you can set up an ad hoc query tool for yourself that dumps out the results to a delimited output. This is actually quite simple:

You need three files, a) the SQL query you want to run, b) the SQLPlus script that will run your query and c) a batch program to run your SQLPlus script.

Here is how I did it:

a) queryOut.sql - This contains a query set up for pipe-delimited output. You can change the delimiter to whatever you wish. Here is a sample query -

SELECT field1 || '|' ||
field2 || '|' ||
field3 || '|' ||
field4
FROM {table}
WHERE {criteria};

b) queryToFile.sql - This contains the SQL Plus script including formatting options so you can dump out a file with your results and no other fluff. Here are the settings I used -

set termout off
set feedback off
set verify off
set echo off
set pagesize 0
set linesize 200
set trimspool on
column dt new_Value mydate noprint
select to_char(sysdate, 'YYYYMMDD') dt from dual;

spool rpt&mydate..txt
@queryOut.sql;
spool off
exit;

c) runQuery.bat - This is the simple batch program that calls SQLPLUS with your username and password and the script to run. Here is what this would look like -

SQLPLUS /@ @queryToFile.sql

I set up the above in a directory for these ad hoc queries and then edit my queryOut.sql file and click on the runQuery.bat which I added to a toolbar. Try it out and let me know if you found this useful or if you have any better ideas...

How to use Decode in Oracle SQL

2006-11-24T23:38:00.000-08:00

The use of the decode function in Oracle SQL is pretty straightforward. You simply specify the column that you need conditional logic on and then specify the conditions as follows:

SELECT DECODE (value,
   <if this value>, <return this value>,
   < if this value>, <return this value>,
   ....)
FROM dual;

If you have a default value that you want to display if any of the conditions are not met, you can add another comma and specify the default value like this:

SELECT DECODE (value,
    if this value, return this value,
    if this value, return this value,
    otherwise this value
FROM dual;

For a thorough description of all features of this DECODE function, see the Puget Sound Oracle User's Group description.

Here are some further examples from another post on my blog:

Most Oracle SQL users do not realize the power of this function. Hopefully this post will enlighten some on it.

What is DPV (Delivery Point Validation)?

2006-11-21T19:39:00.001-08:00

Mailers today who do not use a DPV service are either really out of the loop or they don't really care whether their mail actually arrives or not.

I've been in the category of "really out of the loop" until recently when I became aware of the pitfalls of not using a DPV service to ensure your mailing address is deliverable.

Let's say you process your mailing list through a standardization service without DPV and then try to merge all duplicates together in your database. If you do not validate down to DPV level, you may be losing address data and names from your list . DPV validation will tell you that the mail will actually get delivered to a delivery POINT which other validation levels can not do. ZIP4 encoding can only give you the general range of zip codes and tell you whether or not these are valid ranges, but for all you know you may be mailing to an empty lot or a torn down building, not to mention the high rises that won't deliver your mail without that apartment number.

So mailers who are interested in keeping their lists in good shape should choose some form of DPV solution. I am looking into the PostalSoft validation which is described on their website:

Delivery Point Validation (DPV)

By adding delivery point
validation to your data cleansing process, you can bring data
validation to finer precision. A DPV solution:

Determines whether a particular address — a specific house number,
apartment number, or suite number — is known to the USPS (United States
Postal Service) as a valid point for delivering mail, which helps
reduce mail-order fraud.
Identifies whether an address is a Commercial Mail Receiving Agency
(CMRA), such as The UPS Store, which can reduce fraudulent credit card
orders.
Increases the accuracy of matched records and helps create a more
accurate view of each customer, resulting in more sophisticated
marketing campaigns, such as loyalty programs, or more highly
personalized offers.

ZIP Code Maps

2006-11-21T16:27:00.001-08:00

Here is a basic ZIP Code map showing the different state ZIP codes and ranges. I've been looking for a good ZIP Code map for a while now and the above is pretty straightforward. While this doesn't give you much in terms of detail, it is a good overview.

Next is a great link to a ZIP code distribution map which is an interactive Java applet where you can see the exact location of each ZIP Code range or down to the specific zip code itself.

zipdecode | ben fry

Source Code

Jigloo for Eclipse?

2006-11-15T01:01:00.000-08:00

I've been using Eclipse now for a few months. Originally I started out with the Jigloo form builder tool which I found quite useful. From an earlier post:

Building GUIs for rich client apps in Eclipse is not the easiest thing in the world. I'm looking for windows builders comparable to the Net Beans GUI builder.

I am downloading the Jigloo GUI builder which sounds pretty good but that is coming from company who made it.

What about WindowsBuilderPro? This is a commercial product, not free like Jigloo. How is it better?

I'll try both and see for myself...

On the install, Jigloo is very simple and is just like any other Eclipse plugins. Just copy to the features and plugins directories and your done. WindowBuilder on the other hand has you walk through a install wizard which took only a couple minutes to run through. (Make sure you don't have Eclipse running during the install).

WindowsBuilder has several example apps you can try. In 5 minutes of testing this builder I found it very user friendly. I liked the "quick view" feature where without even compiling you can get a feel for how your app will look during runtime. Now on to Jigloo.

After the few minutes I found it easy to create a basic app. Nothing difficult. I liked the two screen (code/app) view that shows the two-way changes so if you edit the GUI the code changes or you edit the code and the GUI changes.

So far they are both comparable products and I'll spend a bit more time using each on an actual application to see which is the better product.

However, once I found my feet in making Swing applications, I found using a GUI builder to be more of a pain in the neck than a help. I see these GUI builders like training wheels when learning to ride your bike. It is really not that difficult once you get the hang of it, and those training wheels sure get annoying when trying to speed down that big hill...

My Scientology Wedding

2006-11-15T00:18:00.000-08:00

I was glad to hear that Tom Cruise picked the same Scientology Wedding ceremony that my wife and I chose when we got married 5 years ago. This was the Double Ring ceremony and during this the minister holds up two rings and asks you to picture the ARC Triangle in the middle as this represents Affinity, Reality and Communication - a vital part of a lasting marraige. A secret to a succesful Scientology marraige is that you have continuously create it. From the Scientology website on weddings:

"When someone begins on that arrangement called marriage, he is getting into something which is, to say the least, adventurous. When a couple get married, they are doing something they know nothing about. And, from all indications, when they have tried it more than once, they know no more about it the second time than they did the first." ...

"Where people are having trouble with marriage, it is because they are expecting it to run on automatic. They think it will hang together through no effort of their own; unfortunately, it won’t. It has to be created."

One part of the wedding vows is that you agree not to let a day dawn if there is a break in that ARC, in other words, if you have an upset, don't let it sit, but instead bring it up with the spouse and resolve the break in Affinity, Reality or Communication before closing out the day.

"Scientology founder L. Ron Hubbard isolated the three elements that make up Understanding: A is Affinity—which is the degree of closeness, liking or affection one has for something or someone; R is Reality—which is what we agree on; and C is Communication—which is of course the interchange of ideas between two people.

If you increase any one of these points in your dealing with another person you will have greater understanding with that person. Understanding is, of course, everything in a successful relationship.

NVL - Oracle SQL Null Field Handling

2006-11-09T21:31:00.000-08:00

An often overlooked function that comes in very handy is the NVL function in Oracle. This allows you to replace a query result that returned NULL, with a value, such as 0. Here is the syntax:

SELECT NVL(fielda, 0) FROM table;

This will always give you either a value or a "0". This will never return a NULL.

Here is a link for further details on this function:

NVL Function

Oracle Wildcards

2006-11-09T16:21:00.000-08:00

When searching using wildcards in Oracle SQL the most common use is the "%" character which searches for any number of characters. However a useful but less used wildcard is the "_" character which searches for any single character. These can be used in combination to form complex querying.

If you need to query for something that has a wildcard character in it, use the ESCAPE syntax as follows:

SELECT * FROM table WHERE field LIKE '%\%%' ESCAPE '\'

This will treat the '%' as a literal instead of as a wildcard.

Here is another good oracle sql resource:

Puget Sound Oracle Users Group

Blogger Sitemap for Google

2006-11-06T16:56:00.000-08:00

If you are trying to create a sitemap of one of your blogs for google here is how you can do it:

Step # 6: You can add a Sitemap to your account to provide Google additional information about about your blog. Google will process your Sitemap and provide information on any errors in the Sitemaps tab as well your sitemap will be downloaded everyday to index your blog fast.
Select type as : Add General Web sitemap

Now you need to add Atom 0.3 feeds. Generally, you would use this format only if your site already has a syndication feed and this is the only way to add sitemap to your blogger.com account.

Paste url of your Atom feed: For example http://cyberciti.blogspot.com/atom.xml and click on Add Web sitemap:

Update: If you are using a new blogger beta system, use http://yourblogname.blogspot.com/rss.xml

You will get confirmation:
You have added a Sitemap to http://cyberciti.blogspot.com/. Reports may take several hours to update. Thank you for your patience!
And you are done and your blog will be now index very fast (depend upon your posting and content).

How to Rebuild Java from Class File? Use a Decompiler!

2006-11-05T22:04:00.000-08:00

I ran into a situation where I couldn't find the latest source for a project I was working on. All I had was the executable jar file which was the latest version. I searched through my computer for the source code but couldn't find it anywhere. I must not have saved the last version or it get accidentally deleted. Then I discovered Java Decompilers and after searching for a few seconds on the web I found jad.exe - a simple decompiler that reads a compiled CLASS file and converts this into a java source file. Granted it is not perfect but through using this I was able to recover the work done and recreate my source file (through using jad as well as winmerge).

You can download jad here.
You can download winmerge here.

Grand Opening of Scientology London

2006-11-03T18:07:00.000-08:00

A fellow coder from United Kingdom couldn't be reached last week as he was attending the grand opening of London's new Church of Scientology building. He is an old school programmer working mainly in Fox-Pro!?! He attended the opening and I heard it that even the rain couldn't stop the people from flooding into the place. See the photo below with the umbrella's!

Scientology restored two famous buildings in London recently and just held the grand opening of these two new beautiful buildings.

LONDON — More than 3,000 members and friends of the Church of Scientology of London packed Queen Victoria Street in the heart of London for the sensational grand opening of the new home of the UK’s oldest Scientology church.

Steps away from the Tate Gallery and St. Paul’s Cathedral, the beautifully restored historic building at 146 Queen Victoria Street now serves the growing membership of London-area Scientologists. Its opening marks the largest expansion for Scientology in the Church’s 50-year history in London.
...

“This new church packs the full body of L Ron Hubbard’s technologies as derived from the greater well of Scientology knowledge,” said Mr. Miscavige, “and, as such, it provides the full gamut of Scientology activities for the betterment of mankind.”
Fanfare and umbrellas at the opening of the new Church of Scientology of London.

- Photos of the Grand Opening on the Scientology London site

At the grand opening Mr. David Miscavige spoke and gave the opening address.

- Mr. David Miscavige, Official Biography

How to Delete a Column from a Table in Oracle SQL

2006-11-02T09:40:00.000-08:00

Here is the syntax to delete one column from an existing table in Oracle SQL. As this is not used very often I am noting it here for quick reference.

     ALTER TABLE employee DROP COLUMN vacationPay;

Pretty simple.

How to Select Top Results in a Group By

2006-11-01T23:20:00.000-08:00

The other day I ran into an interesting problem with Oracle SQL. I used to use Microsoft SQL Server and last year switched over to Oracle and had to adjust to the peculiarities of Oracle SQL. I had a report that gave the top 10 results of an aggregate query. In SQL this was simple and looked something like this:

SET ROWCOUNT 10
SELECT field1, field2, COUNT(*)
FROM table
GROUP BY field1, field2
ORDER BY COUNT(*) DESC

But when trying to do this in Oracle SQL with no "ROWCOUNT" feature, I was stumped at first. Then I came up with the following which is a bit of a hack, but works:

SELECT field1, field2, CNT
FROM (
SELECT field1, field2, COUNT(*) AS CNT
FROM table
GROUP BY field1, field2
ORDER BY COUNT(*) DESC
)
WHERE ROWNUM <= 10;

If anyone knows of a more standard approach, let me know.

Oracle SQL Tips

2006-10-26T18:43:00.000-07:00

I often am in the middle of some off-the-cuff query and run into one of those technical "gotchas" like a failed compare between two text strings due to trailing spaces or that alter table syntax to modify a column name.

Instead of googling for the answer each time, I've consolidated the websites that I use to find the answers to these common SQL questions and have listed these below for my own and others reference.

Oracle FAQ
Oracle Cheat Sheet
Oracle User Guide

These are the most frequently used resources. I may add to this later but the above will solve most all of your Oracle SQL questions.

Formatting Query Results

2006-10-25T16:58:00.000-07:00

I wanted to format a query result so it displayed as currency. To do this you need to use the TO_CHAR function with the following syntax:

SELECT TO_CHAR(A_NUMBER, 'L999,999,999.99')
FROM TABLE;

This gives you a formatted output in the local currency format.

How to Use Variables in PL/SQL or SQLPlus Scripts

2006-10-25T15:19:00.000-07:00

I am researching how to set up my SQLPlus script to be able to output a text report using a generic script that reads in a variable.

I want to set variable on the command line and then have the script reference this throughout, giving me customized counts based on the variable entered. The results should output to a text file that can then be used as the actual report. I am familiar with how to do this in a PL/SQL script but not in a regular SQL script that I would use in SQL Plus.

The following link helped me with the setting up of my variable.

Detail on how to use variables in SQLPlus

Specifically here were the instructions:

9.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are given below.

1. The DEFINE command sets an explicit value:

define myv = 'King'

This creates a character variable "myv" set to the value "King".

2. The ACCEPT command:

accept myv char prompt 'Enter a last name: '

prompts you for a value and creates a character variable "myv" set to the text you enter.

3. Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:

select first_name from employees where last_name = '&&myuser';

If the substitution variable "myuser" is not already defined then this statement creates "myuser" and sets it to the value you enter.

4. Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:

column last_name new_value mynv
select last_name from employees where employee_id = 100;

This creates a substitution variable "mynv" set to the value in the "last_name" column.

When outputting to a file, I like to use include the date in the file name:

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;

spool &mydate.report.txt
-- my report goes here
select last_name from employees;
spool off

Be sure to use the double period after the variable name in your file name:

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

define mycity = Melbourne
spool &mycity..log

is the same as:

spool Melbourne.log

Use the variable that you set like this:

define dept = '60'
ttitle left 'Salaries for department &dept'
select last_name, salary from employees where department_id = &dept;

Or pass in the variable on the command line like this:

You can pass parameters on the command line to a SQL*Plus script:

sqlplus hr/my_password @myscript.html employees "De Haan"

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

set verify off
select employee_id from &1 where last_name = '&2';

That handled my main concern and gives me a nice text output file that uses my generic script with a variable.

The next point I sought to resolve was how to have my text output be in a delimited format as I plan to read this from an Excel file.

Here is what I found to do this:

--sets sqlplus so output will be tab-delimited
set colsep ' ' /* that's a tab */
SET LINESIZE 3000
SET PAGESIZE 50000
SET ECHO OFF
SET FEEDBACK OFF
--SET HEADING OFF
set trimspool on
set newpage none
set underline off

Here is someone else's point of view on how this should be done.
Digital Voice

Instead of either of the above a simple handling would be to hard code into the generic script "|" between the columns. This would give me a standard pipe-delimited output.

That sounds like the easiest method and is what I commonly use.

Scientology Public Service Announcements

2006-10-24T18:01:00.000-07:00

There are a number of public service announcements that have recently been shown on TV stations around the world. These PSAs are about Human Rights and the basic rights that all men have as based on the International Declaration of Human Rights first put forth over 50 years ago. Watch these and see what your human rights are.

Area Code Maps

2006-10-06T11:14:00.000-07:00

I have recently been looking into how to track phone numbers and the constantly changing area codes. A great resource was found called NANPA. Still not sure what this stands for but these guys are the ones who set the phone numbers, so I would say it is a reliable source. This is the best area code map site I have seen as well.

Area Code Map

Free Java Books

2006-10-03T11:32:00.000-07:00

Here is a link to a massive resource of free on-line Java books.

Free On-Line Java Books

Eclipse Plugins

2006-10-02T19:48:00.000-07:00

I am diving head first into the world of Eclipse plugins. The first breakthrough was to clear up the concept of what a Plug-in really was. I went through the cheat sheets in Eclipse to get somewhat familiar with them and then realized this is exactly what I need.

The Eclipse Plugin headquarters can be found here.

Hibernate Tutorials

2006-09-29T20:08:00.000-07:00

In my first Java database application I used straight JDBC calls to a simple Access database. This was good for practice but now I need a more Object related database approach and Hibernate seems like the way to go.

Here are some recommended Hibernate Tutorials that explain the basics of setting up a Hibernate project.

Hibernate Introduction
Hibernate Tutorial
Hibernate Tips & Tricks
Getting Started with Hibernate

If you are subscribed with ACM (Association of Computing Machinery) or have a Safari account there are some good Hibernate books that can be read online as well.

Eclipse vs Netbeans?

2006-09-27T18:01:00.000-07:00

NetBeans 6.0M3 vs. Eclipse 3.3M2 by ZDNet's Ed Burnette -- Last week Sun and the Eclipse Foundation both released milestone versions of their flagship Java IDE/rich client platforms. This planetary alignment provides a unique opportunity to compare the state of development of these two open source projects....

Velocity in Java

2006-09-27T16:38:00.000-07:00

Velocity is a very useful template tool. I use it with my mailing list software to prepare the jobfiles based on user input through a GUI. Each of the variables are set through the GUI and then the text jobfile is created using Velocity.

Here are some good links to get going with Velocity:

Java World - Velocity
Apache Jakarta - Velocity