Saturday 14 November 2015

Useful TIMESTAMP Codes in OBIEE11g





Below Timestamp related codes are very very useful in Obiee. These codes are to be placed in the Column Formula (fx tab) or Can be used in Filter conditions in Criteria tab.

Current Date:

TIMESTAMPADD(SQL_TSI_DAY, 0, CURRENT_DATE)

Previous Date:


TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)

Next Date:

TIMESTAMPADD(SQL_TSI_DAY, 1, CURRENT_DATE)

Current Month Number:

MONTH(CURRENT_DATE)

* Shows the results in Number format like 1,2,3 etc. Shows the month number.

Current Quarter Number:

QUARTER_OF_YEAR(CURRENT_DATE)


* Shows the results in Number format like 1,2,3 etc. Shows the month number.

Current Year Number:

YEAR(CURRENT_DATE)


* Shows the results in Number format like 2012,2013,2014 etc. Shows the month number.

Current Month Name:

MONTHNAME(CURRENT_DATE)

 * Shows the results in character format like Jan,Feb,Mar etc. Shows the month number.

Last Month Start Date:

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

Current Month Start Date:

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

Next Month Start Date:

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

Current Year Start Date:

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

 Last Year Start Date:

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

Current Month End Date:

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

Current Year End Date:

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)))

Last Year End Date:

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

Tuesday 13 October 2015

How to work with multiple value select prompt in obiee11g Direct Database Request


Scenario:

You have a dashboard prompt with multi-select option and a presentation variable set. You have a report with a field using a filter formula based on the presentation variable.  When multiple values are selected in the dashboard prompt, the query generated is incorrect. The query should select the multiple values selected by a comma. Instead of the results, you get this message:
No Results

The specified criteria didn't result in any data. This is often caused by
applying filters and/or selections that are too restrictive or that contain
incorrect values. Please check your Analysis Filters and try again. The
filters currently being applied are shown below.

This occurs when the Filter SQL function is used in a column formula within a request; for example:
FILTER("Base Facts"."1- Revenue" USING ("Products"."P4 Brand" IN ('@{var_mlt}{FunPod}')))

It does not occur if a standard filter is applied to the analysis.  The incorrect SQL produced is, for example
[2012-02-07T14:47:34.000+00:00] [OracleBIServerComponent] [TRACE:3] [USER-18]
[] [ecid: 20f12d143003324e:d29df33:13558086f8a:-8000-000000000000084b] [tid:
a30] [requestid: 33990013] [sessionid: 33990000] [username: weblogic]
-------------------- Sending query to database named 01 - Sample App Data
(ORCL) (id: <>), connection pool named Sample Relational Connection:
[[
WITH
SAWITH0 AS (select sum(T42442.Revenue) as c1,
T42406.PER_NAME_YEAR as c2
from
SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ ,
SAMP_TIME_QTR_D T42406 /* D03 Time Quarter Grain */ ,
SAMP_REVENUE_FA2 T42442 /* F21 Rev. (Aggregate 2) */
where ( T42406.QTR_KEY = T42442.Bill_Qtr_Key
and T42409.Brand = 'BizTech,FunPod,HomeView'
and T42409.Prod_Key = T42442.Prod_Key )
group by T42406.PER_NAME_YEAR)
select distinct 0 as c1,
D1.c2 as c2,
D1.c1 as c3
from
SAWITH0 D1
order by c2

The problem is that the multiple values are passed in a single string to the physical SQL, and the operator used is "=", when it must be "IN":
and T42409.Brand = 'BizTech,FunPod,HomeView'

 instead of:
and T42409.Brand in ('BizTech','FunPod,HomeView')
 
However, if you create a report filter like this, you get the correct values:
P4 Brand is equal to / is in @{var_mlt}{FunPod}
 
Root Cause of the Issue:
 
Incorrect syntax used in the 'Filter' option, 'Criteria' tab. There is a specific format of syntax that should be used when you want to work with Presentation Variable.

Solution:

You need to make sure the syntax is correct.


Review the correct syntax to use in the Advanced SQL in a filter to reference a presentation variable with potentially multiple values. The syntax differs slightly depending on the column type.
Text column:
Select T1.C1 from SA where T1.TextColumn in (@{myTextVar}['@']{'Default'})

Numeric column:
Select T1.C1 from SA where T1.NumberColumn in (@{myNumVar}{1000})

Date Time column:

Select T1.C1 from SA where T1.DateTimeColumn in (@{myDateTimeVar}{timestamp '2000-01-21 00:00:00'})

Date-Only column:
Select T1.C1 from SA where T1.DateColumn in (@{myDateVar}{date '2000-01-21'})


Time-Only column:

Select T1.C1 from SA where T1.TimeColumn in (@{myTimeVar}{time '00:00:00'})
 

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.5.0 [1308] and later
Business Intelligence Server Enterprise Edition - Version 11.1.1.5.0 [1308] and later
Applies to any platform. 
 

References


OBIEE 11g: Presentation Variable With Multi Value Selected Gives 'No Results' and Wrong Query Generated. (Doc ID 1418868.1)

BUG:13696529 - INCORRECT PHYSICAL QUERY WHEN USING FILTER AND A PRESENTATION VARIABLE MULTIPLE
 

Friday 18 September 2015

[nQSError: 77006] Oracle BI Presentation Server Error: A fatal error occurred while processing the request. The server responded with: Path not found (/shared/xxxxx/xxxx) Error Codes: U9KP7Q94



Scenario:

We have few agents scheduled which runs fine everyday. Few of the agents delivery content is format is Excel 2007 and for few is CSV and PDF. All of the sudden most of the agents did not run as per the schedule and failed after many trails. when we try to run the agents manually from Presentation Services we are getting the same error.
When we go back and check the agents we found that only the agents whose delivery content is Excel2007 got failed but the PDF and CSV format agents ran successfully. When we were checking the log file we got following error.

Agent Log File:

[2015-09-11T12:00:16.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b]  [nQSError: 77006] Oracle BI Presentation Server Error: A fatal error occurred while processing the request. The server responded with: Path not found (/shared/xxxxx/xxxx/xxxx) [[
Error Codes: U9KP7Q94
.
Error Codes: AGEGTYVF

]]
[2015-09-11T12:00:16.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b]     AgentID: xxxxxx/xxxxxx/xxxxxx/xxxxx[[
    ...Trying main Agent loop again.
]]
[2015-09-11T12:00:16.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b] ... Sleeping for 2 seconds.
[2015-09-11T12:00:18.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b]  [nQSError: 77006] Oracle BI Presentation Server Error: A fatal error occurred while processing the request. The server responded with: Path not found (/shared/xxxx/xxxxx/xxxxxx) [[
Error Codes: U9KP7Q94
.
Error Codes: AGEGTYVF

]]
[2015-09-11T12:00:18.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b]     AgentID: /shared/xxxxx/xxxxx/xxxx [[
    ...Trying main Agent loop again.
]]
[2015-09-11T12:00:18.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b] ... Sleeping for 8 seconds.
[2015-09-11T12:00:26.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b]  [nQSError: 77006] Oracle BI Presentation Server Error: A fatal error occurred while processing the request. The server responded with: Path not found (/shared/xxxx/xxxx/xxxxx) [[
Error Codes: U9KP7Q94
.
Error Codes: AGEGTYVF

]]
[2015-09-11T12:00:26.000-05:00] [OracleBISchedulerComponent] [ERROR:1] [] [] [ecid: 0009Z_lvvMs5yW85njT4iZ0001Rn0007nk,0:1:2] [tid: b]     AgentID: /shared/xxxx/xxxxx/xxxxx[[
    Exceeded number of request retries for method RuniBot.
]]
 
Scheduler Log File (Obisch1.log):

processing the request. The server responded with: Invalid Handle Of 'saw::RPCClientEndPoint*' Exception
Error Codes: Q4NU7XSN
Location: saw.subsystem.portal.pdf, saw.delivers.rpc.getDeviceContent, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads
.
[Util Logger] Exception occurred:
    Severity:1
    Type:const saw::Exception*
    File:project/webcomm/rpc.cpp
    Line:479
    Message:A fatal error occurred while processing the request. The server responded with: Invalid Handle Of 'saw::RPCClientEndPoint*' Exception
Error Codes: Q4NU7XSN
Location: saw.subsystem.portal.pdf, saw.delivers.rpc.getDeviceContent, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads
.
[Util Logger] Exception occurred:
    Severity:1
    Type:const saw::Exception*
    File:project/webcomm/rpc.cpp
    Line:479
    Message:A fatal error occurred while processing the request. The server responded with: Path not found (/shared/xxxx/xxxxxx)
Error Codes: U9KP7Q94

Root Cause for the Issue:
We have back tracked the changes that we have done recently and found that the permissions on the WebCatalog got changed. The folder where the agents are placed and the Admin User(In our case its Weblogic) Folder from where the agents will start running had only Read Only access.

Resolution:
We have reset the permissions for weblogic user to Full Control using Catalog Manager (for all its folders and sub folders). It worked all the agents ran successfully.

Note: No need to Restart Obiee OPMN Services after resetting the permissions.