The latest Oracle BI EE 12c VM is now available for download.
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
I recently needed to use the LISTAGG function in a custom Oracle ODI interface. However to my dismay I found that ODI didn't recognise LISTAGG as an aggregate function (i.e. didn't add a GROUP BY clause to the SQL generated).
After a bit of searching on Google I found I wasn't the only person to have hit this and found this blog post which explains a method for adding the function to the language in the ODI topology. I tried and failed - no matter what I did I couldn't get it to work.
So instead I tried a little trick I have used in the past for something simlar - to add a commented out aggregate function in the column source.
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.
One of the advantages of a Cloud BI solution is that it received regular updates and so is always "current" without the consumer having to do anything.
The recent November 2015 refresh has provided a new, cleaner interface as well as a number of additional features.
I thought this worth talking about as I've been in discussions with a number of people recently around best practices for data types etc. The example in the discussion was the implementation of Descriptive Flex Views in Oracle E-Business Suite.
As a bit of background for those who may not be aware, some standard E-Business Suite tables contains a number of generic fields which a developer can utilize to hold their own custom fields. For example, here is a descriptive flexfield against the documents attachment table FND_ATTACHED_DOCUMENTS.
You can see I've created a new attribute against which I can hold some custom information. When I save and "freeze" that, I get a notification that the flexfield has been generated.