Re: OT: Business Objects

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_at_gmail.com> 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_at_david-aldridge.com> 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_at_gmail.com > 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

--
http://www.freelists.org/webpage/oracle-l