Apr 14 2014
If you need to do a mass update of sessions to change the commit type you can use PMREP. This is all explained in the Informatica Help. What is not explained in the help is what value you should enter in the -v option. Here are the values you can enter :
0 – for Source Based Commit
1 – for Target Based Commit
2 – for User Defined Commit.
Example command :
pmrep massupdate -t session_property -n ‘Commit Type’ -v 0 -i session_list -u session_list_log
Feb 11 2014
While loading data from a SQL Server database into Oracle i came across a problem. Everytime i did a full load of the data i got a primary key violation in the target table. This was weird because the source did not have any duplicate keys.
It turned out to be that the bigint from the source was loaded incorrectly into the target. The following value was in the source :
The ID was processed by powercenter as :
Because the ID is truncated this causes multiple keys to be the same.
This can be fixed by setting the following option in your session:
The Bigint will now be loaded correclty.
Sep 11 2012
You can find the most important OBIA documentation at the following locations :
Oracle Business Intelligence Applications (OBIA) Release 7.9.6.3 Documentation Library
Major topics :
Oracle Business Intelligence Applications ETL Data Lineage Guide Release 7.9.6.2 and 7.9.6.3
Oracle Business Analytics Warehouse Data Model Reference Version 7.9.6.3
Major Topics :
Oracle Business Intelligence Applications Product Guides Release 7.9.6.3
Product Guides for:
The product information center is also helpful :
Oracle Business Intelligence Applications (OBIA) Product Information Center (PIC)
Jul 23 2012
Recently I needed to add a barcode on a Business Objects Web Intelligence report.
The barcode font was available on de server and client but it was not showing up in Business Objects Web Intelligence.
The first option I tried was to create a cell with HTML code to force the font.
This worked great but there was one problem. You cannot print the report in HTML mode.
When you want to print it will always convert to PDF format first and then you will loose the correct display of HTML in the cell.
According to SAP it is not possible to add extra fonts to Business Objects Web Intelligence.
After searching the internet I have found different ways of adding fonts with mixed results reported by different people.
The following worked for me om Business Objects XI 3.1
I use the barcode font : C39HrP24DlTt
This is a Truetype freeware barcode font.
Follow the following steps to add the barcode font to Business Objects Web Intelligence:
Between the tag <TTF> add the following :
<font name=”c39hrp24dltt”>
<os type=”all”>
<Attributs style=”0″ filename=”c39hrp24dltt.ttf” encoding=”” aliaspsname=”c39hrp24dltt”/>
</os>
</font>
Also make a backup first before editing the file.
Edit view
HTML View
Jan 03 2012
If you want to check if a relational connection is still used in any session instance you can use the following query on the repository:
SELECT M.MAPPING_NAME
,SI.INSTANCE_NAME INSTANCE_NAME
,SA.ATTR_VALUE TABLE_OWNER
,MSE.OBJECT_NAME READER_WRITER_TYPE
,C.OBJECT_NAME CONNECTION_NAME
,C.USER_NAME
,WA.ATTR_VALUE SQL_OVERRIDE
FROM DWHREP.OPB_SWIDGET_INST SI
,DWHREP.OPB_SWIDGET_ATTR SA
,DWHREP.OPB_MAPPING M
,DWHREP.OPB_WIDGET_INST WI
,DWHREP.OPB_WIDGET_ATTR WA
,DWHREP.OPB_SESS_EXTNS SE
,DWHREP.OPB_MMD_SESS_EXTNS MSE
,DWHREP.OPB_SESS_CNX_REFS SCR
,DWHREP.OPB_CNX C
,DWHREP.OPB_TASK T
WHERE M.MAPPING_ID = WI.MAPPING_ID
AND M.VERSION_NUMBER = WI.VERSION_NUMBER
AND WI.WIDGET_ID = WA.WIDGET_ID
AND WI.REF_VERSION_NUMBER = WA.VERSION_NUMBER(+)
AND WA.ATTR_ID(+) = 1 — SQL Override
AND WA.WIDGET_TYPE(+) = 3 — SQ
AND SI.WIDGET_TYPE IN(1 ,2 ,3 ,56 ,45 ,55 ,84)
AND SI.SESSION_ID = SE.SESSION_ID
AND SI.SESSION_ID = SA.SESSION_ID(+)
AND SI.VERSION_NUMBER = SA.VERSION_NUMBER(+)
AND SA.ATTR_ID(+) = 28 — Owner
AND SI.SESS_WIDG_INST_ID = SE.SESS_WIDG_INST_ID
AND SE.OBJECT_TYPE = MSE.OBJECT_TYPE
AND SE.OBJECT_SUBTYPE = MSE.OBJECT_SUBTYPE
AND SE.SESSION_ID = SCR.SESSION_ID
AND SCR.REF_OBJECT_TYPE = C.OBJECT_TYPE
AND SCR.REF_OBJECT_SUBTYP = C.OBJECT_SUBTYPE
AND SCR.REF_OBJECT_ID = C.OBJECT_ID
AND SE.SESS_WIDG_INST_ID = SCR.SESS_WIDG_INST_ID
AND SI.VERSION_NUMBER = SE.VERSION_NUMBER
AND SCR.WORKFLOW_ID = 0
AND SI.VERSION_NUMBER = SCR.VERSION_NUMBER
AND SI.SESSION_ID = T.TASK_ID
AND SI.VERSION_NUMBER = T.VERSION_NUMBER
AND T.IS_VISIBLE = 1
AND SI.MAPPING_ID = M.MAPPING_ID
AND M.VERSION_NUMBER = SI.VERSION_NUMBER
AND C.OBJECT_NAME IN(:CONNECTIONNAME)
UNION ALL
SELECT M.MAPPING_NAME
,SI.INSTANCE_NAME INSTANCE_NAME
,SA.ATTR_VALUE
,MSE.OBJECT_NAME READER_WRITER_TYPE
,SCR.REF_OBJECT_VALUE CONNECTION_NAME
,C.USER_NAME
,WA.ATTR_VALUE SQL_OVERRIDE
FROM DWHREP.OPB_SWIDGET_INST SI
,DWHREP.OPB_SWIDGET_ATTR SA
,DWHREP.OPB_MAPPING M
,DWHREP.OPB_SESS_EXTNS SE
,DWHREP.OPB_MMD_SESS_EXTNS MSE
,DWHREP.OPB_SESS_CNX_REFS SCR
,DWHREP.OPB_TASK T
,DWHREP.OPB_CNX C
,DWHREP.OPB_WIDGET_INST WI
,DWHREP.OPB_WIDGET_ATTR WA
WHERE M.MAPPING_ID = WI.MAPPING_ID
AND M.VERSION_NUMBER = WI.VERSION_NUMBER
AND WI.WIDGET_ID = WA.WIDGET_ID
AND WI.REF_VERSION_NUMBER = WA.VERSION_NUMBER(+)
AND WA.ATTR_ID(+) = 1
AND WA.WIDGET_TYPE(+) = 3
AND SI.WIDGET_TYPE IN(1 ,2 ,3 ,56 ,45 ,55 ,84)
AND SI.SESSION_ID = SE.SESSION_ID
AND SI.SESSION_ID = SA.SESSION_ID(+)
AND SA.ATTR_ID(+) = 28 — Owner
AND SI.VERSION_NUMBER = SA.VERSION_NUMBER(+)
AND SCR.SESSION_ID = SE.SESSION_ID
AND SI.SESS_WIDG_INST_ID = SE.SESS_WIDG_INST_ID
AND SE.OBJECT_TYPE = MSE.OBJECT_TYPE
AND SE.OBJECT_SUBTYPE = MSE.OBJECT_SUBTYPE
AND SCR.REF_OBJECT_ID = 0
AND SE.SESS_WIDG_INST_ID = SCR.SESS_WIDG_INST_ID
AND SI.VERSION_NUMBER = SE.VERSION_NUMBER
AND SCR.WORKFLOW_ID = 0
AND SCR.REF_OBJECT_TYPE = C.OBJECT_TYPE
AND SCR.REF_OBJECT_SUBTYP = C.OBJECT_SUBTYPE
AND SCR.REF_OBJECT_ID = C.OBJECT_ID
AND SI.VERSION_NUMBER = SCR.VERSION_NUMBER
AND SI.SESSION_ID = T.TASK_ID
AND SI.VERSION_NUMBER = T.VERSION_NUMBER
AND SI.MAPPING_ID = M.MAPPING_ID
AND M.VERSION_NUMBER = SI.VERSION_NUMBER
AND T.IS_VISIBLE = 1
AND SCR.REF_OBJECT_VALUE IN(:CONNECTIONNAME) ;
This was checked on PWC 9.1.0 Hotfix2
Nov 29 2011
Did you know that you can use HTML in the object description field in Business Objects ??
Example :
<font color=red>Description: This will show in Red.</font><br>
<font color=green>Example: This will show in Green.</font><br>
<font color=blue>Source: This will show in Blue.</font>
Try other HTML tags in your descriptions, not all HTML works…..
Aug 24 2011
When using a sql override in a Source Qualifier you have to keep in mind that you have to forward all the ports from your SQ. If you do not forward all the ports Powercenter will alter the query and will change the order of the columns and this will cause it to return the wrong values in the wrong ports.
Mar 16 2011
Make sure that you query the latest mapping version and widget_type = 3 (Source Qualifier).
Then select attr_id = 1 and you get the SQL override (if used in the Source Qualifier).
Feb 10 2011
A couple of weeks ago I came across something strange when using a SQL Override in a Lookup that contained a Union.
For example : SELECT VARCHAR_A VAR
, NUMBER_B NUM
FROM TABLE_A
UNION
SELECT VARCHAR_C VAR
, NUMBER_D NUM
FROM TABLE_B
When you use VAR in your lookup condition, Powercenter rewrites the query when it runs the transformation.
You will only come across this statement when you check the session log.
Powercenter somehow needs te column that is used in the condition at the end of the select.
This is wat the new query will be:
SELECT NUMBER_B NUM
, VARCHAR_A VAR
FROM TABLE_A
UNION
SELECT VARCHAR_C VAR
, NUMBER_D NUM
FROM TABLE_B
In this case it will result in an error when you run your mapping because the datatypes are incompatible.
However when you have several columns of the same datatype in your union this query will run fine but you will get a bunch of bad data in your lookup !
The way to fix this is to build an additional select around your base query:
SELECT VAR
, NUM
FROM (
SELECT VARCHAR_A VAR
, NUMBER_B NUM
FROM TABLE_A
UNION
SELECT VARCHAR_C VAR
, NUMBER_D NUM
FROM TABLE_B
)
By continuing to use the site, you agree to the use of cookies. more information
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.