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.

ClearPeaks at Tech13


Last week we exhibited our services at TECH13, an event organised by the UK Oracle User Group at Manchester Central Convention Complex. Because of the high participation during the last years, the organization decided to split the usual gathering into APPS and TECH. For three days over 200 speakers showed presentations about APEX, Business Intelligence, Databases, Engineered Systems, SOA & BPM, Operating Systems and many more technical topics which were attended by over 1.000 delegates.

The breaks between keynotes gave us time to meet and greet old and new colleagues from the Oracle community at our ClearPeaks stand. Mick Bull, who recently joined our UK team as new sales director, is a well-known face among the Oracle community and presented our services to the delegates.

Highlight was the Tuesday event at the National Football Museum where we enjoyed food, drinks and Christmas mood with other exhibitors. If you don’t know the museum yet, it’s definitely worth a visit.

Congratulations to the UKOUG team, everything was taken care of and we are looking forward to the next time. If you haven’t been there and want to know more about our services contact us here.



What are the new features of ODI12c?


The new release of Oracle Data Integrator (ODI 12c) is now available. Let´s find out what the new features of ODI12c are that this version includes.

First, is there anything fancy in the installation process?

Just one note here: to be able to use ODI Studio, the enterprise installation needs to be selected (instead of the Standalone option) while launching the ODI installation program.


For the configuration part, creating a new login to ODI repository you are requested to specify a new wallet password. ODI12c provides you with a possibility to store your credentials in a wallet that is protected by a password. How much it helps to protect and manage your credentials with a single password!


Goodbye Interfaces, Welcome Mappings

First of all, we do not deal with interfaces anymore; mappings are offered to the developers instead. On this background let´s talk about common features and functions across various ETL tools, like Informatica Powercenter, which has many advantages. As the previous version of ODI and Powercenter are totally different technologies, there is no tool to convert the flow automatically. Either you need to use the Powercenter workflow manager to build a new ODI package, or you can use a Powercenter mapping to create a new ODI interface, keeping in mind all the constraints that ODI11g may have, including its inability to load multiple targets. What changes now? Source and target data tables can now be dragged and dropped to the same canvas, which was not the case for the previous version.

Example canvas with components panel:


Moreover, all objects have IN and OUT connectors. There is also a possibility to use multiple targets within the same mapping as the OUT connector can be mapped to many IN connectors. The split component may be used to direct rows to the desired destination based on particular conditions. And speaking of the split option - a new components panel has been added, containing operations like join, filter, lookup or split to simplify mapping creation (see picture above). The star icon (distinct component), enables selecting particular rows.  Finally you can take advantage of the reusability of mappings, which can be applied across various projects by simply connecting to other components.

All these changes made Oracle Data Integrator more like Informatica Powercenter (or other ETL tools) when it comes to the creation of the transformation mappings. However, it’s good to remember that their main paradigm continues to be different, as ODI is still considered an ELT tool while Powercenter is a traditional ETL tool. Of course there will be supporters of both and the choice may depend on budget or functionality.

More Nice Features

We also found some other features that make our lives easier. One of them is the auto completion and syntax highlighting in expression fields. Another one is that when one column is selected all the related columns are highlighted across the mapping. The entire path for transformations is perfectly visible. Nice, isn’t it? I prefer that much more than checking every single box to track all modifications.

Undoubtedly, adding a debugger was long awaited. It provides you with options to set breakpoints, inspect the values of variables, debug sessions or data. Step by step, you can easily review your work.


We also enjoyed the enhanced security model. Security is assigned to a role and the same level of security is shared by all users belonging to that role. Additionally,  ODI standalone agents are managed by WebLogic Management Framework, which gives us the benefit of centralized management. You are offered a wizard to carry out the configuration:


Agents can be controlled by Node Manager.

Any drawbacks?

As always, there is a discussion around new releases on which features are useful and which are not. Some changes, like the layout, can be considered both as an advantage and a disadvantage. In 12c this is the case for the complexity. You are now able to see more objects at the same time, providing you with some new options like the components panel, but on the other hand it makes everything less transparent and a decent screen size is needed.


Even if this can be considered a small disadvantage, it is not a real problem. More troublesome may be that some operations are not that obvious to implement. Calculations as simple as a sum that used to require building an expression with a SUM function in the target, now obliges you to use the aggregate component prior to calculating the sum.

ODI and Oracle Warehouse Builder (OWB)

As announced, at a later stage there will be a possibility to migrate Oracle Warehouse Builders objects to ODI.  Currently all OWB jobs can be run in ODI using the OdiStartOwbJob tool and the OWB repository is a data server that can be accessed from Topology.


Expectations for this release were high. Surely the changes from the previous version are noticeable and the overall reception is good. The roadmap for ODI12c in the Oracle stack is looking very promising, acquiring a key role as default ELT tool of the new Oracle BI Applications. We are very excited to start working with ODI 12c in our projects.

Which features of ODI 12c do you find the most useful? We would be glad to receive your comments on this topic so let us know your experience.

privacy policy - Copyright © 2000-2010 ClearPeaks