Data Warehousing & BI
Business Intelligence such as OLAP, BI Beans, Discoverer, Data Mining and Data Warehousing such as OWB
Larry yesterday did his keynote here at Oracle Open World introducing the "Oracle(R) Exadata Storage Server". This morning I attended a session about the technical details behind the server. In principal the server works with 8 nodes running RAC on Oracle Enterprise Linux 5. For the storage management ASM is used and since 18.104.22.168 smart scan has been introduced. Smart scan has been designed for very big data sources used in queries, like in Data Warehouses, where tables from tera bytes are common. In principle the following is accomplished using smart scan: (more…)
Most of the presentations for the sessions presented at Oracle Open World 2007, last week in San Francisco, can be downloaded from the Content Catalog at: http://www28.cplan.com/cc176/catalog.jsp. You will need to use the username/password combination cboracle/oraclec6 to download presentations. At http://www.oracle.com/openworld/2007/keynotes.html are all the keynotes, of which the one by Ed Abbo is probably the most interesting one.
My first formal activity at Oracle Open World 2007 was the Oracle Ace Director Product Briefing. A closed session where 30 odd ACE Directors (and you read that any way you like) had assembled to get some glimpses of what Oracle’s future has in store as well as some in depth insights into key products in the Fusion Middleware area as well as the database. The session was concluded by Mark Townsend (Oracle’s senior director of database product management), who delivered what will probably one of the best presentations during the week.
Mark ran us through some of the key features in RDBMS 11gR1 and in some areas laid down the road ahead to 11gR2. Not all of what I heard was new – we have been Beta testing 11gR1 since October 2006, so we have seen quite a bit of it – but some of the things became much clearer or useful than they had seem before.
One of the topics that caught my attention – which frankly it normally never does – was Storage. (more…)
Oracle 11g Total Recall – Flashback in the hands of Database Designers and Application Developers, at last. And: the end of Journalling Tables
One of the new features in the Oracle 11g database is called Oracle Total Recall. This is called marketing! I have read some weblogs where people – mostly DBAs – are surprised at all this attention for Oracle Total Recall, as it seems little more than the Flashback technology that since Oracle 9i has come to the foreground in Oracle 10g with Flashback Query, Flashback Table (even to before undrop) and Flashback Database. How much more does Oracle 11g give us in this area?
Well, in my opinion: in Oracle 11g, for the first time, Flashback has become a tool for Database Designers and Application Developers. And that is pretty important news.
Flashback- in a nutshell – is the ability (more…)
Row generation is a very useful technique for many (semi-)advanced SQL Queries. We have discussed various methods for row-generation in previous articles on our weblog. Examples are the CUBE operator, Table Functions and the Connect By Level < #number of records approach, apart from good old UNION ALL with multiple select from dual. These approaches vary in flexibility and compactness. CUBE and Connect By allow for easy generation of a large number of rows with little or complex control over the values in those rows, while the UNION ALL is elaborate and bulky, even though it gives great control over the exact values.
The Oracle 11g Unpivot operator presents us with a new way of generating rows with great control over the values in those rows and a more compact and elegant syntax than the UNION ALL alternative.
Let’s look at a simple example. (more…)
The PIVOT operation is one frequently discussed when talking about more advanced SQL Queries. Pivoting is the process of switching rows and columns, and is for example an advanced feature in Excel. Before Oracle 11g, there were several approaches to pivoting, that were not work-arounds for the lack of the PIVOT operator in the Oracle SQL Language.
With Oracle 11g, this has changed. Now we do have a PIVOT and an UNPIVOT operator at our disposal. Let's first take a look at UNPIVOT. In Oracle, the UNPIVOT operation is the process of turning Columns to Rows. Put simply, by applying the UNPIVOT operator to a number of columns, every row is split into that same number of rows. Each of these rows has two new columns: one for the column that this row stems from – one of the columns the data set was UNPIVOTed by – and one with the value from the column. The original UNPIVOT-columns are no longer part of the newly created records.
Let's take a look at a simple example, using the well known EMP table. We decide to consider the SAL and COMM columns as two different types of INCOME_COMPONENT. We would like to present our employees with an INCOME_COMPONENT column – and two rows for each More >