BI in the real world – ClearPeaks presents challenges and solutions at 2014 Dubai Innovation Day


The Middle East, and especially UAE, is arguably one of the most dynamic areas in terms of Business Intelligence growth, where BI maturity is rising fast and so are the efforts by local companies to implement their BI platforms in a successful way. What are the most common pitfalls and challenges to overcome, and the key success factors to consider before starting your BI journey? Gianluca Nacci, our MEA Practice Manager, explained to over 200 attendees how to lead a BI project to succeed at yesterday’s Oracle Innovation Day event in the Meydan IMAX Theatre.

After an overview of the common characteristics of BI projects in the Region, Gianluca highlighted how tight cooperation between Business and IT, a rigorous project start checklist and approaching upgrade-consolidation-migration projects in the right way are the key to a successful BI implementation.

BI Projects are Business-driven, IT-executed” he claimed while showcasing examples from some of our BI success stories from UAE, where engaging the Business and obtaining their sponsorship while ensuring commitment and support from IT proved to be the winning combination, particularly when the challenge is regaining momentum to the project – and user confidence - after the failure of earlier implementations.


What are the relevant actions to take before embarking in a BI endeavor? Investing time in creating a proper roadmap, budgeting properly and getting the key players’ buy-in will transform into significant savings in project time and costs. Gianluca showed how a Global Business Analysis exercise before the implementation kick-off paved the way to a successful delivery at one of our customers.

Multiple BI tools, several data sources, legacy data marts, redundancy of information, no simple way to consolidate and manage master data: a familiar scenario for local companies where Business Intelligence has been spread wide but without a scalable plan. Gianluca and ClearPeaks demonstrated with a case study how it is possible to streamline and consolidate the technical and functional reporting capabilities in a single Oracle BI instance, lowering TCO and establishing a single version of the truth for all relevant company data.

Whether you are well into your BI journey or about to take off, choosing the right implementer is key. At ClearPeaks we can look back on a wide range of successful BI projects across EMEA, from End-to-End deployments to rescuing failed implementations. We make sure the right steps are taken so your BI investment achieves the expected ROI. If you already have a BI project in place and have some unresolved issues you can count on our Support Services, or get a BI Health Check to find out about possible improvements of the current situation.

At Dubai innovation day we could again prove that everyone is aware of the benefit a BI system brings to a company, and we are proud to provide this service with our full commitment and passion. If you want to know more about our services or would like to discuss your BI requirements just contact us at


ClearPeaks at the Oracle Business Analytics Summit 2014


orscle business analytics summit

What happens when Oracle invites hundreds of business professionals to get together in the center of London to talk about the latest BI trends? Can anyone still afford to drive a business without having all the data available, real-time and on their mobile device? At yesterday's annual Business Analytics Summit, the Grange Tower Bridge Hotel was buzzing with excitement. With over 500 attendees there is no doubt that businesses have realized the importance of BI, and they can't get enough of it. ClearPeaks was sponsoring the event, with over 14 years of experience as a BI specialist and Oracle partner we didn't want to miss out on the opportunity to meet our UK community.


Not a single seat was left in the speaker’s room, especially when Sir Clive Woodward, Head Coach of England's World Cup winning rugby team and Team GB's Director of Sport for the London 2012 Olympic Games highlighted the many similarities between sports and business analytics. In his customer presentation he talked about the importance to be willing to learn and that any team member should be able to use analytics to take day to day decisions.

Looking at the results of the Endeca software monitoring the Twitter #BASummit for the day we have absolute proof of the overall mood and main topics people were talking about. Tweets started at 8a.m. when all the stands were built up and culminated at around 11a.m., right after Sir Clive Woodward's' presentation. In the top topics and word clouds we can see the positive atmosphere that was perceived during the whole day. Great day, great individuals and great teams are the key topics that proof the overall positive sentiment and the impact of the speakers. The tag clouds show a strong trend towards BI. The benefit the technology brings to companies and the high ROI have clearly reached not only the global players, but also to middle-and small-sized companies. If you want to know how Endeca's sentiment analysis on Twitter works, read our blog post about Endeca on Twitter on the example of a FCBarcelona football game.




Default settings for the soft delete option in Oracle BI APPS


During one of our last OBI Apps Finance implementation and customization projects we were confronted with an uncommon issue. Even though we had run many validation processes and had acquired the agreed approvals within the development and UAT environments, some metric issues appeared after going live with the Production environment.

The first issue we noticed was related to an Accounts Payable (AP) report, which contained the number of in-validated invoices; the metric shown within the Oracle BI report was greater that the real number on in-validated invoices found in the Oracle E-Business Suite database (EBS). Finally AP users confirmed that they do not always follow the EBS best practices and some in-validated invoices were manually deleted instead of setting them to the “cancel” status prior to the deletion.

The second issue appeared in the Accounts Receivable (AR) business area. In this case we identified the mismatching within the aging snapshot table; this table holds the outstanding AR balance information and it was showing outstanding amounts where the balance should be 0. In this case no manual deletions were done, AR users modified payment terms from EBS front-end and when modifying the payment term on the invoice, a new record was created and the old payment schedule record deleted.

We could see that both issues are related to the deletion of records within the EBS source system. In an out-of-the-box Oracle BI APPS implementation, records that are deleted in the source system are not removed from the Oracle Business Analytics Data Warehouse. In case you want to flag these records as deleted (soft delete) in the Data Warehouse, you must enable the related primary extract and delete mappings (the soft delete feature is disabled by default).

Description of the Primary Extract and Delete Informatica PowerCenter mappings:

The following graph describes how the primary extract and delete Informatica PowerCenter mappings interact with the database tables:


Primary and Delete Informatica PowerCenter mappings description:

  • The “_Primary” mappings perform a full extract of the primary keys from the EBS source system and load the result into the primary extract (_F_PE) table.
  • The “_IdentifyDelete” mappings identify deleted records in the source by doing brute force comparison between primary extract table (_F_PE) and the target table (_XACT_F) and load the results into a staging table (_XACT_F_DEL).
  • The “_SoftDelete” mappings update the delete flag column with a value 'Y' on the  target table (_XACT_F) for all the records that were  identified as 'deleted', driving from the staging area table (_F_DEL).

How to enable Primary Extract and Delete sessions on Tasks:

In order to enable the Primary Extract and Delete Informatica PowerCenter mappings you will have to apply changes to the Data Warehouse Administration Console (DAC) application.

Here is the list of steps required:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
  2. Display the Tasks tab.
  3. Query for all tasks containing the string 'Delete' or 'Primary'.

Accounts Payable (OBA APPS

-        SDE_ORA_APTransactionFact_ExpenseDistribution_Primary

-        SDE_ORA_APTransactionFact_LiabilityDistribution_Primary

-        SDE_ORA_APTransactionFact_PaymentSchedule_Primary

-        SDE_ORA_APTransactionFact_Payment_Primary

-        SIL_APTransactionFact_IdentifyDelete

-        SIL_APTransactionFact_SoftDelete

Accounts Receivable (OBA APPS

-        SDE_ORA_ARTransactionFact_ARSchedules_Primary

-        SDE_ORA_ARTransactionFact_Adjustments_Primary

-        SDE_ORA_ARTransactionFact_CreditMemoApplication_Primary

-        SDE_ORA_ARTransactionFact_ReceivableApplication_Primary

-        SIL_ARTransactionFact_IdentifyDelete

-        SIL_ARTransactionFact_SoftDelete.

4. Deselect the Inactive check boxes for those tasks.

Find below a screenshot which shows the AP primary extract tasks, being “Inactive” by default:


5.Reassemble your subject areas and rebuild your execution plans.

Additional considerations

In order to have a smooth implementation of the soft delete option, you may have to perform some additional tasks.

1. Review the “Sql Query” attribute in the Source Qualifier transformation within the SDE Primary mappings:

  • Make sure that the WHERE clause in the SQL query is correctly specified
  • Double check that the SQL query matches with the customizations you may have already applied to your Oracle BI APPS environment

2. Within DAC, select the “_Primary” tasks and verify that the “Truncate Always” checkbox for the target primary extract table (_F_PE) is selected just for the first task in the execution plan.

Example for the AP business area:

  • SDE_ORA_APTransactionFact_ExpenseDistribution_Primary
    “Truncate Always” checkbox checked
  • SDE_ORA_APTransactionFact_LiabilityDistribution_Primary
    “Truncate Always” checkbox NOT checked
  • SDE_ORA_APTransactionFact_PaymentSchedule_Primary
    “Truncate Always” checkbox NOT checked
  • SDE_ORA_APTransactionFact_Payment_Primary
    “Truncate Always” checkbox NOT checked

The screenshots below show how to check and un-check the “Truncate Always” option for the target table within the AP business area:



3. Impact on aging tables:

  • If the soft delete option has been enabled after the system was already in PRODCUTION, you may have some wrong information in the aging tables (it happened to us with the Accounts Receivables business area) so you will have to build a SQL DDL that deletes the related records within the aging table.
  • Double check that the “Sql Query” attribute in the Source Qualifier transformation within the PLP mappings that load the aging tables contains a filter for the soft delete DELETE_FLG field (DELETE_FLG='N')

4. Review your RPD:

Oracle BI APPS repository by default comes with filters on the soft delete DELETE_FLG field (DELETE_FLG='N') however it would be good to double check.

You may also want to take advantage of this soft delete DELETE_FLG field and build reports in order to analyse them.

Find below a screenshot showing the filter on the DELETE_FLG field within the repository:



The soft delete feature in Oracle BI APPS is disabled by default, so the best approach would be to analyse and agree with the business users if they will require the soft delete feature to be enabled and in case they need it, apply the required changes as soon as possible (before the deployment to the PRODUCTION environment) in order to minimise the impact.

privacy policy - Copyright © 2000-2010 ClearPeaks