Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   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

Back onto analytical views, today I'm going to demo using calculated fact measures in analytic views as opposed to standard fact measures.
You may recall from my Introduction to Analytic Views post that we can create simple fact measures within the fact table itself as follows.

Create or Replace Analytic View emp_sales_av
Using emp_sales
Dimension By (
  date_dim
    Key date_wid References row_wid Hierarchies (calendar_hier Default),
  emp_dim
    Key emp_wid References row_wid Hierarchies (employee_hier Default)
)
Measures (
  amount Fact amount
); 

Useful, and with a bit of manipulation, subqueries etc. we can do quite a bit with that basic measure using plan SQL. There is however no need to go through all that effort. Analytic Views support Calculated Measures, and that makes them a whole lot more useful. Taking our model used previously we can quickly and easily extend it to include many more derived measures; and these functions extend out far further than the existing Analytic Functions available in database <lt; 12.2 (although you will note syntactical similarities). We can build measures based on the hierarchies with the AV, and even on levels within the hierarchy. So to add a simple measure of Sales Year to Date, it's as easy as:

amount_ytd As (Sum(amount) Over (Hierarchy calendar_hier Between Unbounded Preceding and Current Member Within Ancestor At Level cal_year))
Last modified on Continue reading
Tagged in: Analytic Views sql
in Technical 830 0
0

It's always nice to see new database features, and I especially like to see features which implement concepts that are commonly coded at the application level. I've posted a number of blogs recently on the new Analytic Views functionality in 12.2, however today I'm going to look at a feature introduced a little earlier in 12c (12.1) - Temporal Validity.

It's probably best explained with an example, so let's take a look at some sample (simplified) date-tracked data such as employees in a Human Resources system.

Person_IdFull_NameDepartmentStatusEffective_From_DateEffective_To_Date
1 Alice Jones Finance Active Employee 01-Jan-2016 17-Aug-2016
1 Alice Smith Finance Active Employee 18-Aug-2016 31-Oct-2016
1 Alice Smith Human Resources Active Employee 01-Nov-2016 18-Nov-2016
1 Alice Smith Human Resources Inactive Employee 19-Nov-2016 31-Dec-4712

For anyone not familiar with what the above is representing, we see that up until 17-Aug-2016 we have an employee named Alice Jones working in the Finance department. From 18-Aug-2016 she has changed her name to Alice Smith, still working in the Finance department. Then from 01-Nov-2016 she begins working in Human Resources up until 18-Nov-2016 when she left the company. This representation of data allows us to query historic data and see what the status of an employee was at any given point in time. First let's create a sample data model to represent that.

Last modified on Continue reading
Tagged in: HRMS sql tips
in Technical 1067 0
0

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

https://www.youtube.com/user/OracleBITechDemos/videos

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

b2ap3_thumbnail_dial2.png

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!

b2ap3_thumbnail_dial3.png

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

b2ap3_thumbnail_dial1.png

...any questions ... please ask.

Last modified on Continue reading
in Techniques 1173 0
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 (
  date_dim
    Key date_wid References row_wid Hierarchies (calendar_hier Default),
  emp_dim
    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 1133 0
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. 

b2ap3_thumbnail_consol1.png

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.

b2ap3_thumbnail_SQLDev.png

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.

b2ap3_thumbnail_DevConnect.png

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.

b2ap3_thumbnail_HV-reports.png

Last modified on Continue reading
in Technical 1071 0
0