Fusing Together Oracle Products with Data and Service Virtualization

We did a demo recently that I thought was worth sharing.  With all the talk of Oracle Fusion in the press recently, I thought it was worth showing how simply and easily you can create custom combinations of data using Ipedo's data and service virtualization techniques.

In the demo we fuse together data from some Oracle 10g databases, with Salesforce.com (ex-Oracle CEO,  so we threw it in) and data moved from SQL Server by Oracle DI.  We put it all into Oracle BI Publisher, run Ipedo on Oracle AS, use another Oracle 10g database as a cache, and lastly move it into Oracle BPEL.  Whew!  Our professional services guys are now almost Oracle experts.

Here's the demo.  It's 15 minutes long, but broken into sections so you can navigate around quickly.

Data Transformations in EII

From time to time I get asked about Ipedo's data transformation capabilities.  I was asked again a few weeks ago by a prospect, so I decided to put two of our developers to work.   I asked them to write a few posts that explain what Ipedo XIP can do in the area of data transformation.

Now we have two great entries that describe both halves of Ipedo's dual-core query engine: one on transforms using SQL, and one on transforms using XQuery.

Both of these just scratch the surface.  Just like the old Chinese expression "A long journey starts with the first step," these two entries only begin to expose the rich set of transformation capabilities available.  But they do cover some common cases in EII, which are often different from ETL needs. 

Let us know what you think.  If you want more examples, all you need to do is ask.  In the words of another Chinese proverb, "Dig a well before you are thirsty."

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.

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.

 

New XQuery Views

For those that missed it, Ipedo announced some exciting new XQuery View capabilities last week, just as XQuery 1.0 became an official standard (you can see my entry on that here.)

Anyway, I thought I'd summarize what we announced.  We think XQuery is going to have an even bigger impact now that the "When will it become a standard" question is out of the way.  Here are the high points:

• Processing of XML Stored in Relational Databases - Ipedo's XQuery can be used in conjunction with the XML processing and XQuery now available in Oracle 10g, DB2, and Microsoft SQL Server.
  
• XQuery View Caching - Ipedo XIP allows XQuery Views results to be cached natively in XML, providing both faster access for users, and post-processing and drilldown using XQuery.
  
• Streaming Result Processing - The new XQuery engine implements query operators that stream data values, ensuring that query evaluation is memory efficient and allowing clients to cursor through results at the lowest computation costs.
  
• Publishing of XQuery Views as Web Services - XQuery Views can now be invoked directly via a Web Services API, making it easy to create advanced data services. Ipedo XIP can automatically generate the WSDL file needed for a particular XQuery View.
  
• Extension Mechanisms for Custom Data Sources - In addition to standard data sources, Ipedo's XQuery extension framework now makes it possible for users to make custom data sources, such as in-house applications accessed using custom APIs.

Srinivas Pandrangi shows a simple example of using XQuery Views in his latest post over in The EII Files.

It's Official: XQuery 1.0 Now a Standard

I was pleased to see in my Inbox RSS Reader this morning news that XQuery has been made a standard by the W3C.  Here's the press release.

When we started Ipedo back in mid-2000, I remember all the vague nods when I would ask people if they knew what XQuery was.  Now, as you can see from the release, there are over 40 products that support XQuery. 

I would like to take a moment to thank all of the contributors to the spec.  As you can read, they have taken over 1000 comments and created over 14,000 (!) use cases, in a really short period of time.  And they have taken flak for it, which I debunked here, since XQuery became a standard in about half the time it took SQL (okay, so I was off by a month...).

For us at Ipedo, not only is this a vindication of our initial vision, but a tremendous opportunity for us and our customers.   The volume of XML has never been greater - from contract documents (ACORD, MISMO, FpML) to RSS feeds, it grows every day.  And with Ipedo being the only vendor with combined SQL/XQuery federation, it means Ipedo can federate over all that XML data in Oracle, DB2 and SQL Server.  Not only have we been keeping our engine up to spec over the years, even being one of the first vendors to run conformance tests, but our customers get the benefit of years of real world production use and tuning in the field.

So today, I answer this question for the final time: "Is XQuery a standard yet?"

A resounding "Yes."

Toward Simple Data Services

For those of you who might have missed it, we put out a new release of our flagship Ipedo XIP in December.  The theme was using EII in an SOA to create data services.

What we hear a lot when we're out talking to customers and prospects is that getting the data right in an SOA is hard, and that what's needed are tools to make it easier.   The last thing organizations want to do is write a lot of custom Java code just to get their SOA up and running.  Kind of defeats the purpose.

So what we've done is add a bunch of features to make it really simple and efficient to consume and publish Web Services.  You can see the full release for details, but here are the highlights:

• One-Click Publishing – The enhanced Web Services wizard allows simple one-click publishing of new Web Services, complete with security profiles, performance management, and automated WSDL generation.
  
• Dual Caching Option – Allows a Web Services View to be cached natively as XML, or in relational form, or both. Native caching improves performance and allows versioning of XML results. Using the relational option allows automatic decomposition of XML into relational form for use in other applications.
  
• Web Services Tables Metadata – Metadata enhancements for Ipedo's Web Services Tables display complete lineage and dependency information for inbound Web Services. Ipedo's Web Services Tables allow users to easily map XML-based Web Services into relational models.
  
• Join Hints for Web Services – Ipedo's query optimizer can now accept hints, including join strategy and join order, that minimize the number of Web Services calls needed to fulfill a query request, and thereby improve overall performance.
  
• XQuery for SOA – Ipedo's XQuery engine has been tuned to provide greater insight and better performance for SOA with a range of advanced functions, including XML payload introspection, fast transformation, content validation, and service metadata introspection. In addition, XQuery can be used to trigger subsequent 'cascading queries' based on the payload content of a Web Service.

I'd also like to put in a plug for our long XQuery heritage.  I think people are finally starting to realize how hard it is to deal with all the data in SOA payloads, and how good a tool XQuery is for the job.

Calculating the ROI of EII: Application Maintenance (Example 5/5)

Because the EII layer can easily handle additions or changes to underlying data sources, organizations can focus on maintaining the EII layer and not the maintenance of every application and its connection. If a data source changes, the administrator only needs to update the mapping in the EII layer; all reports remain unchanged as they use the schema defined in the EII layer.

To figure out if this can save you money, think about this question: How much does it cost to maintain point-to-point connections among all your applications and data sources?

                                                                                         

	Example	Formula
Conventional   Approach
Cost to   develop a customized connector between two applications ($)	$10,000	A
Number of   connections required per year	25	B
Application   maintenance cost using conventional approach ($)	$250,000	A * B
Cost to change   a report because a data source changes ($)	$1,000	C
Number of   changes to reports required per year	200	D
Application   maintenance cost using conventional approach ($)	$200,000	C * D
EII   Approach
Cost to   develop a customized connector between two applications ($)	$0	E
Cost to change   a report because a data source changes ($)	$0	F
Cost to change   a data source mapping in the EII layer ($)	$100	G
Number of   changes to reports required per year	200	D
Application   maintenance cost using EII approach ($)	$20,000	G * D
Potential annual   cost savings from EII ($)	$430,000	(A * B) + (C *   D) - (G * D)

In this example, a company that eliminates customized programming for connecting applications and changing reports will save $430,000 by using EII.

• What value-add projects could your IT team complete if they are not changing report formats?
• How much more motivated would your developers be if they spent less time on software maintenance?

That concludes our five-part analysis.  Hopefully you've found some areas where there might be cost savings.  If you did and would like to take things further, here are some suggestions:

1. If you want to use your own numbers in the above examples, you can request the companion Excel spreadsheet from Ipedo.
2. If your business situation requires a customized financial model, one of our analysts can provide assistance with the process, or even build an interactive spreadsheet based on information that you provide.
3.  Once you complete your financial models and convince your team that EII looks good on paper, a logical next step is to develop a proof-of-concept application that demonstrates cost savings and/or productivity improvement in a controlled environment. Ipedo can help with this as part of the sales process.

Calculating the ROI of EII: Data Consolidation (Example 4/5)

Managing data costs a lot of money.  Just ask any CIO.  In any given company, there is a lot of money spent (and wasted) moving and copying data.  Enter EII.

Using EII to create a virtual, centralized database that cuts across disparate systems can reduce the cost of data maintenance, and eliminate the need for additional investment to store replicated data. Even with a conservative estimate of $50K per data mart ($25K hardware purchase and software licensing, $10K development cost, $5 software maintenance license, $10K personnel to manage the data mart), this gets expensive quickly.

The ROI-unlocking question: How much of your IT budget goes towards moving data around the enterprise and storing copies for different applications to use?

                             

Top-Down   Approach	Example	Formula
Annual IT budget   ($)	$10,000,000	A
% of resources   spent on data migration and replication (%)	25%	B
% improvement   with EII (%) (estimate)	10%	C
Potential   annual cost savings from EII ($)	$250,000	(A * B) * C

In this example, a company that spends 25% of its $10 million IT budget on data migration and data replication would save $250,000 (2.5% of overall IT budget) by using EII to complement its other data integration initiatives.

 

                       

Bottom-Up   Approach	Example	Formula
Cost to deploy   single new data mart ($)	$50,000	D
Number of data   marts deployed per year	10	E
Potential   annual cost savings from EII ($)	$500,000	D * E

In this example, a company that can eliminate 10 data marts per year by using EII will save $500,000.

And with all the focus on CDI and MDM these days, these upside benefits are sure to resonate:

• What is the value of having a “single version of the truth” based in the original data sources, rather than copies of data that become obsolete once the source changes
• How much time do you spend reconciling different versions of data residing in different locations?
• What politics are involved in getting permission to replicate data in your local data mart?
• How could you better deploy your IT resources if they did not have to manage a data mart?

That's it for today.  Next time, our final installment.

EII Haiku

In high technology, we often do things that have never been tried before.  I'm fairly certain this is one of them. 

Since I was recently accused of being too serious with my ROI posts, but not wanting to give way to the senseless prattling of many blogs, I offer you EII Haiku.  I like to think of them as lighthearted but deep.  Enjoy.

Queries federated
Distant rows and columns meet
My warehouse unscathed

Web Services, SOAP
Loosely coupled, lightly known
Join them, EII

Data is scattered
Metadata, SQL meet
All becomes as one

P.S. - If you would like to share your own, please feel free to do so in the Comments section.