The Power of Metadata: Part 2

In Part 1 of this series, I illustrated how Db2 Web Query for i can utilize metadata to improve the information available for reports. This article will continue that discussion, with examples of how metadata techniques can be used to expand the usefulness of your data.

Recall our previous database had some order information (see Figure 1).

Last time, I defined additional columns in the metadata, that didn’t exist in the table, to calculate extended price and cost as well as calculate profit.

Enhancements

Our order table contains two date columns; the date the customer wants the product (XORDAT) and the date we actually shipped it (XOSDAT). A manager has requested reporting to understand whether products generally ship early, on-time or late, as a measurement of shipment performance.

A subject-matter expert supplies the rules for how they have been doing performance analysis with a spreadsheet, shown in Figure 2. They’ve been downloading data from a Query to get the dates and using Excel to build a report.

Their rules show that anything shipped in a window between two days prior or two days after the customer’s requested date will be considered on time. Items shipped before that window will be early and after that window will be late. Items that haven’t yet shipped will be considered unknown or perhaps excluded.

This presents an issue that while the ERP system can provide the dates, the fields to illustrate delivery performance aren’t there. WQ metadata can again help with this! By adding a field that calculates the number of days between the two dates, we could use this number in a report. Further, we might be able to assign a performance category in support of their rules.

The DEFINE statement will be used to create a numeric field containing the number of days between the requested date and the ship date, for each Order Item in the file.

   DEFINE SHIPREQDIFF/I9 MISSING ON ALL=IF SHIPDATE_YYMD EQ '' THEN MISSING ELSE

     DATEDIF( REQDATE_YYMD , SHIPDATE_YYMD, 'D');

      TITLE='Days to shipment', $

The above statement introduces a WQ function, named DATEDIF. The DateDIF function requires three parameters: the start date, the end date and a units value (could be Y, M D) to formulate the output. In this case, we wish to know the number of days so we supply “D” as the third parameter. The function returns a numeric value.

Wait, There’s More …

Sometimes, there isn’t a ship date in the situation where the ship date is still in the future. In these cases, the ERP system supplies ZERO for the date and our conversion of the ship date from a numeric to a date leaves this value empty or null. WQ includes the ability to perform logic in a DEFINE. In fact, similar to a DEFINE within a report, Web Query has an edit panel in metadata which can be used to define required logic.

The IF statement in this DEFINE is taking care of the lack of shipment date for us, with use of the keyword MISSING.

IF SHIPDATE_YYMD EQ '' 

            THEN MISSING

            ELSE     DATEDIF( REQDATE_YYMD , SHIPDATE_YYMD, ‘D')

This is similar to an SQL scenario where you or the database would set a field to NULL if the value was absent. As a result, the new field SHIPREQDIFF will contain either a number of days, (positive, 0 or negative), or be empty when the number of days is not yet known. Adding this field to our report, we see Figure 3.

While it’s interesting to see the number of days, we haven’t yet categorized the performance as the expert had done within the spreadsheet rules above. In order to do that, we can utilize another DEFINE for a field called SHIPSTATUS. The logic of this field uses IF and ELSE IF logic that RPG programmers will find familiar.

 DEFINE SHIPSTATUS/A10=IF SHIPREQDIFF GT 2 THEN 'Late' ELSE IF SHIPREQDIFF LT -2 THEN ‘Early'

   ELSE IF SHIPREQDIFF EQ MISSING THEN 'Unknown'  ELSE 'On-Time';

      TITLE='ShipStatus', $

Improving the View

Formatting this to make it easier to read:

IF SHIPREQDIFF GT 2 

                  THEN 'Late' 

ELSE 

IF SHIPREQDIFF LT -2 

                  THEN 'Early' 

ELSE 

IF SHIPREQDIFF EQ MISSING 

                  THEN 'Unknown' 

ELSE ‘On-Time'

This definition creates a 10-character field containing one of the category names supplied by our expert. Adding this ShipStatus field to the metadata would enable inclusion on a textual report, or perhaps development of a chart depicting overall performance or trend performance. To make the report even more meaningful, I would use a WQ feature known as “traffic-lighting” which highlights the data in the report. Traffic lighting is applied to a field, potentially changing font, text size, text color, or background color, based on the current field’s value. I used red for Late, therefore bad, and other colors as seen in Figure 4.

The resulting report in Figure 5, illustrates the categories more clearly.

Creating Visualizations of the Data

Now that the metadata definition for this table includes several additional fields, WQ can potentially provide multiple reports to the business using the single data source. Or even better, I can create a visualization report. Visualizations are like a dashboard, where a data source can be used to format multiple reports as containers within a page. Figure 6 depicts a visualization example where several possible views of interest are shown.

This Figure 6 visualization depicts (from top left to bottom right):

• Item shipments bar chart, high to low by quantity
• Item profit bar chart, high to low
• Delivery performance bar chart—over time
• Overall breakdown of delivery performance pie chart by category
• Also, a textual grid view of the data

This visualization was created over a very simple example data set and uses just a few of the chart types available in WQ. More sophisticated visualizations could include parameters for selecting product families, time periods or geographies and might use graphics elements such as line chart, geographic maps of countries or world and even word clouds. Most visualizations present an initial view, then allow the consumer to interact with the charts and reports, selecting and filtering the data. This filter capability enables you to include or exclude one or many subsets of data. The end goal being to interactively discern facts or trends that might not jump out on a standard report.

You may have heard the phrase “single version of the truth” mentioned with regard to business intelligence tools. This phrase is attributed to a number of the early BI experts.

What we mean by “single version of the truth” is that by defining information or rules in one place, such as within Db2 or in the metadata layer, we reduce opportunities for report writers and consumers to arrive at different values or conclusions for the same important data. Defining columns and calculations in advance, reduces work required for report creation, hides complexity, and ensures the same calculation are available to all future reports. In the case of either simple or complex computations, if business definitions need to change, it changes in a single place, and reports will adopt the new details.

IBM Db2 Web Query for i is jam-packed with presentation tools that enable companies to present data in improved ways, and on various types of devices, therefore placing more decision-making power in the hands of business leaders. And with smart use of metadata, companies likely also will benefit from the holy grail of reporting—a single version of the truth!

---