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

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

I will be presenting at a number of the Oracle Code events over the coming months on the subject of…..( drum roll please ) SQL pattern matching. Oracle Code is a great series of conferences dedicated to developers who want to get the absolute maximum benefit from using today's cutting edge technologies. If you want to register for any of the dates listed below then follow this link to the registration page . North and Latin America San Francisco ,  March 1, 2017 Austin ,  March 8, 2017 New York City ,  March 21, 2017 Washington DC ,  March 27, 2017 Toronto ,  April 18, 2017 Atlanta June 22, 2017 Sao Paulo , June 27, 2017 Mexico City ,  June 29, 2017 Europe and Middle East London , April 20, 2017 Berlin , April 24, 2017 Prague , April 28, 2017 Moscow , May 22, 2017 Brussels , June 6, 2017 Tel Aviv , July 11, 2017 Asia New Delhi , May ...

Recently I spotted a post on OTN that asked the question:  Can MATCH_RECOGNIZE skip out of partition? This requires a bit more detail because it raises all sorts of additional questions. Fortunately the post included more information which went something like this: after a match is found I would like match_recognize to stop searching - I want at most one match per partition. I don’t want to filter by MATCH_NUMBER() in an outer query - that is too wasteful (or, in some cases, I may know in advance that there is at most one match per partition, and I don’t want match_recognize to waste time searching for more matches which I know don't exist). Can MATCH_RECOGNIZE do this? Short answer is:  NO . Long answer is: Still NO . Going back to the original question… you could interpret it as asking “is it possible to only return the first match”? The answer to this question is YES, it is possible. There are a couple of different ways of doing it. Let’s use our good old “T...

Database 12c Release 2 available for download  Yes, it’s the moment the world has been waiting for: the latest generation of the world’s most popular database, Oracle Database 12c Release 2 (12.2) is now available everywhere - in the Cloud and on-premises . You can download this latest version from the database home page on OTN - click on the Downloads tab . So What’s New in 12.2 for Data Warehousing? This latest release provides some incredible new features for data warehouse and big data. If you attended last year’s OpenWorld event in San Francisco then you probably already know all about the new features that we have added to 12.2 - checkout my blog post from last year for a comprehensive review of #oow16: Blog:  The complete review of data warehousing and big data content from Oracle OpenWorld 2016  If you missed OpenWorld and if you are a data warehouse architect, developer or DBA then here are the main feature highlights 12.2 with links to additional content from OpenWor...

The growth of low-cost storage platforms has allowed many companies to actively seeking out new external data sets and combine them with internal historical data that goes back over a very long time frame. Therefore, as both the type of data and the volume of data continue to grow the challenge for many businesses is how to process this every expanding pool of data and at the same time, make timely decisions based on all the available data. (Image above courtesy of http://taneszkozok.hu/) In previous posts I have discussed whether  an approximate answer is just plain wrong  and whether  approximate answers really are the best way to analyze big data . As with the vast majority of data analysis at some point there is going to be a need to aggregate a data set to get a higher level view across various dimensions. When working with results from approximate queries, dealing with aggregations can get a little complicated because it is not possible to “reuse” a...