RSS

Tag Archives: Performance Tuning

How to bypass Presentation Server Cache in OBIEE 11G

During our development if we wanted to check the report level UI changes, sometimes we will not see the changes immidiately. This is due to Presentation Server Cache. Eventhough we disable the BI Server cache, It doesnt make any effect on presentation Server objects.

So in that situations we need to disable Presentation server cache permanently across OBI Application, To do this add the following parameters in instaceconfig.xml. (Make sure that you have taken a backup copy of instanceconfig.xml)

<ServerInstance>
———

<Cursors>
<ForceRefresh>true</ForceRefresh>
</Cursors>

——–

</ServerInstance>

Now restart the OBI Presentation server service and test the results.

Note: Keep in mind that this parameters cause Presentation Server to always bypass its cache. To be used only for testing – may have serious performance implications.

Keep smiling :)

Thanks,
Shiva

 
3 Comments

Posted by on July 17, 2012 in BI Publisher, OBI EE, OBIEE 11g

 

Tags: , , , , , , , , , , , , , , , ,

OBIEE 11G – Important Security Considerations (SSL) if using external LoadBalancer

In OBIEE enterprise topology, make sure the external load balancer used should be able to terminate SSL requests at the load balancer and forward traffic to the back-end real servers using the equivalent non-SSL protocol (for example, HTTPS to HTTP).

For security purposes, and because the load balancer terminates SSL requests (Oracle HTTP Server routes the requests as non-SSL to WebLogic Server), after SSL is configured for the load balancer, turn on the WebLogic Plugin Enabled flag for the domain. To do this, follow these steps:

1. Log in to the Administration Console.
2. Click the domain name in the navigation tree on the left.
3. Click the Web Applications tab.
4. In the Change Center, click Lock & Edit.
5. Select WebLogic Plugin Enabled.
6. Click Save, then click Activate Changes.
7. Restart the Administration Server and Managed Server.

Tip: WebLogic Plugin Enabled: Specifies whether or not the proprietary WL-Proxy-Client-IP header should be honored. (This is needed only when WebLogic plugins are configured.)

In additon to above, make sure Oracle HTTP Server (OHS) to add the following SSL directives in each <location> section to the ORACLE_BASE/admin/instance_name/config/OHS/component_name/mod_wl_ohs.conf file:

WLProxySSL ON

WLProxySSLPassThrough ON

Tips: Set WLProxySSL parameter to ON to maintain SSL communication between the plug-in and WebLogic Server when the following conditions exist:

An HTTP client request specifies the HTTPS protocol
The request is passed through one or more proxy servers (including the WebLogic Server proxy plug-ins)
The connection between the plug-in and WebLogic Server uses the HTTP protocol
When WLProxySSL is set to ON, the location header returned to the client from WebLogic Server specifies the HTTPS protocol.

Reference: https://blogs.oracle.com/pa/entry/obiee_11_1_1_important1

Thanks,
Shiva

 
Leave a comment

Posted by on July 16, 2012 in BI Publisher, OBI EE, OBIEE 11g

 

Tags: , , , , , , , , , , , , , ,

Things to remember for OBIEE 11G on Microsoft SQL Server

The basic things to remember when configuring OBIEE 11G on Microsoft SQL Server:

1. While running RCU:

  • Use case-sensitive collation in order that the database can reflect any case-sensitivity in your repository naming conventions.
    Run this sql command: ALTER DATABASE OBI COLLATE Latin1_General_CS_AS 
    (This command is for UK English installation, for US locale installation use Latin1_General_CP1_CS_AS)
  • Activate the mechanism of row versioning, so it`s require to enable Read Commit Snapshot.
    Run this command: ALTER DATABASE OBI SET READ_COMMITTED_SNAPSHOT ON

2. When configuring OBIEE 11G Scheduler with MS SQL server:

When installed with a Microsoft SQL Server 2005/2008 database, the Oracle Business Intelligence installer configures Oracle BI Scheduler with a SQL Server 2005/2008 driver. If multibyte data is used, scheduled jobs might fail. To avoid this issue, use Oracle Enterprise Manager to configure the Oracle Business Intelligence system to use a SQL Server 7.0/2000 driver instead.

To configure Oracle BI Scheduler to use the SQL Server 7.0/2000 driver with Microsoft SQL Server 2005/2008:

  1. After you install Oracle Business Intelligence, log into the system through Oracle Enterprise Manager.
  2. Navigate to the Scheduler tab for the Oracle Business Intelligence server.
  3. Click Lock and Edit Configuration.
  4. Click the Database drop-down list and select SQL Server 7.0/2000.
  5. Click Apply.

3. When configuring OBI Publisher 11G Scheduler with MS SQL server:

Prerequisite: Ensure that the Microsoft SQL Server is set up with mixed mode authentication. Also ensure that the user that you enter to configure the scheduler has the “db_owner” role.

The below table describes the fields for the DataDirect driver to connect to a Microsoft SQL Server 2000 or 2005 database.

DataDirect Driver Fields for SQL Server Databases

Field Entry
Database Type: Select Microsoft SQL Server 2000 or Microsoft SQL Server 2005 from the list.
Connection String: Enter the following connection string parameters: jdbc:hyperion:sqlserver://<hostname>:<port>;DatabaseName=<DATABASENAME> For example: jdbc:hyperion:sqlserver:// mydatabaseserver.com:1433;DatabaseName=bipscheduler
Database Driver Class: hyperion.jdbc.sqlserver.SQLServerDriver

4. in RPD Development:

  • When you are creating DSN for SQL Server, use sql server native client 10.0 instead of any other drivers. From Sql server 2008 onward, it is strongly recommended even some documents says dont use ODBC native drivers.
  • In RPD, the Query DBMS for SQL server in 11.1.1.6 only work when you specify the DSN name directly (by default it comes with OLAP_DSN session variable).
  • If you have a column with UNIQUEIDENTIFIER as its data type in MS SQL server, then change that column datatype to BINARY(16) or VARCHAR(40) in OBI RPD Physical layer.

5. Performance Tuning:

The following guidelines pertain to performance tuning parameters for Microsoft SQL Server databases. For more information about these parameters, see your Microsoft SQL Server documentation.

  • Store tempdb on a fast I/O device.
  • Increase the recovery interval if perfmon shows an increase in I/O.
  • Use an I/O block size larger than 2 KB.

Thanks,
Shiva

 
2 Comments

Posted by on July 2, 2012 in BI Publisher, OBI EE, OBIEE 11g

 

Tags: , , , , , , , , , , , , , ,

OBIEE 11.1.1.6.0 is available now

The Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.6.0 has been made generally available.

The software is available for download from OTN.

@ http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html

The related documentation is published and available from Oracle Business Intelligence Enterprise Edition Documentation library.
With this release of OBIEE 11.1.1.6.0, the entire Business Intelligence Foundation Suite has been made generally available, opening the door for “Oracle Exalytics Super-fast, Super-ready”.

For more information on exalytics: http://www.oracle.com/exalytics

Oracle Exalytics installation,configuration and deployment info: http://docs.oracle.com/cd/E23943_01/bi.1111/e24706/toc.htm

Happy Reporting….

 

Thanks,

Shiva

 
Leave a comment

Posted by on March 3, 2012 in BI Publisher, OBI EE, OBIEE 11g

 

Tags: , , , , , , ,

How to increase slider value limit in OBIEE 11.1.1.5?

By default OBIEE 11G has slider value limit of 50. But most of the cases we got to change this setting to 100 or more(at least in my case). To change the default set limit, update your instanceconfig.xml file as explained below.

1. Open Instanceconfig.xml file from the location <MW_HOME>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1
2. Make a backup copy of this file before making any changes as you may risk not being able to start BI service
3. Add the following parameters

<Charts>
<SectionSliderDefault>5</SectionSliderDefault>
<SectionSliderLimit>100</SectionSliderLimit>
</Charts>

4. Restart all BI services either from EM or using commands
<MW_HOME>\instances\instance1\bin\opmnctl stopall
<MW_HOME>\instances\instance1\bin\opmnctl startall.

For more information refer http://docs.oracle.com/cd/E14571_01/bi.1111/e10541/prescatadmin.htm. It covers many more parameters, that may be needed to do other, additional customizations, too.

 

Thanks,
Shiva

 
2 Comments

Posted by on November 16, 2011 in BI Publisher, OBI EE, OBIEE 11g

 

Tags: , , , , , , , , ,

RCU Schema Configuration failed with the error “The specified prefix already exists”

We all know that we need to create RCU schemas before starting OBIEE 11G (In fact, for any Fusion Middleware application) Installation. Because it is the base store for FMW.

Recently I came across a scenario where my customer created RCU schemas with Oracle Repository Creation Utility (RCU) version 11.1.1.3.0 and Installed OBIEE 11.1.1.3.0. Now after new OBIEE 11.1.1.5.0 release he wants to upgrade his application from 11.1.1.3.0. But he is not interested at the In-place upgrade. So we uninstalled weblogic and OBIEE 11.1.1.3.0 and started RCU 11.1.1.3.0 to drop the _BIPLATFORM and _MDS schemas.

The RCU shown up with successful message and so we thought everything is cleaned up and it’s good to create schemas again. I started RCU 11.1.1.5.0 and selected Oracle Business Intelligence component and given prefix as DEV. But am not able to assign this prefix and got a message like “The specified prefix already exists”.  Though I got successful dropped schemas message, just for cross check I looked at database for the DEV_BIPLATFORM and DEV_MDS schemas. There I am not seeing any of them.

Then I checked at dropped schemas log generated by RCU and realized that there is an entry in System.SCHEMA_VERSION_REGISTRY$ table which is stopping me to assign the same DEV prefix which we used for earlier version schemas.

Actually the RCU should drop schemas using mdsschemamanager.pl script. This script has capability to do cascade delete and will delete entries in System.SCHEMA_VERSION_REGISTRY$ table.

To make sure this log into RCU DATABASE as sys and run the following SQL Query:

Select * from System.SCHEMA_VERSION_REGISTRY$;

If this query returns any record then it means the RCU didn’t drop the schemas properly.

Actually what happened in this case is the RCU schemas were not dropped using mdsschemamanager.pl script, it’s just dropped with normal schema delete and the resulting repository record for the schema is not clean. Keep in mind, this is just in case if you want to use same prefix. Otherwise RCU will let you proceed to create new prefixed Schemas.

If the RCU schemas have been deleted incorrectly using the RCU drop user command, then We need to clean these entries before starting new repository schemas creation.

To do this, run the following SQL*Plus command as sys to complete the removal of the RCU schemas records in the repository database:

delete from System.SCHEMA_VERSION_REGISTRY$;

Once you confirmed there are no entries in System.SCHEMA_VERSION_REGISTRY$  table and start the RCU to create repository schemas with the prefix (DEV in my case). The RCU will go smooth and wont complain with any error.

Hope this helps you in your RCU issues troubleshooting.

 

Thanks,

Shiva

 
5 Comments

Posted by on November 16, 2011 in BI Publisher, OBI EE, OBIEE 11g

 

Tags: , , , , , , , , , , , , ,

How to Visualize your Audit Data with BI Publisher? – Setup BI Publisher Usage Tracking

In my other blog post (http://shivabizint.wordpress.com/2011/06/19/bi-publisher-usage-tracking-in-obiee-11g/) I have posted a nice video on BI Publisher Usage tracking. After this I have been asked by many of our friends to know how this can be get in action. while I am preparing to write the steps for this, I have found a great article by Kan Nishida from  blogs.oracle.com on same job.For the benifit of readers am sharing the same steps illustrated by him.

With Fusion Middleware Audit Framework you have an option to store the auditing data into a database instead of a log file, which is the default option. Once you enable the database storage option, that means you have your auditing data (or, user report access data) in your database tables, now no brainer, you can start visualize the data, create reports, analyze, and share with BI Publisher.

So, first, let’s take a look on how to enable the database storage option for the auditing data.

How to Feed the Auditing Data into Database

First you need to create a database schema for Fusion Middleware Audit Framework with RCU (Repository Creation Utility). If you have already installed BI Publisher 11G you should be familiar with this RCU. It creates any database schema necessary to run any Fusion Middleware products including BI stuff. And you can use the same RCU that you used for your BI or BI Publisher installation to create this Audit schema.

Create Audit Schema with RCU

Here are the steps:

  1. Go to $RCU_HOME/bin and execute the ‘rcu’ command
  2. Choose Create at the starting screen and click Next.
  3. Enter your database details and click Next.
  4. Choose the option to create a new prefix, for example ‘BIP’, ‘KAN’, etc.
  5. Select ‘Audit Services’ from the list of schemas. image
  6. Click Next and accept the tablespace creation.
  7. Click Finish to start the process.

After this, there should be following three Audit related schema created in your database.

  • <prefix>_IAU (e.g. KAN_IAU)
  • <prefix>_IAU_APPEND (e.g. KAN_IAU_APPEND)
  • <prefix>_IAU_VIEWER (e.g. KAN_IAU_VIEWER)

Setup Datasource at WebLogic

After you create a database schema for your auditing data, now you need to create a JDBC connection on your WebLogic Server so the Audit Framework can access to the database schema that was created with the RCU with the previous step.

image

  • Click ‘Lock & Edit’ so that you can make changes

image

  • Click New –> ‘Generic Datasource’ to create a new data source.
  • Enter the following details for the new data source:
    Name: Enter a name such as Audit Data Source-0.
    JNDI Name: jdbc/AuditDB
    Database Type: Oracle

image

  • Click Next and select ‘Oracle’s Driver (Thin XA) Versions: 9.0.1 or later’ as Database Driver (if you’re using Oracle database), and click Next.
  • The Connection Properties page appears. Enter the following information:
    Database Name: Enter the name of the database (SID) to which you will connect.
    Host Name: Enter the hostname of the database.
    Port: Enter the database port.
    Database User Name: This is the name of the audit schema that you created in
    RCU. The suffix is always IAU for the audit schema. For example, if you gave
    the prefix as ‘BIP’, then the schema name would be ‘KAN_IAU’.
    Password: This is the password for the audit schema that you created in RCU.

image

  • Click Next.
  • Accept the defaults, and click Test Configuration to verify the connection.
  • Click Next
  • Check listed servers where you want to make this JDBC connection available.

image

  • Click ‘Finish’ !

After that, make sure you click ‘Activate Changes’ at the left hand side top to take the new JDBC connection in effect.

image

Register your Audit Data Storing Database to your Domain

Finally, you can register the JNDI/JDBC datasource as your Auditing data storage with Fusion Middleware Control (EM). Here are the steps:

1. Login to Fusion Middleware Control

2. Navigate to Weblogic Domain, right click on ‘bifoundation…..’, select Security, then Audit Store.

image

3. Click the searchlight icon next to the Datasource JNDI Name field.

4.Select the Audit JNDI/JDBC datasource you created in the previous step in the pop-up window and click OK.

image

5. Click Apply to continue.

6. Restart the whole WebLogic Servers in the domain.

After this, now the BI Publisher should start feeding all the auditing data into the database table called ‘IAU_BASE’. Try login to BI Publisher and open a couple of reports, you should see the activity audited in the ‘IAU_BASE’ table. If not working, you might want to check the log file, which is located at $BI_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/AdminServer-diagnostic.log

to see if there is any error.

Once you have the data in the database table, now, it’s time to visualize with BI Publisher reports!

Create a First BI Publisher Auditing Report

Register Auditing Datasource as JNDI datasource

First thing you need to do is to register the audit datasource (JNDI/JDBC connection) you created in the previous step as JNDI data source at BI Publisher. It is a JDBC connection registered as JNDI, that means you don’t need to create a new JDBC connection by typing the connection URL, username/password, etc. You can just register it using the JNDI name. (e.g. jdbc/AuditDB)

  1. Login to BI Publisher as Administrator (e.g. weblogic)
  2. Go to Administration Page
  3. Click ‘JNDI Connection’ under Data Sources and Click ‘New’image
  4. Type Data Source Name and JNDI Name. The JNDI Name is the one you created in the WebLogic Console as the auditing datasource. (e.g. jdbc/AuditDB)
  5. Click ‘Test Connection’ to make sure the datasource connection works.
  6. Provide appropriate roles so that the report developers or viewers can share this data source to view reports.
  7. Click ‘Apply’ to save.

Create Data Model

image

  1. Select Data Model from the tool bar menu ‘New’
  2. Set ‘Default Data Source’ to the audit JNDI data source you have created in the previous step.
  3. Select ‘SQL Query’ for your data set
  4. Use Query Builder to build a query or just type a sql query. Either way, the table you want to report against is ‘IAU_BASE’. This IAU_BASE table contains all the auditing data for other products running on the WebLogic Server such as JPS, OID, etc. So, if you care only specific to BI Publisher then you want to filter by using  ‘IAU_COMPONENTTYPE’ column which contains the product name (e.g. ’xmlpserver’ for BI Publisher).

Here is my sample sql query.

select     “IAU_BASE”.”IAU_COMPONENTTYPE” as “IAU_COMPONENTTYPE”,
“IAU_BASE”.”IAU_EVENTTYPE” as “IAU_EVENTTYPE”,
“IAU_BASE”.”IAU_EVENTCATEGORY” as “IAU_EVENTCATEGORY”,
“IAU_BASE”.”IAU_TSTZORIGINATING” as “IAU_TSTZORIGINATING”,
to_char(“IAU_TSTZORIGINATING”, ‘YYYY-MM-DD’) IAU_DATE,
to_char(“IAU_TSTZORIGINATING”, ‘DAY’) as IAU_DAY,
to_char(“IAU_TSTZORIGINATING”, ‘HH24′) as IAU_HH24,
to_char(“IAU_TSTZORIGINATING”, ‘WW’) as IAU_WEEK_OF_YEAR,
“IAU_BASE”.”IAU_INITIATOR” as “IAU_INITIATOR”,
“IAU_BASE”.”IAU_RESOURCE” as “IAU_RESOURCE”,
“IAU_BASE”.”IAU_TARGET” as “IAU_TARGET”,
“IAU_BASE”.”IAU_MESSAGETEXT” as “IAU_MESSAGETEXT”,
“IAU_BASE”.”IAU_FAILURECODE” as “IAU_FAILURECODE”,
“IAU_BASE”.”IAU_REMOTEIP” as “IAU_REMOTEIP”
from    “KAN3_IAU”.”IAU_BASE” “IAU_BASE”
where “IAU_BASE”.”IAU_COMPONENTTYPE” = ‘xmlpserver’

Once you saved a sample XML for this data model, now you can create a report with this data model.

Create Report

image

Now you can use one of the BI Publisher’s layout options to design the report layout and visualize the auditing data. I’m a big fan of Online Layout Editor, it’s just so easy and simple to create reports, and on top of that, all the reports created with Online Layout Editor has the Interactive View with automatic data linking and filtering feature without any setting or coding. If you haven’t checked the Interactive View or Online Layout Editor you might want to check these previous blog posts. (Interactive Reporting with BI Publisher 11GInteractive Master Detail Report Just A Few Clicks Away!) But of course, you can use other layout design option such as RTF template.

Here are some sample screenshots of my report design with Online Layout Editor.

image

image

Visualize and Gain More Insights about your Customers (Users) !

image

Now you can visualize your auditing data to have better understanding and gain more insights about your reporting environment you manage. It’s been actually helping me personally to answer the  questios like below.

  • How many reports are accessed or opened yesterday, today, last week ?
  • Who is accessing which report at what time ?
  • What are the time windows when the most of the reports access happening ?
  • What are the most viewed reports ?
  • Who are the active users ?
  • What are the # of reports access or user access trend for the last month, last 6 months, last 12 months, etc ?

I was talking with one of the best concierge in the world at this hotel the other day, and he was telling me that the best concierge knows about their customers inside-out therefore they can provide a very private service that is customized to each customer to meet each customer’s specific needs. Well, this is true when it comes to how to administrate and manage your reporting environment, right ? The best way to serve your customers (report users, including both viewers and developers) is to understand how they use, what they use, when they use. Auditing is not just about compliance, but it’s the way to improve the customer service. The BI Publisher 11G Auditing feature enables just that to help you understand your customers better.

Happy customer service, be the best reporting concierge!.

(Original post: http://blogs.oracle.com/xmlpublisher/entry/how_to_visualize_your_audit_da)

 

Thanks,

Shiva

 
5 Comments

Posted by on November 16, 2011 in BI Publisher, OBI EE, OBIEE 11g

 

Tags: , , , , , , ,

 
Follow

Get every new post delivered to your Inbox.

Join 650 other followers