Re: OT: Business Objects
- From: Robyn <robyn.sands@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx
- Date: Wed, 2 May 2007 16:01:04 -0400
Charles,
We're also using BO and I've spent a fair amount of time working with
our developers to improve performance. I'll second David's comment
re: functions against columns in the conditions, and I'll add that
I've found many of our problems have come from universes built on
unnecessarily complex views. Frequently, the developer use a view
that contains the info they need, but they don't realize the impact of
the info they throw away until they see the trace file. In a very
recent case, the first steps of the plan read 13 million rows only to
throw all but one of them away before preceding the real data.
I've created a few of my own universes, but have been able to make the
biggest performance improvements by rewriting a few key underlying
views.
hth ... Robyn
On 5/2/07, Charles Schultz <sacrophyte@xxxxxxxxx> wrote:
Thanks to all of you for your feedback. I asked the developer about BO, and
I got it installed on my PC so now I can play with it and see for myself
what is going on. There is a bit of a learning curve for me, but it really
helps to give me some perspective on what is going on.
I do think the issue of the Universe definition is playing a key role in the
few performance problems we have encountered. Now I have to figure out how
to tackle that. =)
On 5/2/07, David Aldridge <david@xxxxxxxxxxxxxxxxxx> wrote:
>
> One of the strengths of business objects (IMHO) is that the SQL it
generates is very predictable. The universe designers define the joins, the
entities in the select statements etc., and having done so should be able to
directly infer how reports against that universe will translate to SQL.
Theoretically they are then able to draw inferences about universe
performance.
>
> One issue that inexperienced developers can get into is defining functions
against columns (eg. concatanating first and last names) and then allowing
those objects to be used in conditions. That, of course, can be a recipe for
plenty of lovely full table scans, so some coordination with the creation of
function-based indexes can be beneficial, or maybe better education can lead
them down the path of using more appropriate condition definitions.
>
>
>
> Charles Schultz <sacrophyte@xxxxxxxxx > wrote:
> I know Mark Strickland is using Business Objects, but not sure about who
else. As a DBA, I often get requests to tune BO-generated queries, and so
far they have all been fairly complex and ugly. Since I do not know much
about BO, I do not know if the blame for suboptimal queries lies more with
the product or with the fine folks using it; I am a little biased in
thinking that the product is simply lacking, but I reserve the right to be
wrong. =)
>
> So first, my obvious question: have you in the user community found other
tools that can generate "better" sql, one that exercises a little more
intelligence about the back-end database?
> And next, assuming that perhaps the developers merely need a little
knowledge transfer about BO, what BO Forums would you suggest?
>
> Just for a little background, the developers are writing reports against a
copy of an ERP datastore (SCT Banner in this case). I have been able to
introduce them to analytics which is catching on slowly (BO does not
inherently support analytics, does it?). The datastore is not a "warehouse"
per se, in that the data has not gone through any ETL and there are no
enhancing fact tables or star schemas outside what is provided for the OLTP
application. Some extra indexes and a view or two, but that is it. 10gR2 for
those who would ask. =) Not sure about the version of BO.
>
> TIA
>
> --
> Charles Schultz
>
--
Charles Schultz
--
//www.freelists.org/webpage/oracle-l
Other related posts:
