I am looking for some advice/assistance regarding Oracle Business Intelligence performance.
We have Oracle BI Analytics infrastructure used for Bi reporting on E Business Suite, the user front end experience viewing and creating reports has always been slow since it was installed.
The infrastructure was configured with the informatica, dac and BI web logic services all running on a Solaris server that is also co-hosting E Business Suite live application tier services.
BI is new to me and I don't have alot of experience with the product and supporting its infrastructure, with that in mind I was wondering if anyone could provide any advice on how to diagnose the performance issues from the BI infrastructure perspective? or advise from there own experience if its recommended to run the informatica, dac and BI web logic server services on there own dedicated server?
Performance is a fairly broad topic to try to squeeze into one post, but here are some ideas to consider:
1. Its usually a good idea to separate your OBIEE components from your informatica/dac ETL components. This way, you can scale each of them out.
2. You need to determine what the actual bottleneck is. The statement "user front end experience viewing and creating reports" is not very clear. Are you saying the reports are running slow? If its Reports running slow, you need to find out if the bottleneck is the DB (this is easy to check by running the session SQL directly against the DB). If the SQL runs slow, then you need to tune the DB or server (indexing, partitioning, etc). If the SQL runs fine but renders slow in OBIEE, then perhaps you need to check the presentation server performance (easy to do in 11g w EM).
3. Did you install OBIEE as per the guidelines for clustering, etc.
4. Are you leveraging caching properly.
5. Are you using aggregates, materialized views (Oracle DB), effectively.
6. Are proper filters being applied when reports are created?
Again, this is a broad topic, you need to find the exact issue and then diagnose which tier the bottleneck exists on (DB tier, OBIEE, etc) and then tune that component.