SQL for Data Warehousing and Analytics

Next week I will be in Birmingham at the annual UKOUG Tech conference . I will be presenting on some of the new SQL features that we added in Database 12c to support analysis of big data. I am also planning to cover some of the features that we are planning for  Database 12c Release 2 , which is currently in beta. The talk is titled “ SQL - The Goto Language for Big Data Analytics ” and it should have something for everyone - if you have not made the move to Database 12c then I will be showing some great features that will encourage to start your planning and if you are already using 12c there will be some exciting new features to look forward to with  Database 12c Release 2 .  To give you a taster of what to expect during my session, which is on Wednesday Dec 9 at 3:30pm  in Hall 9, here are some sort-of relevant Dilbert cartoons, courtesy of Scott Adams :     If you would like to download my slides in advance of the session then follow this link:  http://bit.ly/1O5sy11 . See you...

Last week I was in Nuremburg at the excellent annual German Oracle user group conference - DOAG15. This was my first visit to DOAG and I was amazed at the size of the event. It is huge but most importantly it is very well organized and definitely one of the best, possibly the best, user conference that I have attended.  …and then there was the chance to drink some gluhwein  with the database PM team on the way to the speakers dinner.   There were a lot of Database product managers in attendance so it was great chance to catch up with my colleagues from HQ. This, combined with lots of Oracle Ace Directors made for lots of great sessions.Quite surprisingly there was a great spread of English speaking sessions and German sessions with translation. I even attempted a few German sessions that did not offer translation services. Honestly, if you are based in Europe and speak either German or English and you are looking for a conference that offers deep technical content along with produc...

“From the keynotes to the connections to the entertainment, there truly is something for everyone at Oracle OpenWorld”  … This year OpenWorld was bigger, more exciting and packed with sessions about the very latest technology and product features. Most importantly, both data warehousing and Big Data were at the heart of this year’s conference across a number of keynotes and a huge number of general sessions. Our hands-on labs were all completely full as people got valuable hands-on time with our most important new features. The key focus areas at this year’s conference were: Database 12c  for Data Warehousing Big Data  and the Internet of Things  Cloud  from on-premise to on the Cloud to running hybrid Cloud systems Analytics and SQL  continues to evolve to enable more and more sophisticated analysis.  All these topics appeared across the main keynote sessions including live on-stage demonstrations of how many of our news features ...

Most of you probably know that each year I publish a data warehouse guide in iBook and PDF format for OpenWorld which contains links to the latest data warehouse videos, a list of the most important sessions along with hands-on labs and profiles of the key presenters. For this year’s conference I have again made all this information available in an HTML web app that ( should ) run on most smartphones and tablets. The picture below shows the app running on my desktop: This year’s web app will help you get the most from this year’s conference. It includes the following: Getting to know Database 12c  - a series of video interviews with George Lumpkin, Vice President of Data Warehouse Product Management and links to information about Oracle’s Database Cloud Your presenters - full biographies and links to social media sites for all the key data warehouse presenters Must sees sessions - list of all the most important data warehouse presentations at this year’s conference Mu...

A number of people have asked if I could make a smartphone app version of my usual OpenWorld web app ( OpenWorld 2015 on your smartphone and tablet ) which does not require a data or wifi connection. For Android users there is some good news: here is my new Android app for OpenWorld that installs on your smartphone and runs without needing a wifi connection or a mobile data connection. This  DW and Big Data app  is nice and small so it is quick to download and install onto your phone. It contains everything you need to know about data warehousing and big activities at OpenWorld. The app has sections for: Key Oracle Speakers - arranged by category  Data warehouse and big data sessions by Oracle product managers, key partners and customers. Organized by category and day-by-day Hands on Labs. Organized by category and day-by-day Maps to help you find your way around Moscone and the surrounding area To download the Android app click here:  DWBDoow15...

I have published a shared online calendar that contains all the most important data warehouse and big data sessions at this year’s OpenWorld. The following links will allow you to add this shared calendar to your own desktop calendar application or view it via a browser: .ICS format is here:  https://www.google.com/calendar/ical/2ons8f3kke7q1or46qmccd70p8%40group.calendar.google.com/public/basic.ics  - it should be possible to add this to calendar apps such as Apple’s iCal and Thunderbird’s calendar app, Lightning. HTML format is here:  https://www.google.com/calendar/embed?src=2ons8f3kke7q1or46qmccd70p8%40group.calendar.google.com&ctz=America/Los_Angeles  - this should open in any browser, even on your smartphone although all the sessions appear as blue blocks in the calendar for some reason! The calendar also includes all the keynote sessions and most importantly, the hands-on labs. Please note that the hands-on labs are taking place at the Nikko Hote...

      Your Must-See Guide for Data Warehousing and Big Data #OOW2015 There's so much to learn at Oracle OpenWorld - it provides more educational and networking opportunities than any other conference dedicated to Oracle business and technology users. To help you get the most from this year's event I have prepared a comprehensive guide which lists all the must-see data warehousing and big data sessions. It is divided into the following chapters: Welcome to OpenWorld Key Highlights from 2014 Overview of Oracle Cloud and Oracle Database 12c List of Presenters Must-See Sessions Links to product web pages, blogs, social media sites Information about the #oow15 smartphone app Maps to help you find your way around This guide is now available in both Apple iBook and PDF formats. The Apple iBook format will now open on iPads and Mac computers via the relevant iBook app. Please refer to the Apple website for more information. The PDF f...

This is an interesting problem that has come up a few times in discussions (and I think it has been mentioned on the SQL forums as well).  When using LISTAGG  on very large data sets you can sometimes create a list that is too long and consequently get an ORA-01489: result of string concatenation is too long   error . Obviously, it is possible to determine in advance if the error is going to occur using some bespoke PL/SQL code and then take appropriate action within your application to manage the rows that contain stings that exceed the VARCHAR2 limit. Many customers have implemented workarounds to overcome the  ORA-01489  error, however, this has mostly involved the use of complex code which has impacted performance. Wouldn’t it be great if there was a simple yet elegant way to resolve this issue? Actually there is and we can use a few of the most recent analytical SQL functions. If you are using Database 12c you can make use of the  MATCH_RECOGNIZE  function to effecti...

Most of you will already have received an email from the OpenWorld team announcing the call for papers for this year’s conference:  https://www.oracle.com/openworld/call-for-proposals.html . Each year, a lot of people ask me how they can increase their chances of getting their paper accepted? Well, I am going to start by stating that product managers have absolutely no influence over which papers are accepted - even mentioning that a product manager will be co-presenting with you will not increase your chances ! Yes, sad but true! So how do you make sure that your presentation title and abstract catches the eye of the selection committee? Well, here is my list of top 10 guidelines for submitting a winning proposal: 1) Read the "call-for-papers" carefully and follow its instructions - even if you have submitted presentations for lots of Oracle conferences it is always a good idea to carefully read the call for papers and to make sure you follow the instructions. There i...

In the second part of this series of posts on ( Why SQL Part 2 - It has a powerful framework )  of this series of blog posts I explained the underlying framework that SQL is based on. That framework is referred to as “ relational algebra ” and it was first described in 1970 by E.F. Codd while at IBM . It provides well-founded semantics for both modelling and querying a set of data. Codd's framework established a series of basic principles that govern the construction and execution of a query and these can be summarized as follows: Projection Filter Join Aggregate Now that we have examined the basics of the relational model let’s now move on to how the language actually determines the best way to answer a question based on its use of simplified and transparent optimizations. To understand why simplified and transparent optimization is so important it is useful to consider two of the most common programming paradigms and these are: procedural and declarative. Let’s explore the...

In the first part ( Why SQL is the natural language for data analysis ) of this series of blog posts I explained why analytics is important to the business, how the volume of data along with the types of data is continuing to expand and why that makes it vital that you select the right language for data analysis. Many of us work with SQL every day and take for granted many of its unique features, its power, flexibility and the richness of the analytics. This familiarity with SQL means that sometimes we are a bit slow at preventing some of our projects investing in other big data languages such as MapReduce, Impala, Spark, Java and many of the new generation of SQL-like open source projects. While many of these new languages are considered “ cool ”, it is very easy to end up building new proprietary data silos or investing in a language that eventually is replaced by another open source project or eventually fails to deliver the required analytics. One of the aims of this series of blo...