SQL for Data Warehousing and Analytics

Those of you with long memories might just be able to recall a whole series of posts I did on SQL pattern matching which were taken from a deep dive presentation that I prepared for the BIWA User Group Conference . The title of each blog post started with SQL Pattern Matching Deep Dive... and covered a set of 6 posts: Part 1 - Overview Part 2 - Using MATCH_NUMBER() and CLASSIFIER() Part 3 - Greedy vs. reluctant quantifiers Part 4 - Empty matches and unmatched rows? Part 5 - SKIP TO where exactly? Part 6 - State machines There are a lot of related posts derived from that core set of 6 posts along with other presentations and code samples. One of the challenges, even when searching via Google, was tracking down all the relevant content. Therefore, I have spent the last 6-9 months converting all my deep dive content into a book and I have added a lot of new content based discussions I have had at user conferences, questions posted on the developer SQL forum, discussions with my...

Those of you with long memories might just be able to recall a whole series of posts I did on SQL pattern matching which were taken from a deep dive presentation that I prepared for the BIWA User Group Conference . The title of each blog post started with SQL Pattern Matching Deep Dive... and covered a set of 6 posts: Part 1 - Overview Part 2 - Using MATCH_NUMBER() and CLASSIFIER() Part 3 - Greedy vs. reluctant quantifiers   Part 4 - Empty matches and unmatched rows? Part 5 - SKIP TO where exactly? Part 6 - State machines There are a lot of related posts derived from that core set of 6 posts along with other presentations and code samples. One of the challenges, even when searching via Google, was tracking down all the relevant content. Therefore, I have spent the last 6-9 months converting all my deep dive content into a book - an Apple iBook. I have added a lot of new content based discussions I have had at user conferences, questions posted on the developer ...

Did you miss OpenWorld 2017? Then my latest book is definitely something you will want to download! If you went to OpenWorld this book is also for you because it covers all the most important big data warehousing messages and sessions during the five days of OpenWorld. Following on from OpenWorld 2017 I have put together a comprehensive review of all the big data warehousing content from OpenWorld 2017. This includes all the key sessions and announcements from this year's Oracle OpenWorld conference. This review contains the following information: Welcome - an overview of the contents. Let’s Go Autonomous - containing all you need to know about Oracle’s new, fully-managed Autonomous Data Warehouse Cloud . This was the biggest announcement at OpenWorld so this chapter contains videos, presentations and podcasts to get you up to speed on this completely new data warehouse cloud service. Keynotes - Relive OpenWorld 2017 by watching the most important highlights f...

Day 4 is here which makes today #Autonomous Wednesday. Included my definitive list of Must-See sessions for today are two of THE most important sessions at this years conference. You will not want to miss these two sessions: The rest of list is, of course, packed full of sessions and labs covering our Big Data Warehouse technologies and features. These sessions are what Oracle OpenWorld is all about: the chance to learn about the latest technology from the real technical experts. WEDNESDAY'S MUST-SEE GUIDE Don't worry if you are not able to join us in San Francisco for this year's conference because I will be providing a comprehensive review after the conference closes on Thursday. The review will include links to download the presentations for each of my Must-See sessions and links to any hands-on lab content as well. Have a great conference. If you are here in San Francisco then enjoy the conference - it's going to be an awesome conference this ...

Day 3, Tuesday, is here and this is my definitive list of Must-See sessions for today. Today we are focused on the new features in Oracle Database 18c - multitenant, in-memory, Oracle Text, machine learning, Big Data SQL etc etc. These sessions are what Oracle OpenWorld is all about: the chance to learn about the latest technology from the real technical experts. TUESDAY'S MUST-SEE GUIDE Don't worry if you are not able to join us in San Francisco for this year's conference because I will be providing a comprehensive review after the conference closes on Thursday. The review will include links to download the presentations for each of my Must-See sessions and links to any hands-on lab content as well. Have a great conference. If you are here in San Francisco then enjoy the conference - it's going to be an awesome conference this year. Don't forget to make use of our Big DW #oow17 smartphone app which you can access by pointing your phone at thi...

Day 2, Monday, is here and this is my definitive list of Must-See sessions for today. The list is packed full of sessions and labs that follow on from yesterday's (Sunday) big announcements around Oracle Autonomous Database and Oracle Autonomous Data Warehouse Cloud. These sessions are what Oracle OpenWorld is all about: the chance to learn about the latest technology from the real technical experts. MONDAY's MUST-SEE GUIDE Don't worry if you are not able to join us in San Francisco for this year's conference because I will be providing a comprehensive review after the conference closes on Thursday. The review will include links to download the presentations for each of my Must-See sessions and links to any hands-on lab content as well. Have a great conference. If you are here in San Francisco then enjoy the conference - it's going to be an awesome conference this year. Don't forget to make use of our Big DW #oow17 smartphone app which you can...

It all starts today -   OpenWorld 2017. Each day I will provide you with a list of must-see sessions and hands-on labs. This is going to be one of the most exciting OpenWorlds ever! Today is Day 1 so here here is my definitive list of Must-See sessions for the opening day. The list is packed full of really excellent speakers such as Franck Pachot ,  Ami Aharonovich ,  Galo Balda and  Rich Niemiec . These sessions are what Oracle OpenWorld is all about: the chance to learn from the real technical experts. Of course you need to end your first day in Moscone North Hall D for Larry Ellison's welcome keynote - it's going to be a  great one! SUNDAY'S MUST-SEE GUIDE Don't worry if you are not able to join us in San Francisco for this year's conference because I will be providing a comprehensive review after the conference closes on Thursday. The review will include links to download the presentations for each of my Must-See sessions and links to any ha...

