Data Catalyst

Machine Learning Stanford Class

2011-12-05T23:07:00.000-08:00

Few months back my friend Vasanth asked me if I'd like to enroll in the machine learning class offered by Stanford university (http://www.ml-class.org/). I'm in my 9th week in to it now and it's been great experience so far. I was always interested in machine learning and never had a chance to take a course in Data Mining.

This course explains the core concepts of machine learning. Andrew Ng, the instructor, is absolutely phenomenal. I was impressed witth his teaching style and how effective it was.

Some of the topics we covered in the class are
1. Linear Regression
2. Logistic Regression
3. Regularization
4. Neural Networks
5. Support Vector Machines
6. Clustering
7. Dimensionality Reductions
8. Anomaly Detection

The exercises took 6-8 hours each week. Most of the exercises are in Octave and the code is heavily commented to help understand the core concepts. Overall it was an amazing class and a great introduction to machine learning.

So I was keen to know how often statisticians and machine learners use these core algorithms and tweak them up to manipulating stuff at the matrix level. I spoke to a friend who was working at an internet startup doing R for customer/market segmentation and he said he never went down to the matrix level. Seems like R is pretty sophisticated in abstracting the stuff under the hood. Maybe one day I'll get to try it.

Been a while

2011-10-06T22:22:00.000-07:00

It's been a while. A long time. Four months to be precise. I haven't written a single blog entry! As some of you may already know that I moved to San Francisco. I took an offer from Demandbase, a B2B marketing company. I'm working as a Data Engineer. I'm getting my hands dirty with linux (shell scripting is always fun!) and Ruby.

I've been using Ruby for little over a week now and it is fantabulous! Very easy to learn and the code is concise and up to the point. Look out for more posts on Ruby and data handling using Ruby.

Integrating Unstructured Data in a Data Warehouse

2011-06-08T22:11:00.001-07:00

I've been meaning to write about this topic for a while. Here's a succinct excerpt of my thoughts.

Traditional data warehouses are generally relational and fed by back-end systems which contain structured data. Most often than not this data is generated by internal source systems or arrives as external data from partners. But what about the web? There's tons of data out there. Possibly about your company, competitor or a business trend about your industry..and the list goes on. As we share more data on the web, this list is expanding every day. The traditional warehouse is not designed to handle such unstructured data thereby limiting the locus of control of your decision support system.

Search engines are good at handling both structured and unstructured information in various formats (e.g. database tables, XML, PDF, DOC, etc.). Case in point - We helped a client index almost 75GB of unstructured data stored in .pdf, .doc, text files going as far back as 70 years. The ancient files were scanned pdfs which were later OCRed. This is massively helpful not just from a pure enterprise search standpoint but in the terms of opening up the data to other parts of the organization in an easily accessible fashion. So how does this tie to a warehouse? Well, the search index in itself is a warehouse.

So how to access it? - With your existing BI systems. If your tweak your BI tool, you can make REST based HTTP calls to a web server. In goes your query and within a second out comes your data. Search engines are inherently fast! You can use this data for discovering relationships you never thought existed. Obviously this works better with certain types of data than other. I foresee a large interest in this area in the future as enterprises explore more potentially crawl-able publicly available data sources.

Using Audit Logs for Data Integration

2011-05-24T20:51:00.000-07:00

Recently we integrated two applicatons with Solr in near real-time: Siebel and Documentum. We achieved this by monitoring the Audit Logs of the applications. Audit logs work great for data integration where you have to push a change in a business object to another system. Audit logs for mature applications like Siebel and Documentum can be configured to write update/delete events. Usually, this is done by reading from the audit log table (without locking to prevent performance issues) to sniff out interesting events/changes to business objects.

Risks

The auditing mechanism must be bug-free and consistently record events - which is mostly the case with mature COTS software systems.
The audit log is generated by the application. So every-time you need to test your integration solution, you have to make changes in the application to see if they come through. Sometimes, developers don't have access to front-end apps which may cause problems.
Also, thorough testing is required from the application front-end perspective. Care must be taken to capture all event log signatures a particular business action in the application can generate.

Solr Java Service Wrapper restarts after commit

2011-05-24T20:43:00.000-07:00

We noticed our Solr instance was restarting every few days after optimizing. We use the Java Service Wrapper from Tanuki Software to run our instance as a service. The wrapper has a property for Ping Timeout. Basically, the wrapper pings the JVM to see if it is still alive every 30 seconds (default). If it does not get a response from the JVM, the wrapper restarts itself.

Solr optimization is a resource intensive process. I've noticed it used 100% of the CPU for over a minute. This caused the JVM to restart. To go around this issue, I increased the wrapper ping timeout to 120 seconds. This is a quick fix but will not work when the index gets large enough to the point that it takes more than a couple of minutes to optimize. But in that case you are better off looking at some other strategies to speed optimization altogether.

Link Extraction From Tweets using Java Function

2011-05-24T20:36:00.000-07:00

One of the common problems in analyzing tweets is extracting the links / URLs out of tweets. You can do all sorts of analytics on the links such as determine most popular links for a given day, etc. This can be done with Regex (see RegexParser below) but most of the links shared on Twitter are URL-shortened using services like bit.ly, fb.me, etc. To get a better understanding of the links shared in the tweet, you need to resolve the links and get the actual link they are pointing to. In short, reverse shorten or expand it! The following code gives two functions that allows you to extract and resolve the URL to its final destination URL in JAVA.

 
package routines; 
import  java.sql.Date; 
import  java.text.ParseException; 
import  java.util.regex.*;  
import java.net.*;
public class Parsers{ 

public static String  RegexParser(String stringToParse, String regexPattern) {
 
//  Create a pattern to match url 
Pattern p = Pattern.compile("((https?://)?([-\\w]+\\.[-\\w\\.]+)+\\w(:\\d+)?((/)?([-\\w/_\\.]*(\\?\\S+)?)?)*)");
 
Matcher m = p.matcher(stringToParse);
if (m.find()) 
return m.group(1); 
else 
return "";
 
}


public static String ExpandURL(String urlString) {
      String resolvedURL = urlString;     
      try {
            //Open connection and retry till no longer redirected
            HttpURLConnection connection = (HttpURLConnection) new URL(urlString).openConnection();
            connection.setInstanceFollowRedirects(false);
            connection.setRequestProperty("User-Agent", "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.205 Safari/534.16");
            while (connection.getResponseCode() / 100 == 3) {
                resolvedURL = connection.getHeaderField("location");
                connection = (HttpURLConnection) new URL(resolvedURL).openConnection();
            }
      } catch (Exception e) {
      }
      return resolvedURL;
}
}

SolrMeter - Benchmarking and Monitoring for Solr

2011-04-20T16:23:00.000-07:00

Recently we started using SolrMeter for simulating production queries on our test Solr servers. We quickly found out that the tool can be used for more than just simulating queries. It gives very interesting view of your Solr health including graphs of caches, query performance, etc. In order to feed it a good set, you need to cleanse your production Solr request logs to get the select statements only. SolrMeter expects the queries in a certain format. More precisely if your query were

http://localhost:8080/solr/select?q=type:document AND name:SomeName

SolrMeter expects type:document AND name:SomeName in the text file.

To grab and cleanse the production logs, I built a simple utility in .NET that did some regex to extract the required text from the log file. It is usually the text between the ? and &? in the Solr request log. I then fed this newly generated cleansed log file to SolrMeter, set the location of the solr instance, set the query throughput rate (in minutes) and voila! You have a stress test tool ready!

Problems with Solr 1.4.1 Highlighting Query - Running Slow

2011-04-19T05:44:00.000-07:00

I noticed that queries were running really slow on our 1.4.1 Solr instance which we use as Drupal backend for search. Some queries would take as high as 20 seconds!

So I started taking off parameters from the slow queries one by one until I saw a noticeable difference in query time. I noticed removing the hit highlighting was doing the trick. After a lot of digging around on the internet I found this article: http://www.mail-archive.com/solr-user@lucene.apache.org/msg28731.html

The problem is the algorithm 1.4 uses for hit highlighting. It is particularly exacerbated when the field you're trying to hit highlight on has large amounts of data. You can work around this issue by creating a copy of the field and restrict the number of characters to 20,000 so that you get a 40K odd field over which Solr will hit highlight. The performance of this new trimmed field for the purposes of the algorithm will be fine. As soon as I made the change by adding a highlight specific field the query performance improved from 20 something seconds to less than a second!

This doesn't seem to be a problem in 4.x Solr. We have a 4.x in production and it seems to be working fine even for large fields.

wget for Windows and how to append the response to a file

2011-04-04T15:20:00.000-07:00

wget
I've been meaning to write about wget -The awesome and easy to use webpage getter. Essentially wget goes to a URL and downloads the web response to a local file. This is really useful when you're trying to:
1. Kick off a periodic process that access a web-based application through a URL which then triggers an event in the application.
2. Download stuff off the internet like files, etc.
3. All kinds of stuff that has a web-based API...for e.g tweets!

Usage

The following downloads the response to a file on your local drive.

wget http://search.twitter.com/search.json?q=kremlin

Download
You can download wget from here.
You can download the manual from here.

Store Response to Log
If your restful web-service returns statuses of commands, try using this in your batch file

echo START >> log.txt
echo. >> log.txt
D:\utils\wget\wget --user=blah --password="blah" --no-check-certificate https://someURLyouwanttohit  -O - >> log.txt
echo. >> log.txt
echo END >> log.txt

This will append the response to a log file

Disable / Enable HTTP Caching Solr

2011-03-30T21:22:00.000-07:00

Sometimes it is better to disable the HTTP caching on Solr, especially when you are updating the index frequently and want to see the changes to the index instantly. To disable HTTP caching find the following lines in SolrConfig.xml


<httpCaching lastModifiedFrom="openTime" etagSeed="Solr">

Now uncomment the first line "Caching never304...." and comment the next "Caching lastModifiedFrom=...."
Then restart Solr.

Index Size vs Disk Space for Solr Optimize

2011-03-30T20:47:00.000-07:00

You require at least twice as much disk space as the size of the index. This is because of the way Solr optimizes the index. During optimization, Solr creates a copy of the index and when ready replaces the current copy with the new one. An optimize call takes longer than a Commit call. Optimize is cpu/disk/time intensive and must be done infrequently - maybe once a day. Because of the resource intensive nature, optimze must be run during the time of the day when the query volume is low.

