O SpecGold OracleBusIntApps7 clr



   Call us now 

  Manchester Office

  +44 (0) 8450 940 998




Welcome to the Beyond Blog

As you'd expect from the winners of the Specialized Partner of the Year: Business Analytics at the Oracle UKI Specialized Partner Awards 2014, Beyond work with leading edge BI Applications primarily within the UK Public Sector. We intend to share some of our ideas and discoveries via our blog and hopefully enrich the wider discussion surrounding Oracle Business Intelligence and driving improved insight for customers

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Relationship Issues

No, this isn't the latest agony aunt column - I'm talking about data relationships! Specifically, relationships between columns within single tables. For example, let's take a simple list of UK towns with their associated county and country. This was the first hit on Google when I did such a search. Now assume we also have a table of people and the town in which they live. So we might want to ask the very simple and reasonable question Get me all the people who live in a town which is in England and in the county of Essex.

So why would this cause a problem? Well first, we need to understand how Oracle retrieves information from the database and its strategy (plan) for doing that. This is a topic for which there are many entire books written on already, and so I'm just going to give a very basic example with just two strategies.

  1. We could query the towns table to get a list of all the towns matching our predicate (the town is in Essex, England). Then for each town we find, we go to the people table and get the list of people in that town. This kind of approach is commonly known as Nested Loops.
  2. We query the list of towns matching our predicate. Then we query the list of people. Once we have those two lists together, we match the two up (how this is done can vary). This kind of approach is commonly known as a Hash Join where rows are matched using a hash function.

So which approach is better? It depends. If we found only one town in the county of Essex, then clearly it would be quicker to read that one row, and then go and retrieve just the people living in Essex using an index access path. We are done. However what if we found 10,000 towns in Essex? Fetching the list of people for a single town might be very quick, however doing it 10,000 times likely isn't so. No matter how quick something is, if we do it enough times then the process as a whole becomes slow. In this case we would be quicker reading out the two list up front and then matching (i.e. hash joining).
So how does Oracle know how many rows (the cardinality) it is going to process before it has actually processed? Well... it doesn't! It has to estimate this up-front at the time it parses the query based upon the information it knows (statistics of the table and columns). In simple examples this is very basic mathematics:

Number of values in the table / Number of distinct values in the column

So if we have a table of 10,000 records with 500 distinct values, then if we filter on one value Oracle will expect to retrieve 10,000/500 rows = 20.
This basic mathematics extends even further when we filter upon multiple columns. We use standard probability theory that states the probability of two events is the product of the probabilities of those distinct events:

P(X and Y) = P(X) * P(Y)

The probability of retrieving a row is simply the inverse number of rows we expect to retrieve. So again using the example above, say we have the same 10,000 record and column A with 500 distinct values and column B with 20 distinct values. The probability of retrieving a row with single value predicates on A and B is:

(10,000/500)/10,000 * (10,000/20)/10,000

0.002 (0.2%) * 0.05 (5%)

0.0001 (0.1%)

Most of the time this works perfectly fine. However... when we have relationships within our dataset, this breaks down somewhat. For example, if we pick the county of Essex, we naturally know that Essex doesn't exist in any other country. Oracle doesn't though. So considering our single data set, we can estimate the cardinality using the above rules without even loading into Oracle, simply by looking at the data. So let's do that.

Last modified on Continue reading
Tagged in: Performance sql Tuning
in Technical 1444 0

A while back I wrote a post about how to combine measures from different facts in an OBIEE analysis. If you missed that then you can find it here. One of the key issues I highlighted was that generally it is only possible to join up facts where the dimensions you are using are common across both facts. I.e.

Fact Diagram

There is however a technique that can be used to achieve this. Let's assume we want to report on the number of sickness absence days by department in a BI Apps model. A simplified view of these two facts and the mandatory dimensions would be

Absence Workforce Diagram

Last modified on Continue reading
Tagged in: OBIEE
in Technical 2259 0

There have been quite a few posts on the Oracle OTN Forums E-Business Suite space recently with people asking some kind of variation of the following question.

How can I disable specific descriptive flexfield segments for specific users?

As per My Oracle Support Document 372034.1, this functionality is not supported by forms personalization and there is an enhancement request in place to achieve this, however at time of writing this is not supported. So instead I am going to present a method of achieving this using functional setup within the application - Flex Value Security. As this approach uses value set security then it of course follows that the method only works for those flexfields segments having a value set defined against them (of a type that security can be enabled)! That might seem a bit of a restriction at first, and whilst I suppose it is, I would say in general that most descriptive flexfield segments I see across different sites either independent sets assigned to them (or if they haven't then they should have!).

The descriptive flexfield I will be using for demonstration purposes is configured as follows.

Flexfield Configuration

Last modified on Continue reading
Tagged in: E-Business Suite tips
in Technical 3762 0

Oracle have recently released Data Visualization Desktop ( which “gives decision-makers their own personal desktop application to access, explore, blend, and share data visualizations”.  If you already know Oracle Data Visualization in the cloud ( aka DVCS ) then this is pretty much a desktop version of that but can feed data from your BI analytics, easy access to data in on-premise databases, etc. 

So, if your organisation is not ready for the cloud or you have data that can’t reside in the cloud or perhaps you just want a private tool to perform analysis from data held in an on-premise E-Business system, then DV Desktop is the tool for you.

1st image
Let me show you how easy it is to install

Last modified on Continue reading
in Business Intelligence 2817 0

We've been working with a number of customers who want to see context specific charts/graphs displayed when the mouse rolls over values in a table, rather than having to drill.  In order to show an example of this rather slick approach we have created a 30 second video as a demonstration    

Please have look here


Last modified on Continue reading
in Techniques 1940 0