Recently I've been involved in a very interesting project involving data warehousing and geographical information system (GIS) technology. Although the data warehouse was not real-time enabled, the GIS technology added a very interesting dimension to this data warehousing project.

Traditionally, I've used maps to get from point A to point B. For instance, if I am going on vacation, I'll use a map to find out which attractions are nearest each other so I can maximize the number of sights I visit. Until I started working with GIS, it hadn't occurred to me that it is possible to map just about anything so long as it has a geographic data attribute that can be translated into latitude and longitude. If a data element has a latitude and longitude, the data element can be mapped using GIS technology. To illustrate this, consider the weather report on TV where the continental United States is shown in color and warm regions appear in red and orange hues and cooler regions appear in blue hues. Now, imagine the same map where sales figures instead of temperatures are shown and blue colored regions indicate lackluster sales and red colored regions indicate hot sales. With a map like this, all sorts of trends and information can be gleamed at a glance that simply cannot be seen in a spreadsheet or even a graph.

Another interesting discovery was that upon closer inspection data warehouses have geographic information. In fact, virtually every dimension and fact table has some geographic component to it from customer addresses in the customer dimension to transactions and their locations in the fact table. At the aggregated level, things such as street addresses may not make sense and so they get lost in the aggregation process but the more generic geographic data attributes such as city, state and ZIP code typically remain. These geographic data attributes can be geocoded thereby allowing the underlying data to be mapped. (Geocoding is the process of translating a geographic data attribute into latitude and longitude.) Here is an example of customers that were geocoded and mapped:

Figure 1: Geocoded Customers

In addition to geoding and mapping data warehoused data, GIS technology extends to maps three significant business intelligence functions. Drill down, or zoom in GIS parlance, is one such function. GIS technology allows one to layer maps and display specific layers based on the zoom level. On the Internet, when I look up an address on a map, I am able to zoom in or out of the map and see my address at the city level, street level or somewhere in between. This zoom functionality is a form of drill down as it allows seeing different map layers and features (objects on the map) in much the same fashion as when drilling down on summary reports and seeing report data at different grain levels.

The second business intelligence function is parameterization. Many business intelligence reports provide the ability to enter parameters such as date range. With GIS, different regions on a map can be selected and viewed based on location information and zoom level thereby allowing you to see subsets of the map in much the same fashion that reports show different slices of the data, depending on the report parameters specified.

The third function is drill across. Unlike business intelligence reports where drill across means invoking a report from another report assuming they both have the same data grain, in GIS drill across means invoking a report from a map and a map from a report. To illustrate this concept, consider the aforementioned example of a map that shows hot and cold sales regions. In this map, one can zoom on a hot sales region and then invoke a report that shows the sales by product for that region. Next, one can select a particular product on the report and then invoke the map to show where the sales for the product have occurred.

Putting it all together, you get a business intelligence solution that is composed of a data warehouse and reports that are GIS-enabled and maps that are data-driven. This is exciting technology that adds a physical dimension to business intelligence data.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access