This is apparent when you run large loads using dataimporthandler. Monitor the size of the data\index directory in your solr installation to see the size changes. You can add a querystring argument of optimize=false with your dataimporthandler call to run unoptimized commits when the dataimport finishes.

For more info on optional attributes of commit and optimize visit : http://wiki.apache.org/solr/UpdateXmlMessages#A.22commit.22_and_.22optimize.22

Translating Many to Many relationship in a relational database to schema-less structure like Solr

2011-03-02T20:38:00.000-08:00

In a relational schema many to many relationships are generally implemented using intersection tables. Lets say you have two entities Cases and Actions that have many to many relationship. Relationally, you'd create a Case_Action table that stores action_id and case_id. Now, what if you have to represent this relationship in a schema-less data-store like Solr? To start, you'd try to index so that you can get a document of type Case which stores the list of Case fields. This will be your base entity.

(NOTE: In Solr the term document is equivalent to a database row)
Well, there are several ways of doing this:
1. OPTION A: Create a multivalued field called action_id_list that lists all actions for a particular case. Populate this field for every document of type Case.
2. OPTION B: Create a multivalued field called case_id_list that lists all cases for a particular action. Populate this field for every document of type Action.
3. OPTION C: Don't create a multivalued field, instead, create multiple action documents for each case that the action is related to. This means your id will be a composite string of action_id and case_id

The option you choose depends on how you want to access the entities in a flattened structure. Option A and Option B are cleaner and easier to implement than C. But they have a limitation.

Option B should be used only when an action document is expected to store only the case_id and no other case related field. Otherwise, an extra query needs to fetch the required fields from a case document.
Similarly, Option A should be used if no other action field needs to be queried when you query a case document except for the field action_id.
Option C is like a intersection table where it gives you the flexibility to pick whichever fields of the case and action entity. The only drawback is that it makes the id field a little more complicated and perhaps difficult to use in your query.

If you find this article confusing, let me know. If enough people are interested, I can illustrate it with a diagram.

UPDATE: Here are the diagrams as promised. Again,

1. OPTION A: Create a multivalued field called action_id_list that lists all actions for a particular case. Populate this field for every document of type Case.

2. OPTION B: Create a multivalued field called case_id_list that lists all cases for a particular action. Populate this field for every document of type Action.

3. OPTION C: Don't create a multivalued field, instead, create multiple action documents for each case that the action is related to. This means your id will be a composite string of action_id and case_id

Setting Security in Solr running on Jetty using IP Filtering

2011-03-02T18:49:00.000-08:00

We're are using Jetty as a container for Solr and we've been configuring it heavily first for setting it up as a service and later for adding security. We considered the following options for securing the Solr instance

1. IP Filtering
2. Basic Username/Password authentication

We eventually went with IP filtering. IP filtering can be achieved by using the HTAccessHandler. To use the handler you need a .htaccess file which tells Jetty which IPs to allow or block. So your .htaccess file will look

<Limit>
satisfy all
order deny, allow
deny from all
allow from xxx.xxx.xxx.xxx
</Limit>

Allowing from localhost or 127.0.0.1 doesn't work in Jetty 6 and I don't think Mortbay is supporting Jetty 6 anymore. With out of the box Solr configuration, Jetty explodes the solr.war file in the webapps folder to load Solr. However, I unzipped the war file (which is essentially a zip file) in to folder named solr in the webapps folder.

Next, you need to create a solr.xml file that tells Jetty to load a custom context.

<Configure id="solr" class="org.mortbay.jetty.webapp.WebAppContext">
    <Set name="resourceBase"><SystemProperty name="jetty.home" default="."/>/webapps/solr</Set>
    <Set name="contextPath">/solr</Set>
    <Call name="setSecurityHandler">
        <Arg>
            <New class="org.mortbay.jetty.security.HTAccessHandler">
                <Set name="protegee">
                    <Ref id="solr"/>
                </Set>
            </New>
        </Arg>
    </Call>
</Configure>

You can place this file in webapps/solr

Now comment out the redundant deployer org.mortbay.jetty.deployer.WebAppDeployer in etc/jetty.xml. Now, start up solr using java -jar start.jar and you now have a secure Solr instance.

Google Fusion Tables

2011-02-27T19:15:00.000-08:00

Apparently Google had developed a basic database and data visualization stack with Google Fusion Tables. Pretty interesting as it has the basic principles of a database and a business intelligence tool with collaborative data commits.

http://tables.googlelabs.com/

Using Talend to do ETL in to Solr

2011-02-27T10:53:00.000-08:00

After great pain and spending many hours I was finally able to index something in Solr using Talend. Solr and Talend both awesome free tools, are not the easiest to integrate at first. Mainly because in order to user SolrJ ( which is the Java Library for Solr) you have to import several jars. Using those jars and doing intellisense on the methods in them can be a pain in Talend.

First, you need to following jars from your solr instance and put them in the lib/java folder in Talend installation. In your job load these using tLibraryLoad component.

From /dist:

apache-solr-solrj-*.jar

From /dist/solrj-lib

commons-codec-1.3.jar
commons-httpclient-3.1.jar
commons-io-1.4.jar
jcl-over-slf4j-1.5.5.jar
slf4j-api-1.5.5.jar

From /lib

slf4j-jdk14-1.5.5.jar

