Fusing SQL and XQuery to Combine XML and Relational Data for Reporting

There is a lot of talk these days about harnessing content for use in business intelligence.  This is something we've been enabling for some time at Ipedo, but it seems like more and more people are having the need/becoming aware of the possibilities.

Generally speaking, there are two types of content people are interested in - search results from textual documents and access to documents represented as XML.  I know, I know, there are more than that.  But if you look at what people are actually using, I contend they are looking to utilize search results on Web and/or Microsoft Office documents; or they want to search and extract information from semi-structured XML documents, which are usually forms, industry schemas (MISMO, FpML, ACORD et al), or transformed Microsoft Word or Excel.

So I want to show you something really cool.  Take a look at the picture below, which shows how you can use SQL to query semi-structured XML documents.  Perfect for BI.

 

The magic is our Dual-Core Query Engine that marries SQL and XQuery.  We have something called XML Tables that make XML documents look like relational tables.  So you can query a bunch of XML documents using SQL and manipulate it in a reporting tool like Crystal Reports.  Or you can query a combination of relational and XML sources using SQL and manipulate in Crystal et al.

If you want to see something live, you can also see how this same technology works against combinations of relational and Web Services data here.

Balancing Performance in an EII Server

After many years of explaining all of Ipedo's performance optimization functionality bullet by bullet, I finally put it into a picture.   The idea is that you always need to balance the user response time with the impact on the back end data sources.

So, that's all I'll say.  See if this picture does the trick in explaining it.

 

Unstructured Business Content in BI

An interesting article from Colin White over at the Business Intelligence Network called Using Unstructured Business Content in Business Intelligence.  Colin does his usual thorough job, and I have to say the term unstructured business content is pretty nice.  It really helps me separate the useful stuff from what most of us have just been calling "unstructured content."  I think unstructured business content does a better job capturing what's inside industry-specific XML schemas like XBRL, ACORD and others.

And here's a great summary of how this type of content might be used:

A review of semi-structured information shows that a high percentage of this type of information is in an XML format. Tags in XML files provide some semantic information about the contents of the file. There are also an increasing number of industry XML vocabularies, or metamodels, that add additional semantics to XML documents. An example here is XBRL for reporting financial information. XML is becoming the standard approach for exchanging information between systems and between companies.

Examples of applications that can analyze unstructured and semi-structured content and thus enhance BI processing include customer and market intelligence, pricing optimization, customer sentiment and complaint analysis, product safety and quality analysis, regulatory compliance, legal discovery, fraud detection, financial analysis, and IP protection. 

One thing I would like to throw into the mix here is the power of XQuery, especially when it can be easily combined with SQL.  Colin has an extensive section in the article covering the different ways of processing business content, but it comes up a bit short in getting specific on what you can do with XQuery.

First, as opposed to simple search (which he mentions), XQuery can pinpoint, extract and transform relevant pieces of unstructured business content.  Second - and this is the big one - you can do selects and joins with XQuery.  Last, but not least, as we do in Ipedo's product, you can seamlessly combine SQL with XQuery - so creating Oracle + XBRL reports are a snap.  Imagine GROUP BY functions across XML policy reports and your CRM system, all in SQL (with XQuery invoked for you), and plugable right into Crystal Reports.

It's all here today.  In production.