Oracle Financial Analytics: General ledger to sub-ledgers navigation


When implementing Oracle Financial Analytics, it has been noticed that it is a common requirement to enable navigation from general ledger to sub-ledgers reports to obtain information on specific transactions. One of the main reasons for this is to reconcile general ledger to individual sub-ledgers.

The general ledger is the master set of accounts that summarise the sub-ledger transactions. It is used to add information into the financial statements and journal entries.  A sub-ledger is a detailed record of transactions for an individual account that contains details of transactions for an account.

Sub-ledgers serve as support for amounts posted to the general ledger. It is important to reconcile the general ledger balances to the sub-ledger balances on a regular basis to spot possible discrepancies.

In this blog article, I will describe how this process can be approached in OBI Apps. I will describe the OBI Apps data model and the configuration required. Also, I will describe the creation of sample navigation from general ledger to sub-ledger by the creation of an intermediate analysis.

The animation below shows the expected navigation from GL Account balance to Payables details.

Financial Analytics

Note: This information applies when the data source is Oracle EBS.

OBI Apps allows this navigation by default using the ‘GL Journal ID’ column as a link between subject areas. It is available in general ledger transaction subject area and in the following sub-ledgers (In some cases, ‘GL Journal ID’ has to be exposed to the presentation layer):

  • Accounts Payable
  • Accounts Receivable
  • Revenue
  • Purchase Orders
  • Fixed Assets (Available in OBIA 11g)

Understanding the OBIA model

‘GL Journal ID’ is a unique identifier of an EBS journal entry line and it is available in the linkage table W_GL_LINKAGE_INFORMATION_G.

Sub-ledger transaction tables are linked to W_GL_LINKAGE_INFORMATION_G at source distribution level.

Finantial Analytics

For each sub-ledger transaction fact, there is a logical “transaction detail” dimension comprising the transaction fact and the W_GL_LINKAGE_INFORMATION_G table, ‘GL Journal ID’ is available in this dimension. The figure below shows the physical source “Fact_W_AP_XACT_F_GLJournalID” of the logical dimension “Dim – AP Transaction Details”.

Finantial Analytics

With this configuration, it is possible to have ‘GL Journal ID’ available in the sub-ledger transaction subject areas. And since ‘GL Journal ID’ is also available in “Financial - GL Detail Transactions” subject area, it is possible to configure the navigation from general ledger to sub-ledger transactions by using the OBIEE Navigation functionality.

Creating GL to sub-ledger navigation

‘GL Journal ID’ is available in the two subject areas that we want to link, but the level of detail between the source and the target reports are not the same, which is why it is necessary to create an intermediate analysis to link the two reports. This process is described in this section.

  1. Create the GL report and enable navigation.

Using the subject area “Financial - GL Detail Transactions”, create a new analysis that shows the Balance by Accounting Document Source of a selected account for a selected period. Accounting Document Source is used to identify the sub-ledger source.

Finantial Analytics

  1. Create an Intermediate analysis that contains the ‘GL Journal ID’.

Using the subject area “Financial - GL Detail Transactions”, create an analysis that contains the ‘GL Journal ID’ column. Account and period filters need to be created as well. This report will be used to filter the sub-ledger Transactions.

Finantial Analytics

  1. Create a detailed sub-ledger report

Using one of the sub-ledger subject areas, create a new analysis and filter the results by existing report. Use the analysis created in step 2 as the “Saved Analysis”.


image 6 Finantial Analytics

  1. Enable navigation

In the source report (created in step 1), using Action Links, enable navigation to the different sub-ledger reports. The example below shows the navigation to AP Transaction report when Account Document source is Payables.

Finantial Analytics

In conclusion, the OBI Apps data model is prepared to enable the link between General ledger and sub-ledger reports, but it has to be configured at analysis level. This feature can be used for the reconciliation process between General ledger and sub-ledgers.

If you want to go further, create a report that shows summaries sourced by general ledger, a second column that shows sub-ledger totals and a third column that shows the difference between these two amounts, if the value of this column is not ‘0’ navigate to details to discover the discrepancies.


Data Discovery and Analysis: making the best of Oracle Endeca Information Discovery


With the acquisition of Endeca in 2011, Oracle enhanced their already powerful Business Analytics products portfolio with Information Discovery capabilities, potentially allowing customers to analyse structured and unstructured data within the same framework.

Version 3.1 of the tool, released in November 2013, introduced new features such as Data Mash-up from multiple sources, easier and deeper unstructured analysis tools available directly to the business users, a tighter integration with the Oracle BI platform, Enterprise Class Data Discovery and the Web Acquisition Toolkit, a crawler to harvest and analyse website content.

Where, therefore, is Endeca positioned now within the context of Business Intelligence, and how should it be used to make the best of its capabilities? Can it be used as an alternative to OBIEE or other traditional, established Business Intelligence tools? How does its web crawling tool fare against the existing competition? To answer these questions and more, we have put Endeca on the test bench and saw it in action.

In today’s business landscape, analysis of unstructured and large volume data (Big Data) is morphing from a nice-to-have task for cutting-edge, BI-savvy companies to an important driver for business processes in enterprises everywhere. Customer data stored and displayed in social media like Twitter, Facebook and LinkedIn are virtual gold for any marketing department, while sensors capture millions of snapshots on a daily basis that can be used to monitor manufacturing processes and improve their performance. It is not difficult, therefore, to see why Oracle considers Endeca a strategic investment and a key component of its Business Analysis product stack.

In the following paragraphs of this article you will find our honest, no-frills opinion on Endeca Information Discovery 3.1, its new features and our suggestions for making the best of it within your enterprise BI stack.

Integration with Oracle Business Intelligence

One of the most recurring complaints from early Endeca adopters was the lack of integration with the Oracle Common EIM (Enterprise Information Model). As often happens with recent acquisitions, the first Oracle-branded versions of Endeca – starting with Version 2.3 in April 2012 - were mostly a revamp of the existing Latitude tool. Endeca felt like, and actually was, a stand-alone data discovery tool with its own data processing engine and front-end studio.

This has radically improved with Version 3.1. Oracle BI is now a native data source for Endeca and users can now create their Discovery Applications sourcing data from OBI with a two-step easy process. Moreover, the Integration Knowledge Module for Oracle Data Integrator now enables the latter ETL tool to load data directly into the Endeca Server.


There are still margins for improvement, of course. Administration tasks are still performed separately from the rest of the Oracle EIM architecture. Endeca Server does not interface with WebLogic and Enterprise Manager, core of the Oracle Middleware. We would also like to see CloverETL better integrated and possibly merged with ODI, to avoid splitting the overall data workflow and transformation logic in two separate tools. We see a lot of potential in using Endeca Server as a data source to the OBIEE repository, capability that is currently limited to BI Publisher.

We like, however, the concept of e-BS Extensions for Endeca. Based on pre-defined views in Oracle e-Business Suite, the Extensions consist of a set of Studio applications with pre-built content for a broad range of horizontal functions, from Supply Chain Management (Discrete and Process Manufacturing, Cost Management, Warehouse Management, Inventory,…) to Human Capital Management, Asset Management and more. The good level of integration within e-BS makes them a light-weight, easy-to-implement alternative to Oracle BI Analytic Applications module. Like for its bigger brother, however, the customization effort of the pre-built dashboards content required to be successfully used remains a question mark.

Self Service Analysis (Data Mash-up, Provisioning, Applications)

These are the topics that most excited our team when testing the new Endeca capabilities. The range of sources and databases available for data mash-up has been broadened, covering both databases as well as semi-structured data in JSON format and the Applications look and feel has been improved with new visualization options, but in our opinion the most compelling feature of Endeca is the new Provisioning and Applications creation process.

The workflow to create a new Discovery Application is now based on a wizard so user-friendly that we believe the classic buzz-phrase “Business users creating own applications! No more IT overhead!” is not a chimaera anymore but a serious possibility. Yes, establishing the Provisioning Service, connecting to the data source (JDBC for example) and configuring it might require some hand-holding, but once it is done, the wizard simplifies and streamlines the proper Application creation tasks, allowing the business user to perform its data discovery in autonomy.

Also, it is a fact that Endeca Applications look good. Definitely good, actually better than OBIEE dashboards and we can see why business users are usually impressed more by the former than the latter during product demos.

selfservice_endeca - Copy

Web Acquisition Toolkit

“A tool within a tool within a tool” is how our testing team has defined the new web crawling tool embedded in Endeca.

The toolkit looks and feels separate from the Endeca Server (it actually is) and features its own Design Studio where crawling rules and workflows can be defined, organized and scheduled, adding a third layer of data processing complexity: from Design Studio to CloverETL to ODI. In fact, Web Acquisition Toolkit does not use Endeca Server as a target, so a third party ETL tool is necessary to move data accordingly.

However, even if right now there are cheaper and more powerful options on the market, the tool does its job and – if Oracle continues investing in product integration, which we think is very likely – has the potential to become a very interesting feature of future Endeca versions.

wat_interface_endeca - Copy

Best fit for Endeca?

Wrapping up, we can safely say that Endeca is evolving into a compelling component of the Business Intelligence stack for enterprises looking to enable their users to perform rapid-fire data discovery (up to a certain extent, of course – data management, especially in complex enterprise environments, will still be required).

The stand-alone nature of Endeca architecture is a weakness but also a strength, allowing Endeca to be purchased and installed independently from the rest of the Oracle BI stack. However, we can see how e-BS Extensions make Endeca extremely appealing to Oracle ERP existing users.

Could Endeca, therefore, be considered as an alternative to OBIEE (and Oracle BI Applications) as the enterprise Business Intelligence tool? We do not think so. Although its Applications visualization capabilities are very powerful, the best fit for Endeca is to complement OBIEE. While the solid back-end (repository metadata layers, reports and dashboards catalog) of the latter provides corporate reporting in a structured and organised way, Endeca’s real power lies in enabling the business user to individually analyze data patterns on the fly: mix and match different data sources and quickly create new applications to find out the answers they need.

To enable all of the above, Self-service provisioning is where the strength of Endeca shows up. Web sources, unstructured information as flat files can be mashed together, and setting up and configuring another provisioning service to mix it up with the rest is a very easy task.

We at ClearPeaks will keep on the outlook for future enhancements and features of Oracle Endeca Information Discovery. If in the meantime you want to know more about Endeca and how it could add value to your enterprise, contact us.


Exploring unstructured data with Endeca on Twitter


We recently talked about exploring unstructured data with Endeca Information discovery using the example of a football game. As Gitex technology week is taking place this week, we thought it was a good opportunity to look at the actual business benefit of this tool.

1. How many people talked about Gitex on Twitter?
Most tweets could be seen at 11h on Monday, from 13-17h attention stayed constant around 350/h. Please note that you have to add +2h for the time difference in Dubai. This information can measure the impact and success of your event.


2. World map of tweets
While as expected most tweets came from the middle east countries (see second map), we could also see lots of messages from Melbourne/Sidney, New York, Puerto Rico, Germany and Brazil. Endeca will capture a relative number of tweets from each country to analyse positive, negative and neutral tweets about a certain keyword. In our map we just got an overall view of where most Gitex tweets came from, but with an EQL statement you can easily add the evaluation and get concrete tweets. This tool gives you the possibility to get opinions from your visitors.



3. Tag cloud
If we have a closer look at what people were talking about in their tweets, we can detect that the mood was positive at the 33rd edition of the technology week. Even better, we got the opportunity to see which of the 3500 stands stuck out. For example, we can see the terms dubai police, visiting dubai police and black points; the story behind these words is a promotion where every visitor of the Dubai police stand would get 8 black points deducted of their driver´s license for free. The topic went viral and probably caused high traffic at their stand.
From the second day tag cloud we can see that the Abu Dhabi Judicial Department (adjd) was mentioned, during the last years they raised lots of attention because of their keen interest in developing judicial services through more focused investment in IT systems and applications. Later on Todd Carpenter can be found in the cloud, probably because the industry veteran just got rehired for the new predictive analytics department at NAR.

It´s astonishing to see all the details we were able to figure out just by using some of the Endeca functions. We will continue tu use the tool to see what other benefits we can get out of this.

Endeca information discovery

Endeca information discovery

If you want to find out more about how Endeca Information discovery can give you inside information about your brand from social media and blogs, leave us a comment or contact us at

Twitter and Oracle Endeca Information Discovery – Part 2


PART 2: Folling up on the first part of Oracle Endeca Information Discovery

Endeca Studio analysis

After all the loading has been done, a new application in the Endeca Studio can be created with this new data. Then, some meaningful and dynamic dashboards can be created to start “surfing” that data and discovering interesting facts hidden among de hundreds of thousands of tweets that, otherwise, we had missed among the Twitter never ending stream. Let’s get to it.

Observing Messi and Neymar performance repercussion on Twitter

To observe the repercussion of the performance of the star players during the duration of the whole capturing window, the following multi-bar graph/timeline was created:

Figure 1. Messi and Neymar performance repercussion on Twitter

Figure 1. Messi and Neymar performance repercussion on Twitter

It shows how many tweets about Messi, Neymar or both of them where published over time (note that the “Instant” value is coded this way: “21945” means “Day 2 19h 45min”). The peaks at tweets record count mean that some important event has happened about the topic represented by the bar and, for that purpose, the tag cloud component is really useful.

Tag clouds provide comprehensible information about the most frequent or relevant words or phrases in a certain attribute. In our data domain we have two attributes that would be useful to see in that representation: “Tweet Themes” and “Tweet Words”. “Tweet themes” were extracted using the text enrichment engine and provide a meaningful way of understanding the contents of a tweet using a few words. “Tweet words” is just a list of the words inside a tweet and have no meaning by themselves. Building two tag clouds with both attributes provide an easy way of understanding what the tweeters are talking about at a glance. It is advisable to sort the “Tweet words” tag cloud by relevancy instead of by frequency since they are more intelligently weighted.

Let’s see some examples of tag clouds utilisation. At 21:41 (instant 22141) something happened with Messi because 2310 tweets where talking about him. If we click on that bar to filter by this instant and then look at the previously created tag clouds we observe the following:

Figure 2. First goal instant tag cloud

Figure 2. First goal instant tag cloud

We have easily found the precise instant at which Messi scored the first goal.

At 22:36 there is a peak in the “Messi & Neymar” graph so let’s see why through the same process:

Figure 3. First time Neymar and Messi playing together instant tag cloud

Figure 3. First time Neymar and Messi playing together instant tag cloud

People where excited because it was the instant at which Neymar entered in the field and it was the first time he was playing side by side with Messi. One more example of how easy it is to detect important events happening in a time line and identify the main reason using Endeca.

Sentiment analysis

The text enrichment component also provides sentiment analysis information that allows knowing if people are satisfied or not by observing how much positive or negative messages they are tweeting.

Using the metric component this information can quickly be seen textually. We created one “Metrics Bar” using the following EQL query:

Figure 4. EQL sentiment query

Figure 4. EQL sentiment query

The resulting bar can be setup as follows:


Many charts can be plotted to observe a sentiment attribute. Graphs in Figure 5 show two charts:

  • The left one sums up all the sentiment evaluations of the two star players. We can see how the sum is positive for both of them, but it is almost three times more positive for “Messi”.
  • The chart on the right side shows the sentiment evolution over time. Using the value axis dropdown menu, min (more negative sentiment), max (more positive sentiment) and avg (average of sentiments) graphs are plotted.
Figure 5. Sentiment sum and evolution charts

Figure 5. Sentiment sum and evolution charts

If we filter by the minute when Messi scored the first goal (21:41), we can observe how the global sentiment is very positive and the sum of sentiment of the tweets talking about Messi is very high:

Figure 6. Global sentiment at a Messi’s goal

Figure 6. Global sentiment at a Messi’s goal

Where are the tweeters located?

Using the map component, the geographical coordinates embedded in the tweets can be used to place the messages in a map and know, in this case, from which countries the match is being followed.

Since not all the tweets incorporate geographical location information, we can exclude the ones without that information using an excluding filter:

Figure 7. Excluding filter

Figure 7. Excluding filter

The filtering can also be done from inside the map component using an EQL statement. In our example we used three different record sets inside the map to show, in separated tabs by sentiment, positive, negative and neutral tweets.

Figure 8. Tweets geographical distribution by sentiment.

Figure 8. Tweets geographical distribution by sentiment.

Unstructured text search

The powerful Endeca search engine also allows textual searching through structured and unstructured data with some interesting features such as orthographic corrections or “did you mean” suggestions.

Let’s suppose that we want to look at a certain event that happened during our capturing window time. Searching by “goal” and looking at the timeline graph, we can observe that the data peaks for the published tweets occur on the instants when the goals were scored. Additionally, an uncommon event happened during the match: someone from the audience jumped into the field. Searching the word “espontaneo” which is Spanish for “field invader”, we can identify that something regarding that term happened at 22:37 (instant 22237)and that it had something to do with Neymar (as the timeline in Figure 9 suggests).

Figure 9. Searched event timeline

Figure 9. Searched event timeline

By clicking on it and looking at the tag clouds of that instant (Figure 10), we can guess that an intruder jumped into the field and tried to hug Neymar.

Figure 10. Event tag clouds

Figure 10. Event tag clouds

Setting up alerts

The last thing I am going to talk about in this post about Endeca Studio is about how to set up alarms for quickly identifying certain events that could happen amongst our data when certain conditions are met. Let’s suppose we want to monitor the top minutes with more than 1000 tweets (important events). Each alert has to be defined using a filtering EQL query. For example, for the proposed case, the following query was defined for moments with more than 1000 tweets:

Figure 11. Sample EQL query to get minutes with more than 1000 tweets

Figure 11. Sample EQL query to get minutes with more than 1000 tweets

Using an alert message as “{twts} tweets have been published at hour {h} minute {m}.” a graph as shown in Figure 12 is obtained. The data can even be refined by the alarms, for example if we click on the third alert for “Important minutes”  (2481 tweets at 22:55) and look at the tag clouds, we can identify that Dongou replaced Messi at that moment.

Figure 12. Alert results

Figure 12. Alert results

Endeca Studio offers an easy and intuitive way of discovering and visualising information from both structured and unstructured data providing a user friendly interface and useful components. However, this simplicity is highly affected by how well the integrator manages the initial data. So it is important to consider that the right structurisation of unstructured data (as far as possible) paves the way to a better analysis later on.

Twitter and Oracle Endeca Information Discovery


PART 1: Twitter Extraction, Integrator Transformation and Endeca Loading

In the last years we have witnessed how the explosion of social media, rather than just providing an easy way of generating and publishing new personal content, collaterally offers a real-time source of information that shouldn’t be wise to ignore by any company that aims for customer satisfaction (and which isn’t?). That´s why today we want to talk about Twitter and Oracle Endeca Information Discovery.

Nowadays, Twitter is the most representative example of social network providing real time data coming from users all over the world that includes their reactions, opinions, criticisms and or praises to any social event such as a new product release or a football match.

The main drawback of those kind of data sources is that they are heterogeneous, unstructured and some processing needs to be done in order to extract useful information from it. That’s when Oracle Endeca Information Discovery (OEID) comes into play.

This blog article will explain how to capture fresh real time Twitter data in a football match scenario and feed it to Endeca in order to perform unstructured text enrichment and analytics.

Oracle Endeca Framework

Three main components can be identified in the OEID platform: the Oracle Endeca Server, the OEID Studio and the OEID Integrator.

  • Oracle Endeca Server. Stores the data into a key-value pair data model enabling a flexible management of changing and diverse data types, fact that also reduces the necessity for up-front data modelling.
  • OEID Integrator. Gathers and unifies diverse sources of data and also allows the enrichment of unstructured text such as entity and theme detection as well as sentiment analysis.
  • OEID Studio.  Allows the final business user to visualise and discover new insights by analysing the stored data through the intuitive building of interactive dashboards.

Figure 1: Oracle Endeca Framework

In this blog’s use case, the OEID Integrator will be used to parse and classify the raw Twitter data as well as to perform some text enrichment and sentiment analysis over the captured tweets content before loading them into the Endeca Server. Afterwards, the OEID Studio will be used to perform the analytics over the extracted information.

Twitter stream

The public streaming APIs provide low latency real time access to Twitter’s global stream of data. With a “filtering” API call, we are capable of opening a connection with Twitter through which we will be receiving all the tweets that match our query.

For our football match example, we wrote some Java code to capture all the tweets related to the last August 2nd match of the 48th edition of the Joan Gamper Trophy between F.C. Barcelona and Santos, focusing on the two most popular players (Messi and Neymar) with the following query:,neymar,gamper

The analysis conducted in that article comprises between 19:45 and 0:00 CET (match started at 21:30). A total sum of about 180,000 multi-lingual tweets (mainly English, Spanish and Portuguese) were captured.

Tweets are received in JSON format, so some parsing was done “on the fly” while receiving from the stream in order to extract the most important characteristics from the tweets and the authors: tweet ID, date, text, coordinates, user ID, user name, user language, user location, and other useful information.

Figure 2: Example of captured tweet in JSON format

Loading tweets into Endeca

After building the data file containing the tweets captured from the stream and adding the influence information, it’s time to perform the necessary transformations in the data and load it into Endeca. That process, performed using the OEID Integrator, comprises the following steps (sample graphs from the “GettingStarted” demo project are advised to be used to perform common tasks such as domain creation or configuration):

1. Initialise the data domain. The graph in Figure 3 checks if the data domain is already created. If it is already, the domain is enabled and if not, it is then created.

Figure 3: Data domain creation

It is based in the “InitDataDomain.grf” graph from the sample “GettingStarted” project. For it to work, it is just necessary to change de adequate properties in the “workspace.prm” file with our Endeca Server and data domain properties:

Figure 4: Endeca Server and domain configuration in workspace.prm file

2. Load the attributes configuration. As I said before, it isn’t necessary to define the data model (hence Endeca flexibility). However, sometimes it is necessary to override some default attribute settings. Since the last version of Endeca, attributes are “single assign” by default (they cannot take multiple values for the same attribute). So, if for example, we want to store in a single field a list of words that represent the tags that define a tweet, we need to set the “IsSingleAssign” property of that attribute profile to “false”. The following images show the content of the configuration files in our case scenario (profiles, metadata and groups).

Figure 5: Attribute profiles configuration file

Figure 6: Attributes metadata and groups configuration files

“LoadConfiguration.grf” graph from the demo project can also be used here to correctly set-up the attributes properties, metadata, groups and basic search configuration (see Figure below). Some of those configurations can be changed later on by running a modified configuration graph or directly through the OEID Studio.

Figure 7: Attribute configuration loading

3.Transform and load the data. The last step is to manage the captured data from twitter, pass it through the text enrichment components in order to obtain semantic information (like the topics the twitter status are talking about or the sentiment analysis) and, finally, load everything to Endeca server.

Figure 8: Data transformation and loading

Figure 8 depicts the ETL graph of that stage. Data extracted from the twitter stream was stored in two separate data files: tweets and users. Both data flows are merged and then processed through four text enrichment components. These elements are in charge of calling the external Lexalytics[2] analysis module to extract the sentiment, named entities and themes (among other information) from the tweets text in three different languages (English, Spanish and Portuguese) and through a special data set prepared for analysing Twitter messages. Afterwards, data processed is merged together and loaded into the Endeca server ready to be used in the Endeca Studio to perform the analytics.

Finally, the data domain in the Endeca Server is loaded with our data ready to be used through an Endeca Studio application to start extracting useful information. In the next blog post, I will talk about how to build an OEID Studio application.

Read the second part of Twitter and Oracle Information Discovery here:

[1] Lexalytics Text Analysis Software Engine:

privacy policy - Copyright © 2000-2010 ClearPeaks