The following procedure describes the steps to improve the performance of Transactional Business Intelligence custom reports that contain the presentation ID column filter. See Figure 39, : ID Columns Exposed in the Marketing Presentation Catalog for the Marketing presentation and Figure 40, ID Columns Exposed in the Sales Presentation Catalog for the Sales presentation.
To improve performance of custom reports that contain the presentation ID column filter:
Open the Oracle BI Administration tool and then open the Oracle BI repository.
The corresponding logical column under Business Model and Mapping layer for the performance problematic presentation ID column, if the logical column uses CAST() function to convert data type as following:
CAST("oracle.apps.crm.model.analytics.applicationModule.CrmAnalyticsAM_CrmAnalyticsAMLocal"."".""."Dim_CrmCampaignAM_MarketingActivity"."CampaignId" AS VARCHAR(100))The corresponding physical column under Physical layer, if the data type of the physical column is DOUBLE.
Edit the physical ID column to change Type as VARCHAR. Set Length as 100 as the same length as the above logical column CAST( … AS VARCHAR(100)) function used.
For the corresponding logical column in Business Model and Mapping layer, remove the CAST() function from the corresponding logical ID column. For example:
Before:
CAST("oracle.apps.crm.model.analytics.applicationModule.CrmAnalyticsAM_CrmAnalyticsAMLocal"."".""."Dim_CrmCampaignAM_MarketingActivity"."CampaignId" AS VARCHAR(100))After:
"oracle.apps.crm.model.analytics.applicationModule.CrmAnalyticsAM_CrmAnalyticsAMLocal"."".""."Dim_CrmCampaignAM_MarketingActivity"."CampaignId"
Note:
To avoid physical data type changes overwritten by a future reimport of the VO (View Object) in Physical layer, change the data type of the physical ID from DOUBLE to VARCHAR at AM level.
Example:
Use CrmAnalyticsAM.CampaignAM.MarketingActivityPVO.CampaignId as an example.
Modify CampaignAM to add the Custom Property to this AM level:
Key: EnforceCustomDataType_ CrmAnalyticsAM.CampaignAM.MarketingActivityPVO
Value: "CampaignId":"VARCHAR"
or
Value: "CampaignId":"VARCHAR";"CampaignProfileId":"VARCHAR"
Note:
CampaignAM is the nested AM in CrmAnalyticsAM.
Since the Customer Property is created at the nested AM level, the Key value needs to contain the fully qualified name of VO for importing code to read it. For example, “EnforceCustomDataType_CrmAnalyticsAM.CampaignAM.MarketingActivityPVO”.
If the Value field has only one ID column, then you do not add the semicolon (;) at the end.
If the Value field has more than one ID column, then add the semicolon (;) as separator. The last entry will not have the semicolon (;) at the end.
