SQL for Data Warehousing and Analytics

Here's another great story about how to use data warehousing and big data technologies to solve real world problems using diverse sets of data using Oracle technology. BAE Systems is taking unstructured, semi-structured, operational and social media data and using it to solve complex problems such as financial crime, cyber security and digital transformation. The volumes of data that BAE deals with are very large and this creates its own set of challenges and problems in terms of optimising hardware and software to work efficiently and effectively together. Although BAE had their own in-house Hadoop experts they chose Oracle Big Data Appliance for their Hadoop cluster because it’s easier, cheaper, and faster to operate. BAE is working with many telco customers to explore the new areas that are being opened up by the use of big data to manage browsing data and call record data. These data sources are being transformed to provide additional insight for the network operations teams, ...

This post continues on from my first post on analytical SQL " introduction to SQL for reporting and analysis " which looked at the reasons why it makes sense to use analytical SQL in your data warehouse and operational projects.  In this post we are going to examine the key processing concepts behind analytical SQL.   One of the main advantages of Oracle's SQL analytics is that the key concepts are shared across all functions - in effect we have created a unified SQL framework for delivering analytics. These concepts build on existing SQL features to provide developers and business users with a framework that is both flexible and powerful in terms of its ability to support sophisticated calculations. There are four key concepts that you need to understand when implementing features and functions relating to SQL analytics: Process order Result-set Partitions Windows Current Row Let's look at each of these topics in more detail. 1) Processing order. The execut...

  Support Quote ”Oracle Exadata Database Machine is the best platform on which to run the Oracle Database and the X4 release extends that value proposition,” said Oracle President Mark Hurd. “As private database clouds grow in popularity, the strengths of Oracle Exadata around performance, availability and quality of service set it apart from all alternatives.” We have just announced the release of the fifth-generation of our flagship database machine: Oracle Exadata Database Machine X4 . This latest release introduces new hardware and software to accelerate performance, increase capacity, and improve efficiency and quality-of-service for enterprise data warehouse deployments. Performance of all data warehousing workloads is accelerated by new flash caching algorithms that focus on table and partition scan workloads that are common in Data Warehouses. Tables that are larger than flash are now automatically partially cached in flash and read concurrently from both flash and d...

If you are interested in spatial analytics or just wanting to understand what Oracle can offer in terms of bringing graph and spatial analytics to your projects then this is the conference for you:  Location Intelligence and Oracle Spatial Summit . It is taking place next year between May 19-21 in Washington, DC and as well as great presentations there will also be technical training and workshops. The conference is divided into three areas: May 19 - Technical Workshops May 20 - Location Intelligence, HERE Summit and LocationTech Summit May 21 - Oracle Spatial Summit)Location Intelligence, HERE Summit, and LocationTech Summit + Workshops This is, most definitely, the premier educational event for Oracle’s spatial and enterprise information technologies. The Oracle Spatial and Graph Summit will take place on May 19 and May 21 and it will offer workshops, technical deep dives and use case sessions led by the Oracle product management teams. Sessions will cover spatial, locati...

dunnhumby presented at this year's OpenWorld where they outlined the how and why of data warehousing on Exadata.  Our engineered system delivered a performance improvement of more than 24x . dunnhumby pushes its data warehouse platform really hard with more than 280 billion fact rows and 250 million dimension rows for one large retailer client alone, dunnhumby’s massive data requires the best performance the industry has to offer. In Oracle Exadata, dunnhumby has found that solution. Using Oracle Exadata’s advanced Smart Scan technology and robust Oracle Database features. This new environment has empowered its analysts to perform complex ad hoc queries across billions of fact rows and hundreds of millions of dimension rows in minutes or seconds, compared to hours or even days on other platforms.  You can download the presentation by Philip Moore - Exadata Datawarehouse Architect, Dunnhumby USA LLC -  from the OpenWorld site, see here:  https://oracleus.activeevents.com/2013/conn...

I have decided to start a series of blog posts on Oracle's in-database SQL analytics. The aim of this series of posts is to provide some historical background to show how SQL analytics has evolved and then start to explore some of the core concepts behind our SQL analytics followed by an examination of some of the key features and functions. The reason for creating this series of posts is that in talking to customers and partners it is clear to me that many project teams are just not aware of all the SQL features inside the Oracle Database that support enhanced reporting, data sampling, advanced aggregations, statistical analysis, pattern matching and spreadsheet-like data modelling. Today, data warehouse and operational development teams need to quickly deliver new and innovative applications that provide significant competitive advantage and drive additional revenue streams. The challenge facing many teams is to find the right platform and language to securely and efficiently m...

To follow-up on last week's post where I announced the new weekly data warehouse newsletter this week I have setup a Flipboard magazine for tablet/smartphone users. I will update the magazine each month with the very best articles from the various Oracle data warehouse blogs, press releases, YouTube videos, Facebook posts etc. In this first edition there is: a video interview with George Lumpkin, Vice President of Product Management for Data Warehousing and Big Data a customer video of Chinese e-commerce company Yihaodian a look at SQL mashups using pattern matching and spatial analytics a link to the latest big data whitepaper from the Economist and Oracle a review of the multi-tenant feature that is a key part of Database 12c a review of simple and advanced time series with Oracle R Enterprise by the R PM team …. and so much more. You can subscribe to this new monthly  magazine by following this link:  http://flip.it/nw8FU . I hope you enjoy this mo...

Thanks to Brendan Tierney for bringing this excellent online automated news service to my attention…. For a long time I have been wondering how to pull together all the articles from my favourite Twitter feeds, Facebook pages and blogs. Well thanks to Brendan I have discovered a service called  Paper.li . This weekend I spent some time setting up feeds from all my favourite sources related to data warehousing, big data. Exadata and other related Oracle technologies. The result is the " #Oracle DW-Big Data Weekly Roundup " which is designed to " keep you up to date on all the weekly sql analytics, data warehousing and big data news from # Oracle ". The newspaper is refreshed every Sunday night so that it is ready for Monday morning to read over breakfast. It is the perfect way to start the working week…. if you want to subscribe to this weekly newspaper then go here:  http://paper.li/OracleBigData/1384259272  and click on the red SUBSCRIBE link in the top ...

This is your chance to get some winter sun and learn about the new 12c SQL pattern matching! I have just received confirmation from BIWA Summit team that my presentation and hands-on lab on SQL pattern matching have been accepted for the 2014 conference (Jan 14-16 2014 at the Oracle Conference Center, Oracle HQ, Redwood Shores). If you are not familiar with BIWA then here is an overview: The Oracle Business Intelligence, Warehousing and Analytics Special Interest Group (BIWA SIG) is the leading worldwide association of persons interested in the successful deployment of Oracle Database-centric business intelligence, data warehousing, analytical products, EPM/Essbase and Big Data related features and options. BIWA now has almost 2000 members worldwide. BIWA is a not-for-profit entity affiliated with the Independent Oracle Users Group (IOUG). BIWA runs all sorts of activities: Webcasts - BIWA holds monthly webcasts presenting BI and DW experts speaking on BI, Data Warehousing and ...

It is not often you read about how the agricultural industry uses data warehousing so this article in latest edition of Oracle Magazine, with the related video from OOW 2013, on how Land O'Lakes is using Exadata caught my attention:   The Business of Growing , by Marta Bright  http://www.oracle.com/technetwork/issue-archive/2013/13-nov/o63lol-2034253.html A little background on Land O'Lakes: Land O’Lakes is a US company that has grown far beyond its roots as a small cooperative of dairy farmers with forward-thinking ideas about producing and packaging butter. It is a Fortune 500 company and is now the second-largest cooperative in the United States, with annual sales of more than US$14 billion. Over the years, Land O’Lakes has expanded its operations into a variety of subsidiaries, including WinField Solutions (WinField), which provides farmers with a wide variety of crop seeds and crop protection products. This implementation on our engineered systems highlights o...

