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
Subscribe to this list via RSS Blog posts tagged in Oracle

Oracle have released the new Day by Day app for both iOS and Android and it's rather good.  There's a great 6min video here which I highly recommend watching, but I thought I'd share my first experiment with it.

I connected to my demo Oracle Analytic Cloud instance to use the good old Sample App test data.  I then used the microphone to dictate into the app "Revenue in Americas for game station".  Yes - that's right - I just dictated - asked a fact ( the Revenue ) for a dimension value ( Americas which is in Regions ) for a specific Product (  Game Station ).


If you see how that was interpreted, it replayed "America's" and "four" based on my diction and .... went and got exactly the right results!

I doubled checked this figure by actually going and querying it back using the traditional clicking about in the screens. 

I then thought that what would be interesting would be to ask Day by Day the same question but also extend it out to say "by Year" and see how it handles that.  Rather well it would appear.  It initially gave me a line graph that I was then easily able to toggle to a bar chart like this :



Pretty cool.  I thought that I'd tell it that, so I pressed the "comment" button and commented on that


I can then also share that with my "Crew" - or colleagues - as there's quite a bit of social engagement about it. 


There's a lot to like about this - not just the fact I can ask it questions and get instant answers, but also that it can do location specific stuff and give me a feed of things I'm actually interested in and also share that information with colleagues.  I suggest that you do watch the video and if you need OAC/Day by Day setting up then please ask us!

Last modified on Continue reading
Tagged in: Day by Day Oracle
in Business Intelligence 869 0

Whilst BICS comes with complete Schema as a Service, some customers choose to use database as a service (DBaaS) for many reasons (such as the ability to use the on-premise modelling tool and "lift and shift" the model).  DBaaS has recently been upgraded to offer 12.2 as an option for the database which is what we were trialling as part of internal R&D whilst we were formulating some recommendations for a BICS customer.

Whilst doing this we encountered a couple of issues with connectivity but fortunately we were helped out by this paper that has just been released by Oracle in February 2017 called "Known Issues for Oracle Business Intelligence Cloud Service".

In addition this paper covers a few other things you will likely want to know about, so if you have embarked on a BICS installation then I highly recommend giving this a quick read.


Last modified on Continue reading
in Installation and Patches 1060 0

The WITH clause (or subquery factoring clause) is a very useful construct. It allows us to materialize the results of a SQL statement to use multiple times, without having to re-execute that statement. Here is an academic example of that in practice.

With demo As
 (Select * 
   From Employees e
  Where e.job_id = 'IT_PROG')
Select * From demo d, demo d_mgr
 Where d_mgr.employee_id = d.manager_id; 

It's very unlikely that we'd produce the above requirement as-is, however for the purposes of this demo it'll suffice.

Last modified on Continue reading
Tagged in: Optimzer Oracle sql
in Technical 2586 0

Spend Classification is a topic that's coming into sharper focus with our clients as they look to better understand and analyze their spend and make savings across the organization.  Oracle provide a Spend Classification complementary component for Oracle BI Applications to assist with this analysis and over the next series of posts we will look at the reasons for considering such a solution, how to implement it and how to actually make use of it.

Products such as Oracle Purchasing allow the buyer to select categorization when entering PO lines, however there's some issues here.  Firstly, it's not normally really "sense checked" at this point, so as long as a valid selection from whatever categorization setup has been implemented is selected then all seems well.  When the product being procured is "computer mouse" and the buyer enters "Hardware.Mouse" to categorize it, then that's a good start.  However, they could just enter something generic like "Misc.Misc" or perhaps (even worse?) something misleading such as "Environmental Services.Mouse".  This categorization has normally been setup by the Finance department but what if you actually wanted to analyze spend in a different way, not aligned to this categorization but a different taxonomy, also what if you had a number of ERP systems in the organization that had completely separate categorization taxonomies, then it's getting increasingly difficult to categorize spend.

What Oracle provide is a "rules engine" solution that can analyse loaded spend data and compare to a central knowledge base in order to assign a classification to the spend data that will allow the organization to slice and dice it how they like.   

The following is the outline of the process :

·         Create a training data for spend classification.  Consider the "training data" as a cleansed dataset ( and a spreadsheet template is provided ) of all the categories that you desire and a number of examples real data with the fields which need to be assessed in order to make that judgement.  Spend classification will find patterns in the "training data set" to describe the spend for each category and the more relevant information that it has the greater the degree of accuracy.

·         Create a Taxonomy.  Basically, this is hierarchical "Hardware.Mouse" breakdown of all the categorisations to be used.

·         Create a Knowledge Base based on the taxonomy and the training data.  This knowledge base will be used as the master repository against which to compare any batches of spend data that need to be analyzed.  If people can buy computer mice from different suppliers, have different descriptions, etc then the more complete the knowledge base, the more accurate the classification process will be.

·         Perform the classification of a real spend dataset.  This utilises the knowledge base as a "rules engine" to assign categorisations to each element of spend.

