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

In my last blog post on Oracle Database 12.2 I mentioned the new Analytics View feature. Now in this post I am going to describe the basic structure of what an Analytic View is, and then show an example of creating a very simple one. Note - the Oracle documentation for syntax is very thin at the moment and this information has therefore been taken from various sources as well as some trial and error.
Anyway, enough of that... what actually is an Analytic View? As I described previously, it's essentially a database structure to encapsulate an OLAP cube, providing an easy to use object for querying over hierarchical levels with automatic aggregation, storage of metadata and a whole lot more. Think of them as a basic RPD in Oracle Business Intelligence. Oracle describes them as:

Analytic Views (AV's) provide a fast and efficient way to create analytic queries of data stored in existing database tables and views. Analytic views organize data using a dimensional model. They allow you to easily add aggregations and calculations to data sets and to present data in views that can be queried with relatively simple SQL.

Now let's look at the structure of an AV. The model is made up of three objects.

  • Dimension Attribute - Defines a dimension along with the hierarchical levels (multiple hierarchies possible)
  • Hierarchy - Defines the hierarchical structure of a dimension (parent/child)
  • Analytic View - Brings together the hierarchies and defines the measures

A diagram can be useful here to illustrate.

Av Diagram

There are other attributes and concepts that can be used within the above, such as classifications (which allow for the holding of metadata), however I intend to cover those in a future post as that really is a whole topic within itself. So for the purposes of this post we will keep everything very simple.

Update - 23/03/2017 - I have added this as a shared LiveSQL script. Click here to view.

Last modified on Continue reading
in Technical 2853 6

Oracle Openworld 2016 saw the latest Oracle database version announced with immediate availability in the cloud - 12.2. Unfortunately this is not yet available on-premise however a free trial can be obtained over at This release contains some exciting stuff such as 128 byte identifiers (up from 30), additional approximation aggregates over and above APPROX_COUNT_DISTINCT and enhancements to some existing functions such as a trimming option for LISTAGG. However most exciting to us here at Beyond is probably the Analytic View. This is a database feature which allows the representation of a star schema data model within the database (including hierarchical dimensions), with a simplified query syntax. Moreover it allows a complete set of complementary metadata to be stored within the database itself, which can then in turn be utilised by your application. Essentially an encapsulation of the OLAP cube within a database object (or several objects to be precise).

Over the coming weeks/months I'll be posting some in-depth examples and studies of analytic views so watch this space!

Last modified on Continue reading
Tagged in: Database 12.2 sql
in Technical 1193 0

Here at Beyond we recently started updating some of our internal test environments. As part of this I embarked on the rather daunting task of installing Oracle Fusion Applications. Daunting because 1) I'm not a DBA and 2) I've never personally done such as installation myself ever before! What could possibly go wrong? We picked up a copy of Pro Oracle Fusion Applications and set about modelling a quick architecture. We wanted something very basic with miminal effort needed for installation that would simply allow us to try things out. So I opted for a two node setup, partly to keep everything simple and partly because we don't want to have to commission a server farm to run the thing! The basic setup looks as follows.


Both machines are virtualized using Oracle Virtual Box, which allows for some nice options such as dynamic drive sizing and snapshotting of configuration. The latter is a particularly good feature, allowing you to snapshot the machine at key milestones of the installation, on the grounds that if anything is to go wrong you're not back at square one! I think we're going to have to pinch a bit more RAM for the fusion node though as whilst it starts up ok with 100Gb, it does end up swapping about about 10Gb throughout general use.

Oracle haven't made it very easy in getting the software - there is an 11.1.8 release for Linux x86 available on the Software Delivery Cloud, however the 11.1.9 release is only available for AIX. After hunting around Google a while I found some conflicting information whereby one source said you had to request the later versions and another said that the 11.1.9 release was for Cloud installations only. Keen to avoid making life too difficult I went for the 11.1.8 release, primarily because that's what the installation guide in the book is written on.

The Identity Manager (IDM) installation was relatively straightforward - however you can save yourself quite a bit of pain by ensuring you do at least the following as an absolute minimum upfront!

  • Set the file limits in limits.conf
  • Get all required operating system packages installed (including some 32 bit libraries)
  • Configure the hosts file correctly - you can get some difficult to diagnose errors if this isn't done.
Last modified on Continue reading
Tagged in: Fusion
in Technical 2331 1

I've posted a few things over the previous months regarding new or changed functionality in E-Business Suite R12.2, and here is something that I think will be particularly useful. In previous versions of E-Business Suite it's possible to grant access to full account level access to another user, however as of 12.2 this has been enhanced to responsibility-level access. So you no longer have to give another user full access to your account, you can delegate just the specific responsibilities you wish.

It's probably easiest to show this via a demo. I log in using my user "beyond" and select Manage Proxies from the options menu. You might notice I'm using the Framework Simplified view here too! The reason for which will be apparent later :)

Select Manage Proxies

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

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 1204 0