OBIEE Log Level Define

Logging level can be defined for each and every user in the RPD separately. Let’s have a quick look on logging levels:

Log Level	Logging level Information that Is logged
Level 0	No logging
Level 1	Logs the SQL statement issued from the client application Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query
Level 2	Logs everything logged in Level 1 Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application
Level 3	Logs everything logged in Level 2 Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails
Level 4	Logs everything logged in Level 3 Additionally, logs the query execution plan.
Level 5	Logs everything logged in Level 4 Additionally, logs intermediate row counts at various points in the execution plan.

Here, I will show how you can set log level  for a user.

Open the OBIEE RPD in online mode and navigate to Manage -> Identity

Click on action -> Set Online User Filter

Type *, it will show all the user list. Select the user and set the log level.

Save RPD.

OBIEE - Date Expressions

1)      First Day of the Previous Year

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.

2)      First Day of the Current Year

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This calculation returns the first day of the year by deducting one less than the total number of days in the year.

3)      First Day of the Next Year

      TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the      next year.

4)      First Day of the Previous Month

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month
.
     5) First Day of the Current Month

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)


This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.

5)      First Day of the Next Month

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.

6)      First Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.

8) Last Day of the Previous Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.

9) Last Day of Current Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.

10) Last Day of the Next Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
11) Last Day of Previous Year

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.

12) Last Day of Current Year

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.

13) Last Day of the Next Year

TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.

14) Last Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.

15) Number of days between First Day of Year and Last Day of Current Month

 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))

For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

16) First day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY,-6, (TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)))

17) Last day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)

OBIEE 11g - Master-Detail Report Feature !!

One of the new features included in OBIEE 11g is the Master-Detail linking feature. The Master-Detail linking allows you to establish a relationship between two or more views; one view is called the Master and will drive changes in one or more views called detail views.

You can think of a Master-Detail relationship in a similar manner that you would when navigating from one  report to another works, but you do not lose sight of the master view.

So, how do we implement a Master-Detail link?

We start out by defining an analysis that will be used to create the master view. Here we have created a simple analysis and filtered for Year = 2008.

Now we want to configure this analysis so that we can create our master view. We will select the “Per Name Qtr” column as the data driver; so, we open the Column Properties window of this column and move to the Interaction tab.

Here we select the “Send Master-Detail Events” under the Value Primary Interaction. Once you select this option a secondary edit box labeled “Specify channel” will be made available. You can enter any unique identifier in this box. For our example, we have entered “MDS2”.

We now navigate to Results and create the view we want as our Master view. Here we are using a simple tabular view. Notice that our “Per Name Qtr” values do not show the typical hyperlink that you would expect from the time dimension column.

Now we need to create a detailed view that will listen to events from our master view. To do this, we will add an additional graphical view from the same analysis. Our graphical view will look at Revenue by Brand with our Quarter values placed into a view slider.

With our graphical view in edit mode, we need to open the properties window.

One of the properties available to us is the “Listen to Master-Detail Events” checkbox. By checking this box, we can define this view to be a detailed view for the event channel we want. In our case, we will enter the value “MDS2” as our event channel which is the same value we defined on the “Per Name Qtr” column properties which is our data driver on the master view.

We want to validate our Master-Detail report is working as expected. We will do this by placing the compound layout on to a dashboard page. I have edited the compound layout so that my views, master and detail, are side-by-side. When you click on a cell under the “Per Name Qtr”; such as, “2008 Q2” on the tabular master view, the secondary detail view (graph) accepts the Qtr value and the slider automatically moves to the selected value.

OBIEE Log files !!

Today, I will talk about the most important log files which will help you to debug the issues regarding Weblogic Server, Managed Server (BI Server), BI system components.

1.      Weblogic Server:

File Name: AdminServer.log

Location : $OBIEE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs

           

2.      Managed Server:

File Name: bi_server1.log

Location : $OBIEE_HOME/user_projects/domains/bifoundation_domain/servers/ bi_server1/logs

3.      BI System Components:

             BI system component: BI Presentation Services

File Name: console~coreapplication_obips1~1.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1

BI system component: BI Servers

File Name: nqserver.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1

BI system component: BI Cluster Controller

File Name: nqcluster.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIClusterControllerComponent/coreapplication_obiccs1

BI system component: BI Schedulers

File Name: nqscheduler.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1

BI system component: BI JavaHosts

File Name: jh.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIJavaHostComponent/coreapplication_obijh1

Login Issue With Weblogic User !!

Sometime you won’t be able to log in to BI Answer portal using Weblogic user itself. This issue will come because of credential mapping issue for BISystemUser.

In this case we need to reset the password or remapping the credentials of BISystemUser.

To remap the credential of BISystemUser steps are mentioned below:

1.       Login to Weblogic Console portal.

2.       Navigate to: Home >Summary of Security Realms > myrealm >Users and Groups

3.       Select BISystemUser and change the password.

4.       Login to Oracle Enterprise Manager (EM).

5.       Navigate to: Weblogic Domain > right click on bifoundation_domain > Security > Credentials > oracle.bi.system > system.user > Edit .  

6.       change the password. (Give same password as you have given in Weblogic console).

7.       Restart the BI System Components from Enterprise Manager (EM).

Now you should be able to login to BI Answer portal.

OBIEE 11g Cache Clean

To delete the cache of OBIEE:


We can delete the cache through OBIEE analytics page. Login as administrator user and navigate to Administration - > Issue SQL.
Then type as below.

1. Deletes all cache info :   Call SAPurgeAllCache() ;

2. Deletes specific table cache :  Call SAPurgeCacheByTable( ‘Datwarehouse’,  ”,’DWH’, ‘XW_SALES_F’);

3. Deletes all cache related to a specific database :  Call SAPurgeCacheByDatabase( ‘Datwarehouse’ );

4. Deletes specific logical SQL Cache : Call SAPurgeCacheByQuery(‘SELECT   0 s_0,    “Sales Subject Area”.”Time – Dimension”.”MONTH_NAME” s_1, “Sales Subject Area”.”Fact Sales”.”COST_PRICE” s_2 FROM “Sales Subject Area” ORDER BY 1, 2 ASC NULLS LAST ; ‘)

Start OBIEE 11g Server without prompting Username and Password

When we starting BI services for OBIEE 11g always the services will prompt for Admin username and password. To avoid/fix this prompt we have a workaround as follows.

Create a boot.properties file for the Administration Server and BI server. This file enables the Servers to start without prompting you for the administrator username and password.

1.  Go to the following directory:
<Middleware_HOME>/user_projects/domains/bifoundation_domains/servers/AdminServer/security

2.  In this directory, create a file called boot.properties using a text editor and enter the following lines in the file:
username=Admin_Username
password=Admin_Password

3. Save the file and close the editor. (Make sure that the extension of the file should be boot.properties not boot.properties.txt)

4. Place the same boot.properties in following driectory too to start BI server without asking username and password.
<Middleware_HOME>/user_projects/domains/bifoundation_domains/servers/bi_server1/security/

Note:
When you start the Administration Server, the username and password entries in the file get encrypted.  For security reasons, you want to minimize the time the entries in the file are left unencrypted. After you edit the file, you should start the server as soon as possible so that the entries get encrypted.

Informatica Repository Service Error (REP_51002 Database driver event)

Error Syntax:

            serviceType     serviceName    severity            timestamp        threadName    messageCode  message

RS       UAT_REP_SERVICE           1$_:_$ERROR           01/21/2014 12:54:29.412 PM 140524809037552            REP_51002     Database driver event...Error occurred loading library [libclntsh.so.10.1: cannot open shared object file: No such file or directory]Database driver event...Error occurred loading library [libpmora8.so]

REP_51002 informatica

REP_51002 Database driver event...Error occurred loading library [libclntsh.so.10.1: cannot open shared object file: No such file or directory]Database driver event...Error occurred loading library [libpmora8.so]

Cause:

            This is because environment setup and oracle client installation.

Solution:

1.      $ LD_LIBRARY_PATH variable need to set properly ($ORACLE_HOME/lib)

2.      Check missing lib file (libclntsh.so.10.1) is available in $ORACLE_HOME/lib location. If you don’t have exact name, you might have file like libclntsh.so.11.1, which is higher than the missing one. You need to create symbolic for the same as below:

create s symbolic

ln -s libclntsh.so.11.1 libclntsh.so.10.1

Informatica Admin Console is not Launching

Sometime after restarting the Informatica services you won’t be able to launch Admin Console.

Try with the below steps,  hope it will resolve your issue.

1.      Shutdown the Informatica Server.

2.      Then try clearing out the following:

-All Files under $infa_home/tomcat/logs

-All Files/directories under $infa_home/tomcat/temp

-All Files and directories under $Infa_home/tomcat/work

-Only directories under $infa_home/tomcat/webapps. Remember don’t delete any war files here . Delete only directories.

            3.  Startup the Informatica Server.

Informatica Services

To start and stop the Informatica Services :

Navigate to the Informatica/PowerCenter8.6.1/server/tomcat/bin  location and type the below commands:

./infaservice.sh startup  

./infaservice.sh shutdown  

To check the Informatica Services Status:

1.      Integration Service: ps -ef | grep pmserver

2.      Repository Service: ps -ef | grep pmrepagent

3.      Admin Console: ps -ef | grep org.apache.catalina.startup.Bootstrap  

4.      Admin Console: ps -ef | grep com.informatica.isp.tools.tomcatstarter.InfaTomcatBootstrap

Error Handling While Restarting OBIEE 11g Components

You may get different kind of error while restarting the BI components. Some of the common errors I will discuss here today.

Error 1:

Error Syntax:

                        Weblogic.manaement.managementException: Unable to obtain lock on /path......../AdminServer.Lok. Server may already be running

Cause:

If AdminServer.Lok file exists then it will cause an issue while restarting the Weblogic Domain and if AdminServer.lok file exists then it will cause an issue while restarting the Managed Server. You will get the files in below path:

è OBIEEFolder/user_projects/domains/bifoundation_domain/servers/AdminServer/tmp

è OBIEEFolder/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp

Solution:

Simply delete these files and execute the run script again, the errors will go away.

Error 2:

Error Syntax:

When you are running these starting scripts in the background as indicated in Step A and C, the system prompts you to enter the username, when you do so, you end up getting 'weblogic: comment not found' and the system doesn't get started. And sometime you may get authentication error too.

Cause:

boot.properties file is not available in the below mentioned path:

è OBIEEFolder/user_projects/domains/bifoundation_domain/servers/AdminServer/ security

è OBIEEFolder/user_projects/domains/bifoundation_domain/servers/bi_server1/ security

Solution:

            Either you can create a new boot.properties file or same can be copied from the below location:

           

è OBIEEFolder//user_projects/domains/bifoundation_domains/servers/bi_server1/data/nodemanager

Place the boot.properties file to the above mentioned two locations.