Tag Archives: Oracle
Getting More From Oracle Support – Get Hot Topics
OBIEE Administration Tool – Import Metadata shows no schemas
Importing Metadata with the Administration Tool
The client-only install of the OBIEE 11g Administration Tool is installed with a set of OCI libraries. This means that it can support basic Oracle Database interaction, without the need for a full Oracle Client installation on the machine. For example, you can update row counts in the Physical layer of the RPD of tables that are on Oracle.
Unfortunately, the supplied OCI libraries are not complete, which leads to a rather tricky problem to diagnose. When you use the Import Metadata operation (either from the File menu, or context menu on an existing Connection Pool), the step (“Select Metadata objects”) which ought to show the schemas just shows a stub, and no schemas.
No error is shown by the Administration Tool, giving the erroneous impression that there just aren’t any schemas in the database.
Missing OCI library
The Administration Tool writes a log, which by default is in the following rather long path: C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orainst\diagnostics\logs\OracleBIServerComponent\coreapplication\Administrator_NQSAdminTool.log
If you examine the log, you’ll see this error:
[2011-12-16T15:10:12.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: ] [tid: 8b4] [nQSError: 93001] Can not load library, oracore11.dll, due to, The specified module could not be found. [[ . The specified module could not be found. ]]
Installing the Oracle Client
Once you’ve installed the Full Client, restart the AdminTool and the Import Metadata function will now work.
Footnote – tnsnames.ora
Don’t forget that if you don’t install the full Oracle Client and use the OCI functionality provided by the OBIEE installation alone, you will need to configure your tnsnames.ora file in C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome\network\admin\tnsnames.ora. The exception is if you are using Easy Connect DSNs (dbserver:port/sid) in your RPD rather than TNS entries (orcl etc)
Footnote – troubleshooting library issues
Microsoft’s SysInternals suite includes the program ProcMon. You can point this at a running process and see what it’s up to in terms of file access, DLLs, and networking. It is great for detecting things like:
- Which files a process writes to (eg where is a user preference stored)
- Check which PATHs are being searched for an executable / library
- Which tnsnames.ora is being picked up
- What network connections are being made, or failing
- Registry key access
When you run ProcMon you’ll realise how much is going on in the background of your Windows machine – there’ll be screenful upon screenful of output. To focus on the target of your analysis, use the Include Process option to just show AdminTool.exe:
You can then see things like it searching for the oracore11.dll which it is missing:
The next entry shows the log file being updated, giving you the path if you didn’t know it already:
Reminder: OBIEE 10g premier suppport ending soon
Event Triggers in BI Publisher 11g
Event Triggers in BI Publisher 11g give the facility to call a function in Oracle either before or after a data set is refreshed. The function must return a boolean (true/false), and if it returns false the data model will abort execution.
In this article I will demonstrate how to pass a parameter through to a function in the database, and write this parameter to a table in the database. This could be useful for auditing the use of the system. The parameter that I will use is a system variable, User locale, that can be accessed through the BI Publisher System Variable :xdo_user_report_locale. There are several others available (see list here), and you can also reference your own parameters that you define in the dataset.
In this very simple example, we will write an entry to an audit table every time the data set is refreshed. The table we will use is defined as follows:
--Create table CREATE TABLE bip_audit_log (action_desc VARCHAR(255), action_ts DATE);
The interface between the BI Publisher Event Trigger, and writing to the Database table, is a custom PL/SQL package. You can write your own depending on what you want to achieve with the Event Trigger. If you use parameters, then make sure they are declared globally (and see the note below about matching up parameter names, if you are trying to use parameters defined within the data model).
Here is the example package:
-- This is the package declaration. -- The global variable is essential if you want to pass parameters in. CREATE OR REPLACE PACKAGE bip_audit AS g_input varchar2(255); FUNCTION beforeDatasetRefreshWithInput(g_input IN VARCHAR2) RETURN BOOLEAN; END; / -- This is the package body - the function CREATE OR REPLACE PACKAGE body bip_audit AS FUNCTION beforeDatasetRefreshWithInput(g_input IN VARCHAR2) RETURN BOOLEAN AS BEGIN INSERT INTO bip_audit_log ( action_desc, action_ts ) VALUES ( 'Locale : ' || g_input, sysdate ); COMMIT; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN false; END; END; /
Before you use your package, you should test it:
-- test function -- Use this to invoke the function DECLARE result BOOLEAN; BEGIN result := bip_audit.beforedatasetrefreshwithinput('foobar'); END; / -- Now select from the table, and make sure we got an entry SELECT action_desc, TO_CHAR(action_ts,'YYYY-MM-DD HH24:MI:SS') from bip_audit_log; /
Once you have created the PL/SQL package, create a New -> Data Model in BI Publisher/OBIEE and go to the Data Model Properties page and set the Oracle DB Default Package to that which you have created. Make sure that the PL/SQL package is accessible from the Default Data Set connection that is defined for your Data Model.
Build your Data Set as required, and save the Data Model.
Now go to Event Triggers, and click on Create new Event Trigger.
Name the event trigger Write locale to audit log. Set the Type to Before Data, and Language as PL/SQL.
In the lower pane of the window, under Available Functions you should see the name of the package that you set in the Properties page. If you don’t, check that the package exists as defined in Properties, and can be accessed by the user and connection defined as the Default Data Source for the Data Model.
Underneath the package, you should see the function that you defined, listed in upper case. Highlight the function and click the right arrow to move it into the Event Trigger box on the right.
Now manually edit the Event Trigger text to replace the part in brackets:
G_INPUT:VARCHAR2
with
:xdo_user_report_locale
(note the colon prefix)
Save the changes to your data model, and then click on view XML, and click Run.
When you clicked Run, the Event Trigger should have fired, and you’ll see the results of the Data Set as normal. To confirm the success of the Event Trigger, check the contents of the audit table:
SELECT action_desc, TO_CHAR(action_ts,'YYYY-MM-DD HH24:MI:SS') from bip_audit_log;
Result:
ACTION_DESC TO_CHAR(ACTION_TS,' -------------------- ------------------- Locale : en_US 2011-12-01 16:26:56
This completes the simple example, having shown how to pass a parameter through to a function via an Event Trigger. However, read on below to see details of the problems you may expect to encounter deviating from the very narrow example above.
Gotcha!
The above works fine, unless you add your own parameter to the dataset (regardless of whether you try to use it in the event trigger).
If you try to use the above Event Trigger, in a Data Model in which a Parameter exists, you’ll get this error:
PLS-00302: component 'NEW_PARAMETER_2' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored
(where New_Parameter_2 is the name of the parameter that exists in the data set).
I would imagine that the workaround for this would be to amend the package function to also accept the parameter names also defined in the report, even if they’re not used by the function.
Using your own parameters
If you want to use your own parameter (rather than a system variable as above), then from my testing it appears that the issue described here in 2007 (MOS DocID 859980.1) still holds: your Data Set parameter name must match the name of a global parameter defined in your package function.
So in the above example, a parameter defined in the data set as g_input would work as input to the Event Trigger :
BIP_AUDIT.BEFOREDATASETREFRESHWITHINPUT(:g_input)
But changing the parameter name in the Data Set, and its corresponding reference in the Event Trigger, would not:
BIP_AUDIT.BEFOREDATASETREFRESHWITHINPUT(:my_new_parm_name)
This throws the error :
Message oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xdo.XDOException: java.sql.SQLException: ORA-06550: line 2, column 11: Supplemental Detail PLS-00302: component 'MY_NEW_PARM_NAME' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored
Why this doesn’t apply to passing through system variables (for example, xdo_user_report_locale seen above), I don’t know.
Troubleshooting
Watch out for unhelpful error handling within BI Publisher. If there’s a problem with the XML generation (for example – but not limited to – the Event Trigger call being incorrect), expect to get this kind of thing:
XML Parsing Error: mismatched tag. Expected: </img>. Location: http://rm-win01:9704/xmlpserver/servlet/xdo Line Number 2, Column 580:<table style='background=c[...]
The annoying thing about this is that the actual error is tantalisingly close – if you scroll to the end of the line, you’ll see that it truncates:
oracle.xdo.XDOException: java.sql.SQLException: ORA-06550: line 4, column 51:
So to find out the actual error, go to Enterprise Manager (FMC) and under your Web Logic Domain, navigate to your bi_server and right-click, Logs -> View Logs
Within here you should find the error, and by taking advantage of the ECID (Execution Context ID), you can drill through to related log entries. In this instance, that includes both the error at the BI Publisher level and that reported back from the database.
Version
The above was all tested on BI Publisher 11.1.1.5.
Documentation
Event Triggers are covered in the documentation here
store non-OBIEE objects in the catalog
Using the regular Upload functionality you can easily upload a (for instance) PDF file into the Shared Folders of the webcatalog.
The awkward thing is, that if you put a link on your dashboard and use the Browse button to navigate to the particular folder, you don't see the PDF file since it is not an OBIEE object.
A workaround for this is to create a (dummy) analysis and store it in the same folder as the PDF. Then, as described above, put a link on your dashboard and navigate to the dummy analysis. As a final step, (since you now have the path to the folder where the PDF resides) replace the name of the OBIEE analysis with the PDF's name.
There you go ! Now you can have a link on your dashboard to non-OBIEE objects and use your security model on it !
Kind regards,
René