OBIEE Tips & Tricks

Weblogic JDBC ORCL connection over SSL

2015-06-22T19:10:00.001-07:00

If you want to create a jdbc data source in weblogic or convert an existing jdbc data source in weblogic to connect to an oracle database over SSL then follow these simple steps

Step 1 : get the Oracle DB certificate chain in a .pem format

Step 2 : create a JKS wallet

source the setDomainEnv.sh or setDomainEnv.cmd in a command window depending on the OS you are on its located at WL_HOME\user_projects\domains\DOMAIN_NAME\bin
cd to FMW_HOME/Oracle_BI1/jdk/bin
import cert using command

./keytool -import -keystore /path/mystore.jks -file /path/cert.pem -alias cert_chain
provide a pwd when prompted

Step 3 :Edit the URL of jdbc connection pool in weblogic to look like this

Current
jdbc:oracle:thin:@SERVER:1300/service_name

to

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=server)(PORT=1310))(CONNECT_DATA=(SERVICE_NAME=service_name)))

Step 4 : in the properties box where you already have the statement user=some_user add the following lines

javax.net.ssl.trustStoreType=JKS
javax.net.ssl.trustStore=/path/mystore.jks
javax.net.ssl.trustStorePassword=myPassword

save and test ...:-) all done

WL Version used : 10.3.6

Clear OBIEE Cache Usign DAC Task Actions

2015-03-05T16:25:00.000-08:00

1. Login To DAC

2. Navigate to Setup >> Physical Data Sources and set up connection to the BI server

Optional >> In the extended properties tab add the connection pool ( Note it should match the connection pool name in your RPD)

3. Click Test Connection to test the connection

4. Navigate to Tools >> Seed Data >> Actions >> Task Action and click new to create a new action to populate aggregate Enter a name

Click on the Value filed to open the dialog and add a command to be run by nqcmd

call sapurgeallcache();

6. Navigate to Tools >> Seed Data >> Logical Data Sources and create a source for the BI Server

7. Goto Design >> Task and create a new task and associate the action created above in the command for incremental and full load, choose the logical data source created above

8. Add the task to a execution plan in the Following Task tab

9. In the execution plan connectivity parameter subtab click generate and map the logical datasource to the Physical Data source

10.Rebuild the plan and fire away J

convert a .pfx file to an Oracle Wallet

2013-06-19T20:48:00.001-07:00

In case you got a .pfx file from your SSL certificate provider and you want to convert it to an Oracle Wallet follow the following simple procedure

Note : Make sure the .pfx file has the entire certificate chain + a private key

Rename the .pfx file to ewallet.p12
copy the ewallet.p12 to the final destination ( where you want to keep your oracle wallet) this is important since copying the wallet around once its done wont help

Launch the Oracle Wallet Manager
Click Wallet >> Open and point to the location where you have kept the ewallet.p12
Provide password if required
click Wallet >> AutoLogin
click Wallet >> Save

Your Oracle Wallet is now ready for use.....

Adding Test Users to Weblogics embedded LDAP

2013-06-19T20:20:00.000-07:00

You may want to add Users in the DefaultAuthenticator that comes OOTB when Weblogic is installed for various reasons ( Providing access to Test teams or creating multiple users for performance testing etc)

Here is a simple method of creating those users

Navigate to the folder

E:\Oracle\Middleware\wlserver_10.3\server\bin

create a file with any name example addMyTestUsers.py

edit the file and add the following script to it and save ( note the script also adds the created users to a group someGroup )

****************************
connect ("admin","password","localhost:7001")
atnr=cmo.getSecurityConfiguration().getDefaultRealm().lookupAuthenticationProvider("DefaultAuthenticator")
users=['test1','test2','test3','test4','test5']
groups=['someGroup']
for user in users:
try:
atnr.createUser(user,'password','TestUser')
print user,': created'
except Exception, e:
print user,': exist'
for group in groups:
for user in users:
try:
atnr.addMemberToGroup(group,user)
print user,': add to group'
except Exception, e:
print user,': exist in group'

*******************************

Open a command prompt

Cd to E:\Oracle\Middleware\wlserver_10.3\server\bin
run the file setWLSEnv.cmd to set environment variables

then run the command

java weblogic.WLST addMyTestUsers.py

The script will add users that done already exist and associate them to a security group or multiple groups as per values provided

Add users to line: users=[‘ username here‘, …]
Add group names to line: groups =[‘group name here’,…]

Change Locale in OBIEE 11g

2012-03-08T17:20:00.000-08:00

this is going to be a quick and small one :-) had to search a lot to find out how to change locale for all users there are 2 things that need to be done

i am assuming that you want to make en-au as the standard locale for every user

a. add the following tags in the instanceconfig.ini located at <MiddlewareHome>/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/
<ServerInstance>

.....lots of other stuff.....
<Localization>

<AllowedLanguages>en</AllowedLanguages>

<AllowedLocales>en-au</AllowedLocales>

</Localization>

</ServerInstance>

b. edit the localemappings.xml file located in <MiddlewareHome>/Oracle_BI1/bifoundation/web/display/

and change the line

<when matches="en*"><localeDefinition name="en-us"/></when>

to

<when matches="en*"><localeDefinition name="en-au"/></when>

also note that if you want to make further customization to the locale like if you want a - in the date format instead of the / then the file to edit is

<MiddlewareHome>/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/localedefinations.xml

Dynamically Change Style for OBIEE

2010-04-27T18:20:00.000-07:00

The most common requirement in OBIEE is to do branding of the portal i.e to remove Oracle Dashboard logo at the top change colors etc etc....

All this is done by changing the image files and style sheets etc , there are many blogs on this and its also documented in the OBIEE Bookshelf
What I am going to cover here is how to set a different style dynamically for a user or group of users as this may be a requirement at places where there are multiple entities in a corporation who share the BI Enterprise infrastructure

For the sake of this blog lets assume that we want to dynamically change the style based on the User who logs in

1. Create a table with 2 columns UserId, STYLE_GRP

2. Insert the names of the folders you create for the different styles for example under D:\OracleBI\web\app\res\ you created folders s_GRPA, S_GRPB, sk_GRPA & sk_GRPB ( you can copy the s_Oracle10 and sk_Oracle10 folders and modify the images and style sheets as required) into this table. You can base it on division instead of individual user's and retrieve the division name from some other employee related table

3. Import the table created in step 1 in the Physical layer

4.Now open the RPD and create a initialization block in the RPD as shown in image which will retrieve the STYLE_GRP column into a STYLE variable from the table created in step 1 on the basis of :USER variable ( make sure that this block runs after authentication/authorization block )

4. close the RPD file restart the BI server (assuming you have created the block while you were online)

5. Its done ( i cant believe its that easy :-) ) now when ever a user logs in a style will be set dynamically. The STYLE variable is un-documented but it works