Configuration Files – OBIEE 11g

 Hello All,

Please find the OBIEE 11g Configuration file.

Oracle BI Server

1.   NQSConfig.INI

2.   Logconfig.xml

3.   DBFeatures.in

Location

OBIEE11g\Instance\Instance1\config\OracleBIServerComponent\coreapplication_obisn

Oracle BI Presentation Services

4.   Instanceconfig.xml 

Location OBIEE11g\Instance\Instance1\config\OracleBIPresentationServicesComponent\coreapplication_obipsn

Cluster Controller

5.   NQClusterConfig.INI

6.   ccslogging.xml

Location

OBIEE11g\Instance\Instance1\config\OracleBIApplication\coreapplication

Orcle BI Scheduler

7.   instanceconfig.xml

8.   ccslogging.xml

Location

OBIEE11g\Instance\Instance1\config\OracleBISchedulerComponent\coreapplicati on_obischn

Component - JaveHost

9.        config.xml

10.        logging_config.xml

Location

OBIEE11g\Instance\Instance1\config\OracleBIJavaHostComponent\coreapplication_obijhn

-Thanks 

-Rakesh Patil 

Timestamp Query

Hello All,

Please find the time-stamp important query. All timestamp query are very important while developing the Report.

Yesterday’s   Date_yesterday

Current Day  : Current_Date

Last week Week function  : is between  @{Date_7_Days_Ago} and @{Current_Date}

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

First Day of the Current Year 

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

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

First Day of the Previous Month 

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

First Day of the Current Month

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

First Day of the Next Month

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

First Day of Current Quarter

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

Last Day of the Previous Month 

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

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

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

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

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

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

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

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

Thanks you

-Rakesh Patil

-rakeshpatil002@gmail.com

Replace Null Values in an OBIEE

Hello All,

Suppose user wanted to display numeric values in specified format, replace the null values in table, or pivot table with zeros or custom text.

Output Format: Below is the screen shot he is looking to generate. He wanted to put the Name in front of Cost Value

Step:

1.       Create a simple report. I am using the Patil RPD here.

2.       A pivot table view of this data would look something like this by default:

3.      

4.       Now, go to Criteria tab and Go to edit column properties. (Using custom    data format we can specify the custom formatting of columns used in an analysis. This formatting will be applied in report’s tables and pivot tables. To edit a data format for a given column, right click on it and choose Column Properties.)

5.      Select the Data formate  and Check the “Override Default Data format” and Select the Cutom and put the format Name as per screen shot

Here are some examples of using custom numeric masks:

- We can display the values in thousands with K (kilo) letter

#,#.0,Rakesh

- Values in millions with Patil

#,#.0,,Patil

- Values in billions with P

#,#.0,,,Patil

- Add a currency symbol

£# or $#

Explanation

‘#’ is a number sign indicating significant digits

‘#,#’ adds a comma separator for thousands

‘.0’ indicates the number of decimal places (‘0’ for round number, ‘.0’ for one decimal place, ‘.00’ for two, etc.)

‘,’ is a thousand separator (‘,’ for thousands; ‘,,’ for millions, ‘,,,’ for billions etc.)

‘Rakesh’,Patil’,’P’, ‘$’,‘£’ are characters that can be added after or before the number mask

The syntax for custom data formatting for numeric values is:

positive value mask ; negative value mask ; null mask

If we specify the mask without semicolon, the changes will apply to both positive and negative values.

- We can indicate positive values with plus sign (+) and negative values with minus sign (-)

+ #,#.0;- #,#.0

6.       Click on Ok and check the report you will see same output how user was looking.