OUG Ireland BI Special Interest Group launched

Uli Bethke Oracle

At the OUG Ireland 2009 we launched the OUG Ireland Business Intelligence Special Interest Group. All OUG Ireland members are welcome to join. Tony Cassidy from Bearing Point and me will co-chair the SIG. We will hold our first meeting in June or July 09. I will give a presentation on Oracle Data Integrator and how it favourably compares to ...

Consuming a REST web service with Oracle

Uli Bethke Oracle, Web Services

SOAP web services dominate in a SOA environment. However, there is a new kid on the block: REST. This is a more lightweight approach to exchange data over HTTP, which is becoming more and more popular with web mashups and the web 2.0 in general. Amazon, Twitter, Yahoo all expose their APIs via REST. REST is an architecture style for ...

Date dimension script with Oracle SQL

Uli Bethke ETL, Oracle, SQL for Analysis

The script below will create a date dimension in just one SQL statement. We don't use performance-killer nonsense such as cursors, functions etc. We just pick a start date and the number of days we want to create and with a bit of magic of the Connect By clause and the NUMTODSINTERVAL function we do the whole thing in 14 ...

One pass SCD2 load: How to load a Slowly Changing Dimension Type 2 with one SQL Merge statement in Oracle

Uli Bethke DW Design, ETL, Oracle

The other day I came across a useful new feature in the Merge statement for SQL Server 2008. This new feature outputs merged rows for further processing, something which up until now (Oracle 11.1.0.7) is not possible in Oracle. This extra functionality can be used to load a Slowly Changing Dimension Type 2 in one SQL statement. As a result ...

Star transformation in snowflaked schema with compound keys

Uli Bethke Oracle

This article builds on Mark Rittman's recent post on explain plans for star transformation. Every now and again I have come across claims that the CBO only uses star transformation with single part foreign keys on the fact table: "In order for a RDBMS query optimizer to execute a query using a Star Transformation, a single part foreign key with ...