I feel that the optimization techniques used in BO needs to be put in one place. Hence i choose this to be the best place. I have collected a few, which I am including below. Pls add on so that the list gets complete and serves purpose.
Designer
Use Shortcut joins where ever possible.
Avoid using Outer joins whenever possible.
Take off the List of values option for measures.
Use Summary tables and Aggregate Awareness whenever possible.
Use appropriate Hints with objects
Reporter
Creating new document variables dimension or detail will prompt BO do to a reformatting of the document. There are 3 ways to deal with that:
----------a. Work on a document in the structure view, no data is shown, so formatting is much faster.
----------b. Work on a limited subset when you build a report by temporarily setting a condition that will limit the amount of records retrieved.
----------c. Apply an appropriate global filter to diminish the amount of data shown.
Some reports can be dramatically speeded up by creating aggregating measures in the universe (like sum(sales), so instead of retrieving a zillion records, you get of fraction of the number of rows. The reformatting is then quite a bit quicker.
When a sort is specified in the Query Panel, it adds on the ORDER BY clause in the SQL. For optimization, make sorting in report level.
Take off all Variables/Objects not used in report.
Utilize Variables instead of Formulas. This is because whenever a Formula is edited, it stores it as a new one and hence the number of formulas multiplies.
Use off-line mode if possible
Limit the number of data providers to the possible minimum.
I know you are looking for additions, but I’d take off a couple.
Shortcut joins are a pain. Since BO will only choose 1, it is difficult to predict which one it will choose. It could help performance of the query, but you will spend alot of time getting them to work.
Why does offline mode speed up anything other than the login time?
It depends on what you mean by this. Shortcut joins should never be used to resolve a loop. They should only be used as appropriate, meaning as shortcuts. If you have table A -> B -> C, and A has an appropriate key in C, then you can shortcut from A -> C. But don’t use shortcuts to resolve loops. And if you have A -> B -> C -> D -> E, pick one of the tables, but don’t create shortcuts from A -> C, A -> D, and A -> E. It just confuses the issue.
Agreed! Very much so, in fact.
Depending on the version, and if you use an aggregate function in your measures (as you should), this will be done automatically.
Is this automatically something new? I am using 5.1.4 and find it very frustrating to have to manually disassociate LOVs for measures. Like when I create a Count object - COUNT(*) - and fix all the properties and format it 0 instead of 0.00, if I copy and paste this to another class, it keeps everything but the unchecked LOV. I was just wondering about this yesterday when I was training someone on Designer and trying to think of a reason why this is done.
I don’t know when (which version) it started. In Designer, create a new object. Make sure that you use an aggregate function (count() should work). When you click on the second tab (Properties) you should see the object has been automatically classified as a Measure, and the LOV has automatically been turned off.
Interestingly enough, in version 3.1, you didn’t even get a choice. Any object with an aggregate was prevented (not even an option) from having a LOV query.
I’ll be darned but if you aren’t right.
But now take that measure object and cut and paste it and you’ll see that it checks the box to associate a LOV even tho it was unchecked before.
Yes - I have multiple counts in different classes because that is the way the users like it. Some of the classes are fairly long and it cuts down on scrolling.
I hate to be disagreeable (actually, I like to be disagreeable ), but offline mode has nothing to do with that. All offline mode does is prevent you from hitting the repository. You can still refresh the reports if you want.
Offline mode should have no impact at all with reformatting the reports. You can reformat all day long without impacting performance.
If you’ve discovered something different, please let me know. This could be very useful information.
The offline mode helps when the connection is web connection. If only formatting changes needs to be carried out in the existing document, or in case new report tab needs to be created with the existing data in the document cube, then there is no need of connecting to the repository or the data base. In such cases, if refreshing is not needed,offline analysis helps in getting the things done fast.
I found a new thing. I dont know how it optimizes. Just curious on it, and will be looking forward for an explaination…
“Avoid direct entry of SQL”
What i feel is if we enter SQL directly and freeze it, then the designer will only be used for connection. This avoids the job of converting object definition to SQL for business objects right. Hence the performance should increase… But y is it said that “Direct entry of SQL is undesirable”?
Drives me crazy … Sometimes we’ll create a new universe by copy/pasting the objects (and object model) between the two universes. Of course all these special LOV’s won’t copy over directly and need to be recreated (or unchecked) manually.
Good Topic … I’ll add: Optimize all SQL coming out of your report. In DB2 land this means running explains on your final SQL so that appropriate index’s can be created
My mistake, lazy as I am, but thanks. For those who are as lazy as me… search for discussion topic “Adding hints to queries”. This really helped me.
I noticed you mentioned you are using Count(*) for a general count object. I have been advised by my DBA that Count(1) is much more efficient. Using * requires the database to retrieve all the columns to count the rows versus just one column. Could help in performance of complex queries.
(BOB member since 2002-06-25)
Shortcut joins should never be used to resolve a loop. They should only be used as appropriate, meaning as shortcuts. If you have table A -> B -> C, and A has an appropriate key in C, then you can shortcut from A -> C. But don’t use shortcuts to resolve loops. And if you have A -> B -> C -> D -> E, pick one of the tables, but don’t create shortcuts from A -> C, A -> D, and A -> E. It just confuses the issue.
), but offline mode has nothing to do with that. All offline mode does is prevent you from hitting the repository. You can still refresh the reports if you want.
“Avoid direct entry of SQL”
Drives me crazy … Sometimes we’ll create a new universe by copy/pasting the objects (and object model) between the two universes. Of course all these special LOV’s won’t copy over directly and need to be recreated (or unchecked) manually.
(BOB member since 2002-08-15)
4 Scott,