This mini tutorial is designed for business users who want to learn how to create a real-world mashup that retrieves and displays data stored in various Microsoft Excel spreadsheets.
In this scenario, you work for an insurance company, and you have been assigned the task of creating a mashup that allows you and your coworkers to be able to search on a customer ID and retrieve information about that customer, including the following:
- Contact information
- Information about the agent assigned to the customer
- Insurance policy information
- Revenue history
- Unresolved issues
- A map of resources near the customer, including office buildings, other agents, and recommended hotels
Let's start by showing you a glimpse of what your final mashup will look like after completing this tutorial. Notice the search box at the top of the browser where you can type and then submit a customer ID. After you click
Submit, the other areas (widgets) on the page refresh with that customer's information:
To see a demonstration of the final mashup in action, click Video A:
Video A
Now that you understand the purpose of the mashup and have seen it in action, let's walk through the basic steps required to create the mashup:
Step 1: Locating the customer data and creating feeds
In this step, you will locate the customer data used in your mashup and create a feed from the data source so that you can use the data in your mashup.
The customer data is located in a Microsoft Excel spreadsheet that your department maintains. The spreadsheet contains the customer ID, name, address, zip code, the assigned agent, and the sales channel. Click here to download the spreadsheet to your local machine.
Your next step is to make the data in the spreadsheet "mashable." In order to do this, you will need to create a feed of the customer data. That way, when you design your mashup page with the submit form, you can make the customer data available to the entire page.
Click Video B to see how to create a feed from the spreadsheet so that your mashup can retrieve and filter information for individual customers:
Video B
Now, what if your data is located in a relational database instead of a spreadsheet? You will still need to create a feed, but unlike a feed created from a spreadsheet, you will not need to create a separate feed mashup to filter the data, as shown in Video B above. With a database feed, you can perform the query using a SQL statement, and you can configure your SQL statement to pass in parameters, such as the customer ID. The database feed alone allows you to retrieve a particular customer from a database.
Click here to see a lesson about how to create a feed from a database. Note that this lesson does not show you how to create a SQL statement for passing in variables, however. The general SQL syntax is as follows:
select * from myTable where myKey = ':myParamName'
Using the above SQL syntax, the URL to the output feed includes a URL parameter named myParamName. You can find more information about SQL queries and creating feeds for relational databases in the IBM InfoSphere MashupHub User and Administrator Guide. In Chapter 2, go to Step 5 in the following topic: Overview of Feeds > Creating and Registering Feeds > Creating a feed from a relational database query (SQL).
Step 2: Creating your mashup page
In this step, you will learn how to create your mashup page. Your page will contain the company logo, a form for submitting the customer ID, and areas for displaying the customer data.
Click Video C to see how to create a mashup page:
Video C
Step 3: Displaying multiple customer policy information in a tabbed view
In this step, you will download a spreadsheet that contains customer policy information in five separate worksheets and then create five separate feeds -- one for each of the five worksheets. Here is a screenshot of what the mashup page will look like:
Click here to download the spreadsheet.
After viewing the spreadsheet, you will notice that it contains a lot of policy information for each individual customer. Since this information would most likely take up the entire mashup page, you will design your page with a Tabbed View widget. This widget allows you to display the customer information in tabs and save space on the page. The Tabbed View widget is currently not available in IBM Mashup Center, but you can click here to download and install an early version of the widget. Be sure to accept the license agreement. You will need to add the widget to the catalog and then add it to the Lotus Mashups toolbox.
Similar to what you saw in Video A above, you will first create a feed from the spreadsheet, and then create a feed mashup to filter data for a particular customer. Click Video D to see how to do this for one of the five worksheets in the spreadsheet. You will need to repeat this process for each of the remaining worksheets. Be sure to specify the worksheet number for feed.
Video D
Since all the data in the worksheets use customer ID as the key, you will be able to filter the data based on that value.
Now you should have five feeds and five feed mashups. Click Video E to see how to display the feeds in the Tabbed View widget:
Video E
Step 4: Configuring the chart
In this step, you will configure a chart to display the revenue history for each customer. Here is a screenshot of what the chart will look like in your mashup:
In the spreadsheet that you downloaded earlier (policydata.xls), notice that one of the five worksheets contains the revenue history for each customer. You could display this data in a Data Viewer widget, but it will be much more interesting to display the data in a graphical view. Mashup Center provides a Chart widget that can handle most Atom feeds / XML. Click Video F to see how to display the revenue history in a line chart, based on the customer ID that users submit.
Video F
Step 5: Merging different data sources
In this step, you will merge two data sources into one XML feed. You will do this with two spreadsheets, but you can do this with other data sources as well, for example a DB2 database or mySQL.
Each of your customers has an assigned agent. When you submit a customer ID in your mashup, you want to display the assigned agent's information along with the customer information. To do this, you need to merge the customer information with the assigned agent information. In Step 1, you downloaded the spreadsheet (InsuranceCustomers.xls) that contains the customer information. The agent information is located in a separate spreadsheet that you can download here. Your mashup uses the agent IDs to know which agent is assigned to which customer.
Since you have two data sources (two spreadsheets), you need to create feeds from them and then merge the feeds into one XML feed. Then, when you click a customer ID in your mashup, you will be able to see the information for the agent who is assigned to that customer. Here is a screenshot of how your mashup will look:
Click Video G to see how to merge the two data sources into one XML feed:
Video G
Now click Video H to see how to display the agent information in your mashup:
Video H
Step 6: Displaying information in a map
In this step, you will learn how to display data in a map. Specifically, you will learn how to manipulate data and display it in the OpenStreetMap widget.
Before you can display data in the OpenStreetMap widget, you first need to create a feed of data that the OpenStreetMap widget understands. This widget is designed to plot zip codes as well as longitude and latitude coordinates. If you have a feed that contains a list of addresses that you want to plot, consider using external Web services to convert these addresses to longitude and latitude first. For more information about the OpenStreetMap widget, see this wiki article.
In this tutorial, all the customers are located either in New York or Irvine. Based on the customer ID that you submit, the map will display the following information about the location:
- Address -- the Address to plot, such as zip code or longitude and latitude coordinates
- Info -- text or HTML that is displayed on the pin
- Icon -- the image file used for the pin, specified by a URL
Click
here to open an Excel file to help you understand the data.
Here is a screenshot of what the map in your mashup will look like:
Click Video I to see how you can merge the map data with the current location of the customer. Click Video J to see how to display the feed data in the OpenStreetMap widget.
|
Video I Video J
Congratulations! You have completed this tutorial!
About the author
Ronald Leung is a Web 2.0 Mashup Solutions Architect in the Information Management division of IBM. He is located at the IBM Silicon Valley Lab in California. You can reach him at rcleung@us.ibm.com.