Start Code of the tJavaFlex

// start part of your Java code
   String url = "http://localhost:8983/solr";
   SolrServer server = new CommonsHttpSolrServer(url);


Main code

// here is the main part of the component,
// a piece of code executed in the row
// loop

SolrInputDocument doc1 = new SolrInputDocument();
   doc1.addField( "id", row1.CustID , 1.0f );
   doc1.addField( "name", row1.FName, 1.0f );
   server.add(doc1);

End code

server.commit();

In the advanced settings type

import org.apache.solr.client.solrj.*;
import org.apache.solr.common.*;
import org.apache.solr.client.solrj.impl.*;

I got sample data for 350k customers from http://www.briandunning.com/sample-data/ and was able to load it at the average rate of 300 recs/second in to Solr. The awesome thing about this is that you can update your solr index in real-time depending on how you plan to do ETL. The dataimporthandler is restrictive in the way that it indexes files of only certain types.

Solr Caching Explained

2011-02-15T15:26:00.000-08:00

The magic is in the Stats
You can access the statistics on your solr instance by going to http://localhost:8983/solr/admin/stats.jsp. The admin console in solr has ton of useful info that can be used to increase performance.

Types of Performance Improvements
You can gain improvement on the avgTimePerRequest on select queries - which is available on the stats page (You need to know what requesthandler you want to optimize, I look at standard for out-of-the-box config). You can improve performance in terms of time in which the recently committed transaction shows in the index by changing the autowarmcount (this is usually small anyway).

1. avgTimePerRequest
To reduce avgTimePerRequest you need to increase your cache. But beware setting the cache too high can cause performance degradation. This is because solr has to do a lot of cleanup/etc when it starts new searchers (more on this later)The objective here is simple you need to reduce the number of evictions and keep the hit ratio high. And you need to do this for every type of cache solr has.

Solr 4.x has 4 caches out of the box:
a. queryResultCache
b. fieldCache
c. documentCache
d. fieldValueCache
click here to learn more on solr cache. The query result cache is most lightweight i.e. having a high number does not eat too much RAM. documentCache is memory intensive depending on the number of fields you index per document.

2. autowarmCount
The autowarmCount property for a cache can be set in solrconfig.xml. It is the number of cache entries that are handed over from a dying searcher to a new searcher. Every time you commit to the index, Solr starts a new searcher and copies over the cache from the previous searcher that handles request while the copying takes place. This is called autowarming. You can autowarm firstSearcher which is the searcher that gets started when Solr is started. I'd recommend setting a list of queries to pre-populate your firstSearcher cache so that you are warm enough when Solr starts. This can be done in solrconfig.

The larger your autowarmCount, the larger will be the warmupTime and hence a larger delay between a commit and the time the recently committed change is available. If the index is getting committed very frequently i.e. every few seconds, it is better to keep the autowarmCount low. Otherwise the searchers will run on top of the other giving you a performance warning in the logs.

Final Words
To get the most optimum combination of cache settings try using different combinations and see how the QTime changes. I recommend doing load tests by hitting the index with random queries and monitoring the response time. A quick and dirty way to do it is:
I created a simple batch file which uses wget to query the solr instance for a list of random words. To generate the list of random wget statements I used a simple excel formula. I then compare the log files before and after changing my settings using Notepad ++ (to grep or search the lines that contain QTime) and WinMerge (To compare the results of grep)

wget "http://1ocalhost/solr/select?q=your random word&fl=id" -O ->> log.txt

How to create table level permissions is SQL Server

2011-02-15T06:42:00.000-08:00

Recently I had to create a SQL Server user which was going to be used by OBIEE to writeback to the reporting warehouse. Dangerous you may think. It is dangerous because you're under the risk of altering/deleting information from the warehouse forever - with no chances of recovery.

SQL Server is good in setting permissions with various granularity levels. I created a database role of writeback on the database (not server level) and gave it absolutely no permissions to start with. I then added a Securable (Right click -> Properties -> Securables) by clicking on Add and searching for the staging table in which writebacks'd be written to. Set the required permissions.

Now create a database user say writeback_user and map it to the required database with the role of writeback.

I'd recommend writebacks to be written to separate staging table and then consolidated later with the data warehouse tables. This way you can do checks on the data if you may before writing to the warehouse tables.

Download tweets using Talend

2011-01-29T21:55:00.000-08:00

Modules in Talend (or components - as they are called in the Talend world) open a new opportunity to slice and dice the massively available web data. A lot of user content is generated everyday. What if you want to monitor this and scrape it. Take for example tweets. Sentiment analysis anyone?

I built a simple JSON parser with Talend using the tFileInputJSON that takes twitter search results (capped at 1500) and stores it in a file.

The URL string for tInputFileJson reads:
"http://search.twitter.com/search.json?q=egypt&rpp=100&page="+((Integer)globalMap.get("row1.Page_Max15")).toString()

Here Page_Max15 is a sequence generated from tRowGen. With a few mods you can run this incrementally to everyday refresh your warehouse. You'll need to capture the since_id from the tweets.

This opens up limitless possibilities to enhance your data warehouse or fetch data for sentiment tracking etc or index it in Solr and make available to your executive team. Kinda nice to walk in to a meeting and say- "Hey! this is what people think about us yesterday!"

