| PS Query Support | KEVIN RESCHENBERG
03-08-2006 |
Some PeopleSoft customers barely touch PeopleSoft
Query, while others use it extensively. If you've
rolled Query out to various end users, you are probably
already familiar with the common support questions.
I'll go over a few of them today. This is by no
means a full list of the common questions—you
just never know what each user is asking Query to do.
Also, I'm definitely no expert in Query; I prefer
instead to jump into SQL when faced with the slightest
difficulty in Query. But here are a few suggestions.
Security issues
Why didn't John Doe appear on my report? Why
can't I find the Widgets table in the list? Why
can't I see the public query that my coworker
is using? Many Query issues can be
traced back to security.
PS Query automatically enforces row-level security.
Open up a record (table or view) definition in Application
Designer and look at the Use tab on the Properties
dialog. If a Query
Security Record has been specified, that table or view will be automatically
joined by Query. In the HRMS product, department
security is frequently used. (It's not the only way
of doing row-level security. If you read PeopleBooks
you will see wording that indicates that row-level
security is considered a feature ripe for customization, and the
department security is delivered as sort of a suggestion.
You could base your security on anything.)
Security also determines whether a record is
available to a particular user. Each record
definition that you want to be available for Query
must be added to the Query tree. If even one
table or view in a query is not available to
a user, that user will not see the query in
any list.
Performance issues
Row-level security is one reason that PS Query can
appear to be running slowly. In many cases we do not
apply security when building SQL statements, SQR
programs, etc. The extra overhead that Query
(appropriately) applies can slow down execution.
Of course, some users will expect their 10-table
query to execute within seconds. Users also may not realize
that asking for translate table values
or description fields from other tables will add
more joins and more overhead. Since it's "just
a description," they will think of their query
as being very simple when it might not be. There's
not much we can do about this beyond educating
the users.
Another possible performance issue relates to
bad joins in the generated SQL. Query does a good
job of detecting the appropriate join conditions,
but it's not perfect. Also, realize that the order
in which tables are added to the query affects
the joins that are produced. It is possible to
create a Cartesian product situation, and that
will definitely lead to slow execution and affect
other users. This is one of the reasons that some
installations create a separate reporting database.
One way to avoid some of the performance and
complexity issues is to provide a set of denormalized tables
to your users. I've mentioned these before
(here, for example).
These tables bring together many of the commonly
requested fields onto one flat record. As long as
they are accurate, comprehensive, and consistently
updated (daily, for example), they are a great way
to make Query reporting simple and efficient.
Private queries
Users can create queries that are either public or
private. This is a good thing. But what happens
when important queries are stored as private, and
then the user leaves the company? Log on
as that user, open the query, go to Properties,
and change the type to Public.
Lost queries
At times it might be necessary to find the query that
produced a particular report or spreadsheet. Next
week we'll look into the Query definition tables to
see what information is available.
|
