Enable CLOB field in OBIEE
Below are the steps which need to be followed.
Lets assume , ACTION_ITEM table has a column called ACTION_TAKEN which datatype is CLOB.
1. There is not CLOB datatype in OBIEE RPD. For CLOB please select LONGVARCHAR type in RPD physical layer.
2. Its mandatory to define a Keys for that Physical Table in RPD.
3.Open the same column in BMM layer. Select that particular column and click on Edit under “Column Source Type”.
4. Open that Column from Logical Table Source.
5. Need to use LOOKUP DENSE funcation.
Syntax : LOOKUP (DENSE , CLOB Column, PK Column)
6. Move it to Presentation layer and use the same in BI report.
OBIEE 11g Configuration for MS SQL Server Database
Step 1: Apply the updated/recent ODBC driver.
Considered , OBIEE version: 11.1.1.7.1 and Linux 64 bit server.
Need to download the latest Data driver 7.1.5 from https://support.oracle.com
Patch #: 21440314
Apply the patches as per the Oracle instructions. Post to that we need to manually copy and unzip the driver to below location.
/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant
Step 2: System DSN Setup.
Create a System DSN to connect to MS SQL database
Step 3: OPMN.XML file Configuration.
Navigate to below location to do the change for opmn.xml file.
/u01/BIUAT/OBIEE/instances/instance1/config/OPMN/opmn
Backup copies of opmn.xml. Make edits to opmn.xml.
Search for BI components in opmn.xml. These are XML elements, <ias-component>, with the id "coreapplication_obi<...>".
In each of these BI component elements, modify and make sure that these environment variables point to recent ODBC driver.
$LD_LIBRARY_PATH
$PATH
Step 4: ODBC.INI file Configuration.
Navigate to below location to do the change for odbc.ini file.
/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup
Backup of odbc.ini file.
Need to do below changes for [ODBC] tag. TraceDll and InstallDir need to be pointed to new data driver.
Need to modify the below mentioned line for MS SQL Data driver section.
· Tag name should be same as System DSN name which we have created.
· Driver path should point to 7.1.5 .
· Database
· EnableQuotedIdentifiers should set to 1.
· HostName. ( Along with Instance if any)
· PortNumber
Once this changes are done , we need to copy the file to new Data driver folder as well.
/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant/7.1.5
Step 5: USER.SH file Configuration.
Navigate to below location to do the change for user.sh file.
/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup
Backup the user.sh file.
In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:
· For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.
· For HP-UX, the library path variable is SHLIB_PATH.
· For AIX, the library path variable is LIBPATH.
Step 6: BI-INIT.SH file Configuration.
bi-init.sh file used to source env , should point to the DataDirect 7.1.5 driver
Back up bi-init.sh, usually located under
/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup
Make edit to bi-init.sh.
Modify the EPM_ODBC_DRIVER_DIR parameter to point it the location of the DD 7.1.5 ODBC driver library directory.
For example: EPM_ODBC_DRIVER_DIR=$ORACLE_HOME/common/ODBC/Merant/7.1.5/lib
Note that bi-init.sh is only needed only for running OBIEE applications directly from a shell command line.
Step 7: RPD Changes.
Create a new connection pool to connect to MS SQL database.
Disable function "NESTED_ORDERBY_SUPPORTED" in the rpd .
Step 8: Restart BI Services.
Restart all servers (AdminServer and all Managed server(s))
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.
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.
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.
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.
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.
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:
OBIEE - Date Expressions
1) First Day of the Previous Year
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
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
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
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.
6) First Day of the Next Month
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.
7) First Day of Current Quarter
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)
TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)
Nice work
ReplyDeleteAlso please visit my blog site
www.Knowledgespace16.blogspot.com