Custom Google GeoCoder using Talend

2011-01-29T10:18:00.000-08:00

Last month I wrote about how to get geocode information for an address based on zip code using Talend but this approach does not give you the precise address level geocode. You can obtain that using Google's GeoCode API.(keep in mind the usage limits)

I developed a Talend workflow that uses tFileInputJSON to connect to the API when you pass the concatenated address.

All you need is a input source, tFlowToIterate, tFileInputJSON and ofcourse your output. I wrote it to a buffer so that the result of the iterate flow can be aggregated. In the URL field of the tFileInputJSON component use the following:

"http://maps.google.com/maps/geo?q="+((String)globalMap.get("Out.ConcatenatedAddress")) +"&output=json&oe=utf8&sensor=false&key=YourGoogleAPIKey"

Quick and Dirty Sample Data Generation

2010-12-26T20:49:00.000-08:00

I recently tried the sample data generation engine at GenerateData.com. It is a quick and dirty way to generate sample data like names, addresses, etc. The only drawback is that the data is capped at 200 rows.

A more advanced and highly customizable way to generate sample data is usine tRowGenerator component in Talend. You can write your own functions that randomize and create strings from a subset or dynamically generate them.

Build Solr from Nightly Build

2010-12-26T20:40:00.000-08:00

To build Solr from nightly build:

1. Download source code from subversion repository at: https://svn.apache.org/repos/asf/lucene/dev/trunk/
You will need a subversion client like Tortoise SVN.
2. Build example using ant by:
ant example
3. After the build finishes you can start the solr server by:
java -jar start.jar

Geocoding Customer Data using Talend

2010-12-26T19:23:00.000-08:00

Geocoding customer data based on their zip or street address can add value to your data in many ways. For example you can do proximity searches using Solr on your customer data and show them relevant content every time they login to your site.

Talend makes it real easy and fast to achieve this. You can download a list of US Zip codes and a lat/long mapping at GeoNames.org. They have geocodes for other countries as well.

You can then create a basic job in Talend with a two csv input files and a tMap and see the results.

You can use the Google Map API to get more accurate Lat/Long results when you supply the address. The only problem is that there is a limit of 2500 per day. Talend has a connector called tGoogleGeoCoder that does precisely that.

Talend - The open source ETL Tool

2010-12-26T18:37:00.000-08:00

I've been playing with Talend - an open source ETL tool. Watch this space for more blog posts relating to Talend.

Solr on Jetty as a Windows Service

2010-12-22T20:18:00.000-08:00

