Geeks With Blogs
Chris Falter .NET Design and Best Practices

Long Island Expressway...painless dentistry...dry wine...educational television.   Inventing oxymorons like these is a wonderful party game; what others can we come up with today?  How about: airline food...random order...House Ethics Committee...Service-oriented business intelligence....

That last phrase does seem like an oxymoron, at first glance.  Service-Oriented Architecture (SOA) and Business Intelligence (BI) appear to be very different animals in enterprise architecture:

  • Their purposes are very different.  You typically use SOA to create workflows and composite applications, perhaps with an orchestration tool like Microsoft's Workflow Foundation (WF).  You use BI to view and analyze data from disparate applications.
  • Services operate at a fine or intermediate grain (for example, an entity or an aggregate entity).  BI typically operates at a very coarse grain (very large datasets).
  • SOA achieves integration by providing a set of common protocols that allow different applications on different platforms to interoperate.  This is a method-oriented integration strategy.  BI, by contrast, achieves integration by extracting, transforming, and loading disparate data into a dimensional database that contains an enterprise's "single version of truth."  This is a data-oriented integration strategy.

As I was perusing "Service-Oriented Business Intelligence" in Microsoft's Architecture Journal today, though, I realized that SOA and BI can harmonize quite fruitfully in the enterprise.  Per the authors, BI can provide many useful services in an enterprise's service inventory.  Here are some examples that Sean Gordon, et al, provide:

  • A single-validation service can expose the validation logic in the ETL (extract/transform/load) process for use elsewhere.
  • Applications can make use of enterprise-wide data exposed by a reference data service.
  • A dimensional database that houses historical data for a slowly changing dimension (e.g., a customer's location) can expose an entity history service.
  • A calculation service can allow applications to use comparison logic that is housed in an analysis cube.
  • An aggregation service can provide roll-up data for an application--for example, a workflow might need monthly sales data at a decision point.

In addition, services can provide useful data for BI.  For example, a data warehouse agent can subscribe to an event service and collate event data for the dimensional database.  The events can even be used to trigger BI actions, such as an ETL update.

The lowest-hanging fruit that I found in the article, though, was the suggestion of integrating management and operational reports via drill-through.  Why is this such a light-bulb-flashing-above-the-head brilliancy?  The sad truth is that it's hard to do reporting.  If you try to build reports with data from an OLTP database, you can bring down the entire data processing capability of an enterprise for hours when the president of the company decides to run a particularly complex query.  (This really happened at one of my former employers!)   On the other hand, if you try to build all your reports from an OLAP database, you make the "single version of truth" much more difficult to achieve, because the data warehouse has to incorporate every detail of every entity in every application.

To strike the right balance, the authors suggest building management reports (with aggregated data and key entity facts) against the OLAP data warehouse, and operational reports (with details about a single entity or a small set of related entities) against the OLTP database.  The neat trick is that you can drill through directly from a management report into the details of an entity--if the SQL Server cube designer has provided a URL action that links the appropriate cube member to the URL of an operational report.

It just so happens that the friendly product development staff at my current employer has been working hard to implement HTTP views of pretty much every entity in the enterprise system we ship.  To build the URL of a view, you just need the base URL of a web service, the ID of the view template, and the ID of the entity.   (Aren't RESTful services wonderful?)  It should be a straightforward exercise for us to concatenate these three data into a URL, link the URL to a data warehouse element via a URL action...and let the party begin for our business intelligence users!  Or so the Microsoft documentation on URL actions would suggest. 

If any readers have experience with URL actions in SQL Server Analysis Services, please leave a comment about your experience.  Thanks!


Posted on Sunday, August 15, 2010 11:30 PM Software Architecture , SOA | Back to top

Comments on this post: Service-Oriented Business Intelligence: Not an Oxymoron

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Chris Falter | Powered by: