In the previous post, I laid out a data visibility problem and proposed using RSSBus to build an enterprise mashup that inflates a single data entity whose attributes reside in multiple disparate systems.
Before a mashup can be built, we actually need the source data in a ready-to-mashup format. For the mashup I am building, my “contact” data resides in 3 different repositories:
- Database containing the interactions we’ve had with a particular contact
- Web service talking to a CRM system which holds core contact information
- Excel spreadsheet containing external reference data such as the contact’s public web page and blog
On top of this, my mashup will also return a Google result set based on the contact’s first and last name. If available, I also want to retrieve the latest information from the contact’s personal blog (if they have one).
In this post, I will show how to create the feeds for the database, Excel spreadsheet, and Google query.
Building an Excel Feed
My first data source is an Excel spreadsheet. The Excel Connector provided by RSSBus has a variety of operations that let you add rows to sheets, list worksheets in a workbook, create new workbooks, and get data out of an existing spreadsheet.
For our case, we used the excelGet operation that accepts the file path of the workbook, and which sheet to pull data from. A simple test can be executed right from the connector page.
The result of this query (formatted in HTML) looks like this:
Notice how the Excel data comes back using an “excel” namespace prefix.
In my case, I don’t want to return the contents of the entire workbook, but rather, only the record for an individual contact. So, from this Connector page, I can choose to create a feed out of my sample query, and then I can modify the RSBScript to filter my results, and, to put my result set into a different namespace than “excel.”
At the top of my new feed, I outline the title of the feed, and, create a new required input parameter named “contactid.” Input parameters are passed to the feed via the querystring.
Next, I need to set the parameters needed by the Excel Connector. You may recall that we set the parameters when we tested the Connector earlier.
Now comes the meat of the feed. Here I “call” the Excel operation, do an “equals” check to see if the row in the spreadsheet is for the contact with the designated contact ID. If I find such a row, then I create a new “myitem” entity and populate this hash with the values returned by the Connector and sitting in an “excel” namespace. Finally, I “push” this item to the response stream.
So what does this completed feed look like? Below you can see our feed item containing the nodes in a “reference” namespace. I now have a valid RSS feed that monitors an Excel workbook. Hooray for me!
Building a Database Feed
Now, let’s take an existing SQL Server database and return some results as RSS. In my scenario, I have a table for all our contacts, and another table with all the interactions we’ve had with that customer (e.g. lunch, office visit, speaker invitation). The RSSBus SQL Server Connector has a wide range of operations available which perform database inserts, updates, deletes, as well as stored procedure calls, and schema queries for tables, views and more.
This feed starts much the same as the last one with a title and description. I’ve also added a required input parameter for the contact ID stored in the database. Next I have to set the parameters (connection and query) needed by the sqlQuery operation.
Note that most connector operations have a wide range of optional parameters. For the sqlQuery operation, these optional parameters include things like “maxrows” and “timeout.”
Now I need to call the operation. Like the feed above, this feed takes things that come back in the “sql” namespace and put it into an “interactions” namespace. Be aware that the “push” statement pushes EACH returned row as a separate feed item.
What does my response look like? The image below shows how I returned three items in this feed; each represents a different interaction with this contact.
Now I have two feeds based on existing data repositories, and didn’t have to make a single change to those applications to support their RSS-ification.
Building a Google Feed
The final feed we’ll look at here is public internet search for our selected contact. The Google search results should come back in an RSS format that can be consumed by my mashup feed.
My feed takes in two required parameters: “firstname” and “lastname.” Next, I need the two critical parameters for the Google gSearchWeb operation. I first must pass in a valid Google API token (you’ll need to acquire one), and, the search string.
Now I call the operation, and push each result out.
Neato. I can call Google on the fly and make custom queries based on my contact.
Here we saw how easy it is to build the RSSBus script necessary to expose RSS feeds from systems that don’t actually speak RSS.
Next, we’ll see how to work with new RSSBus SOAP connector to query our CRM system, AND, to query my “object registry service” which returns all the system primary keys related to my contact. After that, we’ll see how to mash all these feeds up, and return a single “contact” entity to the RSS client.