The promise of service oriented architectures has been immense, people have rightly pointed out that SOA can lead to better integration, bring IT closer to the underlying business processes, and most importantly, impact a company’s bottom line. The competitive advantage an enterprise gains by aligning its IT to its business strategy is well known and studied. [1]

The trouble is that our world is changing rapidly; business processes of the past are soon archaic in the face of new business relationships, and mergers and legislative changes cause disruption. It is becoming hard to bring new solutions to fruition in time, and harder to maintain the existing ones such that they are re-usable. [2]

Technical visionaries have taken note of this problem, perhaps Nicklas Malik, Application Architect at the Micorsoft IT Technology office, puts its best in his post "Malik's Laws of Service Oriented Architecture":

Malik's Laws of Service Oriented Architecture

• No one but you will build the services you need in time for you to use them
• If you build a service that no one else asked for, you will have built it for yourself
• If you build a service for yourself,  you will optimize it for your own use
  • It is therefore the optimal service for you to use
  • It is very unlikely to be the optimal one for anyone else to use
  • No one besides you will use it
  • You will not use anyone else's

Implication

• Therefore, any team building reusable services must build each one only after two or more people have asked for it, with full knowledge that the resulting service will almost certainly be available too late for any of them to use it.
• Therefore, no team should intentionally build reusable services.

Additional Laws and Corollaries

• If you invest in improving someone else's pre-existing service, you will create a reusable service.
• Creating a reusable service, by improving someone else's service, will cost you more, up front, than writing a completely new one.
• The cost of maintaining a service increases proportionally to the number of consumers that use it.

A major objective of RSSBus is to enable the creation of 'Really Simple Services' - to cut the time to create that first service and to do it in a way such that inevitable changes are not costly. In the next two articles, "SOA Agility: Re-Usable Connectors with Simple Service Configuration" and "SOA Changeability: Data Formats amenable to change" we will explore our solution to the costs of time and change.

This is the last of three postings that show how to 1) capture RSSBus service data in Excel, 2) update the data when the worksheet is opened, and 3) update the data when cell values change.

In order to run the examples, you will have to download the zip file as explained in Part 1.  You should also work through Part 2 as we pick up from there. 

Here is the service definition that we will be using today, FileList3.rsb:

<rsb:info title="FileList3" desc="Feed with required path and mask">

  <input name="path" desc="Directory to list" default="." required="true" />

  <input name="mask" desc="File match pattern" default="*" required="true" />

</rsb:info>

 

<rsb:call op="fileListDir" output="out">

  <rsb:push title="[rss:title | def('untitled item')]">

    [out.*]

  </rsb:push>

</rsb:call>

Recall that required="true" in the info statement gets Excel to prompt for a value for path.  Try this URL:

http://localhost:1110/FileList3.rsb?@xls

Select 'open' on the pop-up, and when prompted enter . (period) for the path and * for the mask.  Excel should load the directory listing for you.

Update data when cells change:

Click the URL above again, but this time select 'save' and save the .iqy file on your desktop.

Now open Excel manually and create a worksheet like this, which will serve as our input form.  The values in column B are period and asterisk, in case you can't read them:

While still in Excel, open  Data -> Import External Data -> Import Data...    In the file open dialog box that appears, navigate to the IQY file you saved above and double click.  Excel will ask you where you want to put the data.  Click an empty cell a little further down on your worksheet and then click 'OK'.

When you get the 'Directory to list' popup, click inside cell B1, where the path value (.) is.  Check the 'Use this value…' and 'Refresh automatically…' checkboxes, then click 'OK':

Do the same for the 'File match pattern' popup, but point to cell B2 where the asterisk is. After you click 'OK', Excel should fetch the data.  Try changing the mask value to *.rsb, or the path to demos, and hit carriage return.  Excel should refresh the data automatically.

For a more interesting example, try building an Excel form for AmazonSearch.rsb, included in the zip file.  You may have to download AmazonOps if you don't have it in your connector library.

This is the end of our series of getting business information into Excel.  To learn more about RSSBus, read the RSSBus Feed Server Quick Start Guide.  To learn more about IQY files, read XL97: How to Create Web Query (.iqy) Files.

This is the second of three postings that show how to 1) capture RSSBus service data in Excel, 2) update the data when the worksheet is opened, and 3) update the data when cell values change.

In order to run the examples, you will have to download the zip file as explained in Part 1.

Update data on open:

Instead of returning CSV data as was shown Part 1, we can have RSSBus return an IQY (internet query) file that contains data import directives.  Excel will execute the directives every time the worksheet is opened, or every time you click “Refresh Data” in the Excel Data menu. 