Solr ships with Jetty which is a HTTP Server and servlet cotainer. You can run Solr as a service by either using Tomcat (http://wiki.apache.org/solr/SolrTomcat) or you can configure Jetty to run as a service.
To run Solr inside of Jetty as a service

1. Download the latest stable Jetty distribution

2. Copy the bin folder in Jetty over to example folder in your Solr instance

3. You will also need to copy lib\win32 folder over to example\lib and etc\jetty-win32-service.xml to example\etc

4. Add the following to example\bin\jetty-service.conf after the line: "wrapper.java.additional.2=-Djetty.logs=../logs"
wrapper.java.additional.3=-Dsolr.solr.home=../solr

5. Now, run the following commands
Jetty-Service.exe --install jetty-service.conf
net start jetty6-service
This installs the instance as a service and starts it. You can now run your Solr instance as a windows service and do a start/stop/restart anytime you like.

To check if Solr is running you access the solr admin console at http://localhost:8983/solr/admin/

Regression testing for Type 1 dimensions in the incremental warehouse

2010-10-03T12:16:00.000-07:00

Let's say you have data warehouse that you do an incremental load on every few hours for real-time data. You have your ETL set to run in micro-batches every few hours and it works perfect. Life is nice. Now, you get a request from the business to add new columns to that Type 1 dimension.

You make changes to the ETL and while testing painstakingly check if the correct column(s) get updated. Well, you don't have to squint your eyes anymore. Checkout the almighty CHECKSUM function! SQL Server and other databases have a checksum function which generate a hash over the set of fields your specify. Run a checksum on the rows that you expect not to be updated and store it somewhere before running the ETL.

SELECT pk, Checksum(mustnotupdatecolumn1, mustnotupdatecolumnn)
INTO checksumsbeforeload

Now run the ETL on your test batch and compare the values

SELECT pk, Checksum(mustnotupdatecolumn1, mustnotupdatecolumnn)
INTO checksumsafterload

You might also want to use this tactic when you are batch updating a table through ETL and expect that some columns should not change.

Create Table As (CTAS) in SQL Server

2010-10-02T15:29:00.000-07:00

An easy way to create a table based on the output of a select statement on another table in SQL Server is to use the into statement

select fielda, fieldb
into newtable
from originaltable

Using INFORMATION_SCHEMA in SQL Server

2010-09-26T19:26:00.000-07:00

The INFORMATION_SCHEMA in SQL Server is a powerful view to access metadata about the database. It is powerful and can help do quick analysis on metadata, column search, etc.

For example:
1. How about a data dictionary of all fields in your database? Can't remember the name of the field you added in the CUSTOMER entity? Just go to the information schema.

SELECT * from INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'Your Table'

2. If you want to Find all the places where URL are stored in the database and given that you stick to a convention such as add "URL" as suffix for all fields containing URL, you can run the following query and get quick results

SELECT * from INFORMATION_SCHEMA.COLUMNS

WHERE column_name like '%URL'

Using Zenoss to Monitor SQL Server Integration Services ETL Jobs

2010-08-20T08:19:00.000-07:00

Zenoss (Zenoss Core) is an open source application, server and network management platform based on the Zope application server. Recently our team implemented Zenoss to monitor production servers for a client. This gave us the ability to monitor event logs on servers in near real-time and set rule based email alerts. Also it was a great step in the direction of unified alerting and monitoring for all applications. Zenoss is ideal when your ETL enviroment does not have access to SMTP servers.

Design

We have a SSIS package that is triggered every few minutes by a SQL Server Job.When an error occurs in ETL(SSIS), the SQL Server Agent writes an error to the server event log. This event is captured by Zenoss. An alerting rule in Zenoss sends an email to admins that need to be notified.

Setup Alerting Rules

To setup an alerting rule in Zenoss, go to Preferences>Alerting Rules>Create a New Rule.

Monitor Jobs

After the alerting rules are setup lookout for emails from Zenoss. I usually forward the emails to folder that I monitor regularly. After receiving an error alert:

1. Fix the ETL error

2. Close the event by clicking on the X button - this will tell Zenoss to stop sending you emails for the past error events. A new email will be sent only if the error happens again.

This approach works well. The only drawback is that depending on the Repeat Time on the job you set, you may be bombarded with emails once an error occurs. A easy fix for that is to simply disable the rule once you have received the error notification.

Collaborative Mind Mapping

2010-08-08T16:54:00.000-07:00

Recently I made a vacation trip to England and Scotland. Before setting on my journey I decided to use Facebook to tap in to the wealth of information about the UK. I created two mind maps on Cacoo -an online collaborative drawing tool; took a snapshot of the diagrams and posted it on my FB profile. Each map listed a few places I knew were interesting to see in London and Scotland. I shared the edit rights of the maps with everyone. But Cacoo requires you to create and account, sign in and do all that to edit the diagram.

I posted the diagram on my profile with an edit link and asked my friends for suggestions. Within 30 minutes I had at least 5 comments suggesting awesome places to visit. What I noticed is that nobody tried to edit the map in Cacoo itself. Maybe they were short of time, maybe they didn't want to go through the sign up process, maybe something else. The diagram above is the final version after I added everyone's suggestions.

Last week I tried again. Only this time it was in Google Docs and it was for the plot of the movie Inception. You can set the security features in Google Docs so that drawing/document editors are not required to sign in - anybody with a link can access. Isn't that purely awesome? Love you Google.

Select distinct on text field in a data warehouse

2010-08-08T16:25:00.000-07:00

Have you ever tried doing a select distinct on a text field in SQL Server 2005? You can't. Typically, business intelligence tools like OBIEE do a select distinct query on every field that needs to be reported from the data warehouse. So, how can you have a text column in a table that needs to be reported upon by a reporting layer like OBIEE?

Make the field varchar(max). This will allow you to run a select distinct on the field. The text field is deprecated in SQL Server 2005.

Also, this concise, informative article sets all the confusion about when to use varchar(8000), varchar(max) straight: http://www.teratrax.com/articles/varchar_max.html

3 quick steps before you undertake a SQL Server Integration Services(SSIS) Data Warehouse

2010-08-01T17:48:00.000-07:00

3 quick steps before you undertake a SQL Server Integration Services (SSIS) Data Warehouse:

Watch Joy Mundy's webcast: Loading a Kimball method data warehouse using SQL Server Integration Services
Use Parent-Child Packages
Choose and stick to a naming convention to name your Tasks and Components. Click Here for Jamie Thomson's suggested naming convention.

Zynga claims to have to one of world's largest data warehouses

2010-07-21T13:12:00.000-07:00

TDWI interviewed Ken Rudin, general manager of analytics for Zynga. They claim to have one of the world's largest data warehouse running on Vertica back end and Tableau front end.

Here's a complete transcript of the interview.
http://tdwi.org/articles/2010/07/21/Visual-Access-to-Data-Volumes.aspx

Its a good read but some parts of it sounds more like a Tableau sales pitch.

Querying XML with Ampersands

2010-06-25T11:23:00.000-07:00

XML does not allow & as values of nodes. For example

John Smith & John Doe is not a valid XML. Hence the ampersand & is usually html encoded to "&" for storing the string in XML. And so are less than(<) and greater than(>) in to < and > respectively.

Now, if we had to search for this node using the contains statement we don't require to html encode the search query. For example, if we want to search for name of a book that contains "This is a & test" in the content node of the following XML

Test Book
<root>
  <book>
    <name>Test Book</name>
    <content>This is a & test</content>
  </book>
</root>

Our xpath query should look like
root/book[content[contains(.,"This is a & test")]]/name

Last Updated Field in Database

2010-06-22T13:06:00.000-07:00

The most convenient way to populate a last updated/modified or created field in SQL Server is by writing a trigger:

USE [DatabaseName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[LastUpdated_TableName] on [dbo].[TableName]
FOR UPDATE, INSERT AS
UPDATE dbo.CaseVoteFact
SET LastUpdatedOn = GetDate()
WHERE PrimaryKeyOfTable in (SELECT PrimaryKeyOfTable FROM inserted)

You can also used GetDate() as a default field but this will only put the date in for inserts and not for updates.

Copying static dimension data across environments

2010-04-08T11:26:00.000-07:00

In a recent deployment of a data warehouse to the TEST environment I wanted to copy all records from the static dimensions table that existed in DEV. Static dimensions store data that changes rarely. For example, the data for entity such as gender rarely changes. Also, static dimensions can be used if there is no easy programmatic way to determine the list of values for an entity.

For this project, during deployment, I first scripted the database schema(tables) and objects such as users, views, stored procedures and triggers in DEV and ran those scripts in TEST. I later deployed the ETL in TEST. In order to run the initial load the data in reference static dimensions had to be populated. Now there are several ways to migrate your data from DEV to TEST but if you have SQL Server Management Studio the one listed below was a quick and easy solution.

1. Simply copy the entire result set grid of the query in DEV: select * from

2. Right click on the corresponding static dimension in the target environment and click on open table.

3. Paste the contents.

Quick and easy!

Using html decode to transform/cleanse rich text data

2010-02-16T14:27:00.000-08:00

Recently I was tasked with transforming incoming data from a transactional system. The incoming data was generated by a rich text editor and hence had html tags embedded within it. I wanted to strip the data of the html elements and convert it to pure string. I was accessing a web service that was exposed by the system to get the rich text html data. In addition, the data was html encoded during the SOAP request to the web service.

For e.g. the string "test" was stored as : "&lt ;div class="user"&gt ;test&lt ;/div&gt ;"

You may notice that the opening and closing angle brackets <> were converted to < and >. To get the string in its original form I used HttpUtility.HtmlDecode() function. Decoding the above string once produced:test. Which was expected but there were certain records which looked like: &lt ;div class="user"&gt ;Gareth’s advice was not followed.&lt ;/div&gt ;

Decoding them once was no good as the #8217; never got decoded to an apostrophe. I decoded the string once again and got the expected output:

Gareth's advice was not followed.

Now I had to remove the html tags and extract the text in between them. Here's the VB code for this:

'Delete text between angled brackets
mStartPos = InStr(strContent, "<") mEndPos = InStr(strContent, ">")
Do While mStartPos <> 0 And mEndPos <> 0 And mEndPos > mStartPos
mString = Mid(strContent, mStartPos, mEndPos - mStartPos + 1)
strContent = Replace(strContent, mString, "")
mStartPos = InStr(strContent, "<") mEndPos = InStr(strContent, ">")
Loop

Do While Left(strContent, 1) = Chr(13) Or Left(strContent, 1) = Chr(10)
strContent = Mid(strContent, 2)
Loop

txt = strContent

Explicit Data Conversion in SSIS Using the Script Component

2010-02-15T08:12:00.000-08:00

There are several ways to convert data in an data flow in SSIS. Here is an example of Explicit Data Conversion in SSIS Using the Script Component. This piece of visual basic code converts a string in to a datetime or SSIS (DT_DBTIMESTAMP)

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Stage1Buffer.AddRow()
If Not (String.IsNullOrEmpty(Row.Stage1Date)) Then
Stage1Buffer.Stage1Date = Convert.ToDateTime(Row.Stage1Date)
End If

Stage1Buffer.EndOfRowset()
End Sub

The if condition prevents the task from failing if an empty string is encountered at Convert.ToDateTime() call. So make sure to include it.

Another way to cast DT_STR to a DBTIMESTAMP is by creating a derived column component and adding a new column with the following expression:
CaseAssignedDate == "" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)CaseAssignedDate

MapReduce ETL for Logs

2010-02-10T16:14:00.000-08:00

I was researching how MapReduce can be applied to ETL in organizations. I found this interesting article which shows how RackSpace.com used MapReduce to search their logs.

Check it out:
http://highscalability.com/how-rackspace-now-uses-mapreduce-and-hadoop-query-terabytes-data

One has to consider that RackSpace.com generates several hundred gigabytes of logs everyday which is on an extreme end of data processing considering the data generated in an average organization. So far MapReduce seems to be a good fit for data intensive ETL such as POS data in retail, clickstream data, etc.

Update: Another interesting article published by the ACM that compares usage of MapReduce and Parallel DBMS. Very interesting read.

http://db.csail.mit.edu/pubs/p64-stonebraker.pdf

Real-time Data Warehousing

2010-02-08T17:42:00.001-08:00

An interesting article regarding various ways of making real-time data warehousing possible. Of course, there are several other technologies which employ map reduce that are not explained in this article as this area remains fairly unexplored in commercial BI. But new companies are coming up in this area making the dream of real-time data warehousing more and more possible.

http://dssresources.com/papers/features/langseth/langseth02082004.html

Conditional Split Equivalent in Control Flow SSIS

2010-01-12T14:47:00.001-08:00

A good way to achieve Control Flow based on a Condition is by using a dummy script task and set the TaskResult property to success.

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

You can then add precedence constraints on the Containers/Tasks to direct your control flow. For E.g., Double click on the connecting arrow and Add an Expression as a precedence constraint -

@[User::RowCount] == 0

SSIS Transformation / Components not showing up

2009-12-07T10:20:00.000-08:00

I wanted to use a Multicast Transformation in SSIS 2005 but for some reason it never showed up. So I scoured the web for a suitable explanation. To my chagrin I couldn't find it.

Eventually, I figured out how to make the Multicast show up. Simply, right click on any transformation in the toolbox and click on Reset Toolbox. This will remove any filters applied on the Toolbox and show all out-of-the-box transformations.

Update: Another way is to click on Tools > Choose Toolbox Items and check the Data Flow/Control Flow component not showing up.

Appending files in DOS

2009-11-09T08:53:00.001-08:00

Today I used a command to append all files in a directory to a single file. I was working on consolidating all .xsd files in to a single one and found this command helpful.

for %f in (*.xsd) do type %f >> aggregate.txt

This will append all files with .xsd extension in the directory in which the command is run and to aggregate.txt. You can use the aggregate file to do searches/etc. I'm not sure if there's a better way to do this but this was simple and quick.

Google goes crazy on me

2009-11-06T06:38:00.000-08:00

Google went crazy on me for a microsecond. Check below. It was back up to its original self right after but I coudn't help blogging this.

Its probably because I have this ideal view of Google as the savior of the masses by bringing to us free and extremely useful applications. They are good and they are rising fast. They are taking over the world. I'd love to see how long they continue to dominate our lives.

A Cloud Computing Success Story

2009-10-21T06:43:00.000-07:00

He makes everything sound so easy :)

http://open.blogs.nytimes.com/2007/11/01/self-service-prorated-super-computing-fun/

Cognos: Dispatcher not initialized Startup Errors

2009-10-19T09:23:00.000-07:00

Getting errors accessing the Cognos Dashboard? Do you get a "Dispatcher not initialized error" ?

Chances are the Cognos service on the box has one of two problems:
1. The SQL Server for Content Manager is not up. Resolution: Start the SQL Server Services manually or using the SQL Server Configuration tool.
2. The Cognos Configuration Manager was not able to start the service correctly and Starting Service from the application returns an error regarding services's configuration. Resolution: Set the timeout setting - "stop wait time in seconds" setting in local configuration pane(left pane) to 200 (max).

It is better to use Cognos Configuration Manager to start/stop the cognos services rather than starting/stopping them manually.

Tableau BI

2009-10-15T10:37:00.000-07:00

So I checked out the Tableau demo after a co-worker pointed the tool out and I have to say - I'm impressed!

The tool seems to work effortlessly, especially in the demo. The coolest part is that it allows you to drag and select only certain data point on the chart and drill-down on those. The charts looked neat!

I will try to download and play with the tool some more and post back more stuff!

reCAPTCHA - hide email address from bots

2009-10-15T10:28:00.000-07:00

A product of CMU CS department - this tool will help you hide your email address from bots.