It’s only 4 days and counting until OpenWorld 2017 starts. If you are coming to this year’s conference then you will definitely want to use our completely free #oow17 online BDW app for smartphones and tablets which is now live :  https://keithlaker.github.io/Storyboard.html#LandingPad . The app includes a day-by-day calendar of all the most important sessions covered in the comprehensive Big Data Warehousing Must-See Guide, see here:  https://oracle-big-data.blogspot.co.uk/2017/08/updated-big-data-warehousing-must-see.html . The day-by-day coverage breaks sessions down into 5 categories: Data Warehousing and Cloud Analytics and Machine Learning Unstructured and development Big Data Hands-on labs each session is color coded to the above topics making it easier to focus on the areas that are most important to you. Please send my any feedback. Hope the app is useful and enjoy OpenWorld 2017: Your Data Warehouse Transformation Starts Here. Landing page wh...

** NEW ** Chapter 5 *** UPDATED *** Must-See Guide now available as PDF and via Apple iBooks Store This updated version now contains details of all the most important hands-on labs AND a day-by-day calendar . This means that our comprehensive guide now covers absolutely everything you need to know about this year’s Oracle OpenWorld conference. Now, when you arrive at Moscone Conference Center you are ready to get the absolute most out of this amazing conference. The updated, and still completely  free,  big data warehousing Must-See guide for OpenWorld 2017 is now available for download from the Apple iBooks Store -  click here ,  and in PDF format -  click here . Just so you know…this guide contains the following information: Page 8 - On-Demand Videos Page 11 - Justify Your trip Page 18 - Key Presenters Page 39 - Must See Sessions Page 83 - Must See Day-by-Day Page 150 - Useful Maps Chapter 1  - Introduction ...

DW and Big Data - OpenWorld 2017 a YOUR DATA WAREHOUSE TRANSFORMATION STARTS TODAY 456 October 1–5, 2017 San Francisco, CA #OOW17 Continue... 114 240

MATCH_RECOGNIZE and predicates At a recent user conference I had a question about when and how  predicates are applied when using MATCH_RECOGNIZE so that’s the purpose of this blog post. Will this post cover everything you will ever need to know for this topic? Probably! Where to start….the first thing to remember is that the table listed in the FROM clause of your SELECT statement acts as the input into the MATCH_RECOGNIZE pattern matching process and this raises the question about how and where are predicates actually applied. I briefly touched on this topic in part 1 of my deep dive series on MATCH_RECOGNIZE: SQL Pattern Matching Deep Dive - Part 1 . In that first post I looked at the position of predicates within the explain plan and their impact on sorting. In this post I am going to use the built in measures (MATCH_NUMBER and CLASSIFIER) to show the impact of applying predicates to the results that are returned. First, if you need a quick refresher course in...

  ….and now it’s here in PDF format as well!   The free big data warehousing Must-See guide for OpenWorld 2017 is now available for download in PDF format - click here , and yes it’s completely free . This comprehensive guide covers everything you need to know about this year’s Oracle OpenWorld conference so that when you arrive at Moscone Conference Center you are ready to get the most out of this amazing conference. The guide contains the following information: Page 8 - On-Demand Videos Page 17 - Justify Your trip Page 19 - Key Presenters Page 41 - Must See Sessions Page 90 - Useful Maps Chapter 1  - Introduction to the must-see guide. Chapter 2  - A guide to the key the highlights from last year’s conference so you can relive the experience or see what you missed. Catch the most important highlights from last year's OpenWorld conference with our on demand video service which covers all the major keynote sessions. Sit back and enjoy the highlights. The...

  It’s here - at last! I have just pushed my usual must-see guide to the Apple iBooks Store.   The free big data warehousing Must-See guide for OpenWorld 2017 is now available for download from the Apple iBooks Store - click here , and yes it’s completely free . This comprehensive guide covers everything you need to know about this year’s Oracle OpenWorld conference so that when you arrive at Moscone Conference Center you are ready to get the most out of this amazing conference. The guide contains the following information: Page 8 - On-Demand Videos Page 17 - Justify Your trip Page 19 - Key Presenters Page 41 - Must See Sessions Page 90 - Useful Maps Chapter 1  - Introduction to the must-see guide. Chapter 2  - A guide to the key the highlights from last year’s conference so you can relive the experience or see what you missed. Catch the most important highlights from last year's OpenWorld conference with our on demand video service which covers all the ...

In my last posts I provided an overview of the Apache Zeppelin open source project which is a new style of application called a “ notebook ”. These notebook applications typically runs within your browser so as an end user there is no desktop software to download and install. Interestingly, I had a very quick response to this article asking about how to setup a connection within Zeppelin to an Oracle Database. Therefore, in this post I am going to look at how you can install the Zeppelin server and create a connection to your Oracle data warehouse. This aim of this post is to walk you through the following topics: Installing Zeppelin Configuring Zeppelin What is an interpreter Finding and installing the Oracle JDBC drivers Setting up a connection to an Oracle PDB Firstly a quick warning! There are a couple of different versions of Zeppelin available for download. At the moment I am primarily using version 0.6.2 which works really well. Currently, for some reason I am ...