Data Warehousing & BI
Business Intelligence such as OLAP, BI Beans, Discoverer, Data Mining and Data Warehousing such as OWB
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 >
One of the interesting new pieces of functionality in Oracle 11g is offered by the DBMS_COMPARISON package. This package allows us to compare two tables – or two views on tables – and inform us on the differences in data. More specifically: the package can summarize if there any changes between the data sets and if so how many. It can also inform us in detail about the specific rows that are different between the two. Finally, DBMS_COMPARISON can converge the data sets, taking one of the two as the master and fully aligning the other one. Using DBMS_COMPARISON, we get on-demand replication for ordinary tables, within schemas, across schemas or even across databases.
In this article, I will demonstrate – on the 126.96.36.199 (Beta) release for Windows – how I can compare table EMP with a clone, whose data has been modified. We will then see how we can re-align the clone with the original (master) table. In this example, I work within a single schema. However, everything I do can be done across schemas and even across a database link, catering for custom data replication scenarios.
Here is our challenge: (more…)
Last week I made my first steps with XML-Publisher 5.6 Desktop and I am very pleased with the simplicity you can create a report in such a short time. When you follow these instructions you can see it yourself. After you have installed the XML-Publisher 5.6 Desktop you open Microsoft Word and you are ready to create the report.
You can start with the header of the report and place for example a logo in the header. In the left corner you see a XML-Publisher menu.
First you have to (more…)