There are 2 distorted strings displayed in the image. One is the captcha check and the second one is taken from what probably is a result of OCRing a book. You can put a bogus string for the OCR version and the engine wont know. Try it out!

http://recaptcha.net/index.html

Javascript Date Validation using toString method

2009-10-14T09:51:00.000-07:00

If you want to test date objects that are initialized by input from forms (particularly text boxes), the toString method comes handy. But beware, Javascript's Date.toString() function returns different values in IE and Firefox if the object is initiated with invalid date.

For example:
var d = new Date("garbage");
alert(d.toString());

Returns
1. "NaN" (stands for Not a Number) in IE and
2. "Invalid Date" in Firefox

Here's a list of all methods for Date objects: http://www.javascriptkit.com/jsref/date.shtml
Also, if you're looking for a jquery plugin for a popup calendar, I'd recommend using Kevin Luck's date-picker (http://www.kelvinluck.com/assets/jquery/datePicker/v2/demo/). We used this at a client and it worked consistently across IE, FireFox and Chrome.

Philip Russom's Next Gen Data Warehousing White Paper

2009-10-05T13:22:00.000-07:00

TDWI's Philip Russom has written a great white paper about where the data warehousing industry is going in the future. It's a good read check it out:

http://download.101com.com/pub/tdwi/Files/TDWI_BPR_NextGenDWPlatforms_Q4_09.pdf

Connect Jquery Datepicker Objects

2009-10-05T12:27:00.001-07:00

Here's a script that binds / connects a jquery datepicker (Kelvin Luck's jquery datepicker plugin: http://www.kelvinluck.com/assets/jquery/datePicker/v2/demo/) instance to another one. This is helpful if the dates in your form need to follow greater/less than rules.

The script sets the start date of child datepicker to the date selected in the HistoryEnabled datepicker -

Date.format = 'mm/dd/yyyy';
$('.date-pick').datePicker({ clickInput: true });
$('.date-pickHistoryEnabled')
.datePicker(
{
clickInput: true,
startDate: '01/01/1996'
})
.bind("change", function() {$('.date-pick').datePicker({ startDate: $(this).val() })});

Using Javascript and HTML to create dynamic pages

2009-10-04T14:14:00.000-07:00

This article gives a brief introduction to AJAX

Recently we implemented an AJAX application for our client using C#, Javascript and JQuery.

JQuery is a lightweight javascript library that enables: easy manipulation of the DOM; and AJAX.

AJAX enables seamless web applications without the annoying flicker/page refresh that exists in traditional applications.

Traditional Web Applications

Web 2.0 / AJAX Web Applications