Tuesday, April 5, 2011

WebFOCUS Difference Between DEFINE and COMPUTE

If you have ever interviewed for a WebFOCUS developer position, you have heard this: "What is the difference between DEFINE and COMPUTE?"  It is a simple question, but surprisingly there are many who cannot articulate a good response.

This question serves the same purpose as the college English 101 class. It immediately knocks out people who should not even be sitting in the room.

Both DEFINE and COMPUTE are phrases used in the WebFOCUS 4GL computer language to perform  calculations. These are robust commands that can be logical (IF-THEN-ELSE phrase), mathematical (such as calculating percentages), comparisons, or calls to other programs (such as user-written subroutine).

So if both do calculations, why are there two of them?

When you create a calculation using the DEFINE statement, WebFOCUS will perform it on every record that meets your selection criteria (an IF or WHERE test). So if you have one million sales transactions and are aggregating them by month, the DEFINE calculation will happen one million times.

On the other hand, WebFOCUS performs the COMPUTE calculations only after its sorting and aggregation steps have finished. So WebFOCUS would first sum your one million records into twelve months and then perform the COMPUTE twelve times.

The DEFINE happens on every detail database record while the COMPUTE is on each aggregated result line. Your next question is, when would you use one over the other?

The common response deals with efficiencies. If you can get the same results performing a calculation one million times versus doing it just twelve times, you want to use the one with the least amount of work involved. That makes your BI reports run faster and more efficiently.

Another common but less valid response is: "if you get the same results either way, just always use DEFINE." That is the non-thinking person's answer.

The real answer is you use either COMPUTE or DEFINE when you must. Sometimes, you must be at the detail record level in order to perform a calculation. Sometimes, you must work with the aggregated values.

One example would involve percentages. You can never calculate ratios and then add them together (at least, not if you want to show correct figures).

Take these three students, their individual grades, and their very small class total:

  • Bob: 24 out of 25 questions (24/25 = 96%)
  • Ted: 23 out of 25 questions (23/25 = 92%)
  • Sue: 20 out of 25 questions (20/25 = 80%)
  • Class Total: 67 out of 75 (67/75 = 89.3%)

If you were to just sum the three detail averages to get a class total, you would get 268% (96% + 92% + 80%), which is not correct. Therefore, you would not use a DEFINE statement to calculate these percentages and then sum them together--you would get the wrong ratio.

Instead, you could use a COMPUTE phrase to summarize the details by class and then calculate the results. The COMPUTE will provide you with the summary figures of 67 divided by 75 for the right ratio of 89.3%.

Of course, handling this single interview topic properly will not get you that WebFOCUS job. It will, however, give you a chance to answer more than one question. 

No comments:

About Me

My photo

I am a project-based software consultant, specializing in automating transitions from legacy reporting applications into modern BI/Analytics to leverage Social, Cloud, Mobile, Big Data, Visualizations, and Predictive Analytics using Information Builders' WebFOCUS. Based on scores of successful engagements, I have assembled proven Best Practice methodologies, software tools, and templates.

I have been blessed to work with innovators from firms such as: Ford, FedEx, Procter & Gamble, Nationwide, The Wendy's Company, The Kroger Co., JPMorgan Chase, MasterCard, Bank of America Merrill Lynch, Siemens, American Express, and others.

I was educated at Valparaiso University and the University of Cincinnati, where I graduated summa cum laude. In 1990, I joined Information Builders and for over a dozen years served in regional pre- and post-sales technical leadership roles. Also, for several years I led the US technical services teams within Cincom Systems' ERP software product group and the Midwest custom software services arm of Xerox.

Since 2007, I have provided enterprise BI services such as: strategic advice; architecture, design, and software application development of intelligence systems (interactive dashboards and mobile); data warehousing; and automated modernization of legacy reporting. My experience with BI products include WebFOCUS (vendor certified expert), R, SAP Business Objects (WebI, Crystal Reports), Tableau, and others.