If you are still wondering about how Exadata can revolutionise your business then I would recommend watching this great video which was recorded at this year's OpenWorld. First a little background...The Abu Dhabi National Oil Company for Distribution (ADNOC) is an integrated energy company that was founded in 1973. ADNOC Distribution markets and distributes petroleum products and services within the United Arab Emirates and internationally. As one of the largest and most innovative government-owned petroleum companies in the Arab Gulf, ADNOC Distribution is renowned and respected for the exceptional quality and reliability of its products and services. Its five corporate divisions include more than 200 filling stations (a number that is growing at 8% annually), more than 150 convenience stores, 10 vehicle inspection stations, as well as wholesale and retail sales of bulk fuel, gas, oil, diesel, and lubricants. ADNOC selected Oracle Exadata Database Machine after extensive resear...

Great information on yet another data warehouse deployment on Exadata. A little background on Swiss Re: In 2002, Swiss Re established a data warehouse for its client markets and products to gather reinsurance information across all organizational units into an integrated structure. The data warehouse provided the basis for reporting at the group level with drill-down capability to individual contracts, while facilitating application integration and data exchange by using common data standards. Initially focusing on property and casualty reinsurance information only, it now includes life and health reinsurance, insurance, and nonlife insurance information. Key highlights of the benefits that Swiss Re achieved by using Exadata: Reduced the time to feed the data warehouse and generate data marts by 58% Reduced average runtime by 24% for standard reports comfortably loading two data warehouse refreshes per day with incremental feeds Freed up technical experts by significantly mi...

One of the overlooked capabilities of SQL as an analysis engine, because we all just take it for granted, is that you can mix and match analytical features to create some amazing mash-ups. As we move into the exciting world of big data these mash-ups can really deliver those "wow, I never knew that" moments. While Java is an incredibly flexible and powerful framework for managing big data there are some significant challenges in using Java and MapReduce to drive your analysis to create these "wow" discoveries. One of these "wow" moments was demonstrated at this year's OpenWorld during Andy Mendelsohn's general keynote session. Here is the scenario - we are looking for fraudulent activities in our big data stream and in this case we identifying potentially fraudulent activities by looking for specific patterns. We using geospatial tagging of each transaction so we can create a real-time fraud-map for our business users. Where we start to move to...

I am currently working on a series of podcasts covering the broad categories of our SQL analytical functions and features and while I was doing some research I came across of series of four articles in the Oracle Magazine. This series of article is written by Melanie Caffrey who is a senior development manager at Oracle. She is a coauthor of Expert PL/SQL Practices for Oracle Developers and DBAs (Apress, 2011) and Expert Oracle Practices: Oracle Database Administration from the Oak Table (Apress, 2010). The four articles are under the banner "Technology: SQL 101" and parts 9, 10, 11 and 12 cover SQL analytics. Here are the links to the four articles: Jan 2013 Having Sums, Averages, and Other Grouped Data March 2013 A Window into the World of Analytic Functions May 2013 Leading Ranks and Lagging Percentages: Analytic Functions, Continued July 2013 Pivotal Access to Your Data: Analytic Functions, Concluded   The articles cover topics such as GROUP BY, SUM, AVG, H...

 Big Data has the power to change the way we work, live, and think. The datafication of everything will create unprecedented demand for data scientists, software developers and engineers who can derive value from unstructured data to transform the world. The Oracle Academy Big Data Resource Guide is a collection of articles, videos, and other resources organized to help you gain a deeper understanding of the exciting field of Big Data. To start your journey visit the Oracle Academy website here:  https://academy.oracle.com/oa-web-big-data.html . This landing pad will guide through the whole area of big data using the following structure: What is “Big Data” Engineered Systems Integration Database and Data Analytics Advanced Information Supplemental Information This is great resource packed with must-see videos and must-read whitepapers and blog posts by industry leaders.  Enjoy Technorati Tags: Big Data , Data Warehousing , Oracle , Training

For data warehousing projects Oracle Partitioning really is a must-have feature because it delivers so many important benefits such as: Dramatically improves query performance and speeds up database maintenance operations Lowers costs by enabling a tiered storage approach that allows data to be stored on the most cost-effective storage for better resource utilisation Combined with Oracle Advanced Compression, it provides an automated approach to information lifecycle management using a simple, efficient, yet powerful way to manage data growth and reduce complexity and costs To help you get the most from partitioning we have released a new tutorial that covers the 12c new features. Topics include how to: Use Interval Reference Partitioning Perform Cascading TRUNCATE and EXCHANGE Operations Move Partitions Online Maintain Multiple Partitions Maintain Global Indexes Asynchronously Use Partial Indexes For more information about this tutorial follow this link to the O...

If you missed my sessions at OpenWorld then don't worry - all the content we used for pattern matching (presentation and hands-on lab) is now available for download. My presentation " SQL: The Best Development Language for Big Data ?" is available for download from the OOW Content Catalog, see here: https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=9101 For the hands-on lab (" Pattern Matching at the Speed of Thought with Oracle Database 12c ") we used the Oracle-By-Example content. The OOW hands-on lab uses Oracle Database 12c Release 1 (12.1) and uses the MATCH_RECOGNIZE clause to perform some basic pattern matching examples in SQL. This lab is broken down into four main steps: Logically partition and order the data that is used in the MATCH_RECOGNIZE clause with its PARTITION BY and ORDER BY clauses. Define patterns of rows to seek using the PATTERN clause of the MATCH_RECOGNIZE clause. These patterns use regular expression...

You've visited our new web page on OTN , you've downloaded the pattern matching  iBook , now register for the webcast! If you are interested in learning about our exciting new 12c SQL pattern matching feature then mark your diaries. On Wednesday, October 30th at 8:00 am (US/Pacific time zone)  Supriya Ananth , who is one of our top curriculum developers at Oracle, will be hosting an OLL webcast on our new SQL pattern matching feature. The ability to recognize patterns in a sequence of rows has been a capability that was widely desired, but not possible with SQL until now. Row pattern matching in native SQL improves application and development productivity and query efficiency for row-sequence analysis. With Oracle Database 12c you can use the new MATCH_RECOGNIZE clause to perform pattern matching in SQL to do the following: Logically partition and order the data using the PARTITION BY and ORDER BY clauses Use regular expressions syntax to define patterns of ...

This year's OpenWorld had major focus on data warehousing and big data. To help you find and download the most important presentations from the conference I have put together a new guide -   OpenWorld 2013 in a box. This new guide is a follow-up to the Must-See guides that were published ahead of the conference. All the sessions listed in those conference guides are included in this new  OpenWorld 2013 in a box guide .  This new booklet is divided into the following sections Overview - links to main keynotes Database 12c - George Lumpkin, VP of Product Management talks about the exciting new features in Oracle Database 12c Must-Download presentations - presentations delivered by the product management and development teams along with key customer presentations. Links - everything you need to keep up to date on Oracle’s strategy and products for data warehousing and big data. The list of must-download presentations is broken down into the following categories: General -...

Interest in big data is growing rapidly and big data was a very hot topic at this year's OpenWorld conference. In response to this Oracle Education has released a new 3-day training course for big data to help customers and partners get hands-on time to develop the skills to acquire, organize, analyse big data so they can make key decisions based on these new data streams . The course lasts 3 days and is offered as a classroom session and/or using or virtual training environment. In this course you will learn to: Define Big Data. Understand Oracle's Integrated Big Data Solution. Describe the Oracle Big Data Components. Define Cloudera's distribution of Hadoop and its components. Acquire raw data using Oracle NoSQL Database and HDFS. Organize the collected data using Hive and Oracle Big Data Connectors. Analyze big data using the Oracle Analytics tool. Derive business decisions using Oracle BI tools. Study the case-scenarios where Oracle Big Data is applied succes...

NEW KEYNOTE: Transforming Businesses with Big Data and Analytics Monday Sept 23 @ 8:00am, Moscone North - Hall D There has been an exciting addition to the agenda for this year's OpenWorld. On the Monday morning (Sept 23), Mark Hurd is using his keynote to talk about how businesses can transform themselves using big data and analytics. Given all the hype around these two hope topics this keynote will look at the promise of big data and how it enables your business to leverage a range of information to provide new services, operate more efficiently, and market more effectively. This keynote will include key Oracle customers and other Oracle experts who will explain how to harness the value of big data; craft an IT strategy to stream, acquire, organize and analyze to make transformational decisions about business operations and products and services. To make sure you do not miss this important session I have updated the Data Warehousing and Big Data Guide to includ...

