SQL for Data Warehousing and Analytics

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...