2012 in review – My Blog …Wish you all Happy New Year !


The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

About 55,000 tourists visit Liechtenstein every year. This blog was viewed about 210,000 times in 2012. If it were Liechtenstein, it would take about 4 years for that many people to see it. Your blog had more visits than a small country in Europe!

Click here to see the complete report.

Fixing the BAD XML Instance Issue – OBIEE 11g


“The current xml is invalid with the following errors: Bad xml instance!” is very unusual error but when this has been encountered it is become frustrating as it sounds the entire report can’t be saved !

I remember last time I had faced the similar issue in 11.1.1.3 version while I was building a new report from scratch . The error enforced me not to even edit the reports so I neither I can move to criteria nor I can save this . That was weird !  The only option left was to edit the XML using Catalog manager but until and unless you know where the problem lies you can’t fix it right ?

Now recently I got similar kind of issue in one of our upgraded 11.1.1.6 report which is a Combined request report and when I tried to edit the Criteria Analysis properties Interaction tab to remove Sort column of un-check any other options it throws me below error during the save:

Okay … so the error was at the very last line of the displayed XML as below:

Line:814, Col:61, Element ‘criteria’ is not valid for content model: ‘((criteria,interactionOptions,views,prompts,listOptions),segmentOptions)’

so the bug is around the green circled tags !! Reason is whenever you un-check some options under “Analysis-Properties” it will throw errors !! Probably this is some problem with the up-graded code in 11g from 10g as I tried to replicate the same creating a new reports from scratch which was pretty cool and removing any interaction works perfectly okay in the dashboard report .

After spending some times the reason of the issue found with the fix . I have customized the location for the below tag from Advanced XML :

<saw:interactionOptions drill=”true” movecolumns=”true” sortcolumns=”false” addremovevalues=”true” groupoperations=”true” calcitemoperations=”true” showhidesubtotal=”true” showhiderunningsum=”true” inclexclcolumns=”true”/>

Edited the XML and moved it from beforesection to the end of all. As it is combined request hence several would be present but placed it after all of them.

 After that apply the changes in Advanced XML and voila …. your problem fixed !  Note that this could be the one of incident with BAD XML error and probably you can found it in several other places with no relation to my above fix note . This those cases you need to dig down into more details of the Advanced XML generated and fix those issues as applicable for your case .

Bottom line is same . Dig into the XML details to find out the crappy code and the fix it ! …

So that’s it …. till next time… Happy weekend 🙂

 

Performance Tuning and OBIEE 10/11g


Hello friends, after long time , I am again back on track … I was quite busy with couple of my mandatory schedules for last couple of weeks!

So today my topic is what are the Performance tuning key measures useful while doing report Performance testing. There are lots and I just tried to pull most of them and cover the highlights which would be very helpful while doing the Report Performance testing. Actually this could be separated across several areas like sometime it would be from DB perspective, sometime Environment parameters and sometime it would be the Repository design perspective. However the target objective is same but we need to work well to understand the performance bottleneck from which angles! This will definitely come with the experience and the different challenging environment and every places different rules needs to be applied.

Database Environment Framework:

  • RAC/Cluster / Grid based in NAS and SAN storage – This is recommended in Enterprise Architecture Platform with robust and performance resilient framework.
  • Exadata / Greenplum Architecture – For high performance and high throughput the database Architecture should be robust and especially when there lot of in memory operations intended for better performance especially for transactional analysis.
  • Memory SGA/PGA/Buffer Cache/ Temp, Undo, Redo tablespaces should be optimized using the best practice guided suitable for the underlying hardware.
  • 64 bit Processor (Multicore- Parallel server) and hyper-thread architecture required for better performance on CPU based calculations

Database Tuning Aspects:

  • Bitmap Index / Unique Index (lookup Explain Plan for less Query Cost and Index required suitable for your scenario …typically for all the join keys involved in Dimensional Star modeling)
  • Partitioning is very important and key areas to get most effective performance benefit as most of the analysis involve the time dimension.
  • Gather Statistics or analyze 30% sample of table at-least for Cost Based Optimizer to use, otherwise Rule Based Optimizer will be used . Oracle recommendation to use DBMS_STATS.AUTO_SAMPLE_SIZE for fastest gathering of stats.
  • DB Compression significantly reduces DB block read and enhance performance
  • De-normalization required rather Normalized Snowflakes
  • Parallel query(DB Environment parameter) to get benefit of report accessed on partitioned data leveraging the parallel CPU processing speeds
  • Faster Aggregation – enable Oracle DB Star Transformation (Bitmap join Indexes)

Tuning OBIEE Environment :

  • Multinode Clustering – Horizontal/Vertical scale-out in terms of multiple Admin and Managed Server in Weblogic domain.
  • Leveraging BI Server Caching to get most benefits out of the Cached queries and return the same result without hitting the database multiple times.
  • Switch off Usage Tracking to avoid extra DB operations running against each query.
  • Tuning WLS Param / JVM memory parameters

RPD:

  • Remove Snowflake across dimensions and create the Foreign Key for each Dim into Fact to create Star model
  • Bitmap Index for all Foreign Key columns (for Summary reports) and Bitmap Join Index (lowest granularity based query for detailed data)
  • NULL and NOT Null column in sync with RPD and Database to avoid full outer join with NVL
  • Number of Elements in OBIEE hierarchy definition to determine efficiency of Aggregated table
  • Fragmentation specification wherever required to logically hit different LTS based on fragmented data value sets
  • Limit the number of Initialization blocks to avoid the login performance degradation. However this feature improved in 11g to allow the database query fired once when that corresponding Values referred by Init block hitted.
  • Optimize setting of some NQSConfig.INI parameters (SORT_MEMORY_SIZE,SORT_BUFFER_INCREMENT_SIZE,VIRTUAL_TABLE_PAGE_SIZE)
  • Setting the query limit for a certain number of records and certain time window helps performance improvement for long running Ad-hoc analysis.
  • Limit the Concurrent user access by setting additional parameters
  • Number of DB connection to open settings in connection pool
  • Turn off higher Query log level to avoid File system IO based operations and performance degradation.
  • Manage optimally some key values like MAX_SESSION_LIMIT (to specify how long DB connections remain open with the accessed session), SERVER_THREAD_RANGE , DB_GATEWAY_THREAD_RANGE (DB and Server execution threaded execution)

Catalog:

########

  • Charting thread and Queue related parameters amendment (MaxQueue, MaxThreads)
  • Cache related tunable parameters:

<!– Remove from the cache everything older than N minutes –>

<MaxAgeMinutes>240</MaxAgeMinutes>

<MaxLastAccessedSeconds>14400</MaxLastAccessedSeconds>

<MaxEntries>10000</MaxEntries>

<!– AbsoluteMaxEntries is the enforced maximum number of entries. When this maximum is reached –>

<!– subsequent queries will fail until the maximum is no longer exceeded. –>

<AbsoluteMaxEntries>20000</AbsoluteMaxEntries>

<!– CruiseEntries is amount of entries the OracleBI Presentation server tries to maintain in its cache. –>

<CruiseEntries>3000</CruiseEntries>

<!– Forces the cache to attempt to remove an old entry when MaxEntries is exceeded. –>

<ForceLRU>true</ForceLRU>

<!– Cleanup this cache every N minutes. –>

<CleanupFrequencyMinutes>

Design:

  • Aggregated Tables /Mviews – Follow this approach if there are huge volume of data in detail fact table. Multi-level aggregated table based design return best results.
  • Typically the biggest gains for “Detail” reports performance is :

    •Star Transformation

    •Bitmap Join Indexes

    •Partitioning

  • The biggest gains for “Summary” reports:

    •Gathering Stats 30%

    •Aggregation

    •Star Transformation

    •Partitioning

2011 in Review of my Blog – An excellent presentation summary from WordPress ..I loved it!


The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 160,000 times in 2011. If it were an exhibit at the Louvre Museum, it would take about 7 days for that many people to see it.

Click here to see the complete report.

OBIEE 11g – orthodox De-install Process


Most of the common question is how to Install OBIEE 11g(11.1.1.5) but a bit un-common question is how to De-install until and unless you are facing problem with current installation process . Here are the steps at high level (not screenshot by screenshot) which will perform the orthodox De-install process. As the selection is quite straightforward hence I am skipping the screenshot’s part. There are several blog where you can find the screenshot to help you out.

Briefly, I had the full installation with Weblogic , RCU , Oracle in some path in Windows Server(2008,R2, 64 bit) and after De-installation I want to continue using the same path so all the De-install process should be very clean so that it will not leave any footprints of previous installation in any of the places for a successful installation once again.

Here you go …

  • Stop all running processes for Admin and Managed servers including BI components services. Stop all the processes, if any, created during service process creation for e.g. Windows Process.
  • Remove the Weblogic Server using the Un-installation wizard under Control Panel.
  • Perform De-install from Oracle Business Intelligence -) De-install option of Program menu
  • Drop the RCU schemas using the RCU batch file -) Drop option
  • Manually remove the RCU Tablespace DBF file (to have a clean new RCU schema) . In my case I didn’t remove the DBF file as the Database server is under DBA’s control .Hence, to be safer side I re-create the RCU during the fresh installation process under different tablespace. This will make sure nothing is going to messed up with your previous installation … See below image:
  • De-install Middleware Oracle home (traverse to ..\[MIDDLEWARE_HOME]\Oracle_BI\oui\bin\ and execute below from command line: setup.exe -deinstall)
  • De-install the Middleware common Oracle Home  (traverse to ..\[MIDDLEWARE_HOME]\oracle_common\oui\bin\ and execute below from command line: setup.exe -deinstall –jreLoc  ..\[MIDDLEWARE_HOME]\Oracle_BI1\jdk)
  • Remove the Middleware Home directory with all contents forcefully,if reqd.
  • Remove the environment variable pointing to earlier locations and the relevant paths if any. This is Important!
  • Re-boot the entire server.
  • Delete the Oracle common keys , if any, from registry. This is Important ! Most of the common problem lies here . So in my case I follow the process of finding my Middleware Home path from Registry and delete all relevant Keys and values . Be careful while playing with registry 🙂
  • Remove the windows service process key from registry , if any
  • As in my case I don’t need to De-install my Oracle Client/DB Home hence I skip the process as I going to re-use the same Oracle Home in the next installation time.So if you are going to do the same probably you can skip De-installing your Oracle Client/DB home.

Thats it , Welldone! until you have performed the clean installation once again …..:)

Happy New Year 2012 to All …


Bonjour et bonne année 2012 …

The new journey begins as we landed to New year 2012… Forgetting the old torn memories as it should be better to be in oblivion …. Keep the memories of delightful times and live in each day now as it is to be spent…. !  It is an immense pleasure to let you know my best New year wishes for you … The people … my near and dear ones , my enemy , my followers (Twitters et all) , my critics , my seniors and beloved juniors , finally them whom I don’t know when they silently came to my blog and escape … Hope for all of them it will be a very good year ahead of you both personally and professionally.

Thank you all who encouraged me to carry on my blog and hopefully GOD & you all bless me and it will be continued in greater extent in this year and I hope my threads someway,  sometime  help you !

last but not the least a Season Greetings Card for all of you… Smile 🙂

Cheers …. and bonne santé …. !

Yours ,

Debashis (DxP)

See you soon with some new geeks and hope you will enjoy … Till then …