ODI METADATA QUERY TO FIND EXECUTION TIME TAKEN BY EACH LOAD STEP

The below query should be executed in ODI metadata schema(ODI_REPO)

SELECT A.LP_STEP_NAME, B.START_DATE,B.END_DATE,(B.END_DATE-B.START_DATE)*24*60 DURATION_IN_MIN
FROM SNP_LPI_STEP A , SNP_LPI_STEP_LOG B,SNP_LP_INST C
WHERE A.I_LP_STEP=B.I_LP_STEP
AND C.LOAD_PLAN_NAME='%'
AND  A.I_LP_INST =C. I_LP_INST
AND LP_STEP_NAME LIKE '%'
AND B.START_DATE > TO_DATE('10/22/2018 5:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND B.END_DATE < TO_DATE('10/22/2018 12:00:00', 'MM/DD/YYYY HH24:MI:SS');

GO URL IN BI PUBLISHER

Below is the GO URL Syntax in BI Publisher

http://example.com:7001/xmlpserver/Samples/Salary+Report.xdo?_xpt=0&_xdo=%2FSamples%2FSalary%20Report.xdo&_xmode=4&dept=10&_xt=Simple&_xf=html

This URL runs the report "Salary Report" located under Shared Folders/Samples. Note the following:

_xpt=0 renders the document in the report viewer

_xdo=%2FSamples%2FSalary%20Report.xdo defines the report path

_xmode=4 renders the document only

dept=10 sets the report-specific parameter "dept" to "10"

_xt=Simple uses the layout called "Simple"

_xf=html sets the output format to html

Specifying Parameters in the URL

The preceding examples render the complete report inside the BI Publisher report viewer with all the report controls. The default layout, default output format, and default parameters are used to render the report. You can add parameters to the URL to specify how the report renders.

When constructing the URL, note the following standard URL syntax:

? - denotes the first parameter

& - denotes each additional parameter

The following table describes more parameters you can add to the URL.

Parameter	Definition	Example Usage
_xpt	Specifies whether to render the report in the BI Publisher report viewer or export the document to a new window appropriate for the output type. For example, if the output type specified is html, the report document (only) will render in a browser window; if the output type is PDF you will be prompted to save or open the PDF document. When this parameter is not specified, the report renders in the BI Publisher report viewer. Valid values are: ■ 0 - renders the report in the BI Publisher report viewer ■ 1- exports the document to appropriate application window	_xpt=0
_xdo	(Optional) Provides the path to the report.	_xdo=%2FSamples%2FSalary+Report.xdo
_xt	Specifies the layout to use. Enter the name of the layout as defined in the report definition. If an invalid name is entered, the default layout is used.	_xt=Manager+Summary
_xf	Specifies the output format. If no value is specified, the default output format is used. If an invalid value is specified, or, if a value is specified that is not enabled for the layout, the report does not render. Valid values are: ■ analyze - use for Interactive output ■ rtf ■ docx ■ pdf ■ html ■ pptx - use for PowerPoint 2007 ■ ppt - use for PowerPoint ■ xml - use for Data ■ excel - use for Excel ■ excel2000 - use for Excel 2000 ■ xslx - use for Excel 2007 ■ csv	_xf=pdf
report parameters as named in the data model	Specify name-value pairs for the parameters specific to the report. You must use the parameter name as defined in the data model.	dept=10
_xmode	Specifies the report viewer mode. If not specified, defaults to view in the full report viewer. Valid values are: ■ 0 - view in the full report viewer. ■ 1 - hide BI Publisher banner, hide parameters, can change layout, other actions: export only. ■ 2 - hide BI Publisher banner. (No Header) ■ 3 - hide BI Publisher banner, hide parameters (No Parameters) ■ 4 - report document only: hide BI Publisher banner, hide parameters, hide other actions, hide layouts. (Document Only)	_xmode=1

Eg:

-------

{HOST}/xmlpserver/FIN_GL%20Reports/Test/CI_GLR_Monthly%20Summary%20of%20Ledger%20Data%20for%20Accounts%20by%20Cost%20Center_MTH/CI_GLR_Monthly%20Summary%20of%20Ledger%20Data%20for%20Accounts%20by%20Cost%20Center_MTH.xdo?_xpt=0&_xdo=/FIN_GL%20Reports/Test/CI_GLR_Monthly%20Summary%20of%20Ledger%20Data%20for%20Accounts%20by%20Cost%20Center_MTH/CI_GLR_Monthly%20Summary%20of%20Ledger%20Data%20for%20Accounts%20by%20Cost%20Center_MTH.xdo&_xmode=0?HOST={HOST}&COST_CENTER_CD={COST_CENTER_CD}&ACCOUNT_CD={ACCOUNT_CD}&PERIOD_NAM={PERIOD}&_xt=Simple&_xf=html

http://example.com:7001/xmlpserver/Samples/Salary+Report.xdo?

_xpt=0

&_xdo=%2FSamples%2FSalary%20Report.xdo

&_xmode=0

&dept=10

&_xt=Simple&_xf=html

_xpt=0 renders the document in the report viewer

_xdo=%2FSamples%2FSalary%20Report.xdo defines the report path

_xmode=0 view in the full report viewer.

dept=10 sets the report-specific parameter "dept" to "10"

_xt=Simple uses the layout called "Simple"

_xf=html sets the output format to html

http://example.com:9704/xmlpserver/FIN_GL+Reports/Test/CI_GLR_Monthly Summary of Ledger Data for Accounts by Cost Center_MTH/CI_GLR_Monthly+Summary+of+Ledger+Data+for+Accounts+by+Cost+Center_MTH.xdo?

_xpt=0

&_xdo=%2FCI_GLR_Monthly%20Summary%20of%20Ledger%20Data%20for%20Accounts%20by%20Cost%20Center_MTH.xdo

&_xmode=0

&ACCOUNT_CD={ACCOUNT_CD}

&PERIOD_NAM={PERIOD_NAM}

&COST_CENTER_CD={COST_CENTER_CD}

{HOST}

&_xt=Simple&_xf=html

ORACLE QUERY TO SPLIT DELIMITED COLUMN VALUE INTO ROWS

We can use the below 2 ways to split a delimited column value into rows:

1. Using XMLSEQUENCE:

WITH T AS (

           SELECT ROW_WID,X_SO_LINE_ID,X_SO_LINE_ID AS STR FROM W_WORKORDER_D

          )

SELECT  ROW_WID WORKORDER_WID,X_SO_LINE_ID,EXTRACTVALUE(VALUE(X), '/B') LINE_ID

  FROM  T,TABLE(XMLSEQUENCE(XMLTYPE('<A><B>' || REPLACE(STR, '|', '</B><B>') || '</B></A>' ).EXTRACT('/*/*')) ) X

  ORDER BY X_SO_LINE_ID

2. Using Regular expression

 

SELECT REGEXP_SUBSTR (X_SO_LINE_ID,'[^|]+',1,LEVEL) LINE_ID,

W_WORKORDER_D.ROW_WID WORKORDER_WID FROM W_WORKORDER_D

WHERE 1=1

CONNECT BY LEVEL <=REGEXP_COUNT (X_SO_LINE_ID,'[^|]+')

              CONNECT BY LEVEL <=REGEXP_COUNT (X_SO_LINE_ID,'[^|]+')

Note: '|' is the delimitter in above queries 

           The performance of the 1st query will be good compared to the second one