Sometimes I find myself talking out of both sides of my mouth
when discussing Db2 Web Query capabilities. The term is related to
contradicting yourself – maybe saying one thing to one person, and another,
opposite thing to another.
While certainly not trying to deceive anyone, the
association with Db2 Web Query is about whether you need to be an SQL
programmer to use it. The short answer is, ABSOLUTELY NOT. However, out the
other side of my mouth, I have to say, it certainly can be an advantage if you
do know SQL!
Db2 Web Query provides a graphical interface to building
reports, dashboards, and BI applications. With the metadata interface
simplifying the database structures for report authors, the graphical interface
is easily used by those other than programmers and database experts. The “Business Analyst” is
a classic power user of Db2 Web Query, building reports from scratch and never
having to code at an SQL or RPG level to accomplish their goals – because under
the covers Db2 Web Query generates the necessary code to access the data and
provide the report logic, formatting, etc.
While we strongly discourage you
from editing any of that code, you can see what is generated by right clicking
on a report or chart and choosing (if you have authority) to open with
text editor. The code probably won’t make any sense to you, and you
definitely DO NOT want to edit anything there as it might mess up the execution
of the report. The code is stored in IBM i in something we call the repository.
Many people have asked the Db2 Web Query
team if you can write your own, or leverage existing SQL code in a report. So
out the other side of my mouth I say “Absolutely!” Reports, charts, and dashboards can contain
data from many different data sources. Db2 file/tables are the most obvious
data source, but did you know you can also leverage existing Query/400
definitions, SQL Views, SQL (or any HLL) stored procedures, Db2 functions
(including user defined functions), and uploaded SQL scripts? You can also
upload Excel spreadsheets and with Standard Edition, access non Db2 databases
such as MySQL, Postgres, and just about any relational database you can get a
JDBC driver for.
A good example of leveraging the power of using SQL as a
data source for reports is one I’ve been showing off lately to simplify some
pretty complex reporting requirements. The examples use new IBM i 7.3 OLAP extensions
to the SQL language. These new capabilities in the SQL language make it
simpler to code, and will perform extremely well.
Suppose you are in Human Resources, and you want to
regularly check salaries of your employees to ensure that are in line with
corporate policies. For instance, maybe all employees need to be within an
average for their department, or within a department you don’t want one
employee to be an anomaly compared to others – either way higher or way lower. You
might want to answer these kinds of questions:
- What is the employee’s salary compared to the average WITHIN their department?
- What is an employee’s ratio of salary within their department and overall company?
- For each employee, can you compare their salary to the two closest behind this person’s salary, and the two closest ahead of them in salary?
While a couple of these might be easily answered by using
the graphical interface to generate the necessary code, that third bullet is
really going to make you think about how you might do that. It might require
the age old approach of using a work file. Db2 Web Query supports the work file
concept using something called HOLD files, all handled within a single report. Within a report, you can
run your query, generate a HOLD (or temporary) file, and then have the report
complete by querying the HOLD file. So while do-able, it is a lot of work to
build this kind of report, and the report itself will take a while to run if
its having to do multiple passes of the data.
An alternative to simplify the creation of, and speed up the
execution of the report is to leverage SQL capabilities in 7.3. You can read
more about these enhancements in Mike Cain’s blog on database enhancements in7.3. For my example, I used new “Partition By” syntax to create logical windows
looking into the data and then performing aggregation functions or OLAP
functions such as LAG and LEAD against the data in each window.
The first step (after learning a bit about how these new 7.3
functions work) is to build the SQL to get an average of salary across each
employee within each department, and secondarily, get a view of each employee’s
salary compared to the two closest salaries (still within their department)
both above and below theirs.
Using my favorite SQL Scripting tool, Access Client
Solutions (ACS), which you can download for free here, I created these two
scripts below that create VIEWs with my select statement (I could have chosen
to just create the scripted select statement instead of putting it into a VIEW
object, and then uploaded that into Db2 Web Query).
To use these now in a report you need to create the metadata
(called a synonym) over your view or uploaded script. The steps are pretty
close to the same whether it’s a view or an uploaded script (you’d choose External
SQL Scripts rather than “Tables, Views, and Other Objects").
Once your synonym is built and tested (use the SAMPLE DATA
option within the synonym editor) to run the SQL statement and validate you get
the data you were expecting you’re ready to build your report, chart, or
visualization:
Note that I added a filter so the “salary analyst” can
select different (and multiple) departments or look at all employees. The prompt is
automatically built and available when you run the report.
Now let's create a drill down with the first SQL View I created. Here is an average salary report with an auto drill down on
any of the department values to link to a chart for just that department’s
employees:
Each of these reports probably took at most about 15 minutes
to develop using the new 7.3 SQL functions and Db2 Web Query. And when I run
the reports the response time is sub-second. You’d be hard pressed to gain that
kind of productivity and response time using the workfile approach! Also, if
your SQL View ever changes, all you need to do is refresh your synonym.
There are many other examples of using SQL within Db2 Web
Query if you are comfortable with it. For instance, a couple of weeks ago I was
with a client that had already written some user defined functions and was
interested in leveraging those to “un-tangle” data in their reports. Why
re-invent the wheel?
In a recent prototyping exercise to demonstrate how you can integrate Db2 Web Query with Watson, our team leveraged an SQL Stored Procedure to build a
weather augmented data mart
using Db2 Web Query as the foundation for data gathering and visualization of the
consolidated data.
Some customers using the old Showcase query builder tended
to write SQL statements, and find they can leverage those same statements within
Db2 Web Query to provide the same data access but enhance the output with the
many ways to display the result set, i.e., leveraging already existing SQL!
SQL is fun and easy to learn – but you don’t have to and can
let the graphical interface generate the appropriate code. No more talking out
of both sides of my mouth (on this topic anyway)!
