Check In / Check Out in OBIEE11G Rpd

Check in and Check out process helps users in accessing the rpd objects mutually exclusive, when a user is accessing a particular rpd object then other user will not be able to make any changes.

Example:-

Opened rpd in online mode with weblogic user

Selected the table SAMP_OFFICES_D

 Double click on the table to make the changes, Clicking on Check Out enables user to make the changes.

Changes can be made in the Physical Table properties window

Now Open the same rpd in online mode with other user Pawan

Double click on the same Physical Table

Click on Check Out

A error message pops up.

Reason:- Same object is being used by the user weblogic, so Pawan user is unable to make the changes.

To Roll up last N months data in OBIEE 11g (Based on Month Prompt MMM-YYYY)

I came across a requirement, where user has to select a value from the month prompt(Ex:- DEC-1981) and report should display the data for the past 4 months (DEC-1981, NOV-1981,OCT-1981 and SEP-1981) .
Here month prompt is derived from the date column.

Results should look like this:-

Steps followed:-
--> Create a dashboard prompt and assign a presentation variable.
--> Create a helper report to restrict the dates
--> Create the base report to see data and filter dates based on helper report

Create a dashboard prompt and assign a presentation variable. 

EVALUATE('TO_CHAR(%1,%2)' AS CHAR,"Scott_emp"."HIREDATE",'MON-YYYY')

Create a helper report to restrict the dates

Helper report is used to list the dates that fall in the last 4 months.This is filtered with the selected month value and gives you the starting and ending date for the four months.

For First Day:-
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( TIMESTAMPADD(SQL_TSI_MONTH,-4,"Scott_emp"."HIREDATE")) * -(1) + 1, TIMESTAMPADD(SQL_TSI_MONTH,-4,"Scott_emp"."HIREDATE")))

For Last day:-
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( "Scott_emp"."HIREDATE") * -(1) + 1, "Scott_emp"."HIREDATE")))

Create the base report to see data and filter dates based on helper report

Create report with the required columns and the filters to restrict the report with the dates.

Add a filter on the date column as below

Add a filter on the date column as below

Save all the reports and add them to the dashboard page and output looks like this

[nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE

Evaluate function enables users to use database functions in OBIEE Answers/Rpd

Below is the error message when using EVALUATE in OBIEE answers.
Error Message:-
[nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE

 To fix the issue, change the value EVALUATE_SUPPORT_LEVEL to 2 and restart the opmn services.

OBIEE 11g direct database requests(DDR) prompt

In OBIEE 11g direct database requests has a limitation that, it cannot be used for the prompts. To achieve that we can create an analysis with the DDR, create a pivot view and drag the required columns to the prompts area which will act as prompt for the report

Create direct database request

Enter connection pool and query 

Click on Results. Result looks like below, we need a prompt for this report.

Edit the report and add Pivot Table view

Click on the column properties which you want to add as prompt and select Duplicate Layer

Example:- JOB

Move JOB column to the Pivot Table Prompts Section

Click results now

OBIEE 11g Catalog Documentation

Reports can be built using catalog manager to list the objects and their details in the catalog.

Below are the steps

Open the catalog manager

Click on Tools -> Create Report

“Select type to report on” based on your requirement. If you want a report on dashboards the Select dashboard and click OK

Check Excel Format, if you want report to be saved in Excel and select the location to Save.

Click OK

 Excel is saved in your location. Open excel to see the report output.

OBIEE 11g Rpd Documentation

Repository Documentation utility  in Admin tool helps us to give the detail level of information about the rpd objects(Physical table, Physical Column, Logical Table….. )

Click on Tools -> Utilities and select Repository Documentation and click Execute

Save the csv file in your preferred location 

Open the saved csv file 

DEV_BIPLATFORM and DEV_MDS passwords expired.

When verified the opmn services, scheduler is down and the log for the scheduler is 

[2013-10-17T10:27:11.000+05:30] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: ] [tid: 23c4]  [68008] Scheduler Error:  [[
Current Table: S_NQ_JOB.
[nQSError: 17001] Oracle Error code: 1017, message: ORA-01017: invalid username/password; logon denied
 at OCI call OCISessionBegin.
Current Table: S_NQ_JOB_PARAM.
[nQSError: 17001] Oracle Error code: 1017, message: ORA-01017: invalid username/password; logon denied
 at OCI call OCISessionBegin.
Current Table: S_NQ_INSTANCE.
[nQSError: 17001] Oracle Error code: 1017, message: ORA-01017: invalid username/password; logon denied
 at OCI call OCISessionBegin.
Current Table: S_NQ_ERR_MSG.
[nQSError: 17001] Oracle Error code: 1017, message: ORA-01017: invalid username/password; logon denied
 at OCI call OCISessionBegin.. Errors were encountered while initializing Scheduler's backend database, check database configuration.

Both the passwords DEV_BIPLATFORM and DEV_MDS are expired, so we need to alter the user details with the previous passwords.

 Start the OBIEE domain again and it should work now.

If you don’t know the existing passwords of the users DEV_BIPLATFORM and DEV_MDS then reset the passwords in the database as shown above and also change the password in the following places

Log into console and go to datasources

 Click on the datasource name and then Connection Pool, Change the password to new password and save, Similarly change for all the 4 datasources and save.

Log into em-> Business Intelligence -> Coreapplication

Lock and edit Configuration and change the password

Then Restart the OBIEE domain and everything should work now.

OBIEE 11g Purge Cache Automatically

Open the OBIEE rpd and create a new Database in Physical Layer

(Right click on Physical Layer - > New Database)

Enter Database Name, Type and check Allow direct database requests by default

And Click OK

Create Connection Pool for the database

(Right Click on Database -> New Object -> Connection Pool)

 Enter Details of the Connection Pool
Enter Name,Call Interface Type and select Datasource Name from the drop down (appropriate ODBC) 

And Save the rpd

Log Into OBIEE Presentation and create a new Analysis to purge OBIEE cache

 Click on create Direct Database Request

 Enter "Database"."Connection Pool Name" and "Call SapurgeAllCache(); " in the SQL Statement box.

 Click on Results. Report results shows that Cache has been Successfully purged.

 And Save the analysis "Cache Purge".Select the report in catalog and click more and click Schedule

 Provide the details of the scheduler to schedule the report to execute. Whenever the scheduler executes the report then cache will be purged automatically.