Metrics aggregation and reporting have always seemed to be a part of every job I've had. Over the years I've developed a system that allows me to slice and dice just about anything using Excel, SQL and a little bit of code. I used to rely heavily on the Data Transformation Services in SQL 2000 Enterprise Console, and haven't really found a good replacement (read: free replacement) until today. I just came across this little snippet of SQL that does the trick very well, here's what you do:

1. Create a new table in your database

Create a new table in your database, making sure each column data type is compatible with the corresponding column in your CSV file.

2. Properly format your input CSV file

What every data you want to suck in should be in a standard CSV file format as such. Save the file in a conspicuous location like c:\.

3. Run this script

Finally, execute the following script on your SQL Server. It should locate the CSV file, and import all the rows. Note, if it encounters an error on any single row, it will simply exclude that row in the resulting table. That could be a bit of a problem if you've got a lot of data.

This script seems to work in SQL Server 2005 and 2008. For more information, check out MSDN's reference material, there seem to be a lot more bells and whistles than I'm using here in this simple example. http://msdn.microsoft.com/en-us/library/ms188365.aspx

For Mix07 in London this week I created a sample site search to illustrate creating your own site search engine using the Live Search API. I've posted the sample code here, along with some notes on implementation (including a couple features I didn't have time to finish).

Here's a quick list of the features covered:

• Spelling corrections
• Web search results
• Image search results
• OpenSearch 1.1 (Putting your search in the browser searchbox)
• Live Search Macros

A really handy feature for web-based data tools is ability to let your users download their data in a CSV format so they can edit it in Excel or their favorite spread sheet program. Fortunately this is also really simple to code:

This is a web-based tool for biologist to search our catalog of chromosomes for specific genetic sequences. It also supports plug-ins which can search for variations or meta-data for the specific sequences.

The framework is written in C++ and uses CGI to interact with the browser. It is a little uncommon (and painful) to see a CGI application written in C++, but I chose this setup because I wanted to build a plug-in architecture so that the basic engine could be extended indefinably with new search algorithms. And future engineers wouldn’t have to keep reinventing the UI. And I wanted it to be really fast. Although the final architecture is very flexible and extensible, I never really found a good solution for the plug-in architecture, e.g. runtime binding of additional libraries. What I came up with was a plug-in model using abstract virtual classes. You could easily write your own plug-ins, but to deploy them you needed to recompile the main application and redeploy all the code. And it turns out, no one really wants to do that.

Files:

• Overview: Basic, Detailed
• Design Document
• Source Code
• Plug-in Module SDK
• Example Plug-in: Proximals Module

Simple program written in Java to scan a FASTA file from NCBI for complete, partial and complementary palendrome patterns. It also outputs an easy-to-read HTML file with the results. 

• Palendrome.java - source in Java