OBIEE Cache Management
OBIEE caching is a file based system. It (OBIEE Caching) is not in memory. Extension of cache file is .TBL Caching is very useful in achieving better erformance by not going to database and getting results from cache file on OBIEE OBIEE server server itself itself.. !nd !nd this this "ay "e can save save net"or net"or# # resou resource rcess by not ma#ing ma#ing round round tri. tri. Busine Business ss Intelligence system user usually $ueries a larger %ata &arehouse or %ata 'arts. !nd "hen as# fe" imort things they are loo#ing for and erformance !L& !L&!* comes+++. Cache hels to ma#e a BI, "in "hen it comes to erformance. &e have to enable cache. -or that "e need to go to /*Config.II file and enable the cache by saying YES. 0) -irst "ay1 &e &e can see cache entries from the Administration tool > Manage> Cache
2) *econd "ay1 &e can see caching by chec#ing file on a server directory (Location for file is usually "e gave the ath in NQSConfig.INI). So Cache helps s in
0) !chieving better erformance erformance for $uery by not going to %atabase server and satisfying re$uest from cache file on OBIEE server itself.( There are some criteria for cache hit to occur) 2) It saves net"or# resources by not ma#e round tri. Bt !ith performance comes some cost. 0) 3uman 4esources to mange 2) 5hysical 4esource (data storage etc) 6) 7eeing cache u to date. (If users gets data $uic#ly but its not fresh8udated then it does not ma#e sense either).
Belo" is the rocess flo" or Architectre of OBIEE cache
Cache *ystem !rchitecture *tes to Configure the Cache
*te01 Change /*Config.ini file ,o to 9 OracleBI9server9Config9 /*Config.II /*Config.II In /*Config.ini go to cache section you "ill see something as belo" screen shot
Cache *esction of Config file !ll the arameters are basically self exlanatory li#e ath of file si:e number of ro"s etc etc Oracle suggests having cache file on high seed storage. Step "# Managing the cache.
&e can do cache management "ith various techni$ues A 1 Configuring cache arameters sho"n in the file above !dministrator tool B 1 Controlling cache at hysical layer in !dministrator $able $able le%el caching &ere you can manage caching through directly hysical Table. Table.
%ouble clic# on 'h(sical table > )eneral tab> enable caching otion by clic#ing. This "ay you can enable 5articular table. There are 2 otions for ho" long u "ant to see the cache Entry. 0)
Caching never exires
2)
Cache ersistence time
C 1;sing Cache manager *# !utomatically urging cache entries E# *eeding cache and event ooling table. A # Configring cache parameters in NQSConfig.ini
,o to < C!C3E = section of the config file + E!BLE should be E* for cache to "or#. " %!T!>*TO4!,E>5!T3* 1 eed ath "here cache file to be stored and max allo"ed si:e , '!?>4O&*>5E4>C!C3E>ET41 'ax ro" to be in any cache file. !nd hence his arameter hels to avoid run a"ay $uery results being cache - '!?>C!C3E>ET4>*I@E 1 'ax si:e of each cache file '!?>C!C3E>ET4IE*1 '!?>C!C3E>ET4IE*1 'ax cache file in the directory secified. Note1 &hen limit secified in '!?>C!C3E>ET4I '!?>C!C3E>ET4IE* E* reached server "ill delete the Least 4ecently ;sed (i.e file "hich has not been used since long) to accommodate the ne" entry / 5O5;L!TE>!,,4E,!TE>4OLL;5>3IT*1 default is O. It "ill store the agg result even if that re$uest is being served from from cache 0 '!?>*; '!?>*;BE BE?5 ?54>* 4>*E! E!4C3 4C3>%E >%E5T 5T31 31 it seache seachess the exres exresss till till B # Controlling cache at ph(sical la(er in Administrator tool
the level level secif secified ied for the cache cache hit. hit.
5hysical 5roerty Of table 1Cache 5lease note the cacheable chec#box. By default all the h ysical tables are cacheable. rest t"o otions determines cache is valid till "hat time A Cache ne%er e1pires "ill ut the entry in cache ermanently unless its cleaned by aramer above Cache persistence time This "ill determine validity time of cache C #2sing Cache manager
Oen reository in online mode ,o to 'anageDCache ou "ill see the screen as belo". OBIEE administrator administrator can control the cache from here as "ell.
Cache 'anager Cache Tab
Cache 'anager 5hysical tabl
Cache Oerations ou can see all the cache entries as sho"n in the screen above. ou can urge the cache from here see the s$l being generated Coy save s$l and see the info "hich basically sho"s you the config arameters for the cache. *o this "ill be helful in urging the cache entries manually. I "ould suggest you to exlore all the otions. *# Atomaticall( prging cache entries Cache entry "ill automatically gets urged "hen '!?>C!C3E>ET4IE* '!?>C!C3E>ET4IE* limit reaches or "hen on hysical layer it reaches that time. !nother &ay to !utomatically urge cache is to use even ulling table. Event ulling table reads the data ( or status of the ETL ) and based on that it urges the cache. The roblem "ith this is1 it is not urely even base. i.e "e can do li#e urge the cache and seed cache "hen ETL is done. 3o"ever it can read the ulling table at secific interval and decides its action. It a searate ost in itself. I "ill address this in my future ost. E# Seeding cache
In normal English *eeding cache means running reort automatically or manually (mostly in non business hours) before user actually actually needs it. *o "hen users needs that data it reads from the cache and hence erformance is really ama:ing. 0 you can run reort maually to generate cache( this is not ossible for non business hours) 2 setting and iBot to run the reort at secific time so that it generates cache. 5lease refer the screen rint belo" for better understanding.
*eeding Cache1 ou ou may create an iBot for the reort for "hich you "ant to seed the cache. In %estinaton tab select Oracle BI *erver Cache chec# box to seed the cache as sho"n above.
OBIEE REPORT/DASHBOARD PERFORMANCE TUNING 1. First check NQServer.log 2. Admin Tool -> click Session Manager. Check if there is an !ottleneck and accordingl resolve. "efresh the sessions### $. Analtics or Ans%ers -> Administration-> Manage Sessions Check the &hsical '(er and e)ec(te from th e !ack-end*+,. Performance Tuning at real time erformance T(ning is the h(ge to&ic to ans%er/ let0s take ste& ! ste& a&&roach as follo%s# Chec# "hether the same $uery is running or anyone udate the $uery.If the same $uery is running then follo" the stes given belo"
•
•
hen o( o&en the dash!oard &age first fig(re o(t %hether rom&t rom&t or "e&ort is taking more time. 3f it is &rom&t check for an m(lti select &rom&ts %hich are taking time to l oad all val(es.. *!est &ractice is set defa(lt val(es.
•
3f "e&ort is taking longer time set some defa(lt fi lters and '(er for 4(st co(&le of records and check th e re&ort &erformance.
•
Take &hsical SQ5 from the session log *if SQ5 is not generated check for log level %hether it is less than 2.
•
"(n this hsical SQ5 in the T6A7 or an SQ5 ,ditor.
•
Check ,)&lain &lan for the cost of th e '(er.
•
8ash 9oins:Cartesian 9oins are al%as kills &erformance.
•
Some-times Force 3nner-4oin also hel&s to force the ta!les to have inner-4oin !et%een t%o ta!les.
•
Check are there an ;F(ll Ta!le Scans< ha&&ening instead of 3nde) Scan.
OBIEE 10G/11G - Event Pooling table (Event Table) to purge the cache after ETL proce
The use of an Oracle BI *erver event olling table (event table) is a "ay to notify the Oracle BI *erver that one or more hysical tables have been udated and then that the $uery cache entries cache entries are stale. Each ro" that is added to an event table describes a single udate event such as an udate occurring to a 5roduct table. The Oracle BI *erver cache system reads ro"s from or olls the event table extracts the hysical table information from the ro"s and urges and urges stale cache entries that reference those hysical tables. The event table is a hysical table that resides on a database accessible to the Oracle BI *erver. 4egardless of "here it residesFin its o"n database or in a database "ith other tablesFit re$uires a fixed schema. It is normally exosed only in the 5hysical layer of the !dministration Tool Tool "here it is identified in the 5hysical Table Table dialog box as being be ing an Oracle BI *erver event table. It does re$uire the event table to be oulated each time a database table is udated. !lso because there is a olling interval in "hich the cache is not comletely u to date there is al"ays the otential for stale data in the cache. ! tyical method of udating the event table is to include */L I*E4T statements in the extraction and load scrits or rograms that oulate the databases. The I*E4T statements statements add one ro" to the event table each time a hysical h ysical table is modified. !fter this rocess is in lace and the event even t table is configured in the Oracle BI reository cache invalidation occurs automatically. !s long as the scrits that udate the event table are accurately recording changes to the tables stale cache entries are urged automatically at the secified olling intervals.
Polling Table Structure ou ou can set u a hysical event olling table on each hysical database to monitor changes in the database. The event table should be udated every time a table in the database changes.
The event table needs to have the structure sho"n belo". The column names for the event table are suggestedG you can use any names you "ant. 3o"ever 3o"eve r the order of the columns has to be the same in the hysical layer of the reository (then by alhabetic ascendant order)
Column Name by alphabetic Data Type Null ascendant order
Advise Description
CHAR or VARCHAR
Populate the CatalogName The name of the column only if the eent catalog where the table does not reside in the Yes Yes physical table that same database as the was updated physical tables that were resides. updated. !therwise" set it to the null alue.
#atabaseName
CHAR or VARCHAR
Populate the #atabaseName column The name of the only if the eent table does database where not reside in the same Yes Yes the physical table database as the physical that was updated tables that were updated. resides. !therwise" set it to the null nu ll alue.
!ther
CHAR or VARCHAR
Yes Yes
CHAR or VARCHAR
Populate the $chemaName The name of the column only if the eent schema where the table does not reside in the Yes Yes physical table that same database as the was updated physical tables being resides. updated. !therwise" set it to the null alue.
CHAR or VARCHAR
No
The name has to match the The name of the name de%ned for the table physical table that in the Physical layer of the was updated. Administration Tool.
#AT'T()' #AT'T()'
No
CatalogName
$chemaName
TableName TableName
&pdateTime
Resered for future enhancements.
This column must be set to a null alue.
The time when the To To ma*e sure a uni,ue and update to the increasing alue" specify eent table the current timestamp as a occurs. This needs default alue for the to be a *ey column. or e/ample" +uni,ue- alue specify #'A&0T
that increases for C&RR'NT1T()'$TA)P for each row added to !racle 2i. the eent table.
&pdateType &pdateType
(NT'3'R
No
$pecify a alue of 4 in the update !ther alues are resered script to indicate a for future use. standard update.
Step by Step for the Oracle database Create the user CREATE user OBIEE_REPO IDENTIFIED BY OBIEE_REPO; GRANT connect,resource TO OBIEE_REPO; Create the table
In 0Hg1 Obi>3ome9bi9server9*chema9*!E5T Obi>3ome9bi9server9*chema9*!E5T.Oracle.s$l .Oracle.s$l --------------------------------------- Create the Event Polln! Ta"le# --------------------------------------CREATE TAB$E %_N&_EPT ' (PDATE_TYPE DECI)A$'*+,+ DEFA($T * NOT N($$, (PDATE_T% DATE DEFA($T %Y%DATE NOT N($$, DATABA%E_NA)E ARC.AR/'*/+ N($$, CATA$OG_NA)E ARC.AR/'*/+ N($$, %C.E)A_NA)E ARC.AR/'*/+ N($$, TAB$E_NA)E ARC.AR/'*/+ NOT N($$, OT.ER_RE%ERED ARC.AR/'*/+ DEFA($T N($$ N($$ ;
In 00g the table is resent in the BI5L! BI5L!T-O4' T-O4' metadata reository reository.. Import the table with ODC !not with OCI" Imort the table "ith O%BC and change the call interface connection ool to OCI OCI..
If you dont have an Oracle O%BC connection you can c an simly suress the table after imortation "ith OCI and coy aste on the schema OBIEE>4E5O the follo"ing ;%'L statmement11 statmement DEC$ARE TAB$E 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0 A% 0%_N&_EPT0 NO INTER%ECTION PRII$EGE% ' READ; DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0(PDATE_TYPE0 A% 0(PDATE_TYPE0 TYPE 0DO(B$E0 PRECI%ION *+ %CA$E + NOT N($$AB$E PRII$EGE% ' READ; DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0(PDATE_T%0 A% 0(PDATE_T%0 TYPE 0TI)E%TA)P0 PRECI%ION *1 %CA$E + NOT N($$AB$E PRII$EGE% ' READ; DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0DATABA%E_NA)E0 A% 0DATABA%E_NA)E0 TYPE 0ARC.AR0 PRECI%ION */+ %CA$E + N($$AB$E PRII$EGE% ' READ; DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0CATA$OG_NA)E0 A% 0CATA$OG_NA)E0 TYPE 0ARC.AR0 PRECI%ION */+ %CA$E + N($$AB$E PRII$EGE% ' READ;
DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0%C.E)A_NA)E0 A% 0%C.E)A_NA)E0 TYPE 0ARC.AR0 PRECI%ION */+ %CA$E + N($$AB$E PRII$EGE% ' READ; DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0TAB$E_NA)E0 A% 0TAB$E_NA)E0 TYPE 0ARC.AR0 PRECI%ION */+ %CA$E + NOT N($$AB$E PRII$EGE% ' READ; DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0OT.ER_RE%ERED0 A% 0OT.ER_RE%ERED0 TYPE 0ARC.AR0 PRECI%ION */+ %CA$E + N($$AB$E PRII$EGE% ' READ;
5hen you import the table with !C(" the &#)0 metadata are metadata are not the same. They hae di6erent synta/ for the de%nition of a column. You You can remar* in the two below statement that the !C( metadata has an e/tra '7T'RNA0 clause that you don8t %nd in the !#9C metadata. %eclaration of the column C!T!LO,!'E C!T!LO,!'E after imort "ith OCI1 DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0CATA$OGNA)E0 A% 0CATA$OGNA)E0 E2TERNA$ 0o"ee_re3o0 TYPE 0ARC.AR0 PRECI%ION 4+ %CA$E + N($$AB$E PRII$EGE% ' READ;
%eclaration of the column C!T!LO,!'E C!T!LO,!'E after imort "ith O%BC1 DEC$ARE CO$()N 0ORC$0##0OBIEE_REPO0#0%_N&_EPT0#0CATA$OGNA)E0 A% 0CATA$OGNA)E0 TYPE 0ARC.AR0 PRECI%ION 4+ %CA$E + N($$AB$E PRII$EGE% ' READ;
De#ne the table as an event table
You You can restart the !racle 9( $erer serice to be sure sure that the table is now seen seen as a good eent table. (f you hae any error in the N:$erer.log N:$erer.log during during the start" correct them. them. Test T est •
•
Create a report with a product attribute to seed the cache. (nsert a row to gie to !9('' the instruction to delete the cache from all $:0 ,uery cache which contain the product table.
IN%ERT INTO %_N&_EPT ' u35ate_t63e, u35ate_ts,
5ata"ase_na7e, catalo!_na7e, sche7a_na7e, ta"le_na7e, other_reserve5
A$(E% ' *, s6s5ate, 8orcl %.8, N($$, 8%.8, 8PROD(CT%8, N($$ •
5ait 5ait the polling interal fre,uency and erify that the cache entry is deleted and that you can %nd the below trace in the N::uery.log N::uery.log %le. %le.
999A57nstrator:e++++:e++*c:----/+*+<+=<*= ++:4>:?? -------------------- %en5n! @uer6 to 5ata"ase na7e5 ORC$ '5: *?=/: select T4==+#(PDATE_TYPE as c*, T4==+#(PDATE_T% as c/, T4==+#DATABA%E_NA)E as c?, T4==+#CATA$OG_NA)E as c4, T4==+#%C.E)A_NA)E as c>, T4==+#TAB$E_NA)E as c= ro7 %_N&_EPT T4==+ here ' T4==+#OT.ER_RE%ERED n '88 or T4==+#OT.ER_RE%ERED s null 7nus select T4==+#(PDATE_TYPE as c*, T4==+#(PDATE_T% as c/, T4==+#DATABA%E_NA)E as c?, T4==+#CATA$OG_NA)E as c4, T4==+#%C.E)A_NA)E as c>, T4==+#TAB$E_NA)E as c= ro7 %_N&_EPT T4==+ here ' T4==+#OT.ER_RE%ERED 8oracle*+!8
999A57nstrator:e++++:e++*c:----/+*+<+=<*= ++:4>:?? -------------------- %en5n! @uer6 to 5ata"ase na7e5 ORC$ '5: *?=*: nsert nto %_N&_EPT'0(PDATE_TYPE0, 0(PDATE_T%0, 0DATABA%E_NA)E0, 0CATA$OG_NA)E0, 0%C.E)A_NA)E0, 0TAB$E_NA)E0, 0OT.ER_RE%ERED0 values '*, TI)E%TA)P 8/+*+-+=-*= ++:44:>?8, 8orcl %.8, 88, 8%.8, 8PROD(CT%8, 8oracle*+!8
999A57nstrator:e++++:e+++?:----/+*+<+=<*= ++:4>:?? -------------------- Cache Pur!e o @uer6: %ET ARIAB$E &(ERY_%RC_CD8Re3ort8,%A_%RC_PAT.8
999A57nstrator:e++++:e++*c:----/+*+<+=<*= ++:4>:?? -------------------- %en5n! @uer6 to 5ata"ase na7e5 ORC$ '5: *?=/: select T4==+#(PDATE_TI)E as c* ro7 %_N&_EPT T4==+ here ' T4==+#OT.ER_RE%ERED 8oracle*+!8 !rou3 "6 T4==+#(PDATE_T% havn! count'T4==+#(PDATE_T% * 999A57nstrator:e++++:e++*c:----/+*+<+=<*= ++:4>:?? -------------------- %en5n! @uer6 to 5ata"ase na7e5 ORC$ '5: *?*=: 5elete ro7 %_N&_EPT here
%_N&_EPT#(PDATE_T% TI)E%TA)P 8/+*+-+=-*= ++:44:>?8
Support The cache polling event table has an incorrect schema
In the /*erver.log /*erver.log file H>=++* The cache 3olln! event ta"le %_N&_EPT has an ncorrect sche7a#
ou ou must imort the event table via O%BC 6.. &hen you use OCI the ;%'L of the table has a difference. ou ou can then change the call interface to OCI. The physical table in a cache polled row does not e$ist In the /*erver.log /*erver.log file H>>++* The 3h6scal ta"le ORC$::%.:PROD(CT% n a cache 3olle5 ro 5oes not eJst#
Jerify Jerify the location of your table. -or instance in my case the good location "as Korcl *311*3154O%;CT* as you can see in the icture belo".
The cache olling *ELECT $uery failed for table &hen you get the follo"ing trace in //uery.log //uery.log its because1 •
•
your eent table was deleted or that the name in the physical layer doesn8t match any more the table in the database.
/+*+-+=-*> +:>/:/ H>>++? The cache %_N&_EPT# /+*+-+=-*> +:>/:/ Hn&%Error: *++* ++14/: ta"le or ve 5oes not eJst at OCI call OCI%t7tEJecute# Hn&%Error: *+*+ /+*+-+=-*> +:>/:/ H>>++> The cache ta"le %_N&_EPT#
3olln! %E$ECT @uer6 ale5 or ta"le Oracle Error co5e: 14/, 7essa!e: ORA-
%&$ state7ent 3re3araton ale5# 3olln! 5elete state7ent ale5 or
faulting module /*Cache.dll Oracle BI *erver can literally crash "hen you use the examle scrit that is given in the documentation ("ith an OT3E4 column). ou ou can find this information in the even vie"er of &indo"s. Descr3ton: Faultn! a33lcaton N&%%erver#eJe, verson *+#*#?#4, aultn! 7o5ule N&%Cache#5ll, verson *+#*#?#4, ault a55ress +J+++*="4?#
*olution1 change the structure of the table by using the scrit in the schema directory d irectory (such as above in the document)