·         Check, Amend, Approve.

This sounds straightforward, but there's a lot of background detail to this process; for example one can employ a standard knowledge base using APIs or an advanced model that utilizes Oracle Data Mining techniques.    

I was initially a bit cautious when performing the installation of the product as the current version is "7.9.6" which is a nod to the older version of BI Applications.  Fortunately, it is indeed certified with the releases of BI Applications - and there's a script specifically to change some of the product views to align them to the new data structures provided with the 11.1.1.X versions of BI Applications.

Once the installation is complete, the application is accessed in a rather unique way that makes access quite seamless for users as some of the Spend Classification dashboards are actually containers for embedded applications as can be seen below.

 The tool actually does integrate with iProcurement so that when any off contract spend is performed, then the buyer can be provided with an assisted pop-up that suggests appropriate categorisation to at least help out in that respect.  This requires the profile "POR: Enable Category Classification on Non Catalog Request page" to be set.

The next planned blog entries for Spend Classification will look at the key documents that Oracle provide for this product, notes on the implementation and how to actually use it to perform analysis.  Stay Tuned!

Last modified on Continue reading
in Techniques 3990 0

One of the products Oracle are currently pushing very heavily is Oracle BI Cloud Service (BICS). BICS allows customers to leverage the features of Oracle Business Intelligence (a limited set of them at least) without the need for on-premise hardware, maintenance overhead, upgrade management and so on. Data can be either uploaded directly into the tool or can be scheduled directly from a source system (such as an Oracle E-Business Suite environment) using Oracle's new tool BICS Datasync. I emphasize "new" because it actually appears to be a re-brand of the old-faithful Database Administration Console (DAC) used in the legacy BI Applications 7.9.6.x.

Data Sync

Whilst BICS is clearly still a product that has a long road to journey upon before it contains the same functionality as the full OBIEE product, one great thing is that you get a little sneak preview of the kind of features that one can expect in the next up and coming major releases of OBIEE.

As with the downloadable SampleApp virtual instances that can be downloaded from Oracle, the BICS subject area comes with the Sample Sales objects installed. This is all very well however sometimes it can be a little "boring", so to get something a bit more meaningful and useful I went for a look around the UK Government Data portal. This is a great resource for getting real-world public-domain datasets of all sizes. I eventually choose the NHS Sickness Absence Rates data set, for no other reason that it was reasonably sizeable (30k+ rows) and broadly interesting to someone who generally works with HR data. I loaded this quickly into a staging table using the BICS upload tool and then created some basic dimensions from those.

Data Upload

create table xxnhs_dates as 
  select distinct abs_month date_key, to_date(abs_month,'YYYY-MON') date_val, to_char(to_date(abs_month,'YYYY-MON'),'Mon') date_month, to_char(to_date(abs_month,'YYYY-MON'),'YYYY') date_year from xxnhs_sickness_absence;
create table xxnhs_regions as 
  select distinct hee_region_code region_key, hee_region_name region_name from xxnhs_sickness_absence;

create table xxnhs_orgs as
  select distinct org_code org_key, org_name, org_type from xxnhs_sickness_absence;
create table xxnhs_sick_absence as 
  select abs_month date_key, hee_region_code region_key, org_code org_key, fte_days_sick, fte_days_available from xxnhs_sickness_absence;
drop table xxnhs_sickness_absence;

Ok, I know that there was no need to be doing multiple conversions on the date dimensions but in the interests of copying and pasting, it's what worked out the quickest for me at the time!
Anyway, once that was done it was then time to create my data model. No Admin Tool needed here - just the (quite basic at the moment) online model tool.


Unfortunately one of the key restrictions in BICS at the moment is that you can only have a single subject area (without using DAAS - Database as a Service). So we therefore have to build within the seeded subject area for now. Anyway, all that saved back we can start to use the data. Here is a Visual Analyzer project, tool which is a more dynamic version of OBIEE Answers, allowing multiple views to be combined in a single project where data of interest can be focussed and drilled into.

The TreeMap View introduced in the latest OBIEE version is a great tool for visualizing relative contribution to a measure. You can see this blog post by Mark Daynes for further information.

Funtionality from the existing product line is of course still available, as is demonstrated below using a trellis chart to show annual performance by organization type.

So there's a very quick demo from start to finish of something that's (hopefully) a bit more interesting than Sample Sales data! In fact the holds some genuinely interesting data sets. If you are starting out getting to grips with a tool such as BICS/OBIEE then I'd definitely suggest using something like this. Using your own data can get users a little too tied down with the detail at times and obscure the bigger picture, whilst Sample Sales is quite often too abstract.
As I mentioned earlier, there still looks to be a reasonable way to go for BICS to be able to compete against OBIEE from a functionality perspective however it's certainly on its way!

Contains public sector information licensed under the Open Government Licence v3.0

Last modified on Continue reading
Tagged in: BICS OBIEE Oracle
in Strategic Views 4687 0