Deploying new Font in OBIEE


Purpose: I want to use some font beyond the list of fonts available to use in OBIEE reports. OBIEE report has basic fonts(Arial , Courier, Garamond, Times , Tahoma) as below but I want new font list like below “Lato” fonts highlighted in yellow.12-29-2017 8-48-50 AM

Lato is the the font designed by polish designer Lukasz. https://fonts.google.com/specimen/Lato

Here are the steps how to make this font available to use in OBIEE:

  1. Download all the fonts (.ttf) files
  2. Make “Lato” available to Operating system with 3,4 & 5 below
  3. Deploy the lato font in OBIEE server nodes. In my linux environment, this has been installed under orabi user account who is the owner of OBIEE setup and installation. This should be fine if the Linux Admin install it using root .
  4. First create a directory as ‘.fonts’ under your linux home dir , in my case it is ‘/home/orabi/.fonts’ and copied all .ttf files here. Then use command ‘fc-cache -fv’ in shell . This should show as below:

    12-29-2017 9-03-18 AM
    Flush the OS font cache to load new 10 Lato fonts
  5. Now deploy Lato font in OBIEE . Use below steps:
  6. Edit [MiddlewareHOME]/Oracle_BI1/bifoundation/web/msgdb/messages/commonuitemplates.xml and Add Lato under : WebMessage name=”kuiFormatUIFontOptions” as below in Magenta:

    <WebMessage name=”kuiFormatUIFontOptions” translate=”false”><HTML><option value=”Arial”>Arial</option><option value=”Arial Black”>Arial Black</option><option value=”Arial Narrow”>Arial Narrow</option><option value=”Lato”>Lato</option><option value=”Lato-Black”>Lato-Black</option><option value=”Lato-BlackItalic”>Lato-BlackItalic</option><option value=”Lato-Bold”>Lato-Bold</option><option value=”Lato-BoldItalic”>Lato-BoldItalic</option><option value=”Lato-Hairline”>Lato-Hairline</option><option value=”Lato-HairlineItalic”>Lato-HairlineItalic</option><option value=”Lato-Italic”>Lato-Italic</option><option value=”Lato-Light”>Lato-Light</option><option value=”Lato-LightItalic”>Lato-LightItalic</option><option value=”Courier New”>Courier New</option><option value=”Garamond”>Garamond</option><option value=”Lucida Sans Unicode”>Lucida Sans Unicode</option><option value=”Microsoft Sans Serif”>Microsoft Sans Serif</option><option value=”Times New Roman”>Times New Roman</option><option value=”Tahoma”>Tahoma</option><option value=”Verdana”>Verdana</option></HTML></WebMessage>

  7. Copy: [MiddlewareHOME]/Oracle_BI1/bifoundation/web/msgdb/messages/commonuitemplates.xml to [MiddlewareHOME]/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/msgdb/messages/commonuitemplates.xml , if the directory doesn’t exist create it
  8.  Now we need to update OBIEE schema directory to register this new information to OBIEE server. So edit [MiddlewareHOME]/Oracle_BI1/bifoundation/web/schemas/analysis_formats.xsd and add below in Magenta. Note the order matters as in which order you want the font list to be appeared in your UI display.

    <xs:simpleType name=”fontFamily”> <xs:annotation> <xs:documentation> family of fonts supported </xs:documentation> </xs:annotation> <xs:restriction base=”xs:string”> <xs:enumeration value=”Arial”/> <xs:enumeration value=”Arial Black”/> <xs:enumeration value=”Arial Narrow”/> <xs:enumeration value=”Lato”/> <xs:enumeration value=”Lato-Black”/> <xs:enumeration value=”Lato-BlackItalic”/> <xs:enumeration value=”Lato-Bold”/> <xs:enumeration value=”Lato-BoldItalic”/> <xs:enumeration value=”Lato-Hairline”/> <xs:enumeration value=”Lato-HairlineItalic”/> <xs:enumeration value=”Lato-Italic”/> <xs:enumeration value=”Lato-Light”/> <xs:enumeration value=”Lato-LightItalic”/> <xs:enumeration value=”Courier New”/> <xs:enumeration value=”Garamond”/> <xs:enumeration value=”Lucida Sans Unicode”/> <xs:enumeration value=”Microsoft Sans Serif”/> <xs:enumeration value=”Times New Roman”/> <xs:enumeration value=”Tahoma”/> <xs:enumeration value=”Verdana”/> </xs:restriction> </xs:simpleType>

  9. After this bounce OBIEE . Simple opmn restart should work else bounce complete weblogic.
  10. Now you can continue using Lato font everywhere in your Dashboard Page/Reports.

Note that : Be cautious once editing the analysis_formats.xsd and any typo here can cause the entire reports / catalog validation failed throwing “Catalog Object Validation Failed” or “Bad XML” . You can always spot this error and reason of objectionable tags under OBIPS logs . Check the log and it should tell what XML tag has issue.

OBIEE tab/page title Color/Font/Background customization


So coming days I am going to post some cool stuff in OBIEE version 11g onwards and which is not available ad OOB in tool . Lets start with it …

Defining the OBIEE tab color is not out of box feature but here is how you can customize Tab/Page Title/Name by using custom Javascript in OBIEE 11g . Didn’t tried out in 12c but the fundamentals are same.

Each tab is defined by two classes in OBIEE . One is .secondaryTabEnabled and other is .secondaryTabSelected and tabs are part of table id = dashboard_page_x_tab (where x starts at 0 to define starting tab) and the corresponding spans are as below :

12-29-2017 7-11-08 AM
First Page Title color changed to Orange

So, to change the text color of first tab, I have added below code in 2nd tab in a Text components in dashboard layout and marked it as HTML Markup.

<!–script>
var span1 = document.querySelector(‘#dashboard_page_0_tab span’);
span1.innerHTML = ‘<!–font color=”#F38A00″> Page1orTab1 </font–>’ ;

</script–>

(Just remove !– and – – at beginning and end of script and font tag above)

In case the syntax is garbled here is the actual code how it should look like :

12-29-2017 10-57-08 AM

So what we did here is, called the Javascript native querySelector function to find the id of the 1st tab/page and replace the innerHTML of the span element with custom change that I want.

And to remove that tiny expand/collapsible button, just add display:none; in Dashboard layout section properties :

12-29-2017 8-02-03 AM

Similarly, if you want this Orange color to be persisted for 1st tab, keep adding this code in each new dashboard page.

In this way you can highlight tab with background color and change the font family or font style . There are certain limitation for customization here this hack perfectly works.
And that’s it …. stay tuned !

 

OBIEE 12c – “CSF error” OR “OFM security error” OR “no matching Authentication Protocol”


So , Nowhere in OBIEE 12c Installation said that, you need to have certain pre-requisite condition in the DB to ensure successful installation and  you end up scratching head why you are getting all the nonsense in OBIEE installation logs when everything you did right !

Yes , unfortunately that life with Oracle tool ! (! Sigh ! ) and if you are the one person who hit this error you can try out the option below to fix.

While installing brand new OBIEE12c (not an upgrade)  we have seen issue below and log : /home/orabi/oraInventory/logs/config<time>/startallservers.log

OPMN failed and BIServer , BIPServer and BIScheduler all was in SHUTTING DOWN state.

Below is the server status (./status.sh)

Name                Type            Machine                     Status

—-                    —-                 ——-                       ——

AdminServer     Server            dfatobi                   RUNNING

bi_server1        Server             dfatobi                   RUNNING

obips1             OBIPS            dfatobi                   SHUTDOWN      

obijh1              OBIJH             dfatobi                   RUNNING

obiccs1           OBICCS          dfatobi                   RUNNING

obisch1          OBISCH          dfatobi                   SHUTDOWN      

obis1              OBIS               dfatobi                   SHUTDOWN     

And you see the errors below in bunch of   log files  :

[OBIS] [ERROR:1] [] [] [ecid: ] [sik: ssi] [tid: f1b51720]  [46137] CSF error encountered. Error code: 43131. [[^M

file: server/Utility/Generic/Src/SUGCSF.cpp; line: 133

 [OBIPS] [WARNING:16] [] [saw.csf.cache] [ecid: ] [tid: ] [SI-Name: ] [IDD-Name: ] [IDD-GUID: ] [userId: ] OFM security reported error 43131[[

File:csfwrapperimpl.cpp

Line:213

errCode:28040 errSQLStateBuf:HY000 errMsg:[Oracle DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-28040: No matching authentication protocol

errCode:0 errSQLStateBuf:08003 errMsg:[DataDirect][ODBC lib] Connection not open

Oracle BI Server starting…

errCode:28040 errSQLStateBuf:HY000 errMsg:[Oracle DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-28040: No matching authentication protocol

errCode:0 errSQLStateBuf:08003 errMsg:[DataDirect][ODBC lib] Connection not open

Oracle BI Server startup failed.

nqsserver: Oracle BI Server shutdown.

This means you might end up with Hitting Oracle bug ? My DB server was Oracle 12c R2 12.2 . Lot of places in OTN, Oracle said you might try Oracle 12c R1 but I recommend try below step to see if this works for you .

Try disabling OAS (Oracle Advanced Security) . Ask Oracle DBA to change the DB with below in sqlnet.ora file and you are good to go …

SQLNET.ENCRYPTION_SERVER = rejected 

If that doesn’t help you can try enabling OBIEE (creating a sqlnet.ora file under [DOMAIN_HOME]/config/fmwconfig/bienv/core/) for OAS decryption. However I tried this option and it doesn’t work as for new and scratch installation where do I get this before until installation is done… so previous step does the trick .

SQLNET.ENCRYPTION_CLIENT=accepted
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

The actual reason for the issue is : You are using an Oracle Database (for RCU or datasource) with Oracle Advanced Security (OAS) enabled and with SQL*Net Encryption.

 

OBIEE 12c : Component communication ports


As we know OBIEE 12c is different in terms of lot of things , it is also different from its ancestors in terms of the internal system and other components communication ports. Here is a quick snapshot of all available ports:

ADMIN Server : 9500 (EM, Console , DMS)
BI Server : 9502 (Analytics, XMLPserver,BI Publisher, Mobile , Mapviewer, , Visual Analyzer(VA)

OBISCH_SELF_xxx – OBISCH.obisch1 – 9511 – tcp (s)
OBISCH_MONITOR_xxx – OBISCH_MONITOR.obisch1 – 9512 tcp(s)
BIANALYTICS_URLS, ORACLE_BI_SECURITY_SOAP_URLS  – 9505/analytics
OBIPS_HOSTS – 9507
OBIJH_HOSTS – 9510

ORACLE_BI_SECURITY_SOAP_URLS , ORACLE_BI_SEARCH_REST_URLS , ORACLE_MAPVIEWER_URLS  – 9505

ORACLE_BI_JAVADS_SERVER_URL, ORACLE_BICOMPOSER_URLS – 9502

OBICCS_NODES – 9508
OBICCS_MONITOR_NODES – 9509
OBIS_NODES, OBIS_HOSTS – 9514 (This is the communication port to define in System DSN when creating new ODBC data source in OBIEE 12c , Note : default would be 9703 which is fine for 11g and NOT for 12c)
OBIS_MONITOR_NODES – 9515

Also do keep in mind the ports configuration in default installation. This can be changed in weblogic EM but this are the standard communication ports in 12c.

8-19-2018 5-48-46 PM

Excerpt from Oracle 12c documentation:

NOTE:  The default client/server communication method for the Oracle BI Server has changed from Distributed component object model (DCOM) to TCP/IP. Support for DCOM will be discontinued in a future release. For sites already running the Oracle BI Server that want to continue to use DCOM until support is discontinued, leave this field set to its default value and define a Windows system environment variable named NQUIRE_DCOM to force the usage of DCOM. Set the variable value to 1. (To define a system environment variable, select System from the Control Panel, click the Advanced tab, and then click the Environment Variables button to open the Environment Variables dialog box.)

OBIEE 12c: uploadRpd Failed: Failure in trying to acquire lock. Check bi-lcm-logs or diagnostics.


If you see below error after uploading the 12c RPD make sure you have right parameter passed at the command line .

uploadRpd Failed: Failure in trying to acquire lock. Check bi-lcm-logs or diagnostics. Error Desc Code: DESC_CODE_SERVER_EXCEPTION

This typically happens if the instances are wrong which mean -SI parameter in uploadrpd utility . Please make sure you double check and correct this .

In my case ssi is the default -SI argument value at the command line and you should be able to use cd /u00/app/Oracle_Home/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/customizations to land to right path where ssi is my -SI argument.

So double check and this might be your issue and once you right service instances you should be good unless some other issues.

Also non comprehensive bi-lcm-logs tells to lookup under managed server log path and see the error description in details under file bi-lcm-rest.log.x at : /u00/app/Oracle_Home/user_projects/domains/bi/servers/bi_server1/logs

De-install OBIEE 12c in <1 minute


Rather going to GUI or achieving this executing bunch of Unix commands I will show you how to do this in 1 minute and possibly less than 1 minute 🙂

My VM Host is Linux and I have SYSDBA access to DB : 11.2.0.4 (minimal required for 12c)

Step 1 : Stop all running processes 

Go to BITools location :  /u00/app/Oracle_Home/user_projects/domains/bi/bitools/bin

Then ./stop.sh   # this will stop all processes associated with BI

Step 2:  DROP RCU Schema  — Needs SYSDBA privilege or ask DBA

Select * from System.SCHEMA_VERSION_REGISTRY where MRC_NAME='<add DB PREFIX>’;   — Check DB Prefix where RCU is installed

delete from System.SCHEMA_VERSION_REGISTRY where MRC_NAME='<add DB PREFIX>’;

Commit;

Locate all RCU schemas and Perform DROP schema operation . Typically it is 9 RCU schemas.  For my case its below:

drop-schema

Step 3: remove the app folder :

in my case :   rm -rf  /u00/app

You can achieve this above by writing a Unix script combining all steps and just press one button .

This steps applicable for Linux env but for Windows you might need to clear extra stuffs sitting inside Registry.

If you don’t have direct access to database then for Step 2) you can user deinstall utility to drop the repository automatically and let the scripts do that internally.

 

 

OBIEE Agents/Alert Subscription using Java Webservice


After long time I am publishing a interesting post. I could do that long time back but time was not permitting to do so.

So let me first explain the requirement : We had 10g for couple of years before we transitioned to OBIEE 11g. During migration process we lost Subscribers who had subscription to individual Agents/iBOTs. There are several reason why we had lost this, because one of our BI product Catalog is so huge with millions of tiny files (because it has not been cleaned for long time plus it had past 5 years of junk users/folders which never deleted or cleaned) moving those files with archive/un-archive and then OBIEE 10g to 11g upgrade was never successful. So we had to plan for either manually do Subscription for those Alerts or Automatically do that.

Manual was not an option as there were thousand of Alert subscribers who subscribed to individual agents. So that is why the solution come into play. I was never a Java coder but tried my best to code it in below phases:

  1. Install JDeveloper 11g software which will be our UI and java code compiler/execution interface
  2. Create a Application and then Create a Project.
  3. Under one Project add New -) Web Services -) Web Service Proxy
  4. Always select JAX-RPC Style coding
  5. Then Add your WSDL web link which is OBIEE WSDL URL (For e.g : if your OBIEE URL is : http://(host):9704/analytics/saw.dll?Dashboard – your WSDL URL will be http://(host):9704/analytics/saw.dll?wsdl
  6. Then Select the corresponding webservice. In this case we need “SAWSessionService” and “IBotService”

The step by step are in the Slideshow:

This slideshow requires JavaScript.

So finally you see how two Webservice has been added. Now you need the code which is AutoAlert.java and what it does step by step is :

  1. We had a lookup table with all username and password under csv file which is A4.csv here. This csv is has list of users for which we want the Alert subscription automatically for one specific Agents
  2. The code with parse the csv ,take user/password from each row
  3. It will create new OBIEE session for each row and then subscribe that user with specific agent in that OBIEE session itself
  4. Finally once you edit that Agent in OBIEE you will all users subscribed automatically.

For this java file you need to create it under Project as New -) Java -) Java Class and named it as AutoAlert.

5.png

Then paste the code as below: Also replace the OBIEE URL with your env’s URL.

++++++++++++++++++++++++++++++++++++++++++++++++++++

package oracle.bi.webservices;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashMap;

import webservices.bi.oracle.v6.Logon;
import webservices.bi.oracle.v6.LogonResult;
import webservices.bi.oracle.v6.SAWSessionServiceSoapPortClient;
import webservices.bi.oracle.v6.IBotServiceSoapPortClient;
import webservices.bi.oracle.v6.ItemInfo;
import webservices.bi.oracle.v6.Subscribe;
import webservices.bi.oracle.v6.SubscribeResult;

public class AutoAlert
{
public AutoAlert() {
super();
}

public static void main(String[] args) {
try
{
BufferedReader br = new BufferedReader(new FileReader(“D:\\A4.csv”));
String sCurrentLine;
String first = “Username is: “;
String second = “Password is: “;
HashMap data = new HashMap() ;
while ((sCurrentLine = br.readLine()) != null) {
String[] information = sCurrentLine.split(“,”);
String username = information[0];
String password = information[1];
System.out.println(“Username: ” +username + ” / ” + “Password: ” + password );
data.put(username,password);

Logon my_logon = new Logon();
LogonResult logResult = new LogonResult();
my_logon.setName(username);
my_logon.setPassword(password);
String log_sessid = new String();
SAWSessionServiceSoapPortClient new_soap_port_client = new SAWSessionServiceSoapPortClient();
new_soap_port_client.setEndpoint(“http://<obiee_host&gt;:9704/analytics/saw.dll?SoapImpl=nQSessionService”);
logResult = new_soap_port_client.logon(my_logon);
log_sessid = logResult.getSessionID();
System.out.println(“Logon Session ID: “+log_sessid);

IBotServiceSoapPortClient ibot_soap_access = new IBotServiceSoapPortClient();
ibot_soap_access.setEndpoint(“http://<obiee_host&gt;:9704/analytics/saw.dll?SoapImpl=ibotService”);
Subscribe sub=new Subscribe();
SubscribeResult subresult=new SubscribeResult();
sub.setPath(“/shared/Agents/Agency360 Alerts/Pace – Revenue Penetration Rank Decline”);
sub.setSessionID(log_sessid);
sub.setCustomizationXml(“”);
subresult = ibot_soap_access.subscribe(sub);
System.out.println(“Subscribing to Alert: “+sub.getPath());
}
}
catch (java.lang.Exception e) {
e.printStackTrace();
}
}
}

++++++++++++++++++++++++++++++++++++++++++++++++++++

Before you execute this 2 changes required to be done in OBIEE end:

  1. Login as Admin user and go to “Manage Catalog Groups” -) Under Presentation Admin add “Authenticated User” . (This is required because I am not using Impersonate method for my Java webservice call)
  2. Also under “Manage Privilege” add “Presentation Admin” under SOAP  option.

3. Now once you run the project you will see below : with each login each session has been initiated and subscribe to Agents: The failure below because user password was expired

WebService error 2

4. Also if step 1 and 2 is not configured proper way you will see “Insufficient Privilege” issue.

WebService error 3.jpg

WebService error 4

5. Now when this started executing just monitor the Session here and you will see Webservice call :

Manage Sessions while running Alerts Sunscription

6. Finally when everything is done. You will see users are subscribed to Agent as below:

8

7. If you want to run this Subscription for multiple Agents you just need to code such a way and change that Alert path in below method call :  sub.setPath(“/shared/Agents/Agency360 Alerts/Pace – Revenue Penetration Rank Decline”);

8. So you are all set and enjoy writing Java codes to do OBIEE stuff 😉 😀