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

DV Desktop v2 has been on general release now for a week or so and I highly recommend the following Oracle video to see some of the new functionality

There's a lot to like about this release - the hugely enhanced connectivity and workflow for example, but some things I haven't seen many people talking about yet is the SDK or the ability to use plugins to add new content; here's a quick way of doing this.

1) Goto the Oracle BI Public store


2) Select a plugin and download it.  Note that you DO NOT UNZIP the file and also you will need to create the /plugin directory.  By default on windows your %LOCALAPPDATA% directory is hidden so you'll probably need to unhide it to find it!


3) Volia - the new visualisation is available for use; here I have tweaked the setup of the dials and used them in my application.


...any questions ... please ask.

Last modified on Continue reading
in Techniques 1803 0

In my previous blog post An Introduction to Analytic Views in Oracle 12.2 I briefly mentioned that one of the killer features of Analytic Views is their ability to hold a huge amount of metadata within the database objects themselves, which can then be accessed and utilized from the application tier. Last time we built up a very simple Employee Sales view, which I will reuse again for the purposes of this demo. If you haven't already read the previous blog then I'd suggest doing that before continuing here.

So taking our very simple data model - what does it actually represent? Well, I have already told you that, it represents sales by employees, however what if I wanted the database to provide you with that information? So you don't need to maintain a separate set of documentation or have a local expert who can recall the details from memory. Well, using the Classification clause we can do just that. We can modify the view definition we created previously to the following:

Create or Replace Analytic View emp_sales_av
  Classification Caption Value 'Employee Sales'
  Classification Description Value 'Employee Sales Amount over Time'
Using emp_sales
Dimension By (
    Key date_wid References row_wid Hierarchies (calendar_hier Default),
    Key emp_wid References row_wid Hierarchies (employee_hier Default)
Measures (
  amount Fact amount
  Classification Caption Value 'Sales Amount'

Note I have also given a more descriptive classification to the measure we defined. If we now create a very simple application (I am going to use Oracle Application Express due to its power, ease of use and the fact that it comes ready shipped with an Oracle Cloud Exadata Express instance), we can make use of that metadata within the application very easy by using the dictionary views for AV's. First, we will get the information about our model and display it.

Last modified on Continue reading
in Technical 1692 0

Oracle Exalytics Express is the first production release of the Oracle 12c release 2 database ( aka Oracle 12.2 ).  It comes with some amazing features such as analytic views which we have alr4eady blogged and will be expanding on shortly.

As soon as your instance has been spun up you'll want to connect SQL Developer to it, so here's a few hints and tips to assist.  Note that Exadata Express provides you with a pluggable database (PDB) so if this is your first time with a 12c database then I recommend reading up all about how container and pluggable databases work.

Firstly you'll need to enable SQL*Net access and then click on the "Download Client Credential" in your console. 


This will download a zip file to which you allocate a password and you'll need to store that somewhere in your filesystem that SQL Developer can see it.

Now you'll need at least version 4.1.5 of SQL Developer for this to work, so upgrade if you're on an earlier one.  I'm a keen user of the 4.2 early adopter.


Create a new connection and use the new "Cloud PDB" connector.  The configuration file as the one that you downloaded when you were in the console.  Press "Test" and hopefully you should see the "success" message that I have.


That should be you up and running. 

.If you're following John's blog published on the 8th of October on Analytic Views then you may find the following useful.  SQL Developer now comes with a bunch of pre-built reports that give you some great insights of the analytics and their metadata that have been created.


Last modified on Continue reading
in Technical 1553 0

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 3900 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 1775 0