BI Server Cache and Maximum Number of Rows Processed

3310714

Hi,

I'm trying to understand the way OBIEE 12c processes data and have these questions. 

1)  I have BI server cache ENABLED and my Maximum Number of Rows Processed is the default 65,000.  I have a table with 100,000 records.  If I create an Analysis to return all records from the table, the BI server retrieves 100,000 records from the DB, creates a cache entry file for the 100,000 records, but only renders the first 65000 records, right? 

2)  I have BI server cache DISABLED and my Maximum Number of Rows Processed is the default 65,000.  I have a table with 100,000 records.  If I create an Analysis to return all records from the table, the BI server retrieves 100,000 records from the DB, the results sit in memory somewhere in BI server, but only renders the first 65000 records, right? 

Is my understanding of how OBI processes data correct?  Please advise.

rmoff

Before I give you what I think is the answer, let me pose a question of my own : did you try it? It's very easy to observe what's happening, by looking at the obis1-query log and Usage Tracking (how many rows returned from DB) and AdminTool (how many rows in the cache).

So my answer (based on informed guesses - I have not tested to verify)

1) OBIEE will cache the *logical resultset*. Therefore if it returns 65k to the client, I would expect to see 65k in the cache, and a query to the DB with FETCH FIRST 65000 ROWS ONLY.

2) As above - I would expect the query to the DB to curtail it at 65k rows. If the DB doesn't suppose such syntax then I'm not sure what mechanism OBIEE would use to drop the remainder. Possibly the bulk cursor would just stop fetching once the limit had been reached. That's a complete guess though.

3310714

Thanks for your response.  I have tried #2 so far.  I wasn't sure if the 65,000 record limit is imposed at the DB level or the BI Server level. Your answer suggests it is imposed at the DB level.  

rmoff

You can see for sure by running your query with LOGLEVEL=2 and inspecting the query sent to the database in obis1-query.log

3310714

I set my user to LOGLEVEL=2 in the repository, online mode.  After I ran a sample Analysis, I can't find the obis1-query.log in Enterprise Manager?  Is it only available by logging into the server directly?  I also tried going to Manage Sessions and click on the "View Log" link, but the result says "No Log Found"   Any ideas?

[Deleted User]

Is it correctly registered in EM?

3310714

Hi Christian,

I'm not able to see these logs for OBIS.  Can you tell me how you get to this screen?  I can only see these:

[Deleted User]

3310714

I followed your instructions, but still only see the AdminServer and BI logs for the Weblogic Domain.  Is there something I need to configure to turn on the OBIS logs? 

[Deleted User]

Not really they must be there in any standard isntall.

If you followed the instructions please screenshot where you are so we can see that you're in domain_bi/bi/obis1

3310714

I searched in Oracle Support and there's a bug with my version, 12.2.1.0.0 where obis1-query.log doesn't get updated by setting LOGLEVEL=2 in the repository, Manage --> Identity option:

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=19o6vj188j_9&_afrLoop=326294545831637#SYMPTOM

I was able to use the workarounds to get the logs writing to obis1-query.log.  However, I'm still not able to see these logs within Enterprise Manager.  On the last step when I click on "Target Log Files...", it just takes me to the Weblogic Domain logs.  So my guess is it's another bug for this version.  I tried it out in my Virtualbox, DEV, and TEST environments.  Here are the steps: