JavaScript/JSON: Record Visualizer

One product we sell at Melissa Data is a list of data that matches each customer's demographic needs. We buy data ourselves in a raw form from many different vendors which we refine into a standard format that is efficient and convenient for our customers to access. Because the data we buy comes in so many different sellers' formats, several steps must be taken in the process that rearranges it into our standard format. An important type of information that we want to visualize in our development tools at Melissa Data is the seller source of each bit of data.  With that we can tell what fraction of our data comes from which source, the quality of the data from each of them, and where problems have occurred between one assimilation phase to another.  When reading from a mountain of contact records a picture is often worth a thousand words.  The Record Visualizer project I was assigned produced a Javascript-based tool within a Java servlet-based Apache website that presents data within the records that come into existence at each phase of our assimilation process.  Records created during the build are linked by horizontal edges to their parent or children to show the sequence the data follows. The InfoVis Javascript jit library provides an easy way to show this sequence via a tree as is shown below.

Each node on the tree is labeled with a record ID.  When one is selected it changes colors and the data within the record is shown on the rightside panel.  Note the alternate coloring of the panel text that is intended to distinguish between different items in a compact sequence of entries, and the tooltip that drops down whenever the mouse pointer is hovered over the purple demographic bullet point. The tooltip is a means to present sorted detail info concerning the bullet point while minimizing screen clutter.

The lower screenshot shows the page again with the configuration panel dropped down to reveal the list of record fields that can be shown or hidden in the right side panel.

C#/SQL: Search Builder: Defining a Fuzzy Search

We assimilate between ten to twelve billion records at Melissa Data that we collect via donation or purchase into just over a billion high quality records that are current, accurate, and more complete. Each record consists of many fields, the contents of which possess a different commercial value to our various customers. Some are interested in a record if one of its fields contains a specific value or its value is a member of a certain set, while others are interested in certain combinations of values in a set of fields.

Typically, customers assemble an SQL query to access the record set of interest in a database, and assembling that approach often becomes long and confusing as the customer's intent is fine-tuned. An ordered list of fuzzy constraints applied to their search is relatively easy to list and understand, and will usually result in better matches to their search intent. These simple fuzzy statements concern which fields are of interest, and also concerns such as: the precedence field matching should be applied when there are multiple fields being considered; the quality of the match levels; the level of match completeness desired; which, and what number of a record's fields must match; is one date range preferable to others; and more. The Search Builder is the application Melissa Data sales engineers use to convert customers' intentions to a data search.

The DataGridview provides add, remove, sort, and modify options.

This is an application designed and orginally coded in C# with WPF and SQLite. I later switched to Winforms when I couldn't find capabilities in the WPF datagridview necessary for user stories that arose later in the project.

The checked or unchecked states of the grid of checkboxes shown above in the DataGridview in both the Rank and Terms tabs are read as binary strings from the SQLite database (e.g. 10010110000101011 & 01110011011101110 correspond to two of the rows with 17 columns).

The Setup tab, seen below, contains a list of the database files previously opened and a list of the user-specified names for saved fuzzy queries which are stored in an SQLite database.

The Setup Tab

The strings seen in the controls in the Candidates and Tests tabs are loaded from a dump of the table and field names in each customer's contact database.  Those names are combined with verbs that help construct good fuzzy statements such as "lastname.required.unaltered".

The Candidates Tab

Java/SQL: A Test Case Builder

When we see the data that we work with at Melissa Data we generally see a huge blob of a few billion records in a few files. As the program which assimilates the billions of records into a set that is merely 10-20% of the size of the orginal records runs it is pretty difficult to perceive a problem from looking at logs or inspections of the records. Even if you devote the time to a well-distributed sampling of a few million records across the dataset you are probably only peeking into less than .1% of the records there. No warm feeling of confidence is found there, even if we had the staff to study the few million records in that .1%. Add to that the fact that the assimilation application sequence uses terrabytes of disk space and runs over two weeks time each time it generates a product dataset and you'll see that we have a practical need for a way to locate and focus on problems.

