Active Directory Integration in OBIEE 12c

This post lists steps to integrate Active Directory with OBIEE 12c installation so authentication in to OBIEE Analytics is taken care by AD.

1) Open Weblogic Console http://<server>:9500/console and use Weblogic credentials to sign in
2) Click on Lock & Edit in Change Center on top left
3) Click on Security Realms on left hand side and select myrealm (or the custom realm created) in next screen
4) Click on Providers tab and then stay in Authentication tab
5) Click on New button and in the next page give this new authentication name eg. ADAuthenticator and select type as ActiveDirectoryAuthenticator and click Ok
6) Click then on new authenticator created (ADAuthenticator) and change the Control Flag as SUFFICIENT under Configuration>Common tab and Save
7) Click then on Provider specific and fill in LDAP details which need to be obtained from LDAP admin
8) Then click on Performance tab and update the following properties.

 Max Group Hierarchies In Cache: 1000

 Group Hierarchy Cache TTL: 600

 Max SID To Group Lookups In Cache: 5000

9) Click Save and go back to Providers list and select DefaultAuthenticator and then change the Control Flag to Sufficient similar to step 6
10) Then click on Reorder button in Providers page and then make sure new ADAuthenticator is top in the list followed by DefaultAuthenticator
11) Click on Activate Changes in Change Center in top left and restart all services (Environment > Servers>Control>Force Shutdown and then restart)
12) This is an extra step for OBIEE 12c per the documentation.

Copy bi-sql-group-provider.jar file from DOMAIN_HOME/plugins/security to Oracle_Home/wlserver/server/lib/mbeantypes

After restart login using AD credentials should be successful. Next step is to setup a database table that will hold user/group association to have object/data level security but that is for another day please let me know in comments.

Cheers!

OBIEE 12c Node Manager keeps going Down

If you encounter Node manager service going down frequently in OBIEE 12c, it is probably because of insufficient heap memory size. In that case you will see error similar to below in the nodemanager log file.

<SEVERE> <Fatal error in NodeManager server>
java.lang.OutOfMemoryError: unable to create new native thread


In this case follow below steps...

1) Bring down all services (Nodemanager, Admin, biserver and bicomponents)
2) Go to <ORACLE_HOME>/oracle_common/common/bin/commBaseEnv.sh
3) Bump up the perm size to something like  -Xms32m -Xmx1024m
4) Save and restart alll services

If you are facing this issue it might be likely that you are having insufficient memory for Admin and biserver as well. Go ahead and change files in below location to have them fixed.

<DOMAIN_HOME>/bin/setDomainEmv.sh


Good Luck!!

Purge Global and Local Cache - OBIEE 11g

If you have clustered node environment using a Global folder to store cache or is a one node environment with Cache stored locally following process will be able to help purge cache both locally and in a global folder. This post is specific to a Linux environment and for a multi node environment this process needs to be repeated in each node.

1) Create a text file with below text. This is the same code you would use to clear cache from presentation (Administration > Issue SQL >)

Call sapurgeallcache()

Save this file at path - /instance/instance1/bifoundation/OracleBIApplication/coreapplication/setup

-- for second node it will be /instance/instance2......


2) Create a .sh file (How to create .sh file - http://www.linfo.org/create_shell_1.html) with below code and you can probably name it as cachepurge.sh

/<OBIEE_HOME>//instance/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh

/<OBIEE_HOME>/Oracle_BI1/bifoundation/server/bin/nqcmd -d AnalyticsWeb1 -u <Admin Username> -p <password> -s /<OBIEE_HOME>//instance/instance1/bifoundation/OracleBIApplication/coreapplication/setup/<file from step 1>

**Replace values in <> with your environment specific values**

3) You can setup a crontab on .sh file so that it is scheduled and executed at intended time. If you want to run it manually then use below in each node

./cachepurge.sh

Setting up job in crontab - http://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/

This process should clear local cache and global cache, Global Cache will be cleared based on the frequency interval you have setup


Hope this Helps!

Exporting Data from OBIEE

As I mentioned in my previous post, exporting a detailed report with columns having more than 15 digits in to excel was an issue in OBIEE 10g. Thanks to guys at Oracle, this is now fixed with OBIEE 11g.

