RSS and the XIP Platform

A call with a prospect this week took an interesting turn when the prospect expressed an interest in integrating the content from an RSS feed with other information that they had available through the XIP platform. For pure WOW Factor, what happened next should make our marketing folks very happy.

Over the next three minutes (we timed it), we pulled in our prospect’s favorite RSS feed and showed how they can use XQuery to filter and transform its content and make it available to their queries. For a bonus, we rustled up an Ipedo commander script that would automate the process for the future (so it would take seconds to repeat the demo). With RSS feeds gaining importance as a means of syndicating and distributing content, this could be of general interest for XIP users. So here is a synopsis of how to integrate RSS content into your information architecture.

The primary construct through which information is made available to the XIP platform is the View. So we start off by creating a view that represents the RSS feed. This is as simple as typing the URL and clicking a button. The snapshot along side illustrates this.

That done, you can use the view in any XQuery query (or in a SQL query through the use of the XMLTABLE operator that can transform XML to a tabular structure). For instance the following query:

 

{-- filter headlines, returning all with a given keyword in the title --} let $key := "AMERICAN" for $xbrlItem in view("publicWS/xbrlrss")//:item where contains($xbrlItem/title, $key) return $xbrlItem

 

Would return a result such as:

<item     rdf:about="http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/0001193125-08-005373-index.htm"     xmlns:edgar="http://www.sec.gov/Archives/edgar"        xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">       <title>NORTH AMERICAN TECHNOLOGIES GROUP INC /TX/ (0000808013) (Filer)</title>      <link>http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/0001193125-08-005373-index.htm</link>       <description>8-K</description>       <pubDate>Fri, 11 Jan 2008 15:32:02 EST</pubDate>       <edgar:xbrlFiling                 xmlns:edgar="http://www.sec.gov/Archives/edgar">         <edgar:companyName>NORTH AMERICAN TECHNOLOGIES GROUP INC /TX/</edgar:companyName>        <edgar:formType>8-K</edgar:formType>        <edgar:filingDate>01/11/2008</edgar:filingDate>        <edgar:cikNumber>0000808013</edgar:cikNumber>        <edgar:accessionNumber>0001193125-08-005373</edgar:accessionNumber>        <edgar:fileNumber>000-16217</edgar:fileNumber>         <edgar:xbrlFiles>           <edgar:xbrlFile                 description="FORM 8-K" file="d8k.htm" sequence="1"                 size="15621" type="8-K"                 url="http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/d8k.htm"/>           <edgar:xbrlFile                 description="XBRL INSTANCE DOCUMENT"                 file="namc-20070930.xml" sequence="2" size="37002"                 type="EX-100.INS"                 url="http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/namc-20070930.xml"/>           <edgar:xbrlFile                 description="XBRL TAXONOMY EXTENSION SCHEMA"                 file="namc-20070930.xsd" sequence="3" size="193491"                 type="EX-100.SCH"                 url="http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/namc-20070930.xsd"/>           <edgar:xbrlFile                 description="XBRL TAXONOMY EXTENSION CALCULATION LINKBASE"                 file="namc-20070930_cal.xml" sequence="4" size="938968"                 type="EX-100.CAL"                 url="http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/namc-20070930_cal.xml"/>           <edgar:xbrlFile                 description="XBRL TAXONOMY EXTENSION LABEL LINKBASE"                 file="namc-20070930_lab.xml" sequence="5" size="1154367"                 type="EX-100.LAB"                 url="http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/namc-20070930_lab.xml"/>           <edgar:xbrlFile                 description="XBRL TAXONOMY EXTENSION PRESENTATION LINKBASE"                 file="namc-20070930_pre.xml" sequence="6" size="1576867"                 type="EX-100.PRE" url="http://www.sec.gov/Archives/edgar/data/808013/000119312508005373/namc-20070930_pre.xml"/>         </edgar:xbrlFiles>       </edgar:xbrlFiling>     </item>

Since it can be queried using XQuery or SQL, this content can also be integrated into other views that access related content. The view can also be cached and the content indexed so that the performance of queries over the content can be fast. Yet another example of how a variety of data sources can be integrated and made available in different ways to consuming applications.

What good is an Information stream?

Information flows in Enterprise software architectures present applications with an opportunity and a problem. The opportunity, that is all too often ignored, is that observing these streams in transit can be a source of operational business intelligence. Aggregating, analyzing, interpreting and presenting intelligence from information flows can provide deep insights into the operations of business processes. The problem is one of efficiency and effectiveness; introspecting flows without introducing undue latency and at the same time being capable of performing sophisticated processing on those flows.

 
Ipedo’s XIP addresses a subset of the problem; that concerned with processing XML document flows. Since XML is a popular format for serializing information, a number of applications are found producing and consuming XML flows. XIP provides a construct named Feeds for processing streams of XML documents efficiently. Feeds are composed of one or more Stages, each of which performs a specific type of operation on the stream of XML documents. A number of stages can be chained together to achieve desired processing of an XML document flow.

Here is a screenshot of a feed definition. This feed operates on a stream of ACORD (Insurance industry standard) XML documents. It is composed of three stages - an XQuery stage which checks for the presence of a specific element, and if absent adds in the element with pre-defined default content; an XSLT transformation stage; and an aggregate index (more on aggregate indexes later) stage that keeps track of aggregates that apply to the entire flow.

To create a feed, you start by defining the stages that make up the feed. Each stage is identified by a name, and can be one of several kinds, as seen in the next screenshot.

• XML Rules can be used for semantic/business rule validation
• XSLT transformations can make structural changes to the document
• XQuery can be used to perform both transformations and queries that span the input document and a variety of data sources available to the XIP platform
• The Java DOM Filter can be used for custom processing coded in Java
• Compound Aggregate Indexes (CAI) can be used to keep running aggregates (counts, sum, average etc.) of values found in the XML document stream.

What follows are illustrations of how stages are defined. Here is an XQuery stage which accesses the input XML document to the stage using the input() function and can then use standard XQuery syntax in composing queries that can not only transform that input, but also enhance it on the basis of information queried from any external data source (web services, relational databases, flat files, custom java applications, you name it). These constructs make it possible to extend the processing power of feeds in virtually limitless ways.

Another example is a CAI (Compound Aggregate Index) stage that maintains indexes on aggregates computed on a flow of documents. For instance, you can keep track of the count of documents of a given transaction type, or you can keep a running total of order values for a given customer. These indexes can feed operational intelligence dash boards that will provide real time business monitoring capabilities.

The Feeds feature is interesting to anyone considering adding value to information flows by monitoring them, applying syntactic or semantic validation to those flow units, enhancing the quality of the data by filling in missing information or transforming that flow to match a target applications needs. The possibilities are endless.

Transformations with the XIP XQuery Engine

The last post touched in the transformation capabilities in XIP’s SQL engine. Now, we turn to its dual-core counterpart, the XQuery engine.

XIP can federate queries over relational, XML and other semi/unstructured data sources. On the consumption side, it can expose a virtual relational or XML database interface to applications. Inherent to this is the ability to transform data from various source formats to required target formats. The XML-2-XML transformation functionality in XIP is available through its XSLT and XQuery engines. These engines provide a standards based, powerful way of specifying any arbitrary transformation from one XML source format to another. In this post we will focus on the XQuery engine, but a number of ideas also apply to the XSLT engine in that both these engines share the same, extensible function library.

Typical transformation abilities in the data integration scenario include schema conversions including and not limited to changes in document structure, combining/splitting or otherwise transcoding content of elements and attributes, joining, filtering, union, aggregating/summarizing etc. XQuery provides constructs for all of these. I will show a few examples and give some direction as to how the platform can be extended to achieve more complex transformations. As an EII platform, XIP does not have nearly as comprehensive a transformation tool set as you would expect from a leading ETL vendor. But this is also appropriate, since real time data integration is typically not suited for very complex or computationally expensive transformations.

Functions and Operators:

To start with the basics, XIP’s XQuery engine comes with an extensive function library which includes all the basic functions for string manipulation, numerical functions such as floor/ceiling/round, aggregate functions such as avg/min/max/count etc., data type conversions, date manipulation functions and so on. Also available are a number of operators for number and date arithmetic. This is a rich set of functionality for creating new values from source data.

Structural Transformations:

Utilizing the path expressions, Conditional expressions, Element constructors and constructs like FLWOR, there are virtually limitless ways you can process a source document structure and construct an arbitrarily complex output XML structure. I will illustrate this point through a transformation analogous to a pivot in the relational world. Consider the sample document shown below:

Converting this structure to use one in which the data values become element names is a simple matter of using computed element constructors along with a FLWOR construct.

You can see that an approach like this can tackle arbitrarily complex structural transformations.

Unions are another common operation in an ETL scenario. A simple a query that combines a series of customer records from an XML view over on Oracle database and a view over the salesforce.com web service would be written as follows:

<LargeCustomers>

            {view(“oradata/crm”)/Customers/Customer[NumEmp > 5000]

              union

            view("salesforce.com/Account")/AccountList/Account[NumberOfEmployees > 5000]

            }

</LargeCustomers>

The expressions that generate the customer elements (in this case, the path expressions) can be arbitrarily complex XQuery expressions.

Extensibility

XIP’s XQuery engine also allows custom transformations by allowing you to code custom external functions in Java. This allows for just about any transformation that you can dream up. The XQuery modules extension feature also allows you to package up common enterprise transformation functions as a library that can be shared by other application developers and data architects.

What you have seen in this post is but a glimpse of XML transformation capabilities in the XIP platform. Get a download and start playing with these features to see what else you can do.

Data Transformation Using the Ipedo SQL Engine

There are lots of times when you need to transform data in a query federation scenario. In this section, I will discuss data transformation capabilities built into Ipedo’s SQL engine.

Ipedo’s SQL engine is based on industry standards and implements SQL92 and SQL99 features. It supports all core data types, SQL statements and built-in functions, thereby providing transformation capabilities for data conversion, data transformation (or conditional transformation) and data manipulations. Furthermore, when used in conjunction with advanced features such as user defined function in the SQL engine user can even integrate or interface with other BPM or messaging products. However, I will postpone the discussion of user defined function to the next post. In this post, let’s look at examples showing various ways data transformation can be implemented using the SQL engine.   

Example 1: Adding New Column Data Base on Country

Let’s first execute a query “select customerid, companyname, contactname, address, city, postalcode, country from customers” and look at the output shown below:
Notice the output: it shows a list of customers and the countries where customers are located. However, there is no indication of the region where customer’s country is located. Let’s rewrite the query and add a column with region information based on the country. We’ll use a case expression to conditionally specify region for different countries. For simplicity, we’ll categorize all countries in North and South America as in region “Americas” and all European countries as region “Europe”.

select customerid, companyname, contactname, address, city, postalcode, country,
case when country='Brazil' then 'Americas'
when country='USA' then 'Americas'
when country='Venezuela' then 'Americas'
Else 'Europe'
End as region
from customers
order by region

Now, execute this query and look at the output shown in the screen below. You’ll see a new column “Region” and it display the region values either Europe or Americas. You can use the same technique to check for data errors and other purposes as well.

Example 2: Checking for Error Conditions

In the example above, I’ve made an error that you may have already noticed. I forgot to include countries such as Argentina, Canada and others in the Americas region. As a result, they’re mistakenly being categorized into the Europe region. Let’s write another query to catch those errors.

Again we add another case expression to check for each country and region value. If we find an error, we’ll display “ERROR: WRONG REGION CODE DETACTED” next to the country under a new column “Error”. For countries that are correctly categorized, we’ll display “NO ERROR FOUND”

select customerid, companyname, contactname, address, city, postalcode, country,
case when country='Brazil' then 'Americas'
when country='USA' then 'Americas'
when country='Venezuela' then 'Americas'
Else 'Europe'
End as region,
Case when (country='Argentina' or country='Canada' or country='Mexico')
      and region = 'Europe'
     then 'ERROR: WRONG REGION CODE DETACTED'
Else 'NO ERROR FOUND'
END AS error
from customers
order by region, country

Execute the query and here’s the output.

Example 3: Concatenate Data and Convert Data Types and Currency

Let’s look at the output below by first executing the query: select orderid, customerid, freight, shipcountry from ADMIN.ORDERS.

