Re: BI / Reporting tools

My advice: - if your BI/Reporting app is going against production:

* Create a "controlled ad-hoc" environment (or the reverse angle is - get
out of ad-hocracy - no tools that allow free form SQL). Utilize forms to
front end the query so users can run the report over and over with
different selection criteria - but on the back you you've done the work to
TUNE this query - appropriate indexes, memory pool considerations, even
aggregates if applicable. Don't let end users create free form queries but
give them flexibility to run TUNED queries with different output options
selected at run time (even embed the form in an Excel spreadsheet).

* learn about DB2 for i query optimization. There's a lot of magic that
happens in DB2 for i, but not without some guidance through indexing
techniques or knowing your data to write the correct query. I helped a
client the other day take a Cognos query that was running 100's of times a
day from 8 minutes (every time it ran) down to less than 1 second

* People here have commented that meta data is a negative thing. But with a
BI tool that leverages meta data you can do things like pre-define joins or
even push the join to DB2 via SQL Views that run more efficiently. You
don't have to, but....you can do things to minimize exposure of run away
queries.

* regarding security - you can do any number of things there to ensure,
even if you don't have object level security implemented (bad bad bad) you
can control who can access what at the row or column level, whether you put
that into the database directly with Row/Column Access control (RCAC) at
V7.1, or through the use of SQL Views or even if the BI tool itself
supports row and column level security (Western and Eastern region managers
need data from sales table, but shouldn't see each other's regional data -
is an example of row level security). Pick a tool that can ensure you can
secure at the user level so people can ONLY run reports through that above
mentioned form and cannot build their own report against tables that are
not secured.

Doug Mack
Analytics Consultant, DB2 for i
IBM Systems Lab Services
Mobile: 360-481-1271
email: mackd@xxxxxxxxxx
twitter: @mckdrmoly




From: Jim It <jim_it@xxxxxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 04/19/2016 05:19 PM
Subject: Re: BI / Reporting tools
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

A Couple things on the reporting tool. This will be used in our

production system and it's intended for end-users,

some report will need to be setup initially set by the developers with

scheduling capability to automatically

run reports.

Forgot to add that we are in version 7.1

Jamal, the utmost importance is, if you plan on using your production
server for reporting as well you must be aware that:

Preserving the production system is the most important thing. Any bad SQL
statement can bring the biggest systems to it's knees.

Most importantly:
1.) Once the database servers are turned on, it is up to the iSeries
administrators to limit access. Default access is allow all, if user has a
valid iSeries user profile. This includes any SQL Statement including
update.
2.) Bad queries. This is most likely the #1 problem. If you plan on
launching any SQL statements on your production database, a veteran DB2 for
I DBE should review each statement.

Nearly as important, keep up with the SQL updates. There have been so many
updates throughout the years but they are completely ignored. Why because
it still works.

(As I speak, one of our legacy programs has been creating an index for an
OPNQRYF for 14 hours! Are you freaking kidding me!)

Jim

p.s. Regarding security, I worked once at a company which I was a normal
regular user and did not have command line access and no special
authorities but the company had no security checks in place. I was able to
do everything I can today having full SECOFR authority.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.