Some advice on how to performance tune your SAP BI BusObjects solution:
Universes and DBMS:
- I believe that the order of tables in any modern DBMS does not play a role (see table weight), since all of modern DBMS use cost-based optimization nowadays (instead of old, rule-based optimizer).
- Regarding aggregate tables:
I would prefer to create aggregate tables in the database and then let the database handle the optimization “automagically”, this is possible e.g. with Oracle using Materialized Views for aggregate tables and the “query_rewrite” option. This is totally transparent to the universe, which means less work. And it will affect ANY frontend tool (in a positive way), regardless of if the Frontend tool is utilizing a universe or not…
- Regarding measures:
Often there still seems to be missing knowledge about SQL aggregation versus (local) projection. In brief, a measure is only a measure if it uses a SQL aggregation IMHO, see my post here with a detailed explanation.
- Derived tables
are essentially nothing else but InLine Views, but with the added feature of being able to incorporate the @PROMPT function. As such they actually might be quite fast, faster than you think if the @Prompt restricts the derived table enough and is based on a db indexed database column. This is true in particular if you create a derived table with a SQL analytical function such as RANK OVER PARTITION combined with a @PROMPT to restrict to TOP x (prompted) rows for example.
- Consider using the JOIN_BY_SQL universe parameter to “push-down” the merging of multiple microcubes (of the same granularity) down to the DBMS level.
Multiple microcubes will be generated when SQL is split because of fan/chasm traps, see also universe contexts and alias tables as well as: Daves Adventures in Business Intelligence: Do I Have a Fan Trap?
- Do not forget that the result set must be transported from your data source/DBMS to your SAPBI4 platform, so check for network latency as well as bandwidth.
- Do not forget that DB indexes cannot be used if you are applying a SQL function such as Upper, SubStr, etc. on a table column in your WHERE clause. Look at function based indices instead then (Oracle), see here for details.
- Index any table columns used in any join and on columns that are often used in a restriction/query filter
- Update your database statistics or you are running the risk that DB indices might not be used by your DBMS optimizer!
- Consider using bitmap indices (Oracle) in a dimensional modelled database. As a rule of thumb they perform better for table columns used in query filters.
- De-normalize your data model for optimal BI Reporting/Analytics, see: dimensional model a la R. Kimball, this is very important if you are using an OLTP as a data source for your reports/analytics.
- Of course consider using SAP HANA as your DBMS instead as well ; -)
With respect to report design in Webi:
- Use the Query Stripping feature available with SAPBI4.x, now also working for relational universes!
- Avoid using multiple data providers, rather consolidate them whenever possible as a rule of thumb; data providers/queries are ALWAYS executed sequentially, no parallel execution! Avoid merging of dimensions if possible as this is a costly operation. For more detail on how to speed up your Webi reports, please see here.
With respect to the SAPBI4 platform:
Consider using a so called “split deployment”, separating your JAVA Web Appplication Server (e.g. Tomcat) from your Web Server (e.g. Apache), which is serving the static web pages, see here for details: http://scn.sap.com/docs/DOC-6191
Also, with WebIntelligence the pure DHTML interface might often perform better than the JAVA applet, sadly both interface types do not offer parity when it comes to features/functionality.
Finally, DO SIZE your SAPBI4 platform accordingly taking into consideration:
number of active concurrent users (ACUs)
type of users: Consumers, moderate active users, very active users/power users
type of frontend tools (WebIntelligence, Crystal Reports, DesignStudio, etc.)
Size and complexity of reports (small, medium, large or easy, medium, complex)
For more details on sizing, please see: SAP BusinessObjects BI4 Sizing Guide | SCN and Best Practices for SAPBO BI 4.0 Adaptive Processing Servers[list][/list]
Andreas (BOB member since 2002-06-20)