RSS

Things to remember for OBIEE 11G on Microsoft SQL Server

02 Jul

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

Advertisements
 
2 Comments

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

 

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

2 responses to “Things to remember for OBIEE 11G on Microsoft SQL Server

  1. NV Residences

    August 2, 2013 at 11:45 am

    Hi there, I discovered your site by the use of Google while searching for a similar subject, your
    web site got here up, it looks great. I have bookmarked it
    in my google bookmarks.
    Hello there, simply became alert to your weblog through Google, and
    found that it’s really informative. I am gonna watch out for brussels. I’ll be grateful in case you proceed this in future.
    Many other people can be benefited from your writing. Cheers!

     
  2. mike

    March 21, 2014 at 4:07 am

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

    I do not see this anywhere.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: