Background and Feature Description
Related Bug Number : 21287929
Product:
Oracle Business
Analytics Application : 11.1.1.8.1
OLTP Version : EBS
R12.X
Feature :
Converted the slim
union in the extraction query of
SDE_ORA_PERSISTEDSTAGE_GLLINKAGEINFORMATION_AEEXTRACT to FAT union in order to
improve the performance of incremental run.
Tech Note instruction overview:
Please
note customer must follow the standard customization process to make the change
in custom folder instead of OOTB folders. This
is needed in order for our future ODI metadata patches to work in your
repository.
As part of the
standard customization process, you will copy the existing mapping folder to a
custom folder, make changes in the custom folder, delete the OOTB scenario from
the original mapping folder, and then generate the new scenarios in the custom
folder using the original OOTB scenario name.
If you are not
familiar with this procedure, please see the detail instruction in
‘‘Instructions_How_to_Make_Change_on_ODI_Mappings.doc’.
Solution:
Steps: ODI
Changes
1
Follow the step of ‘1 Create Custom Folder and Enable version control’ in
Instructions_How_to_Make_Change_on_ODI_Mappings.doc, create
CUSTOM_SDE_ORAR12XX_Adaptor folder, copy task folder
SDE_ORA_PERSISTEDSTAGE_GLLINKAGEINFORMATION_AEEXTRACT from SDE_ORAR12XX_Adaptor
and enable version control.
2 Open the yellow
temp interface
“SDE_ORA_PersistedStage_GLLinkageInformation_AEExtract.W_ORA_GL_LINKAGE_AE_PS_SQ_XLA_AE_LINES”
as shown in the screenshot below
3 Now go to
Quick-Edit tab. Then go to ‘Select Dataset’ dropdown and select ‘Manage
Datasets’ option. Configure the datasets as below.
4 After creating
the datasets, go to ‘Mapping’ tab - >Select “dataset 2” tab. This dataset
tab will not have any mapping in that dataset. Customer need to manually create
the mapping exactly similar to the one already present in “dataset 1” dataset.
5 After creating
the mapping for “dataset 2”, go to “Quick-Edit” and select “dataset 1” and then
open the filter section and select the filter as shown below:
6 Open the filter
and paste the new filter.
OLD:
RUN_FULL_INCREMENTAL("#IS_INCREMENTAL",AELINE.CREATION_DATE
>= TO_DATE_VAR('#INITIAL_EXTRACT_DATE'),RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED',(EXISTS
(SELECT 1 FROM
(
SELECT AELINE.AE_HEADER_ID,AELINE.AE_LINE_NUM FROM
QUALIFY(XLA_AE_LINES) AELINE WHERE
AELINE.LAST_UPDATE_DATE>=TO_DATE_VAR('#LAST_EXTRACT_DATE')
UNION ALL
SELECT AELINE.AE_HEADER_ID,AELINE.AE_LINE_NUM FROM
QUALIFY(XLA_AE_HEADERS) AEH, QUALIFY(XLA_AE_LINES) AELINE WHERE
AEH.AE_HEADER_ID=AELINE.AE_HEADER_ID AND
AEH.LAST_UPDATE_DATE>=TO_DATE_VAR('#LAST_EXTRACT_DATE')
) TEMP
WHERE AELINE.AE_HEADER_ID = TEMP.AE_HEADER_ID AND AELINE.AE_LINE_NUM=
TEMP.AE_LINE_NUM
)),
(EXISTS (SELECT 1 FROM
(
SELECT AELINE.AE_HEADER_ID,AELINE.AE_LINE_NUM FROM
QUALIFY(XLA_AE_LINES) AELINE WHERE
AELINE.CDC$_SRC_LAST_UPDATE_DATE>=TO_DATE_VAR('#LAST_EXTRACT_DATE')
UNION ALL
SELECT AELINE.AE_HEADER_ID,AELINE.AE_LINE_NUM FROM
QUALIFY(XLA_AE_HEADERS) AEH, QUALIFY(XLA_AE_LINES) AELINE WHERE
AEH.AE_HEADER_ID=AELINE.AE_HEADER_ID AND
AEH.CDC$_SRC_LAST_UPDATE_DATE>=TO_DATE_VAR('#LAST_EXTRACT_DATE')
) TEMP
WHERE AELINE.AE_HEADER_ID = TEMP.AE_HEADER_ID AND AELINE.AE_LINE_NUM=
TEMP.AE_LINE_NUM
))
)
)
NEW:
RUN_FULL_INCREMENTAL("#IS_INCREMENTAL",AELINE.CREATION_DATE
>=
TO_DATE_VAR('#INITIAL_EXTRACT_DATE'),AELINE.LAST_UPDATE_DATE>=TO_DATE_VAR('#LAST_EXTRACT_DATE')
)
7 Now select
“dataset 2” and open the same as shown above and copy the below:
NEW:
RUN_FULL_INCREMENTAL("#IS_INCREMENTAL",AELINE.CREATION_DATE
>= TO_DATE_VAR('#INITIAL_EXTRACT_DATE') AND 1=2,AELINE.LAST_UPDATE_DATE <
TO_DATE_VAR('#LAST_EXTRACT_DATE') AND
AEH.LAST_UPDATE_DATE>=TO_DATE_VAR('#LAST_EXTRACT_DATE')
)
8
Save this interface now.
9. Regenerate the
scenario “SDE_ORA_PERSISTEDSTAGE_GLLINKAGEINFORMATION_AEEXTRACT” with the same
name as exists now as follows:
10. Save the
change and following the step of ‘3 Delete the old scenario and create new
scenario’ in Instructions_How_to_Make_Change_on_ODI_Mappings.doc’ to
create new scenario for SDE_ORA_PERSISTEDSTAGE_GLLINKAGEINFORMATION_AEEXTRACT
in CUSTOM_SDE_ORAR12XX_Adaptor folder.
Steps: Data Fix
This change is for improving performance and no data fix is
involved.
No comments:
Post a Comment