OBIEE 10g Performance Testing: Tests without Indexes Run Faster

2 months ago, I worked with our BI team developers to do OBIEE 10g performance testing.  I asked them: what’s the scenario you would like to achieve?  They told me the following scenarios:

·         They provided 10+ dashboard & reports for me

·         They would like to measure the response time of a single user to login to OBIEE and view dashboard & reports successfully. Therefore, 10 + dashboard & reports meant 10+ different users.

·         The test result determined if the tests in OBIEE 10g with indexes or without indexes ran faster or not.

Since the scenarios were very straightforward, I used Visual Studio 2010 to create web performance tests to meet their needs. I also asked them: would you like to do load test that simulated concurrent users to login to OBIEE?  They said no.  That was for another project.  

Prerequisite:

·         They needed to create test users (20 users).

·         20 users had the permission to login to OBIEE to view dashboard & reports successfully.

·         Cleared user’s caches or turned off user sessions before running my web tests

·         Prepared for the scripts of adding or dropping indexes in the related tables in Oracle 10g.

Created Tests:

·         I used Visual Studio 2010 to create web performance tests to login to OBIEE and view the dashboard & reports they provided ( 13 tests for 13 users )

·         When creating tests, it took me much longer (didn’t know how much time) to see the data showing up in dashboard completely. Therefore I used for loop for the specific WebTestRequest (I set Baseline 5 times and I adjusted it at any time if tests failed)

·         Validated if data was really showing up after dashboard & reports were completely loaded.

Ran the tests with indexes:

When running 13 tests in serialization, some tests failed because it took a lot of time to load the report completely and validation failed.  After talking to BI developers, they investigated some SQL queries and found it was really slow to get the data back.  Therefore I changed for loop from 5 times to 10 times. This time all tests passed.

Ran the tests without indexes:

When running 13 tests in serialization, all tests passed compared to the tests with indexes.

Conclusion: Tests without indexes run faster than tests with indexes in our Oracle 10g platform.

Decision:  They decided to use OBIEE 10 g without indexes in their next release.  

Current situation: I asked one of our BI developers: is OBIEE 10g without indexes running very smoothly? He said: pretty good and it has been running smoothly since last release.