We needed an application that provides a means to select and process a much smaller set of records so we have a chance to view the source of problems and correct them.
The Test Case Builder is an application I wrote that allows the generation of a Melissa Data dataset that contains perhaps thirty or forty records instead of a few billion. This is valuable because it requires relatively little time to complete, produces a set of data of a size that can be juggled within a person's brain, and allows for sampling that relieves our fear that we've not seen enough to be confident.

The Test Case Builder is a Java class that is started in a script file at a Linux command line. A command line parameter must be specified that names the configuration file that contains the locations for the database, the output location, and where driver files should be placed -- and where they will be daemon monitored. It provides no UI as it is intended to be run on a remote Linux server with no UI capability beyond text-based menus.

Running the Test Case Builder involves preparing a couple of prerequisites so it has the data available to retrieve the data records. First up, a Melissa Data dataset must be generated -- typically containing around 3 billion records. The second prerequisite requires another application I wrote named the IDIndexer to comb through the huge dataset and produce a database that links the record ID of every record in the huge dataset with the name of the file in which the record is stored and the numerical offset into the file where the record's text can be read. From there a user can drop a file into a "requests" directory that contains a list of the record ID's whose record text is wanted for study. These request files usually contain a list of record ID's that were flagged as interesting during the Test department's work.
The Test Case Builder monitors the requests directory and when it finds a request file it opens it and reads the list of record ID's. For each record ID in the file's list the Test Case Builder queries the IDIndex-produced database for the name of the file it came from and the file offset of the record. It next opens that file and reads the record text and writes it to a file that will contain the input for a tiny version of an assimilation build.
Each of the six phases of the assimilation process produces a set of slightly improved and refined records. At each phase any records that were combined to create a record in the new phase are stored within a field of the new child record so the ancestry can be traced. Each time a record is read by the Test Case Builder the list of its parent records is read and they, too, are recursively searched so every source of data that went into a finished product record will be included in the output file.

When the Test Case Builder is done it has generated three files that contain different generations of records, and that can be opened and viewed in the Build Viewer application or can be fed to a very small dataset build process so the effects of each stage of the build can be examined and understood by the development team.

C#/SQL: The Record Viewer: Quick Access To 3,000,000,000 Data Records

The products offered by Melissa Data are personal and business contact lists intended for use by marketing or search organizations. Melissa Data combines billions of partial records received from several sources into a smaller set of current and complete records. Marketers can use the high quality Melissa Data product to reach customers who are more likely to become actual purchasers and avoid wasteful effort and postage costs associated with attempting to contact previous or duplicate addresses. A search business can learn and provide the latest updates concerning the contact information for people or businesses.

There are far too many records in play for us to accurately realize: i) the effects of modifications to our development process; ii) changes in source and types of input; and, iii) software that converts the raw data to the finished output. One of my purposes at Melissa Data is to produce applications that make retrieval and viewing of records practical at any stage of their assimilation.

One application I wrote for those purposes was a C# Winform record viewer that interfaced with both XML and mySQL backends, the MD Build Viewer. The Build Viewer allows list, view, search, and jump access to every one of the billions of records at each step in their production, their heirarchy, and changes resulting from modifications in a preceding phase. The final version of the records are read from a mySQL database and the parent records are read from a very large flat text, proprietary format text file.

Handling that amount of data reflected on the Build Viewer requirements in the form of performance goals, such as: The UI loads access to the data (approx. 3 billion records) in less than 15 seconds so the user experiences minimal delay (On that count, consider that it takes far longer than 15 seconds for the comparatively simple Linux wc shell command to count the number of lines in a 3 billion line file); Switching between records, presenting parent records, and jumping to records should be rendered in either a standard or cascading sidebar form; Clicking on any record field will jump to the breakout of that record component; Right-clicking on a street address will pop up a Google map with the address shown in the map. Here are a few views:

The view above shows the treeview record list and the detail of the selected record.

