September 2011

Tech Info

Create Summary Data with SEQUEL Tables

Do you need to extract transaction data to your PC for analysis? Is the amount of data too large for your PC to handle? Do you need to move (pivot) the data in a spreadsheet to make it easier to use? Do you have people that need to take this data on the road to critical accounts?

You can download data from your IBM i DB2 database to a PC using tools such as FTP, Client Access, JDBC, and ODBC. The problem is that your PC may not be able to handle the volume of records. It would be nice to have a way to summarize the data, either on the IBM i system or on the PC, and to see the detail behind the summaries—the data behind the data.

SEQUEL Gives You Options

With SEQUEL you can easily create spreadsheet-like tables that can be downloaded, exported to Excel, or used interactively for detailed analysis. SEQUEL has two, easy-to-use table options: Host Tabling and Client Tabling.You can create either table using the Table Wizard and you can create most tables in only four steps. The intuitive design interfaces make modifications and enhancements easy.

Figure 1. SEQUEL data view before tabling.

Figure 1 shows how downloaded transaction data might look as a list in a standard SEQUEL view. Without SEQUEL tabling, this type of request could mean millions of records—too much data to send to a Windows desktop. Using either SEQUEL table options, you can quickly summarize these transactions into manageable tables.

View Spreadsheet-Like Summaries

Use SEQUEL Host Tabling to automatically summarize IBM i DB2 transaction data into spreadsheet-like files with summary rows, columns, and totals. Enhance your table by adding rankings, percent of total, and other calculations. The SEQUEL Host Tabling views organize data into columns for regions, time periods, or other categories. And, you can output this summarized data to multiple formats, including XLS, XLSX, XML, or CSV.

Figure 2. SEQUEL view data organized using Host Tabling.

Save Table Results in Numerous PC Formats, Including Excel

You can create PC output from the table results display or from the main ViewPoint Explorer screen. And, you can save table results locally, on a network drive to share, or you can e-mail them. You can even automate the process by using the Table view in a SEQUEL script.

Powerful OLAP Features to Analyze Data

A SEQUEL Client Table is an interactive solution for dynamic Desktop OLAP (DOLAP) and cube analysis. With a Client Table, it’s possible for you to build client/server views that provide end users interaction with IBM i databases to explore and summarize large quantities of data quickly.

Once a Client Table is displayed, users can perform interactive analysis locally, including data filtering, drill-down, data marking, dimensional pivoting, ranking, print, preview, and graphing. And, all of this is available without changing your current relational or multi-dimensional database systems.

Figure 3. SEQUEL view data organized using Client Tabling.

Take Your Data on the Road

SEQUEL Client Tables are the perfect way to take your data on the road. First, you consolidate data from your database into a portable, standalone module. Then, you use the Client Tabling viewer drag-and-drop features to reorganize the data and view the results instantly without affecting the data in the database. And, you can do all this without connecting to the host server (unless you need to refresh the data).

Host vs. Client Table

Host and Client tables are quick, easy ways to summarize large amounts of detail data. Both can create PC files and both have wizards. However, there are some differences between them:

Host Tables can:

√ Have numeric or character column fields

√ E-mail results in various PC formats

√ Save results to the IFS

√ Create physical files from results

√ Be scheduled easily

√ Have conditional columns

√ Calculate percent of totals in more than one group

Client Tables can:

√ Sort on data row totals and data columns

√ Round percents to the nearest whole number

√ Use a subset of the data brought to the PC

√ Be filtered dynamically

√ Look great in dashboards

√ Have data markers on more than one field and value

√ Insert pictures at group breaks

√ Pivot displayed data

Check out SEQUEL Tables Today

SEQUEL stops the data overload on your desktop and makes your data easier to use and more portable.

By Steven Smith, Technical Consultant

IBM i Solution Edition for Help/Systems

Purchase SEQUEL (or other software solutions from Help/Systems—Robot Automated Operations Solution; PowerTech IBM i security solutions; and Bytware antivirus and monitoring solutions for IBM i) and enjoy big discounts on training, services, and IBM POWER7 systems.

For details, contact your local IBM Business Partner, or Doug Fulmer at doug.fulmer@helpsystems.com. Or, click here.