Mash active and archived data using IBM Mashup Center and Optim

A reporting solution

For cost, operational efficiency, and regulatory requirements, enterprises are required to archive transactional data. But that data might still be useful for reporting purposes. This article shows how easily you can use IBM Mashup Center to modify a reporting mashup to combine data from a DB2® relational database with data that was archived using the Optim™ Data Growth Solution.

Share:

Thanh Pham (thanhp@us.ibm.com), Solution Architect, IBM

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.



Louis Mau, Solution Architect, IBM

Louis Mau is part of the InfoSphere MashupHub development team. His current focus is to help customers build situational applications using the IBM Mashup Center. Prior to this role, he was the architect for DB2 Everyplace Sync Server, which helps synchronize data from enterprise databases onto a small-footprint database running on mobile devices.



21 January 2010

Also available in Japanese

Introduction

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
Diagram with Mashup Center Server in center, and Optim Server and DBMS Server feeding into it.

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
Flow diagram showing relationships between Customers, Addresses, Sales, Male Rates, Female Rates, State Lookup, Orders, Details, Shipping Instructions, and Items.

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
Screen cap: CurrentOrders window shows salesmen and their territories on the left and the number of sales in a bar chart on the right.

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
Screen cap: Wiring window showing Widget To receive content and the Content to Receive at the top, and Widget to Send Content, Data Viewer, and Content to Send on the bottom.

The data viewer widget is populated using a feed generated from the salesman relational table. To create a relational feed, complete the following steps:

  1. Select Create > New Feed from the Mashup Center catalog page.
  2. Provide the database connection information. A list of tables appears.
  3. 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
Screen cap: SQL Query Builder showing DB2ADMIN.SALES selected and all the columns selected
  1. 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
  1. 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
Screen cap: Operators showing the flow defined as Order, filterBySalesman, groupbyYear, Transform, sortByYear, and Publish

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.

  1. From the order amount feed, use the Filter operator to select the set-of-order amount associated with a given salesman.
  2. Group all the order-by-year with the Group operator.
  3. Use a Transform operator to sum all the order amounts within a year.
  4. Use a Sort operator to order the output in ascending order of the year.
  5. 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.

  1. Select the IBM Optim Archive feed generator.
  2. Provide the IBM Optim Archive connection information to retrieve archived orders.
  3. Create a mashup to merge the active and archived orders.
  4. Select Edit as New for the data mashup that computes yearly sales for a given salesman.
  5. 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
Screen cap: Select the feed data source window with IBM Optim Archive selected in the Enterprise 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
Screen cap: Specify the Optim Data Source window with New for Connection Profile, Optim-Demo for Connection Profile Name, Driver Manager for Connection Type, DEMOAF for Database Name, demoserver for Host, 2552 for Port, and user1 for Username

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
Screen cap: IBM Mashup Center showing flow from activeOpen and archived to Combine and then to Publish

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
Screen cap: Order Amount by Year on the left and Actions list with Edit As New circled

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:

  1. Publish the report page to the Hub catalog. This creates a Page object entry.
  2. Navigate to the Hub catalog, and go to the Show Details window for this newly created entry.
  3. Click Add to Mashup Builder to create a copy of the report page.
  4. Rename the copied report page to Combine Orders when adding to the Mashup Builder.
  5. Refresh the Mashup Builder view, and select the Combine Orders window.
  6. 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
Screen cap: IBM Mashup Center window showing flow between operators Combine to filterBySalesman to groupby to Transform to sortByYear to Publish

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
Screen cap: Combine Orders window like Figure 3 showing Salesman_names on left and bar graph from 2004 to 2008

Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Lotus
ArticleID=462540
ArticleTitle=Mash active and archived data using IBM Mashup Center and Optim
publish-date=01212010