Google Analytics

Friday, 20 November 2015

Review of Data Warehousing and Big Data at #oow15

“From the keynotes to the connections to the entertainment, there truly is something for everyone at Oracle OpenWorld” 


DW BigData Review Of OOW15
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 can be used to increase the performance and analytical capability of your data warehouse and big data management system - checkout the on-demand videos for the keynotes and executive interviews.

If you want to revisit the most important sessions, or if you simply missed this year’s conference and want to catch up on all the most important topics, then I have put together a comprehensive review of this year’s conference which is divided into the following sections:

  • Overview
  • Key Feature Areas
    • Oracle Database 12c for Data Warehousing
    • Big Data
    • Cloud
    • Analytics and SQL
  • Hands-on Labs
  • OpenWorld 2016
Each of these sections includes all the most important sessions and links to product pages and all the hottest social media sites. If you are using the iBook version some of movies have been preloaded and all the presentations have been preloaded into the iBook - if you are using the PDF version then you will have to follow the links to view the videos and to download the slides for each presentation. You can download my review in both Apple iBook by clicking here and PDF format by clicking here. Hope this proves useful and if I missed anything then let me know.

Please note that downloadable presentations for some of the sessions are not available because they included content about features that will be part of the next release of Oracle Database. For these sessions I have provided links to the relevant product pages on OTN where you will find information about all the latest greatest features of Database 12c.

Finally, if you missed this year’s conference then I guarantee that will not want to miss #oow2016 because it is going to be even bigger and even more exciting than #oow15. The last section of the review booklet provides 10 reasons why you will want to be at #oow2016. Overall it's a great read

Technorati Tags: , , , , , , , , ,

Monday, 12 October 2015

OpenWorld 2015 on your smartphone and tablet - UPDATED!

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
  • Must attend labs - list of all the most important data warehouse hands-on labs at this year’s conference
  • Links - a list of links to the most important data warehouse sites
  • Live Twitter - twitter feeds from our product management teams
  • Maps - some useful maps for the local area to help you get around the various conference locations, grab a coffee, get something to eat 
OCT 12 - UPDATED: Added new sessions and incorporated some scheduled changes to existing sessions as well.
To run this web app on your smartphone/tablet follow this link:
Android users: Last year when I tested this app on my Samsung S3 there was a bug in the way the Chrome browser displayed frames because scrolling did not work . This year I have swapped to an LG3 android phone and everything appears to be working correctly. However, if the app does not work as expected on your phone then try using either the Android version of the Opera browser.
If you have any comments about the app (content you would like to see) then please let me know. Enjoy OpenWorld.

Technorati Tags: , ,

Thursday, 8 October 2015

Android App for Data Warehousing and Big Data at #oow15


Android App for OOW15


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.apk. Please note that your phone may require you to allow installation of apps from sources other than Google Play Store. The app does not require access to any other app, emails, logs, contacts etc on your phone.

My plan for next year I will have the app posted on both the Google Play Store and the Apple App Store. Therefore, it would be really helpful to get some feedback please on this app - is it a good idea to have a dedicated app, what additional content would you like to have, etc etc.

Hope this helps you get organised for this year’s incredible conference. Enjoy OpenWorld. If you get a chance then please stop and say “Hi” at our demo booth: SQL Analytics and Analytic Views, the booth id is SLD-046. 


Technorati Tags: , , , ,

Tuesday, 6 October 2015

Online Calendar for Data Warehousing and Big Data Sessions at #oow15 now available

Cw22 lg oow 2227098


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:




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 Hotel which is at 222 Mason St, map here, (includes directions from Moscone Center). You should allow 12-15 mins to walk from Moscone Center to the Nikko Hotel. 

Unfortunately, your calendar is unlikely to have all the glorious colors that you see in the picture although I am not sure why that information is not encoded into the HTML page view. The colors do serve a purpose because they identify the type of session:

  • RED - keynote session
  • LIGHT RED - data warehouse/big data general session
  • YELLOW -  big data session
  • GREEN - analytics session
  • LIGHT BLUE - unstructured data
  • BLUE - hands-on lab

Hope this helps you get organised for this year’s incredible conference. Any comments then let me know and if I missed your data warehouse/big data session then let me know and I will add it to the calendar.

Enjoy OpenWorld. If you get a chance then please stop and say “Hi” at our demo booth: SQL Analytics and Analytic Views, the booth id is SLD-046. 


Technorati Tags: , , , ,

Friday, 7 August 2015

Must-See Session Guide for Data Warehousing at #oow15


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 format will work on any smartphone, tablet and/or computer.

Please use the following links to download your preferred version of the must-see guide:

If you have any questions about these guides then please feel free to contact me at


Technorati Tags: , , , , , ,