The next shows the details of one of the individuals in that record. The yellow tree nodes are those that have been reviewed and the red ones have been read and marked via a right click menu selection for later review. The user can suppress all but the red nodes or store them to a serialized list that can be loaded in a later session. A mnemonic key sequence provides a means to jump to the next or previous red node or to a specified record number.

Here are views of the sidebar view that shows the contents of the parent records of the one selected in the treeview. The upper will replace the parent record present in the sidebar with the one just selected from the list or reached via the up or down arrow key.
The lower shot shows the cascade option where the records overlap as the user clicks or accesses them via the arrow keys.




And here is one that show a Google Map inset resulting from right-clicking on an address in the record field and a sidebar view showing the same.

C++: Support of Windows Group Policy for Attachmate

Around the time that I finished the project to rewrite the API set in Attachmate's Extra! Personal Client product I saw an increasing number of articles in the trade magazines concerning the subjects of management and distribution of applications. This was a time that straddled the shift from desktop applications to web-based. Enterprises were keen on improving their return on investment of their software dollars -- often described as reducing the enterprise-wide cost of ownership of a product.

I asked my development manager if I might devote a project's time to researching this area and perhaps develop something if I ran into a good idea. After receiving his OK I obtained and installed several products including Novell's LanDesk Manager and Citrix WinFrame. In the end I felt that Microsoft had, in its Microsoft Management Console, a good candidate that I could quickly fit with Attachmate's products.

At design time, based on the MMC capabilities, I saw my goals as:
1) prepare the Extra! product for ease of distribution on a corporate LAN or WAN.
2) prepare the Extra! product so its features list can be centrally provided or withheld from a user based on one or more of their Windows network group memberships.

First up, I wrote an Administrator file for Extra! (known as an ADM file) that mapped a user's permission to access Extra!'s menu items and configuration settings. I then added code to Extra!'s frame class that obeyed the MMC protocol configured by an MMC administrator when the new extra.ADM file was used. An MMC administrator could then assign a mapping of features that should be available, disabled, or hidden to a specific user, a group of users, or the entire enterprise from his console.

With that, a network administrator could prepare Windows roaming profiles for an individual, a group, or the enterprise that silently installed an Extra! product from a network server, that contained a preconfigured set of product session parameters, and a policy set that provides or prevents access to Extra! product features as appropriate for the user.

Apart from the development efforts using the guinea pigs in Development, I did three different demonstrations to Product Management, QA, and Customer Support in which I brought a clean Windows laptop into the conference room -- or borrowed one from somebody in the room -- and logged in to an account that was configured to use a roaming profile. I showed the start menu items that came from the profile and also that Extra! was not installed on the PC. Then I clicked on the start menu item for Extra! which resulted in a running Extra! session showing up on the desktop about 20 seconds later. When checked, everyone attending could see that several of the menu items were disabled -- corresponding to the policy settings I set earlier in the server room while impersonating an MMC administrator. I repeated the same demonstration using a different roaming user account to show that that other user had a different set of menu items enabled and disabled.

This work coincidentally was a significant contributor to Extra!'s ability to meet Windows 2000 Logo certification the next year.

SQL/Master Data: Information Integration at Attachmate

Back in the earliest days of Attachmate's history there were three employees and the one manufacturing/sales/software development/support department. As the company began to grow new departments were formed to handle specialized tasks such as Sales, Marketing, Shipping, Software Development, Testing, and Customer Support. As each department grew they separately designed a uniquely structured Advanced Revelations (AREV) relational database to handle information relevant to their specific mission in the company. For example, Sales kept data about transactions and the customer systems involved. At the same time, but on a different database, Customer Support logged data about customer systems, problems, and missing features that, if implemented into Attachmate's product would provide for compelling sales opportunities.

And as this growth was occurring Marketing was sending advertising mailers to customers for products Sales had recently sold to them and customers were frequently asked the same questions each time they communicated with a different department.  That is, information in every department's database was valuable for numerous reasons if it could be made available to the other divisions. I was given the opportunity to devise a way to organize and manage the different departments' databases so accurate, consistent, and up-to-date information would be available to everyone.

