BI Administration Tool - import metadata select metadata objects shows nothing.

The administration tool is missing ddl as seen in the log file - Administrator_NQSAdminTool.log:


[2012-07-27T10:18:20.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: ] [tid: 76c]  [nQSError: 93001] Can not load library, oracore11.dll, due to, The specified module could not be found. [[
.
The specified module could not be found.


I have found the solution here.
http://www.rittmanmead.com/2012/05/obiee-administration-tool-import-metadata-shows-no-schemas/

Custom install Oracle DB Client and make sure to install Oracle Call Interface (OCI).

Start Weblogic Without Entering Username and Password

Go to:
cd $MW_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/security/

and create file boot.properties this inside


username=Admin_Username
password=Admin_Password


Reference:
Enterprise Deployment Guide for Oracle Business Intelligence
E15722-01
5.2 Creating boot.properties for the Administration Server on APPHOST1
http://docs.oracle.com/cd/E14571_01/doc.1111/e15722.pdf

Uninstall Exalytics software

OBIEE
Oracle_BI1/oui/bin/setup.sh -deinstall
oracle_common/oui/bin/runInstaller -deinstall -jreLoc $JAVA_HOME

Weblogic
fmw/wlserver_10.3/uninstall/uninstall.sh

TimesTen
TimesTen//tt1122/bin/setup.sh -uninstall

Go to /etc/TimesTen
rm -rf /etc/TimesTen/*
touch /etc/TimesTen/instance_info

UnInstall TimesTen:
http://docs.oracle.com/cd/E11882_01/timesten.112/e21632/install.htm#autoId39

Remove Weblogic Server

$MW_HOME/wlserver_10.3/uninstall/uninstall.sh

Exalytics silent installer

It needs to be run in that way:
./setup.sh /home/oracle/EXALYTICS_
INSTALLERS/bi/bishiphome/Disk1/bimachine/scripts/bim-setup.properties

Go to Disk1/bimachine/
from there run setup.sh and the full path to bim-setup.properties
Otherwise an error message an error message saying:



./setup.sh scripts/bim-setup.properties

BUILD FAILED
/home/oracle/EXALYTICS_INSTALLERS/bi/bishiphome/Disk1/bimachine/scripts/bim-setup.xml:26: The following error occurred while executing this line:
/home/oracle/EXALYTICS_INSTALLERS/bi/bishiphome/Disk1/bimachine/scripts/bim-setup.xml:37: The following error occurred while executing this line:
/home/oracle/EXALYTICS_INSTALLERS/bi/bishiphome/Disk1/bimachine/scripts/bim-wls.xml:20: bim.temp.dir ${bim.temp.dir} does not exist!

Total time: 0 seconds

will appear.

Uninstall TimesTen

/u01/app/oracle/product/TimesTen/tt1122/bin/setup.sh -uninstall

$TIMESTEN_HOME/bin/setup.sh -uninstall


Reference:

Uninstalling TimesTen on UNIX systems

Uninstall Oracle Fusion 11g

$MW_HOMW/oracle_common/oui/bin/runInstaller.sh -deinstall -jreLoc $JAVA_HOME


Reference:

6.4.7 Deinstalling the Oracle Common Home

http://docs.oracle.com/cd/E23943_01/bi.1111/e10539/c6_deinstall.htm#CIHFDFEE

Uninstall OBIEE 11g on Linux

From OBIEE installation run ($ORACLE_HOME/oui/bin):
fmw/Oracle_BI1//oui/bin/runInstaller -deinstall

Save the settings for silent uninstaller as described here:

http://docs.oracle.com/cd/E23943_01/bi.1111/e10539/c6_deinstall.htm#autoId20
./runInstaller -silent -deinstall -response path_to_deinstall_instance_file/deinstall_instance_filename.rsp



Install Oracle and OBIEE under the same user in linux.

I was always installing Oracle DB under oracle user and OBIEE under obi user.

In this way under oracle $ORACLE_HOME was point to the database and under obi it was pointing to $MW_HOME\Oracle_BI1
The idea for separation was first suggested to me by Gerard Nico's blog .

So the variables that need to set are:

ORACLE_HOME=$MW_HOME/Oracle_BI1
TNS_ADMIN=$ORACLE_HOME/network/admin
# Create tnsnames.ora under MW_HOME/Oracle_BI1/network/admin


If setting the PATH variable make sure $MW_HOME/Oracle_BI1/bin is before database $ORACLE_HOME/bin.

If OBIEE is going to the DB tnsnames.ora, this type of error might show up:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)

Accidentally deleted a datafile

I have accidentally delete a datafile:

This is how to get the database running again:

Here is a dirty workaround to open the database. Connect to sqlplus as sysdba.

1. STARTUP NOMOUNT;
2. ALTER DATABASE MOUNT;
3. ALTER DATABASE DATAFILE '/filesystem01/datafile11.dbf' OFFLINE DROP;
4. ALTER DATABASE OPEN;
5. DROP TABLESPACE TEST INCLUDING CONTENTS; 

Instructions were found on: http://www.runningoracle.com/product_info.php?products_id=203

Run the Administation Tool in Linux

OBIEE Administration tool comes only for windows.

With wine install Oracle Business Intelligence Developer Client Tools on linux machine.

The installation is very straightforward.

Type winefile and a dialog will open select the downloaded client tools. Double click and continue with the installation.

You will get the Administration Tool, BI Command Prompt, and Job Manager.

Long running queries - progress

In long running queries I am always interested in how long has it been so far and how much longer is left. 

Here sofar/totalwork gives the approximate percentage complete and elapsed_seconds gives for how long it has been running.

select
   l.sid,
   l.sofar,
   l.totalwork,
   l.start_time,
   l.last_update_time,
   l.elapsed_seconds,
   l.elapsed_seconds/60 minutes,
   s.sql_text
from
   v$session_longops l     
left outer join
    v$sql s
on
   s.hash_value = l.sql_hash_value
and
   s.address = l.sql_address
and
   s.child_number = 0;

 

credit: http://www.praetoriate.com/oracle_tips_longrunning_ddl.htm

Turn off the cache

In OBIEE 11g turning off the cache can be tricky.  By default the cache is on.

If not sure whether the cache is on, check NQSConfig.INI file (path  instances/instance1/config/OracleBIServerComponent/coreapplication_obis1).

Line 39  - this shows the cache is enabled:

[CACHE]

ENABLE = YES;  # This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager

As the commend says the setting is managed by the Enterprise Manager so it is best to change it there. Changing the value in the NQSConfig.INI will create confusion.

Log in in the Enterpise Manager, then Capacity Manager tab and Performance sub-tab. Click on Lock and Edit Configuration button.
Uncheck Cache enabled, click on the Apply button , then Activate Changes and restart.

It keeps the cache that it has collected so far. So I go and manually delete the cache files before the restart. The cache files are stored here OBIEE_Location/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/cache.

Create Hierarchies - Database Side

Hierarchical data in transaction databases usually comes with parent-child relationship. For example manager-employee or brand product.

Primary Key	Employee ID	Employee Name	Manager ID
1	10	John Doe	20
2	20	Jane Doe	30

Transactional Table - ttable


The goal is to turn this information into useful level base hierarchy that OBIEE can used for drilling up/down.


 It this case drilling up:

row_wid	Employee	Level 2	Level 3	Level 4	Level 5	Level 6
1	John Doe	Jane Doe	Jay Smith	Joane Smith	Jack Doe	Jack Doe
2	Jane Doe	Jay Smith	Joane Smith	Jack Doe	Jack Doe	Jack Doe

Hierarchical Table - htable.



This is a common transformation. So there should be nice, fast ways to achieve it. There are some solutions:

• multiple joins - one join between each two levels
  SELECT t1.Employee_NAME as employee,
                 t2.Employee_NAME level 2,
                 t3.Employee_NAME level 3 .........
  FROM ( SELECT Employee_Name, Manager_ID FROM ttable) t1,
             ( SELECT Employee_Name FROM ttable WHERE t1.Manager_ID = ttable.Employee_ID ) t2,
  ( SELECT Employee_Name FROM ttable WHERE t2.Manager_ID = ttable.Employee_ID ) t3,
  ..........................................

• In Oracle database this can be achieved with connect by and system_connect_path
  
  The advantage here is that system_connect_path will give the entire path of the hierarchy. In same cases this might be very useful.
  
  
• Order the levels and the pivot the table
  
  If the levels can be queried ordered(the lowest level first or last), then if the result is pivoted the hierarchical table will be created.
   
• With loop in pl/sql or java.
  
  I would try to avoid that method unless it is completely necessary. For example if there are a lot of considerations involved in creating the hierarchy.
  
• Parent-Child hierarchies in obiee

Links:

Uninstall OBIEE 10g on Linux

Uninstalling obiee 10 g is so easy.


OracleBI_home/uninstall
./setup.bin

or if you do not have the X setup
./setup.bin -console


This is the result:

./setup.bin -console
InstallShield Wizard

Initializing InstallShield Wizard...

Please wait...

-------------------------------------------------------------------------------
Oracle Business Intelligence 10.1.3.4.2 will be uninstalled from the following
location:

/u01/app/oracle/obiee

with the following features:

Oracle Business Intelligence JDBC Driver
Oracle Business Intelligence Systems Management
Oracle Business Intelligence Server
Oracle Business Intelligence Cluster Controller
Oracle Business Intelligence Scheduler
Oracle Business Intelligence Client
Oracle Business Intelligence Presentation Services
Oracle Business Intelligence Presentation Services Plug-in and BI Office
Oracle Business Intelligence Publisher

Press 1 for Next, 3 to Cancel or 4 to Redisplay [1] 1

Please wait while processes shutdown.


|-----------|-----------|-----------|------------|
0%         25%         50%         75%        100%
||||||||||||||||||||||||||||||||||||||||||||||||||

Uninstalling the Oracle BI Server PerfMon counters...


Uninstalling the Oracle BI Presentation Services PerfMon counters...


Uninstalling Oracle Business Intelligence 10.1.3.4.2...

-------------------------------------------------------------------------------
The InstallShield Wizard has successfully uninstalled Oracle Business
Intelligence 10.1.3.4.2. Choose Finish to exit the wizard.

Press 1 for Next, 3 to Finish or 4 to Redisplay [1]



Links:

Uninstalling Oracle BI Under Linux and UNIX

http://docs.oracle.com/cd/E12096_01/books/AnyInConfig/AnyInConfigInst219.html#wp1013327