RE: BI / Reporting tools -- MIDRANGE-L

Sage advice. Well said.

--
Jim Oberholtzer
Agile Technology Architects

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Doug
Mack
Sent: Tuesday, April 19, 2016 7:55 PM
To: Midrange Systems Technical Discussion
Subject: 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.

--
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.