Tuesday, January 31, 2012

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.


Tuesday, January 24, 2012

Create Hierarchies - Database Side

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




Primary KeyEmployee IDEmployee NameManager ID
110John Doe20
220Jane Doe30

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_widEmployeeLevel 2Level 3Level 4Level 5Level 6
1John DoeJane DoeJay SmithJoane SmithJack DoeJack Doe
2Jane DoeJay SmithJoane SmithJack DoeJack DoeJack 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:



Tuesday, January 17, 2012

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