To have RSSBus return an IQY file, use the @xls format parameter on any RSSBus call (this has the same effect as selecting “Excel” under “show feed” in the RSSBus Admin Console):

http://localhost:1110/FileList1.rsb?@xls

RSSBus will return a file called RSSBusfeed.iqy that you can save or open.

When you click the URL and choose open, the browser will start Excel, which then opens the IQY file and executes the commands inside.  They tell Excel to go back to the same script, but this time request the output in HTML format using the @html parameter.  The data is returned in an HTML table that Excel can recognize, parse and display.

Call the URL again but this time save the IQY file.  Double click on the IQY file, and Excel will fetch the data as before.  Open the IQY file with Notepad and look at the data fetch commands inside.  You will see the call-back to the same URL with the HTML formatter.

Getting Excel to prompt for RSSBus input parameters:

Look at the service definition FileList1.rsb below, it includes an rsb:info directive that provides a default value for the path input parameter to the fileListDir operation:

<rsb:info title="FileList1" description="Feed with default path">

  <input name="path" desc="Directory to list" default="." />

</rsb:info>

 

<rsb:call op="fileListDir" output="out">

  <rsb:push title="[rss:title | def('untitled item')]">

    [out.*]

  </rsb:push>

</rsb:call>

If we make the path parameter required, like this (see FileList2.rsb):

<rsb:info title="FileList2" description="Feed with required path">

  <input name="path" desc="Directory to list" default="." required="true" />

</rsb:info>

...

RSSBus will add a directive to the IQY file to have the browser prompt for the value for path just before executing the data fetch.  Try this URL:

http://localhost:1110/FileList2.rsb?@xls

If you select ‘open’ in response to the browser open-or-save pop-up, Excel will start and prompt for the value for path.  Type . (a single period) and hit return.  Excel will complete the call to RSSBus and display the returned data.

Click  URL again, but this time save the IQY file, and open it with notepad.  Note the directive that tells Excel to prompt for the path parameter:

WEB

1

http://127.0.0.1:1110/FileList2.rsb@html

path=["path","Directory to list"]

...

As an exercise, try adding support for the mask parameter of the fileListDir operation, by adding a new line for mask to the info directive of FileList2.rsb.   When Excel prompts for a value for a mask, try * or *.rsb.

In Part 3 we will use cells in the spreadsheet as an input form to the service.

To learn more about RSSBus, read the RSSBus Feed Server Quick Start Guide.  To learn more about IQY files, read XL97: How to Create Web Query (.iqy) Files.

 

This is the first of three postings that show how to 1) capture RSSBus service data in Excel, 2) update the data when the worksheet is opened, and 3) update the data when cell values change.

Excel is the information processing tool of choice for many business and scientific users, and RSSBus supports these users with powerful data import features that bring all kinds of web and enterprise information directly into Excel.

We will use a local directory listing for our first example.  You can easily modify the example to work with many other information sources by changing the connector used.  Please visit the RSSBus Connectors page on the web site to see the full range of connectors available.

If you want to run the demo yourself, first download and install a trial copy of RSSBus Desktop from the RSSBus Downloads page, and then download this zip file containing the example files.  Place the .rsb files in the www/ directory of your RSSBus installation, and CVSFeedFormatter.dll in the bin/ directory of your RSSBus installation. 

Here is FileList1.rsb, which simply generates a list of files in a directory.  This file is included in the zip:

<rsb:info title="FileList1" description="Feed with default path">

  <input name="path" desc="Directory to list" default="." />

</rsb:info>

 

<rsb:call op="fileListDir" output="out">

  <rsb:push title="[rss:title | def('untitled item')]">

    [out.*]

  </rsb:push>

</rsb:call>

To view the file listing as RSS, call this URL:

http://localhost:1110/FileList1.rsb

Capture service data in Excel with the CSV formatter:

In order to get this file listing into Excel, we'll use a feed formatter.  A previous tutorial presented the CSV (comma separated values) feed formatter.  The CSV formatter returns mime type application/vnd.ms-excel with the data formatted like this:

"Last name","First name","Phone"

"Jones","Bob","123-4567"

"Smith","Joe","345-7890"

Microsoft Excel can directly read and write this format.  You can invoke the formatter by calling any RSSBus operation with the @format=csv parameter like this:

http://localhost:1110/FileList1.rsb?@format=csv

Your browser will ask if you want to save the .csv file, or open it immediately with Excel. 

If you are interested in seeing how the CSV formatter works, you can get a copy of the source code from the zip file in the CSV formatter tutorial.

In Part 2 we will learn how to update the data every time the spreadsheet is opened by using IQY files.

To learn more about RSSBus, read the RSSBus Feed Server Quick Start Guide.