Spreading the word

Mashups can help get more information out of your data. Contrary to first impressions, creating them is a doddle – if you want to avoid writing your own code, there’s even software to do it for you.

In a previous column I wrote about how mashups and BI systems share the common goal of extracting information from a mass of data. A mashup can, for example, take geographical data from any of your source systems and display it using the mapping software (such as Google maps) that is freely available on the Internet.

However, raw databases contain a huge mass of data. It may well be more efficient to run a BI process that extracts just the data that interests you and then use a mashup to create a map view of that. For example, you might use a BI process to identify your most profitable customers located within 100 miles of your headquarters and then use a mashup to see if they are clumped geographically.

I've been working recently on a system that does exactly this – marries a BI system with a mashup. The description below is quite detailed mainly because, as a BI person, I had some concerns that creating a mashup would be difficult. This turned out to be a classic case of "fear of the unknown" and now, of course, having found it to be a doddle, I am a boring zealot who wants to spread the word to everybody else.

Laying the tables
The BI system in question does many things, one of which is to extract small sets of data from a large set. This data has always had a textual geographical component (for instance, "Perry Hill, Herefordshire"). Our first job was to ascertain the latitude and longitude for each of these locations (to save you the trouble of looking it up, Perry Hill is 52.04422N, 2.77323W).

We then built a lookup table of the locations, which contained the textual description, the latitude, the longitude and also the same location information stored as a spatial data type. (All the prototyping work has been done using Microsoft SQL Server 2008, which now boasts support for these new spatial data types.) This lookup table was placed in the staging area of the data warehouse.

When data is loaded from the source system it contains many rows that have the same textual location. A transformation process uses the lookup table to add the latitude, longitude and spatial data to each row. (It's apparent that we're storing redundant data here because the lat/long and spatial data are inter-convertible, but there's method in this madness.) A load process then pushes the data into the warehouse.

Once it is in there, we can run our BI processes and pull out the subsets of rows that interest us and write the subset to a table. (This isn't an absolute necessity, but simplifies the next stage of the process.) Now we have the data we want to map, how do we mash it up with the map?

In the database engine we write a stored procedure that returns the data set from that table. Then we write a web service (see "What is a web service?"), which calls the stored procedure and returns the data as XML. Finally, we write the mashup; an application that talks to the web service and to Microsoft Virtual Earth (which is what we used, but using Google Earth is also an option). The mashup calls the web service for data, gets it back, sends it to Virtual Earth which plots the points and returns what is essentially a web page showing the map complete with your data locations. The locations can be displayed on a map or a satellite image, and you can zoom in and out in the normal Virtual Earth fashion.

The technical angle
Let's look in more detail at how both types of positional data (spatial and lat/long) are handled.

We're storing redundant data in the warehouse, as mentioned earlier. Our stored procedure pulls the latitude and longitude from the answer table and sends these values to the web service where they are turned into XML data. This is sent to the application and the application sends it to Virtual Earth, where it's displayed.

At this point you might be wondering two things:
1. If spatial data and lat/long are inter-convertible (which they are using the functions in SQL Server 2008), why are we storing both in the warehouse?
2. Why are we storing the spatial data at all if it is not used (it's the lat/long data that we send to the web service)?

No problem, we have the answers:
1. Generally speaking, we don't worry too much about storing redundant data in a warehouse if it gives a speed advantage, which it does in this case.
2. We're using the spatial data within the BI process to extract the data we're interested in. Then we use the latitude and longitude values to plot the points on a map. Having both spatial and lat/long data in the answer table means the stored procedure will run faster. The stored procedure could also convert the spatial data into lat/long for transmission to the web service, but this would slow it down.

So we use the spatial data type for the BI process and the lat and long for the mashup.

One of the huge advantages of spatial data types is that we can ask questions like "How many customers are located within 100 miles of our HQ?" Try doing that with latitude and longitude values.

Finally, the web service and the mashup were trivial to write. We estimate that the two together took about 150 lines of code and five developer days to create and test. The prototype system shows a lot of promise and has been rolled out to a few users for testing. It's already given those users new insight into existing data, which is the goal of any BI project.

Knowledge Services
Mashups may appear taxing to develop, requiring a range of different components to be brought together. The task can be made easier with specialist software. Some products let you build a mashup without a single line of code. KT Labs (www.ktlabs.com) has a product called Knowledge Services, described as software that "provides a programming-free platform to create and manage knowledge".

Knowledge Services is highly integrated with Google Maps. With Knowledge Services you can build territory maps for sales areas, distribution ranges and so on, and such a map can show different types of data: existing customers, leads, your stores, your competitors' stores, your distribution centres and so on. The display for each point can be based on a parameter (stores with high sales figures have a larger marker on the map than those with low figures) or linked to an attribute (different colour markers for out-of-town stores and town centre stores).
For managing sales territories, an algorithm is supplied which uses sales and distance data in order to assist the process of allocating customers to sales teams in the most efficient manner. It is even possible to draw on a map and save a description of that area back to the underlying database.

A geocoding module can take an address and assign a latitude and longitude to it: you're unlikely to get pinpoint accuracy this way but it would be a huge time-saver if you currently store, say, customer addresses without positional data.

Knowledge Services is an early emerger in the field of BI mashups and worth a look.

What is a web service?
A web service is a device that sits on the Web and exposes itself (if you'll pardon the expression) to the Internet at large as a URL. If you send an XML string to the URL, it will return a set of XML data. It gets that data by talking, completely concealed from prying eyes, to a database, calling a stored procedure in the database. The database returns the data, the web service turns it into XML and sends it out.