Welcome to the Beyond Blog

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

Oracle Row Generators and Cardinality

I'm quite a fan of row generators. If I know I need a row for every day of the year for example, I generally avoid holding (and maintaining) a table of these dates, and instead will generate them. This is by no means a universal rule, so take each case on its merits. select trunc(sysdate,'YYYY') + level -1 dt from dualconnect by level <= add_mon...
Continue reading
  561 Hits
  0 Comments
561 Hits
0 Comments

The Basics of Consumption Queries in SQL

I was inspired to post this blog by a post on the OTN Forums where a user was asking about calculating the consumption of hours against a target - that is, given a target of X hours with N hours per day, how many days would it be until they reached the target? I knew I had done similar things several times in the past, and each time I always seem t...
Continue reading
  344 Hits
  0 Comments
344 Hits
0 Comments

Calculated Measures in Analytic Views

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_avUsing emp_salesDimension By...
Continue reading
  640 Hits
  0 Comments
640 Hits
0 Comments

Date Tracking with Temporal Validity in Oracle 12c

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 Vali...
Continue reading
  753 Hits
  0 Comments
753 Hits
0 Comments

Using Data Dictionary Metadata in Oracle 12.2 Analytic View Applications

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 Sal...
Continue reading
  891 Hits
  0 Comments
891 Hits
0 Comments

An Introduction to Analytic Views in Oracle 12.2

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 ...
Continue reading
  2106 Hits
  0 Comments
2106 Hits
0 Comments

Relationship Issues

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 ta...
Continue reading
  1012 Hits
  0 Comments
1012 Hits
0 Comments

Using LISTAGG and other meta columns in ODI interfaces

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...
Continue reading
  3419 Hits
  0 Comments
3419 Hits
0 Comments

(Mis)using the WITH clause in Oracle

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...
Continue reading
  1610 Hits
  0 Comments
1610 Hits
0 Comments

Predicate Evaluation Order - Using Generic Fields

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. Background As a bit of background for those who may not be aware, some standard E-Business Suite tables c...
Continue reading
  1628 Hits
  0 Comments
1628 Hits
0 Comments

Post Load Processes - Don't Neglect Your Statistics!

It is quite common for warehouse developers to customise standard processes to add additional fields. Ideally this is done by modifying the appropriate mapping(s) to pass the field through, regardless of the technology use (Informatica, ODI etc). However in some instances, generally due to the complexity of a particular mapping process, developers ...
Continue reading
  1738 Hits
  0 Comments
1738 Hits
0 Comments

Integrating external command line tools with Oracle data

One thing I find interesting to explore is the integration of different tools with Oracle which may not always be obvious. For example, take the fantastic UNIX command line tool gnuplot . Using this we can very easily produce graphs and charts directly from Oracle data with very little effort. We can even extend that out further to other utilities ...
Continue reading
  1751 Hits
  0 Comments
1751 Hits
0 Comments

Generating Rows Automatically in Oracle

Generating Rows Automatically in Oracle Quite often there is a need to generate rows in SQL. Whether it be to generate a row for every date between X and Y (the most common I personally find), or simply just to generate some dummy data for testing purposes. Or even just as an academic exercise it is good practice to explore the options for achievin...
Continue reading
  1996 Hits
  0 Comments
1996 Hits
0 Comments

SQL and PL/SQL Mistakes, Misunderstandings and Misconceptions - Part 2

If you missed part 1 then you can find it here.  In this post I'll be focussing on a somewhat common theme -  indexes . Whilst this may be more database design than coding concepts, the principles of course underpin any SQL statement that you write and such concepts are arguably one of the most important aspects of writing any piece of co...
Continue reading
  1924 Hits
  0 Comments
1924 Hits
0 Comments

Star Transformations with Bitmap Indexes

One area of data warehousing that often confuses people (typically those who have come from an OLTP development background) is bitmap indexes... why do we use them from a design point of view? How does the optimizer use them? What benefits do they have? Whilst this topic alone can (and does) cover entire books, I'm hoping to give a brief overview o...
Continue reading
  2503 Hits
  0 Comments
2503 Hits
0 Comments

SQL and PL/SQL Mistakes, Misunderstandings and Misconceptions - Part 1

I wasn't too sure of what title to use for this blog post. I wanted to share some things that I see quite regularly at various sites in SQL or PL/SQL code where the developer has either not fully understood what they have written, or have done so under assumptions or risk. These are not necessarily always "mistakes" as such (and they may similarly ...
Continue reading
  2397 Hits
  0 Comments
2397 Hits
0 Comments

Demonstration

Request a demo of our products here

REQUEST DEMO

Contact

Beyond Systems Limited

Barnett House, 53 Fountain Street,

Manchester M2 2AN United Kingdom

 

 

Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk