You are currently on IBM Systems Media’s archival website. Click here to view our new website.


How to Build Dashboards in DB2 Web Query for i

using business data

Has someone in your business requested a dashboard? Do you know what a dashboard is and how to create one? Dashboards have become popular for monitoring business performance, and you can build one on your IBM i system, but where do you start?

IBM DB2 Web Query for i is a modern reporting tool that can present data on multiple devices. The Web Query toolset allows you to develop useful reporting applications using the DB2 database or other objects. You can incorporate text, charts, graphs, and images, deploying the result to desktops, laptops, tablet devices or phones. You can create applications once and run them anywhere.

Web Query offers several ways to provide your organization with Documents or Dashboards. This article will explore basic dashboard concepts and some Web Query features.

By extracting data from ERP systems, with query tools, or via re-keying or downloads to spreadsheets, analysts can build charts and graphs that prove the old adage “A picture is worth 1000 words.”

Data Presentation

A dashboard typically presents data from a source that allows readers to focus on one or several metrics, often called Key Performance Indicators (KPIs), because they relate to business performance. KPIs might depict current or historical data, using text, color, icons, graphs or symbols. But what are the right key metrics? There’s no single answer to this question, because each business will have its own measures of success. In the enterprise, metrics likely include dollar or quantity values related to sales, profits, service levels, inventory, product quality or employee headcount. Enterprises may also benefit from comparisons to competitor’s values for similar metrics or to industry benchmarks.

Figure 1, provided by Google Finance, shows various metrics that relate to IBM stock: a snapshot of current information at a single point in time, a comparison to prior periods, plus an actual, percentage change and movement by increase or decrease. In this presentation, the chart is interactive, so you can change the duration to depict between day (in hours) and year durations (in months) for the historical trend.

IBM DB2 Web Query for i, allows you to build presentations similar to Figure 1, utilizing data from your enterprise, by facilitating the retrieval of columns from DB2 tables, views and other objects or sources.

For a company utilizing a software package or an ERP system, supporting data for metrics is probably available but may need summarization or reformatting in order to make sense for KPI presentation. The task of determining key business metrics will often require discussions to arrive at the most important metrics. This is likely to become even more interesting in the future, given the flood of big data upon us.

Metric Presentation

Let’s say XYZ Corporation has agreed on several metrics and decided to develop a dashboard that present periodic status and trend review. They track sales information and have a forecast of what they expect sales will be in the future. In DB2 terms, this means locating the sales order table and the sales forecast table. Using these tables, they could build queries to compare actual order achievement to forecast expectations, by accumulating sales data points on a monthly basis and using these on a chart. One axis shows Dollars and the other axis shows Time. A chart of monthly attainment, comparing Forecast to Actual might look like Figure 2, enabling us to see actual performance vs. expectations for each month. That’s sort of like looking in the rear view mirror, though, because the months have already past, right?

Another presentation of this information might be done using cumulative totals. This has the potential to be more forward looking, because in addition to past performance, the chart may give viewers a sense of the future, answering the question: “Even though we missed the forecast target in recent months, are we on track to meet overall annual expectations?” See Figure 3.

In this example, assuming a strong finish to the current and future months, XYZ Corp. can perhaps see whether they are on the way to attaining their yearly objectives.

Developing the Dashboard

The process of developing metrics and dashboards, involves consideration of response-times and query performance, just like any other application-building endeavor. Managers or C-level executives don’t want to wait long for a dashboard to appear. For charts like Figure 3, there are several ways to get the data into the form that supports the chart plotting:

  • Read the operational data files. Whenever the chart is viewed or refreshed, summarize needed data for the report. With relatively small files, this works great. With millions or even billions of records, this approach might be a long running application, not meeting response expectations for some viewers.
  • Stage the data by creating a table or view that is rebuilt or refreshed periodically, perhaps on a timed basis, providing the data required to produce the charts. This approach can improve response.
  • Make data a component of a metric database, populated with data points, created over time, providing a smaller number of records, already summarized, with just what is needed for the reports. This technique might offer rapid response for numerous time-based queries. To compare one year to another, showing the difference and percent change, we’d need to summarize period-to-Date information, as well as summarize the same period in the prior year. If we had taken the metric database approach, we might already have the last-year or year- over-year data on hand in summary form.
  • Temporal Features

    IBM recently added Temporal features to DB2 for i, which will likely provide improved ways to approach year-over-year, period-to-period comparisons. When the new DB2 for i features are enabled in IBM i 7.3, data can be saved in a way that allows you to look at data during points in time in the past. With Temporal processing in place, next month or next year, you might write two queries, one to get current period and one for prior period, specifying a common elapsed time, such as month or quarter of each year. Whether utilizing the Temporal feature or not, such a yearly comparison might look something like this chart in Figure 4.

    Data in Numeric Form

    Another view of similar data might express a comparison in numeric form as seen in Figure 5.

    For such Sales metrics, there could be a variety of presentations or views depending upon what a business needs to measure. To put together a dashboard, the first task is to develop an extraction strategy that allows us to go get at the appropriate numbers behind the dashboard. DB2 Tables, Views, Materialized Query Tables or SQL Stored Procedures are all excellent approaches to support such presentations. DB2 Web Query for i can consume each of these database object types, producing one or more reports or charts from a data source. Once combined in the Web Query portal as a page, or in a single document, you can see the power of a dashboard to review business activity Figure 6.

    I like the concept of a metric database, populated by programs used in conjunction with DB2 Procedures, Views or MQTs. The metric database could contain descriptions, a definition of the metric, a timestamp when gathered, and the numeric values associated with each of the metrics captured. The metric repository likely contains a smaller number of records than the operational data, because of pre-summarization. This leads to faster responses. Data records might be further summarized rapidly by logic that involves descriptions, dates, or timestamps. When necessary, drill-down to the actual operational data could provide further insights about a selected period or subset of data, again limiting the number of records to be processed.

    Job Scheduler

    In the Standard Edition of Web Query, a job scheduler component can run reports to summarize or deliver data to the portal, files, users or folders. Or, the IBM i job scheduler could run a job to read and summarize operational data, creating metric records for a relevant period. Using RPG, SQL or Web Query, you can harvest data that could contain daily snapshots, from which weekly, monthly or year-to-date summaries could later be obtained. Once created, one or more metrics can be presented in a dashboard showing current values or trend analysis. Figure 7 shows another dashboard example that might be viewed on a browser or mobile device.

    The Information Builders Mobile Favorites application, available from platform App stores, can be used to review data on tablets or phones. IBM and Information Builders Inc., have worked diligently to make DB2 Web Query for i an affordable, feature-filled and easy-to-use Business Intelligence offering for the IBM i market.

Rick Flagler is an information technology consultant, teacher and mentor.

Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters