OBIEE: Enable Log-Level from Report / Analysis

To get OBIEE generated SQL in an environment where LOG is disabled, you can enter below statement in Advanced tab of the report/analysis and click Apply.

SET VARIABLE LOG_LEVEL = 2;

Now you will be able to view Log from Administration > Manage Sessions.

Hope this helped!

SQL/OBIEE: Query CURRENT YEAR / QUARTER / MONTH

To query current year, current quarter and current month, run below query:

SELECT
TRUNC(cast(TO_CHAR(SYSDATE,'YYYY') as VARCHAR(20))),
TRUNC(CAST(TO_CHAR(SYSDATE,'Q') AS VARCHAR(20))),
TRUNC(CAST(TO_CHAR(SYSDATE,'MM') AS VARCHAR(20)))
FROM DUAL;

OBIEE 11g Note: This query can also be used in OBIEE RPD in defining Repository Initialization Block (Repository Variable) to use CURRENT_YEAR, CURRENT_QUARTER, CURRENT_MONTH rather than Session variable.

OBIEE 11g: Enable Analysis/Report Performance Improvement

OBIEE 11g analysis has query performance option to "Share query with multiple users" that improves performance after initial run. This option comes handy for reports that are used in the dashboard.

To enable this option, go to: 

1. Analysis' Advanced tab 

2. In the bottom of the page select "Share query with multiple users (may improve performance after initial run)

3. Click "Apply" and save your analysis.

OBIEE 11g: Manually Clearing Cache from Presentation Services

To manually clear ALL Cache from OBIEE Presentation services, go to:
Administration > Issue SQL >
   Call SAPurgeAllCache();

To manually clear Cache by specific Database, from the same path (Administration > Issue SQL)   

   Call SAPurgeCacheByDatabase('DB Name');
   ('DB Name' can be obtained from RPD's Physical Layer)

OBIEE: Refreshing / Updating GUIDs (including Clustered environment)

To regenerate user GUIDs - perform the following steps: 

(Note: In clustered environment GUID regeneration must occur with only one node operating at a time). 

1. Stop Oracle BI Server and Presentation Services on all nodes except where you are regenerating the user GUIDs.

cd ORACLE_BASE/admin/instancen/bin
./opmnctl stopproc ias-component=coreapplication_obips1 
./opmnctl stopproc ias-component=coreapplicaiton_obis1


2. Update the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI file:
    a. Open NQSConfig.INI for editing at:
        ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn

    b. Locate the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter and set it to YES, as follows:
        FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;
    c. Save and close the file.

3. Update the Catalog element in instanceconfig.xml:
    a. Open instanceconfig.xml for editing at:
        ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obipsn

    b. Locate the Catalog element and update it as follows:
        <Catalog>
        <UpgradeAndExit>false</UpgradeAndExit>
        <UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs>
        </Catalog>
    c. Save and close the file.

4. Restart the Oracle BI Server and Presentation Services using opmnctl:
cd ORACLE_BASE/admin/instancen/bin
./opmnctl stopproc ias-component=coreapplication_obips1
./opmnctl stopproc ias-component=coreapplication_obis1
./opmnctl startproc ias-component=coreapplication_obis1

After you confirm that the Oracle BI Server is running, then start Presentation Services:
./opmnctl startproc ias-component=coreapplication_obips1
5. Set the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI file back to NO. Important: You must perform this step to ensure that your system is secure.

6. Update the Catalog element in instanceconfig.xml to remove the UpdateAccount GUIDs entry.

7. Restart the Oracle Business Intelligence system components using opmnctl:
cd ORACLE_BASE/admin/instancen/bin
./opmnctl stopall
./opmnctl startall

Doc Reference: http://docs.oracle.com/cd/E23943_01/bi.1111/e10543/privileges.htm#BIESC721

OBIEE: Checking Repository Schema version

To check schema version for repository (RCU): connect to the database and run below query:

SELECT * FROM schema_version_registry

Here you can see, I have 11.1.1.6 and 11.1.1.7 schema version.

Hope this helped!

OBIEE 11g: Freeze Catalog - Toggle Maintenance Mode ON/OFF

Maintenance Mode is used for administration of Presentation Services. When Maintenance Mode is turned ON, users will not be able to save changes in Presentation Catalog - since it goes in to a read-only mode.

To turn Maintenance mode ON:
1. Log-in as an Administrator and click on Administration:

2. Under "Maintenance and Troubleshooting" section - click on Toggle Maintenance Mode - and notice the current status change to: ON/OFF

3. During maintenance mode, users will receive the following message:
"Unable to change edit/view mode: System is in Maintenance Mode. Please try again later."

Note: In clustered mode make sure to Toggle Maintenance Mode in Presentation Services of each computer.

Hope this helped!

Downgrading Internet Explorer to Previous Version

If you would like to remove Internet Explorer 9/10 update, follow below steps:

1. Close all programs, go to Start > Control Panel
2. Click Uninstall a Program under Programs category.
3. Click View installed updates option in the task pane.
4. Now select Windows Internet Explorer 9 or 10 shown under Microsoft Windows updates. 

5. Click Uninstall and follow the instructions on the screen.



6. After completing IE 9/10 uninstall Restart your PC and you are DONE!

Hope this helped!

OBIEE 11.1.1.7g Catalog Upgrade Scan

 Once a new catalog has been deployed and catalog path has been changed from Fusion Enterprise Manager, it can be updated from analytics' Administration tab:

1. Go to Administration

2. Click on Scan and Update Catalog Object that Require Updates: 

3. Wait until catalog progress indicated completion. 

Hope this helped!

Leaders do...

"Managers (subordinates) do Things Right; Leaders do the Right Things!"