Using a GIS tool in BI

At the recent course on Dimensional modelling the question was asked about GIS and BI.

As a former GIS analyst I provided several comments but I forgot one item.

The following link is to an ESRI addon for a client GIS tool called OLAP for ARCgis. It will allow you to connect from the GIS client tool to an OLAP data source (limited number supported).

One of your spatial data sets (a map layer of sales districts for example) can connect to your OLAP sales district dimension based on your defined key and the user can get out whatever values he wants to work with. Color his regions based on sales volumes? scale points in size Store location based on profit, whatever he can think of.

Could do this to your customers dimenion BUT the ESRI tool has a 2 gig limit as it is 32 bit so has its limits.

http://www.esri.com/software/arcgis/extensions/olap/index.html

Robert Hart

To continue with GIS I thought I would provide links to a few articles on SOLAP (Spatial OLAP)

The first three articles are all from Laval University

http://sirs.scg.ulaval.ca/Yvanbedard/article_nonprotege/400.pdf
http://spatialolap.scg.ulaval.ca/visual.asp
http://geosoa.scg.ulaval.ca/~badard/ogrs2009-towards_mobile_solap_infrastructure-tbadard_et_edube-final.pdf

I preferred the articles from Dalhousie but the links are dead. I have included one link anyway in case it is only temporary.

http://cgmlab.cs.dal.ca/Members/obaltzer/SOLAP/bib/rivest05solap

Lastly there is also a demo below.

http://ors-demos.state.sc.us/demosolap/solap.htm

Believe it or not, it is likely that you are all doing SOLAP already. If you have a dimension with a spatial attribute (address, sales region, state, country, ...) then guess what, your doing SOLAP as that is all it is.

So this would mean that brining GIS functionality into the data warehouse would be beneficial to all of us. The obvious question is how to do it? For most of us we will have a sales district or other such attribute from our business system that serves as a dimension and GIS will remain external. If required we will probably pass the data to a report developer to produce a map image on a presentation tier / web page and this will be as far as we are required to go.

But what if you need to go farther? What if you need to QA/QC data against it's spatial aspect (what sales region is this customer in?). What if you need to generate new spatial dimensions from existing data (merging regions, new regions, dimensions for roads of a highway maintenance department, lakes, rivers, ...). Or to take it further what if you need attributes from a completely GIS source to build your dimensions or facts.

Most large GIS systems are database based now. There are also well established standards around GIS and the larger databases have GIS functionality and data types built inside them.

I am in the situation where I deal with spatial data. I've found that there are a number of open source tools that can help as well as one very good tool from a company called Safe Software that integrates with SSIS, Data Stage, and Informatica. I don't want to push a product but have found it to be very useful and well supported in the GIS community (http://www.safe.com/technology/spatialETL/overview.php)

So, now for discussion. Should we store GIS data inside the data warehouse? Should our dimension have spatial data types inside them or just an identifier to the GIS system? Are we building Spatial data warehouses?

There are some interesting challenges to this. As an experienced GIS and business analyst I believe that business data is orders of magnitude more complex then GIS data. I've seen GIS data warehouses and have learned that most are just a copy of multiple source systems with no transformations and do not cover what we consider a data warehouse. GIS data at it's most basic is a point, a line, or a polygon. By themselves this data is meaningless so GIS systems extend this with business attributes and graphic symbology that define the feature. An identifier for the lot of land you own, the owner, how many lanes to this highway, the name of the sales region, etc. BUT this extension to the feature is normally simplistic in a single table / row of data often referred to as the business table (at least by ESRI).

At this time I am not bringing the actual GIS data (point, line, polygon) into my data warehouse but not because of any complexity in the data but rather a limitation of presentation products and political issues. It's easier to simply generate the maps from our GIS environment with WMS (Web Mapping Services) then have our GIS over top of the data warehouse but I'm interested in what others do/think. Has anyone tried spatial data types in a dimension?

Any comments?

Robert Hart

This is an area that I am not too familiar with, but I and colleagues have an interest in.

My (simplistic?) take on SOLAP is that it includes a map as the interface for both presenting results and defining queries.
That is, the measures can be presented on a map where different boundaries are the dimension values.
Drilling down would take you up and down boundary levels - Continent>Country>Region>County etc.
By changing the level that the boundary is defined at, the query is being redefined and so the measures would automatically adjust for the level being specified.

So whilst I agree that most (all?) OLAP systmes have geographic data, it is only by presenting it through an interactive map interface that it becomes truly SOLAP.

To that end, I guess MDX needs spatial extensions to enable the interaction.
Which is what I think these guys have done:

http://www.spatialytics.org/projects/geomondrian/

I'm not sure if I agree with what they are suggesting for extensions to MDX.

Looking at OLAP it is about precalculating for faster retrieval of information. The kind of extensions they are talking about would be changing your OLAP server into a GIS server. How many customers are within x distance of a location. Intersections between different spatial areas. How many customers in an area.

Many of these things could be done as part of the ETL process (Customers in a predefined area) with areas or other spatial related attributes stored as dimensions or done using the spatial data extensions in the databases with SQL against the star schema but extending MDX and changing the OLAP server?

I can see storing the GIS data in the star schema but those types of analysis would be better in a presentation side tool or in the ETL.

Robert Hart