![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Many business enterprises generate large amounts of electronic data that are archived for a variety of purposes. Examples include archiving transaction data for auditing, and keeping the online operation database to a manageable size. A business enterprise might also be required to archive electronic data for regulatory purposes. IBM Optim Data Growth Solution enables you to archive historical transaction records, storing them securely and cost-effectively in a compressed archived file. When viewing and searching both active and archived data, it is inconvenient to have to first restore the archived files back into the system where the active data resides. This article presents an alternative method that involves fetching both the online and Optim-archived data from their native repositories and combining them using a mashup.
IBM Mashup Center provides a lightweight process to build a new application for that alternative method. Using the built-in feed generators, you can access active enterprise data and combine this with archive data when the need arises. The example in this article shows how someone can easily extend a mashup application that was built for tracking active order information to also include archived order information. This information has the greatest benefit if you have some familiarity with using Optim for archiving data and with using the IBM Mashup Center to generate relational feeds and to create data mashups.
Exploring the system configuration
This article briefly explains how to build a sample application from the DB2 data source, and then it details how to reuse this application with the combined active and Optim-archived data. In the example, the business enterprise data is stored in a relational DB2 data source, and the archived data is stored in IBM Optim's compressed archive file format that resides in the file system, as shown in Figure 1.
Figure 1. System overview
Figure 2 shows the database and tables of the DB2 data source. As the production system transactions grow over time, this system has a rule defined to keep only the last two years of orders. The older orders are moved to the IBM Optim archive. This database and tables come with the installation of IBM Optim Data Growth Solution.
Figure 2. Database schema
Examining a mashup application tracking active order
Figure 3 shows an example report page created using IBM Mashup Center. The page is used to review performance of different salesmen based on active data residing in a relational database. This section describes the report's construction. The next section describes how to easily modify the report to incorporate IBM Optim archived data from previous years.
Figure 3. Current orders
The report page has two widgets:
- The data viewer widget displays the list of salesmen's names and their associated territories.
- The chart widget displays the yearly sale data for the selected
salesmen. The number is computed from the active orders from 2006
October to 2008 October. The input from the chart widget comes from a
feed the Mashup Center generates called
/mashuphub/client/plugin/generate/entryid/400/pluginid/10?salesmanId=NC005. Notice that the feed has a single query parameter:salesmanId
When you click on a row in the data viewer widget, the chart widget changes to display the sales for the selected salesman. This behavior is achieved by wiring the data viewer widget to the chart widget. The wiring specified that the salesman ID is to be sent to the chart widget when a row is selected. The chart widget substitutes the salesmanId parameter in the URL with the passed in value, which is then used to fetch the corresponding yearly sales. Figure 4 shows the wiring.
Figure 4. Widgets wiring
The data viewer widget is populated using a feed generated from the salesman relational table. To create a relational feed, complete the following steps:
- Select Create > New Feed from the Mashup Center catalog page.
- Provide the database connection information. A list of tables appears.
- Select a table by selecting the corresponding checkbox. This populates
the list box on the left with the list of columns. For the example,
select all the columns by selecting the
*column, as shown in Figure 5.
Figure 5. Salesman table
- To generate the feed containing total sales-by-year for the chart widget, retrieve the order information from the active database. Each row in the order table can be associated with multiple records in the details table, as shown in Figure 5. The amount of each order is computed by summing the amount column of the associated details records. The required SQL is fairly complex, as shown in Listing 1. These SQL statements might be specified in the Advanced window when you create a relational feed.
Listing 1. SQL statement
select o.order_id, o.cust_id, o.order_date,
rtrim(o.order_salesman) sales_id, d.amount
from (select i.order_id order_id, sum(i.item_quantity*i.detail_unit_price) amount
from db2admin.details i group by i.order_id) d,
db2admin.orders o
where d.order_id=o.order_id
|
- Use a data mashup to transform the order information feed to the format the chart widgets needs, as shown in Figure 6.
Figure 6. Data mashup to compute yearly sales
Note that the data mashup consists of a series of operators chained together. Each operator represents a step in the transformation process. Following are additional details about each step.
- From the order amount feed, use the Filter operator to select the set-of-order amount associated with a given salesman.
- Group all the order-by-year with the Group operator.
- Use a Transform operator to sum all the order amounts within a year.
- Use a Sort operator to order the output in ascending order of the year.
- Use the Publish operator to generate the result feed in XML format that the chart widget can display.
This concludes the quick tour of the existing mashup application.
Editing the mashup application to track both active and archive orders
Set up the IBM Mashup Center for IBM Optim
The Optim archive feed generator of Mashup Center uses the Attunity Connect Thin Client. Attunity Connect provides a type-III JDBC driver for JDBC Version 2. This comes as a set in the Java library when you installed Optim server. The set of JARs includes:
- nvjdbc2.jar
- nvapispy2.jar
- nvlog2.jar
Copy these JARs into the Mashup Center installation folder at the location
{install-folder}/Hub/installedApps/Mashup
Hub.ear/mashuphub-enterprise.war/WEB-INF/lib and restart the
server.
Create an Optim feed and mashup
Imagine a situation in which you need to extend the example report to display salesman order history since the company inception, not just active orders from the last two years. The following steps show how to extend the example mashup reporting application to incorporate the archived orders when the non-active order data are stored in the IBM Optim Archive.
- Select the IBM Optim Archive feed generator.
- Provide the IBM Optim Archive connection information to retrieve archived orders.
- Create a mashup to merge the active and archived orders.
- Select Edit as New for the data mashup that computes yearly sales for a given salesman.
- Modify the report mashup's chart widget to use the two new feeds.
Figure 7 shows the list of feed generators available from the IBM Mashup Center product. Among these are the Relational Database Query (SQL) feed generator that you used in the previous section and the IBM Optim Archive feed generator.
Figure 7. Hub feed generators list
Figure 8 shows the information needed to connect to an IBM Optim archive. This window is very similar to the window for a Relational Database Query feed generator. The window requires:
- The name of the archive, such as
DEMOAF - The name of the server host, such as
demoserver - The port number, such as
2552 - The credentials (username and password)
Figure 8. IBM Optim Archive connection information
The IBM Optim Archive feed generator provides a simple GUI to create a feed from a single archive table and a set of columns, and it provides an input box for you to input complex SQL statements. Because the archive has been created with the same schema as the active data, the same SQL statement from the previous section can be used here to fetch the order amounts.
Figure 9 shows how to build a mashup combining two sources: active orders and Optim-archived orders. Use the Combine operator, to combine the content of both feeds into one feed.
Figure 9. Data mashup to combine active and archive order feeds
From the catalog of the IBM Mashup Center, you can view the list of feeds and data mashups. You can edit or copy data mashups using the Edit As New selection. For the example in this article, make a copy of the mashup that calculates the yearly sales numbers, as shown in Figure 10. Because the elements of the combined-orders-feed are similar to the elements of the active-orders-feed, you can reuse the same logic and simply change the Source operator to use the output from the mashup that combines active and Optim-archived orders.
Figure 10. Copy a data mashup
For the last step, instead of building the mashup report page from scratch, make a copy of the report page, as shown in Figure 11, and then modify the copy by following these steps:
- Publish the report page to the Hub catalog. This creates a Page object entry.
- Navigate to the Hub catalog, and go to the Show Details window for this newly created entry.
- Click Add to Mashup Builder to create a copy of the report page.
- Rename the copied report page to Combine Orders when adding to the Mashup Builder.
- Refresh the Mashup Builder view, and select the Combine Orders window.
- Change the chart URL to use the data mashup that calculates the yearly-sales-numbers from both active and Optim archived data.
Figure 11. Reuse a copy to create a new mashup
Figure 12 shows the final results of the example process. Notice that the sales numbers now begin from 2004 instead of 2006.
Figure 12. Active and archive orders
IBM Mashup Center provides a platform for easily mashing up data from various sources. Managing business-enterprises data might require access to both the active and the archived data. Using the transformation capability and the presentation features of IBM Mashup Center, you can combine data from both sources and gather new insights from the integrated data.
Learn
- Learn more about IBM Mashup Center from their
wiki.
- Read Solution development using DB2 and
InfoSphere MashupHub (developerWorks, April 2009) for a
step-by-step guide to creating DB2 database feeds.
- Refer to IBM Mashup Center application use
cases, Part 1: Defect tracking and management mashup, and IBM Mashup Center applications use
cases, Part 2: Defect management mashup application construction,
(developerWorks, August 2008), which provide a detailed introduction to
building mashups.
- Go to the Integrated Data Management
community space to learn about IBM Optim capabilities.
- Find Access Optim archive file data
using Cognos for step-by-step instructions for connecting to and
reporting on archived data using Cognos as the reporting framework.
- Browse the Mix and Mash
Blog to get in touch with news on IBM Mashup Center.
- Learn more about Information Management at
the developerWorks Information
Management zone. Find technical documentation, how-to articles,
education, downloads, product information, and more.
- Stay current with developerWorks technical events
and webcasts.
Get products and technologies
- Check out the IBM Lotus Greenhouse to try a
hosted Mashup Center server.
- Build your next
development project with IBM trial software, available
for download directly from developerWorks.
Discuss
- Participate in the discussion forum.
- Participate in more discussion forums
for Optim for Lotus, UNIX®, and
Windows® and Optim for z/OS®.
- Check out the developerWorks blogs and get
involved in the developerWorks community.
Thanh Pham is a Solution Architect for InfoSphere MashupHub. His focus is to build a community around enterprise mashups. Before this role, he was an architect for the ECM/Filenet Business Process Framework product. Thanh has spent the past two decades in software development working on projects in many diverse areas, including real-time kernel, high-speed switching systems, networking protocols, terabit routers, multimedia, digital conferencing, small footprint database, electronic commerce, messaging transaction tracking, business process, and SOA.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