Going in, I expected that my goal was to decide the best technical design for an end-product data warehouse. However, it turns out that establishing nth level normalization is not the biggest challenge in this type of an endeavor. I realized early on that I was put in charge of this project because several months of weekly meetings attended by the nine department heads involved had failed to pound out a well-normalized corporate database. Each of the nine departments with an AREV database had a lot of stored data in their database, but each was constructed in a dialect that reflected the needs and understanding of the types of people who worked in the department. That is, while much of the data was exactly the same from one system to the next, there was a lack of what are known as "unique identifiers" shared by all of the databases to identify a field type. For example, a field in one department's database might cover multiple fields in another department's. Another common problem was where a field in one department's database might concern the same data, but be named differently, and some departments had developed a serious religion about their choice of field names.

The steps I chose to reach an agreement involved producing a candidate list of the fields that emerged from running historical queries against all of the databases using the each department's terminology. To avoid the head butting that had occurred before my entry to the project I submitted in series -- specifically not in parallel -- the candidate table and field list to each of the department managers involved. (If any of you managers involved are reading this, my strategy was to gain managers' acceptance in order from the least agreeable of you to the most. ;-) ). Each time I ran into need for a change when discussing the list with one manager I would polish the changes in and start over at the first manager, successively gaining approval until the job was done. Yes, at the start it looked like an O(n^2) endeavor, but happily the managers turned out to be agreeable and it was finished in O(n) time.

From that effort, together with the purchase of a newer web-based database engine, the goal to create one master corporate database was completed. It provided a lot of strategic information, made clear what tactics worked well from one project or product lifetime to the next, and allowed us to optimally manage our efforts in ways that certainly played a role in Attachmate's later success.

C++/Java Invocation API: Java App Meets Vista Logo Certification?

Attachmate wanted to prepare an update to their Windows-native product, Reflection X, in Java so it could be run on several previously unsupported operating systems. At the same time, another significant goal was supplied from Attachmate's Marketing department, which specified that Reflection X must pass Microsoft's Vista Logo Certification.

Vista Logo requires a candidate application to meet more than 30 Microsoft-specified requirements that are to be tested by a third party validator, in this case Veri-Test. Two items from the list of requirements are 1) the application must provide a fully capable MSI installer; and 2) the application must conform to the Windows Error Reporting (WER) initiative. In a nutshell, the WER requirement will be met when 1) serious runtime exceptions are recognized by the application; 2) the user is warned via a common Windows dialog that asks if the problem should be reported to Microsoft; 3) if the user selects OK, a minidump is transmitted to the Microsoft WER site where it can be bucketed for statistical purposes and from where the user can be redirected to a website that provides further information on a workaround or product update; & 4) an application restart action can be started.

I prepared a WIX MSI installer that installed the Reflection X jar files, documents, graphic images, language resources, and JVM, and then installed and started the Reflection X Server, the underlying Windows service. Difficulty with the WER requirement came from the fact that the Java JVM is a virtual machine that contains its internal threads within structured exception handlers (SEH). That means that any serious or fatal Java exception that originates in Reflection X -- the kind that should be reported via the WER -- would be caught by the JVM, never making its way to a WER handler in RX's Java launcher application.

The interesting solution to this problem was found in the precedence given to different exception handler types. As mentioned above, a SEH is defined to surround each Java thread and therefore catches all exceptions that originate within the thread. If an exception is not handled in an application's SEH then the JVM will eventually catch it with one of its own handlers, ensuring that none escape to an interested WER manager. Another exception type, a Vectored Exception Handler (VEH), is given the first look at every exception thrown within the application by the operating sytem before it makes it to any SEH. I added a VEH to Reflection X's Java launcher application that evaluates every exception Reflection X generates and reports serious or fatal failures to the WER before passing them on to the SEH that should eventually handle it.

As a result of this design, and I think much to Microsoft's chagrin, Reflection X 2008 is the only Java application to earn Vista Logo Certification.