One thing I have noticed when looking at many bigs data projects is that a lot of time is spent collecting data but then the next step, analyzing that data, can quickly become overly complicated.  Working with many big data sources and analyzing this data is just too hard. SQL, on the other hand, is the most commonly used language for data analysis. So how can we combine these two? This session discusses Oracle’s analytical SQL capabilities and how complex analytical queries running on large to extremely large data sets are becoming a reality, even on datasources outside the relational world. I will be delivering a presentation at OpenWorld on using SQL to simplify and enrich the analysis of big data. It is on Tuesday (September 24) at 10:30am in Salon 7 of the Marriot Marquis Hotel - this hotel is a short walk from the Moscone Center. Sign up now ( there is limited space available! ) to attend this session and learn how Oracle sees the future of a unified analytical...

Just a quick reminder that I will be running a hands-on lab for the new Database 12c pattern matching feature at this year's OOW. This is a great opportunity to learn more about this exciting new feature and ask any questions you might have about how to use pattern matching in your projects. Both myself and our expert developers will be in the room to help you and answer your questions. The lab is divided into 5 sections: Part 1 - Introduction to SQL Pattern Matching  Part 2 - Searching for more complex patterns Part 3 - Understanding how SQL Pattern Matching works Part 4 - How to use built-in measures Part 5 - Advanced Topics  I will have all the content available on a USB stick so you can take the schema, data and workshop away with you at the end of the lab. Click through to the OOW content catalog to register for this lab:  https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=10068 . The number of seats available for this lab is strict...

This is great new whitepaper written in collaboration with The Economist. In the report Oracle's big data strategist, Paul Sonderegger, looks at how big data is becoming part of everything from New York City’s inspectional services to Intel’s silicon manufacturing, from public policy implications to visual design innovations, and more. The paper will help you understand how many companies and using big data to remake themselves and their industries, and using analysis of data-based decision processes to tell compelling stories through data visualization and predictions. Big data is future and it will have a major impact on society. Click on the link now and find out how to find value in Big Data http://www.oracle.com/us/technologies/big-data/finding-value-in-big-data-1991047.pdf Technorati Tags: Analytics , Big Data , Data Warehousing , Oracle

I have converted my last whitepaper on in-database analytics to the multi-media iBook format so it can be viewed on Apple's iPad and iPad mini. This great new iBook contains a lot more content including how-to videos and presentation slide-shows for each product. This is a must-have multi-media guide to Oracle's data warehousing analytics:   Chapter 1: Oracle’s Approach to Analytics Chapter 2: Oracle's Rich Framework for Analytics Overview of analytics and extensibility framework SQL Data Mining Spatial Analytics Network Analytics Multidimensional Analytics Unstructured Analytics Extensibility Framework Chapter 3: Realizing the benefits of in- database analytics Chapter 4: Conclusion Chapter 5: Where to get more information This iBook is packed full of information that help you get the most Oracle's in-database analytics. You can download the iBook from my Google Drive folder  here . As I am still experimenting with delivering content via the iBook f...

Our BI team has added SQL pattern matching to their demo VM environment which is used by the sales teams and Oracle partners to demonstrate the capabilities of our BI system. There is a short demo on YouTube:  http://www.youtube.com/watch?v=rgwAgckaiTU  - "SampleApp V309 What's New Overview" which shows a SQL pattern matching dashboard in OBI EE. If you jump to point 1:50 this is where the pattern matching content starts and it runs for about 15-20seconds - obviously there is a lot of great content so do please watch the complete video . The data set in the demo is the publicly available flight information data which is commonly used with the R language demos. Technorati Tags: Big Data , Data Warehousing , Database 12c , Oracle , Oracle Database 12c , Pattern Matching , SQL Analytics