OBIEE 11g - Manually Upgrade Catalog from 10g to 11g

This allows for catalog upgrade from 10g to 11g version without having to run the GUI tool - UA. 


1. Copy 10g catalog to a temp location
2. Stop 11g BIPS
3. Update instaceconfig.xml to the 10g catalog location i.e. <CatalogPath>10g_Location</CatalogPath>
4. Add these flags to instanceconfig.xml –
<Catalog>
<UpgradeAndExit>true</UpgradeAndExit>
</Catalog>

5. Start BIPS – This will upgrade the catalog and shutdown automatically
6. Remove the flag true from the instanceconfig.xml
7. Start BIPS again



Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE 11g - RPD Upgrade issues

Sachin
JainSys Inc. These are couple of issues I have encountered so far while upgrading from 10g to 11g on the RPD side.
These are simple issues, but the error message are not very forthcoming. Hope this helps you get through.

1. All the TNS entries required in the RPD should be available in the TNS file for the server to start successfully after the conversion. If you see these errors in the log files, update the TNS entries to match the 10g server's.

[OracleBIServerComponent] 
[nQSError: 17014] Could not connect to Oracle database. 
[[[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach.



The TNS entries should be made in this location:

MW_HOME\network\admin\


More details about the directory structure at : OBIEE 11g - Demystifying the directory structure

2. If your 10g RPD has BI Event Tables for caching, remove them before the upgrade. The upgrade process does not convert them over properly, and it will result in errors related to "invalid cache" .


[nQSError:58004] A fatal error occurred while initializing the cache.

3. If you were trying to upgrade the catalog, and the Upgrade tool froze somewhere in the middle, then there is a good chance that you will not be able to start PS anymore.
There will be all sorts of error messages in your error logs, but none of them will point to this:
Look at the instanceconfig.xml and search for the following entry:

<Catalog>
<UpgradeAndExit>true</UpgradeAndExit>
</Catalog>
OR

<ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">
         <ps:UpgradeAndExit>true</ps:UpgradeAndExit>
      </ps:Catalog>

If you see this, remove this entry and restart the services.
Some errors that will show up in the saw.log files if this is the case:

[OBIPS] [NOTIFICATION:1] [] [saw.catalog.scrubacls.scrub] [ecid: ] [tid: ] Scrubbed 0 of 661 records[[
File:scrubacls.cpp

[OBIPS] [NOTIFICATION:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] Upgrade catalog started[[
File:initializecatalog.cpp

Multiple entries similar to this

[OBIPS] [WARNING:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] WARNING: missing SAW_CATALOG_REGISTER_IOOBJECT_FACTORY for scorecardperspectivelist1[[
File:upgradesearchhandler.cpp


I will be updating this log with other issues I encounter!


Sachin
JainSys Inc.

WebLogic - Boot Identity File

Every time a weblogic server is started using scripts or when it is setup as a Windows service, it needs the Weblogic admin userid and password.

One way to ensure that the scripts are not waiting for the user response every time is to create and store the user credentials and save them as a file. the boot identify file starts out as a plain text file, but when the server is started for the first time after (every time a change is made to the file) the entries are encrypted and it is more secure than storing unencrypted credentials in a startup or shutdown script file.

Steps to create the boot identify file:

1. Start the Administrator server once and provide the credentials when prompted.
2. Create a text file and place the following text in it:

username=username

password=password

3. Save the file as boot.properties in the security directory of the server's root directory
D:\app\Administrator\product\11.1.0\MiddleWareHome\user_projects\domains\bifoundation_domain\servers\bi_server1\security
4. The server automatically uses this file at next start and stop.
5. The content of the file after the encryption looks something like this
#Sun Dec 05 17:51:49 CST 2010 password={AES}T2DxUm0Oy3kG3xxxxxxxxxxxxxxxysyJaxjpOSg5Tqw\= username={AES}IV+SFI84mucU0xxxxxxxxxxxxxxxxxxCVfWIMPOtlU8\=

OBIEE 11g - Demystifying the directory structure

Sachin
BuzzClan LLC

Unlike 10g installation, which contained everything within the OracleBI and OracleBIData
directories, the 11g installation's footprint is rather complex. This is due to
the fact that the installation also includes Weblogic, EM and other components.
These are the main directories created on a typical installation:

• wlserver_10.3: The WebLogic Server home, which contains Java components, one Administration
  Server, and one or more Managed Servers.
• user_projects: The User Projects directory, which contains product domains, including
• one or more Oracle Business Intelligence domains.
• Oracle_BI1: The Oracle home, which contains all the binary files (read-only) that
  are specific to Oracle Business Intelligence.

Figure shows a typical Oracle Business Intelligence directory structure in a Fusion Middleware home. At the top of the directory structure is the Middleware home, which contains at least three subdirectories: the WebLogic Server home subdirectory, the User Projects subdirectory, and the Oracle BI home subdirectory. If you remember the installation, you provided the location for the MiddleWare directory at this screen. Recollect this and lets walk to the other locations.

• MW_HOME -- MiddleWare directory
• WL_HOME -- MW_HOME/wlserver_10.3/
• DOMAIN_HOME -- MW_HOME/user_projects/domains/bifoundation_domain/
• ORACLE_INSTANCE -- MW_HOME/instance/instance1

This link lists the names and locations of configuration files for each Oracle Business Intelligence system component. You are advised to use Fusion Middleware Control to update Oracle Business Intelligence configuration settings and ensure that changes are correctly propagated.

This figure illustrates the various components Under ORACLE_INSTANCE directory.
Logs, configuration files, cache, catalog, repository are now stored under this directory as per the schema shown. The general structure is

• MW_HOME\ORACLE_INSTANCE\config\COMPONENT\bi_component_name\
• MW_HOME\ORACLE_INSTANCE\logs\COMPONENT\bi_component_name\

The different components are :

• OPMN –
• OracleBIClusterControllerComponent    -- ClusterController
• OracleBIJavaHostComponent             -- Javahost
• OracleBIPresentationServicesComponent -- Presentation Services 
• OracleBISchedulerComponent -- Oracle BI Scheduler
  
• OracleBIServerComponent               -- Oracle BI Server
• ORACLE_HOME\upgrade\logs              -- logs for upgrade related activities.

Oracle BI content changes from 10g to 11g

Directory or Files	10g Location	11g Location
AdminTool.sh equalizerpds.sh/equalizerpds.exe JobManager.sh MigrateEUL.sh NQClient.sh nqcmd.sh/nqcmd.exe	BI_ORACLE_HOME/server/Bin	BI_ORACLE_HOME/bifoundation/server/bin
DBFeatures.INI NQSConfig.INI	BI_ORACLE_HOME/server/Config	ORACLE_INSTANCE /config/OracleBIServerComponent/coreapplication_obisn
NQClusterConfig.INI	BI_ORACLE_HOME/server/Config	ORACLE_INSTANCE /config/OracleBIApplication/coreapplication
NQQuery.log NQSAdminTool.log NQServer.log	BI_ORACLE_HOME/server/Log	ORACLE_INSTANCE /diagnostics/logs/OracleBIServerComponent/coreapplication_obis1
Oracle BI Server repository directory: SampleAppLite.rpd paint.rpd	BI_ORACLE_HOME /server/Repository	ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/ coreapplication_obisn/repository
Samples: order.xml Product.xml samplesales.udml	BI_ORACLE_HOME /server/Sample/samplesales	ORACLE_INSTANCE /bifoundation/OracleBIServerComponent/coreapplication_obisn /sample/SampleAppFiles
Usage Tracking: SQL_Server_Time UsageTracking.rpd UsageTracking.zip	BI_ORACLE_HOME /server/Sample/usagetracking	ORACLE_INSTANCE /bifoundation/OracleBIServerComponent/coreapplication_obisn/ sample/usagetracking
Other Schemas (for example): Oracle_alter_nq_acct.sql SAACCT.DB2.sql SAACCT.MSSQL.sql SAACCT.Oracle.sql Note: Use the Repository Creation Utility to install the Oracle BI Schema	BI_ORACLE_HOME /server/Sample/Schema	ORACLE_INSTANCE /bifoundation/OracleBIServerComponent/coreapplication_obisn/schema
credentialstore.xml instanceconfig.xml (for Presentation Services) userpref_currencies.xml	OracleBIData/web/config	ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/ coreapplication_obipsn
javahost sawlog0.log	OracleBIData/web/log	ORACLE_INSTANCE /diagnostics/logs/OracleBIPresentationServicesComponent/ coreapplication_obipsn
catalogmanager.exe	BI_ORACLE_HOME \web\catalogmanager	ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\ coreapplication_obipsn\catalogmanager\runcat.cmd
instanceconfig.xml (for Oracle BI Scheduler)	OracleBIData\eb\config	ORACLE_INSTANCE\config\OracleBISchedulerComponent\ coreapplication_obischn
odbc.in user.sh	BI_ORACLE_HOME/setup	ORACLE_INSTANCE /bifoundation/OracleBIApplication/coreapplication/setup

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

External table based authentication and autorization in OBIEE

Sachin
JainSys Inc. The basic out of box security available with OBIEE is via the administration tool. To administer the users/groups one logs in to the RPD and open Security Manager (Manage/Security). Groups, users and their relations are managed via this.

The drawback to this method is that every time we need to add users or manage groups, this becomes an admin task involving IT. In an enterprise application, there are other means of maintaining user access like SSO or custom admin screens which allow for the designate admin roles in the user community to manage access to users. This generally runs off of a custom profile implementation.
A typical layout for this type of security implementation is done via a set of three tables:

• USER
• ROLE
• UserInRole

This allows a many to many association between users and roles.

To take advantage of this in OBIEE we have to configure the system to query the database on login. The steps required to do so are as follows:

1. Setup database connection.

Start by importing from the database which has the security/profile tables.This Connection pool will be used later in the process.

2. Once we have the connection pool ready we now need to set up an Initialization Block. An IB is executed every time a user logs in and a session is being created. The idea is to get teh User name and password passed on through the login page and use that information to query the database and authenticate the user.

3. Open the RPD and Create an new IB by clicking the menu
and then on the Variable Manager :
We need to do this on a "Session" IB not a "Repository" as this will be done as each user logs in.
Select the Data Source Type as "Database".
(If you do not see Database as an option then you may not have a valid ODBC or Oracle client on the server. If you have been able to complete the import step above then this shold not be an issue. Else this is about time you install a client to connect to the data store.)

4. Initialization string: We now need to define a query which will take the UserId and Password passed on to the login screen and check the tables to see if this user exists and have access to this application or not. This is also our chance to grab items from the user profile like Display Name, groups they are assigned to etc.
Typical sql at this point is:

select username, grp_name, SalesRep, 2 from securitylogons where username = ':USER' and pwd = ':PASSWORD'

:USER is a way to indicate that the value of that variable will be substituted on run time.

5. Variable Target: This section allows us to define some variables to contain the results. There are some predefined session level variables:
USER ; PASSWORD; DISPLAYNAME; LOGLEVEL; EMAIL etc
See this for details

The variables should be lined up in the order they are being returned from the query to allow proper assignment.

Check the "Required for authentication" check box to indicate that this IB is used for authenticating users.

Save.

6. Testing : The logs for any issues with this test can be found at {OBIEE Dir}\OracleBI\server\Log\NQServerLogs.log.

I kept getting :
[nQSError: 13011] Query for Initialization Block 'Authentication' has failed.
[nQSError: 17001] Oracle Error code: 911, message: ORA-00911: invalid character at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.

because I had the semicolon at the end of the query. Apparently statement preparation did not like it.

Once there is data in underlying tables, this should be a easy!

7. Next tasks: setting up groups and managing RPD level and Report level security. That's calls for another blog i guess!! Sachin
JainSys Inc.

OBIEE Connection to a MSAS 2005 Cube

1. Configure HTTP access to SQL Server 2005 Analysis Services. - Use these links to configure your DB server to expose the Cubes using XMLA as webservices. For Win2003 For WinXP
   
2. In the instructions, please make sure that the last step is done correctly. It asks to modify the ini file and change the server name of the MSAS db. it defaults to localhost, and if the db name is different then you need to change it to reflect that name. You may get an error "The specified module could not be found" if the server name is not correct.
3. Open OBIEE Administration Tool.
4. Create a new RPD (or open an existing one).
5. Open Menu File--> Import-->From Multi-Dimensional
6. Enter the url to the configured web directory “http://MyMachine/olap/msmdpump.dll”
7. Hit Enter.
8. The next screen should show a list of Cubes available in the server. (mine only shows the server name, no cubes under it)
9. I hit Import.
   
10. <I am getting an error in the Administrator server at this time.%gt
    

OBIEE Error GDO7WNZA

If you encounter this error, here is the check list.

1. Check the Oracle presentation Services. Is it Started?
2. If you are using IIS as the Web server:
The virtual directory created during the install may not be an application yet. Right click on the directory, and press the "Create" button. This will make it an application.
Restart IIS, and try accessing the link again.

-SJ

JainSys