Notice the amount of freight charges are all in US dollars for all countries, including both European countries and Americas countries. Secondly, there’s no currency symbol displayed. Here is what we are going to do. We’ll look for all countries in the Europe region (as we classified in our previous queries) and convert their freight charge from US dollars to European common currency Euro and use letter “e” as the symbol for the Euro. We will then convert the freight charge from decimal type into a character string and concatenate the currency symbol with the freight charge in character form. Here is the query:

select orderid, customerid, freight,
case when (select region from exchangerate where shipcountry=country)='Europe'
then 'e' || cast(freight*(select rate from exchangerate where country =           shipcountry) as char(20))
when (select region from exchangerate where shipcountry=country)= 'Americas'
then '$' || cast(freight*(select rate from exchangerate where country = shipcountry) as char(20))
end as localizedFreightCharge,
shipcountry
from ADMIN.ORDERS

The “||” symbol is the operator for concatenation and the cast function is used to convert decimal data type to character type. Finally, the currency exchange rate we use for calculation and determine which country uses what currency is by using a sub query lookup.

That’s it. As you can see, you can do a lot data transformation just with the SQL language.

Building and Leveraging Web APIs

A number of useful services including Google, Amazon, EDGAR Online and Salesforce.com are now accessible on the internet through web APIs. The popularity of Service Oriented Architectures has led to similar techniques being used within enterprises to expose services on the intranet. As opposed to conceiving a SOA from the ground up, owners of data or application Silos can expose their offering as a service to the rest of the enterprise, allowing integrated applications to grow organically. Knowledge of a service that is available within the enterprise can trigger the creative thought needed to conceive of new ways to exploit those services to achieve greater productivity or new revenue streams.

 
We run into a number of examples of these in our interactions with our customers. Banks typically have a number of these services available enterprise wide – currency exchange rate information, credit ratings, financials, etc – which allow multiple applications to leverage this master data sources. We have seen configuration management systems used this way in IT firms, the data from which becomes fodder for risk analysis engines that determine patches that need to be applied to any system. The examples are many, but the underlying theme is the same. Applications and data are like Lego blocks. You can put together these blocks in a number of ways to solve increasing complex problems with great revenue potential. The only requirement is that these blocks have any interface to the outside world that lets them plug together.

 
This is nothing new. It has been done dozens of different ways in the past with Corba, DCOM etc. What might be new today is the fact that the current approach leverages a platform that is ubiquitous, language/development platform neutral and proven to be scalable. Utilizing basic building blocks like HTTP and XML(or JSON if you are one of those critical of XML for its overhead) you can quickly expose a service to the world. At Ipedo, we have come to embrace this approach to application development. Building views, either from scratch or by wrapping existing services, bringing together views to provide some new integrated functionality, and exposing this functionality again as view, is at the heart of the development methodology. The trick then is to make this system perform well, optimizing the functionality through methods such as pushing-down operations, caching and a host of other rule and cost based optimizations.

 
A standard portion of the Ipedo demos to customers includes examples of how these things I just described can be done. A bunch of examples involving public services like Google, Amazon, Salesforce are available, along with examples of how to build your own services. Check them out.

JSON and Ajax

You are familiar with Ipedo's strong XML credentials, but some of my colleagues have recently done some very interesting work on an open source project supporting JSON, a competing/complementary data format. JSON (JavaScript Object Notation) is a light-weight and simple way to package information to be interpreted in a way that is very natural for JavaScript. The project I am referring to is called ChaiDB, a project on SourceForge. ChaiDB is a general purpose database library that allows applications to build embedded databases. In that way it is similar to BerkeleyDB.

The project also includes a sub-project, JSON-Cache, which is an application of ChaiDB. As the name suggests, this project implements a method to cache JSON objects. This could be particularly useful now that Ajax services are starting to take significant interest in JSON. Given that JSON is a less verbose, less type-stringent alternative is attractive to Ajax services which don't particularly need that capability of XML. The cache will be helpful for those implementors that need that extra boost to performance that JSON and JSON-cache will afford them. Be sure to check it out.

Using Ipedo XQuery Views

Ipedo’s XIP includes an XQuery engine that is one of the most mature implementations in the market. This XQuery engine has been deployed by a number of large enterprises for more than three years. It is also unique in its ability to query not only collections of XML documents, which is the best known use of XQuery, but also relational databases, web services, common data formats like CSV and fixed length formats. In addition, the Ipedo XQuery engine also allows users to create custom data sources and make them available to XQuery developers. All this extends the power of the XQuery engine well beyond basic XML query.

 

The XIP XQuery engine implements a large built-in function library and also allows users to develop library modules. Library modules are collections of user defined functions that can be reused across queries. This helps in modular development.

 
Additionally, the XIP XQuery engine supports the concept of a view. Once a view is created, it can be accessed using the view()function. Here is an example of how it can be invoked.

 
(: Show all customers in Brazil :)

<Brazil> {

  for $c in view("sqlserver/customers")/table/row

  where $c/Country = 'Brazil'

  return   

    <customer>{

           $c/CompanyName, $c/Address, $c/City

     }</customer>

}</Brazil>

 

The view named “sqlserver/customers” is a view created over the customers table in a sqlserver database. Once the view is created, this table is accessible to XQuery developers. In this query, for instance, the developer is retrieving all the customers in the table whose Country attribute holds the value “Brazil”. The XQuery engine does intelligent optimizations to execute the most efficient query possible against the sqlserver instance, retrieving the smallest data set possible. Sophisticated queries with complex join conditions can be executed against the multiple backend data sources. Views can also be defined over web services, XML sources accessible over HTTP, CSV files, Fixed length format files, and any custom data sources for which the user can program access.

 
These capabilities make the XIP XQuery engine a unique offering in the Enterprise Information Integration space. In combination with the distributed SQL query engine, also offered in XIP, this set of capabilities represent one of the most powerful ways of querying and integrating distributed data.

The One-click Web Service

Another post from Jamie Wang, Ipedo's Director of Engineering. He's rapidly becoming one of my regular guest bloggers. Enjoy. -S.P.

Many developers have wondered how much effort it takes to expose the information they have combined in an EII server to an SOA. In the XIP 4.2 release, the process to publish virtualized relational and XML information as a Web Service has been greatly simplified.  So simple, in fact, that we call it the “One-click Web Service.”

In this new release, Ipedo has introduced a new concept called “Data Services.”  This is an area where all of the views are published and made available as a service.  The release was designed to meet both the needs of team development and better support SOA.

Under Data Services, you will see that Ipedo XIP supports three types: Web Services, SQL Data Services and XQuery Data Services. These are all based on industry standards. The Web Services model supports SOAP binding and WSDL. The SQL Data Services model uses standard JDBC access and has full SQL 99 compatibility. The XQuery Data Services model supports XQuery 1.0. This is a very powerful combination of standards support. Just imagine how you can access information no matter what data access standards they require.  All of this is only one click away.  In this entry, we’ll focus on the Web Services model.

Creating a Web Service

The first step in publishing a view is to add a Web Service and give it a name. We’ll call this one ‘CRM Data Service,’ which will publish data from relational databases, as you can see the screen below. We click OK to save the Web Service.

Publish a View

The next step is to decide which view you want to expose to your user as the CRM Data Service. Here I have a view CUSTOMERORDERS that combines customer information in Microsoft SQL Server and order information from Oracle. Next use the Publish Wizard to publish the view. See the screen below. We click OK and voila, the view is published as a Web Service.

We wanted to write a longer post, but that’s all it takes.  Really.

From ER Modeling to XML Web Services Using Ipedo XIP

A guest post from Jamie Wang on ER modeling and Web Services. Jamie is Ipedo's Director of Engineering. -S.P.

One thing we hear more and more about from customers is wanting to service-enable existing relational databases.  With Ipedo XIP, it’s quite easy to do.  You can even use existing data models from ER modeling tools to speed things up.   Let me walk you through the process.

In the XIP 4.2 release, exposing relational data through Web Services was made even easier, now requiring just a few clicks. For this entry, I will use the same HR model used in Peter’s last installment “Reusing Ipedo Views in Modeling Tools”. I will first explain how to use graphical modeling tool as an aid to understand complex relationships among relational data and I will then demonstrate how to quickly build Web Services to deliver data to the user community using Ipedo XIP.

Understanding ER Diagrams and Data Relationships

The diagram above shows a main entity Employee and other entities that have direct or indirect relationships to the Employee. The relationships are represented by the edges connecting entities. For example, looking at the diagram you can understand that some of Employee’s attributes, such as employee name and hire date is described in the Employee entity. Other attributes, such as department and location, are described in other entities - Departments and Locations respectively. Moreover, looking at attributes under each entity closely, one can gain another level of understanding of the relationships how Department is related to Employee and Countries (attribute relationships, defined primary and foreign keys, and constraints etc.).

Identify and Build Aggregated Information View

Now, with better understanding of various relationships in the diagram, we can methodically identify and build aggregated information for a specific subject. The employee detail view in the diagram below shows all information you need to know about an employee and is presented in a single entity (or an aggregated view of many related entities).

Publishing Ipedo View as Web Services

There are a number of ways you can build views easily in Ipedo. For example, you can map existing relational views as a remote table in Ipedo, or use the Ipedo View Builder to combine views, remote tables and XML tables to create additional views. You can even write your own SQL DDL statement to create views in Ipedo. Here, I will show you how to map an existing view already built in a remote database and publish it as a Web Service.

To create a view combining disparate remote databases in Ipedo, you’ll need to define one or more remote data sources first. This provides the necessary connection information and credential for accessing to the remote information.

Creating Data Source and Remote Table using Ipedo Administrator

Right click on Data Source element in the navigation tree in the Administrator and choose New Data Source. Entering appropriate information in the New Data Source wizard and create a HUMANRESOURCE data source as show in the picture above.

Next use remote table wizard to map a relational view as Ipedo remote table EMP_DETAILS_VIEW. Right click on Remote Tables element in the navigation tree. Choose New Remote Tables and following instructions of the Remote table wizard as shown below.

Create a Web Service Name

In the navigation tree, left click on Data Service and right click on Web Services to enter the Web Service name HRDataSvc as in the picture below.

Use Web Service Publishing Wizard to Publish EMP_DETAILS_VIEW

Right click on EMP_DETAILS_VIEW under remote tables and choose Publish. In the publishing wizard, select Web Service type and select HRDataSvc. Enter a name for the remote table you want to publish as Web Service. By default, the name of the remote table name is used in the name field. Enter a description if you want to. Click OK to save and the Web Service will be displayed under Data Services as shown in green.

That’s all it takes to publish relational data as Web Services. In fact, this process can be used to publish any constructs in the relational model whether it is a remote table as shown here or a XML table or a dimensional view.  If you have a lot of tables to combine and publish, everything you’ve seen here can be easily scripted.

In the next installment, I will show you different ways you can consume Web Services data using Ipedo.

Reusing Ipedo Views in Modeling Tools

Part two of Peter Spielvogel's post on using Ipedo with external modeling tools. Peter is Ipedo’s Director of Product Marketing - S.P.

In my last post, I discussed how to use a data modeling tool such as such as CA ERwin, Embarcadero ER/Studio, or IBM Rational Rose to build virtual tables in Ipedo.

Another situation that customers ask about is how to share the views they build in Ipedo with other architects that need to understand the relationship among tables and how disparate data sources are joined to one another.

Again, my colleague, Jamie Wang, Ipedo’s Director of Engineering, identified this straightforward process and trained me how to do it.

In this case too, Ipedo’s ability to behave like a relational database allows data modeling tools to reverse engineer the schema objects and relationships among them. Let’s see how this works.

 

For the purpose of this example, we have created a data model in Ipedo joining some human resources tables. The Ipedo Administrator console appears below.

 

Next, open your modeling software. In this case, we are using CA ERwin. Select the option to Reverse Engineer a database.

 

Select the options and which driver to use. We’ll choose to reverse engineer Ipedo’s Logical/Physical model and use the generic ODBC driver.

 

Choose what information from the database (Ipedo in this case) you want to extract. Since our interest is in reusing the view (model) we built in Ipedo, we want ERwin to reverse engineer everything - tables, column names, primary and foreign key relationships, etc.

Click next and the data model from Ipedo appears in ERwin. Now, you can reuse the information, annotate it according to your corporate guidelines, and combine it with other enterprise data models.

 

So, now you can do your bottom-up data integration using Ipedo, and share the data models with other applications using your favorite modeling tool.