However in case you have a big file with more than 10000 records (I know its not ideal but sometimes Users would like detail data), I follow below process which is also efficient than downloading a file with bigger size in excel format.


> Export > Download data as csv
> Save the file as a text file with .txt extension
> Open New Excel file and go to Data tab
> Click 'from text'
> Select Delimited, click Next
>Select Delimiter (Comma or Tab), click Next
> Select the columns (with more than 8 digits) and select Text on top in Data Format
> Click Finish


Excel I believe can accept data up to 1 Million records.

Hope this is helpful

Export to MS Excel Issue from OBIEE (> 16 digit number))

I was having this issue for a long time now and luckily found a trick in OTN to overcome this.

When you have a column with more than 16 digit number in your OBIEE 10g report and if you export the report to excel, MS Excel converts the number to an exponential value.

eg: 1234E67898765432 to 123 E+79

This can be avoided by following suggestion provided by Satya in OTN forum below.

https://forums.oracle.com/forums/thread.jspa?threadID=2382153

There are other ways to fix these numbers but I think this is the best option as it would not require Users to go through the process of converting.

Hope it helps.

Authentication in OBIEE

Authentication is the process of validating the user's credentials logging in.  Three types of authentication that can be performed in OBIEE are

a)      RPD Authentication (Default)

b)      External Database Authentication

c)      LDAP (Light Weight Directory Access Protocol)

a) RPD Authentication

This authentication is the default authentication which can be set up in RPD in offline mode.This is simple and straight forward authentication where users are created in RPD with respective passwords.

à Open RPD in offline à go to Manage -> Security -> Create users/groups

Keep logging level – 2 for the users so that log can be seen in Answers where we can check the resulting SQL query.

b) External DB Authentication

Create a table in database with userid’s and passwords as columns. This table can be used for authentication for userid’s and passwords.

à Open RPD in offline mode > Manage> Variables> Action> new> session> initialization block.

Name- Anything(DB Authentication) then go to EDIT DATA SOURCE and write a SQL Query to authenticate the user logged in.

 

Give connection pool details, test the query and go to EDIT DATA TARGET

New Variable > name >UserId and check required for authentication.

more detailed explanation is given by Kumar here

d)     LDAP

This is a real time technique for authentication. Before doing Authentication create all the groups that are needed in RPD.

Goto manage> Variables> action>new>session>Init Block, name – LDAP Authentication, datasource type – LDAP

New and give respective details which are usually provided by LDAP Admin. In advanced, domain identifier.

SSL – Secured Socket Layer, if checked ask admin for .kdb(key database files)

Copy them and paste in c:\OBI\Server\Config.

Sometimes certificates are converted to kdb files using Ikey manager.

Time Series Functions

These functions help to obtain the data analyzed based on the time. There are two types of Time Series in OBIEE viz..

1)     Ago

2)     To Date

  Following are the prerequisites and procedure to create time series function in OBIEE RPD.

a)     Create a dimensional Hierarchy (ex: YearàQTRàMonth) and convert it to the time dimension. DC on hierarchy and check the box time dimension.

b)     Define the Chronological Key. A chronological key should always be the lowest level in the table and the data in it should be in the format of 20090102 or 200109 (year, month format). DC on level (preferred/last—detail) go to KEYS tab and check chronological key.

c)     Duplicate the existing column, rename and specify the formula for the column.

1)     Ago: This function when specified in any column displays data that is month ago, 2 months ago depending on the specified formula. DC on new column check use logical columns and click on exp. Builder. In the left column select functions and then time series and then AGO. Then in metric go to logical tables and select metric and in level go to time dimension and select month (1 month ago) and give the period as 1(since it is 1 month ago) then transfer two new columns to presentation layer

.

Ex: sales: 100,200,300 à -, 100, 200 (1 month AGO).

2)     To Date: This functions allows us to display the aggregated measures based on the formula specified. DC on new column check use logical columns and click on exp. Builder. In the left column select functions and then time series then select TO DATE. Select metric from logical tables (revenue) and as level go to time dimension and select year(YTD) and OK.

YTD- year to date – Displays aggregated values that is with respect to month since it is YTD. Similarly for QTD and MTD.

Ex: sales: 100,200,300 à YTD Sales: 100,300,600

Please let me know for any comments/questions.