Wednesday, 15 April 2015

Managing overflows in LISTAGG

IMG 0196 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 effectively create chunks of strings that do not exceed the VARCHAR2 limit.

If you are using an earlier version of the Oracle Database then we can make use of the powerful SQL Model clause that was introduced in Database 10g.

In fact the beauty of the workarounds outlined in this blog post is that you can define your own maximum size for the string returned by LISTAGG so you have complete control over the size/length of the result set that is returned.

For example, let’s assume that we have the following statement (to keep things relatively simple let’s use the EMP table in the schema SCOTT)
LISTAGG(ename, ';') WITHIN GROUP (ORDER BY empno) AS namelist

FROM emp
GROUP BY deptno;
this statement will generate the following output:

---------- ----------------------------------------
 Let’s assume that the above statement does not run and that we have a limit of 15 characters that can be returned by each row in our LISTAGG function. We can use the Database 12c SQL pattern matching function, MATCH_RECOGNIZE, to return a list of values that does not exceed 15 characters. First step is to wrap the processing in a view so that we can then get data from this view to feed our LISTAGG function. Here is the view that contains the MATCH_RECOGNIZE clause:

   PARTITION BY deptno
   ORDER BY empno
   MEASURES match_number() AS mho
   DEFINE B AS LENGTHB(S.ename) + SUM(LENGTHB(B.ename) + LENGTHB(‘;’)) < = 15
You might well ask: why don’t we put the LISTAGG function inside the measure clause? At the moment it is not possible to include analytical functions such as LISTAGG in the measure clause. Therefore, we have put the LISTAGG function in a separate SQL statement:
 LISTAGG(ename, ';') WITHIN GROUP (ORDER BY empno) AS namelist
FROM emp_mr
GROUP BY deptno, mno;
The above code will produce the following output:

---------- ----------------------------------------
10         CLARK;KING
10         MILLER
20         SMITH;JONES
20         SCOTT;ADAMS
20         FORD
30         ALLEN;WARD
30         MARTIN;BLAKE
30         TURNER;JAMES
Using 12c MATCH_RECOGNIZE, we are able to get all the values by sending just one query. Now not everyone who is hitting this ORA-01489 error is running 12c, so what do you do if you are running an earlier version of the database and need an efficient fix for this error? Prior to 12.1, the overflow can be handled using SQL Model clause. This approach is a little more complicated. First we create a view that returns the row number:
  row_number() OVER (PARTITION BY deptno ORDER BY empno) as rn
FROM emp;
the above statement generates the following output with the row number added for each employee within each department. Thinking about this in Excel terms, we will use this view to feed our SQL Model clause, therefore, the view will allow us to create a “worksheet” for each department and then list the employees for each department as a row on that “worksheet”:
===== ====== ========= ===== ========= ==== ==== ======= ==
 7782 CLARK  MANAGER    7839 09-JUN-81 2450      10      1

 7839 KING   PRESIDENT       17-NOV-81 5000      10      2
 7934 MILLER CLERK      7782 23-JAN-82 1300      10      3
 7369 SMITH  CLERK      7902 17-DEC-80 800       20      1
 7566 JONES  MANAGER    7839 02-APR-81 2975      20      2
 7788 SCOTT  ANALYST    7566 19-APR-87 3000      20      3
 7876 ADAMS  CLERK      7788 23-MAY-87 1100      20      4
 7902 FORD   ANALYST    7566 03-DEC-81 3000      20      5
 7499 ALLEN  SALESMAN   7698 20-FEB-81 1600  300 30      1
 7521 WARD   SALESMAN   7698 22-FEB-81 1250  500 30      2
 7654 MARTIN SALESMAN   7698 28-SEP-81 1250 1400 30      3
 7698 BLAKE  MANAGER    7839 01-MAY-81 2850      30      4
 7844 TURNER SALESMAN   7698 08-SEP-81 1500    0 30      5
 7900 JAMES  CLERK     7698 03-DEC-81 950        30      6

Now the next step is to calculate the running total of the length of the text string of employee names within each department. The SQL Model clause allows us to calculate the running total and reset the total if it exceeds the number of characters that we have set as the limit - in this case 15 characters. This code is wrapped in the first of the views that we are going to build:

FROM emp_sqm_view
   PARTITION BY (deptno)
   MEASURES (empno, ename, LENGTHB(ename) len, 0 cum_len)
   RULES (
    cum_len[1] = LEN[1] + LENGTH(';'),
    cum_len[rn > 1] = CASE WHEN cum_len[cv()-1] + LEN[cv()] + LENGTHB(‘;’) <= 15
                      THEN cum_len[cv()-1] + LEN[cv()] + LENGTHB(';')
                      ELSE LEN[cv()] + LENGTH(';')

Next we need an id that we can use to group together the list of names that fit within our boundary of 15 characters. Again we will use the SQL Model clause to compute the break points and increment the counter within each department:
FROM emp_model_1
   MEASURES (empno, ename, len, cum_len, 0 sub_id)
   RULES (
     sub_id[1] = 1,
     sub_id[rn > 1] = CASE WHEN cum_len[cv()] = len[cv()] + LENGTHB(‘;’)
                           THEN sub_id[cv() - 1] + 1
                           ELSE sub_id[cv() - 1]

 now we have the following output:
====== == ===== ====== === ======= ======
    10  2  7839 KING     4      11      1
    10  1  7782 CLARK    5       6      1
    10  3  7934 MILLER   6       7      2
    20  1  7369 SMITH    5       6      1
    20  5  7902 FORD     4       5      3
    20  4  7876 ADAMS    5      12      2
    20  3  7788 SCOTT    5       6      2
    20  2  7566 JONES    5      12      1
    30  1  7499 ALLEN    5       6      1
    30  2  7521 WARD     4      11      1
    30  3  7654 MARTIN   6       7      2
    30  6  7900 JAMES    5      13      3
    30  5  7844 TURNER   6       7      3
    30  4  7698 BLAKE    5      13      2

we can now use the column headed sub_id along with the department number to create GROUP BY clause which will finally generate our list of employees within each department where each row in the column namelist will contain a maximum of 15 characters:

LISTAGG(ename, ';') WITHIN GROUP (ORDER BY empno) AS namelist
FROM emp_model_2
group by deptno, sub_id;
The above query creates the following output which matches the final output we achieved using the MATCH_RECOGNIZE clause:

---------- ----------------------------------------

If you are using the LISTAGG function and sometimes getting an ORA-01489: result of string concatenation is too long  error then hopefully the above workarounds are useful.

The code samples shown above were created by Lei Sheng who is a Principal Member of Technical Staff, Database Development. Lei is worked on many of the really cool analytical SQL features including the SQL Model clause and the new MATCH_RECOGNIZE clause. So big thanks to Lei for sharing these workarounds.

Technorati Tags: , , , , , ,

Friday, 27 March 2015

Open World 2015 call for papers - my simple guidelines


OOW Banner 2013


Most of you will already have received an email from the OpenWorld team announcing the call for papers for this year’s conference: 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 is an excellent section towards the end of the call-for-papers web page, "Tips and Guidelines"

2) Address the theme of the conference - If this is available when the call the for papers is announced then try to address the theme of the conference within your abstract.

3) Address the key data warehouse technical focus areas - for this year’s conference the key focus areas for data warehousing will be: big data information management architectures, the logical data warehouse, partitioning, analytical SQL, pattern matching, parallel execution, workload management, multitenant, in-memory and Big Data SQL. If possible try to include one or more of these focus areas within your abstract.

4) Have a strong biography - You need to use your biography to differentiate and build credibility. This is an important topic because it allows you to differentiate yourself from all the other presenters who are trying to get speaking slots. Your biography must explain why you are an authority on the topic you have chosen for your presentation and why people will want to listen to what you have to say.

5) Have a strong business case - build your presentation around a strong business case, relevant to your industry and/or your target audience (DBAs, developers, architects etc). Try to explain in clear and simple terms the problem you needed to solve, how you solved it using Oracle technology and the direct technical/business benefits.

6) Make the title and abstract interesting - Your title and abstract must be easy to read and make sure you introduce your main idea as early as possible. Review the titles and abstracts from previous conferences as a guide. Ideally make the issue relevant to the delegates attending OWW, get to the point, and make sure it is easy to read.

7) Look at previous presentations - the content catalog for last year’s conference is available online,see here. You can review all the titles and abstracts that were accepted and use them as guidelines for creating your own title and abstract for this year's conference.

8) Write clear outcomes - The majority of the best presentations have clearly stated outcomes. What do you expect that conference attendees will be able do or know at the end of your session? Consider including a sentence at the end of your abstract such as the following: “At the end of this presentation, participants will be able to . . . .”

9) Don’t submit your paper right away - Once you have a title and abstract show it to a few colleagues. Get some feedback. You probably know many people who’d be happy to give you ideas on making your proposal better.

10) Keep number of submissions low - You do not increase your chances of getting a paper accepted by submitting lots of different sessions.


I have collected all the above and posted it in a handy pocket-size PDF format so you can read all these great tips offline. The PDF is available for download from here: Call-For-Papers Guidelines.pdf

Obviously I cannot guarantee you success if you follow these guideline but I hope they prove helpful. Good luck with your submission(s) and I look forward to seeing at you at this year’s OpenWorld conference in the beautiful city of San Francisco. 



Technorati Tags: , ,