One of the advantages of Data Vault are the easy load patterns that can be used to load hubs, links and satellites. They are well documented in several books about Data Vault. But what happens if you want to load multiple versions in one batch? In this blog post, I will write about multi-version loads into a satellite table.
Category Archives: ETL
About Data Warehouse Design Patterns and Lazy Programmers
I’m a lazy programmer! That was one important detail I learned from Roelant Vos in his training last week. But there were many other interesting topics. A personal summary of a 3-days class about Data Warehouse Design Patterns.
Materialized View Refresh for Dummies
Materialized Views are often used in data warehouses to improve query performance on aggregated data. But what if it takes too long to refresh the materialized views? Here are some basic rules to improve refresh performance.
10 Tips to Improve ETL Performance – Revised for ADWC
The Autonomous Data Warehouse Cloud (ADWC) is a self-configuring, fast, secure and scalable platform for data warehouses. Does this mean we don’t have to take care anymore about performance of our ETL processes? Which performance tips are still important for us, and where can we hand over the responsibility to ADWC? A revised version of an old blog post, with regard to Oracle’s Data Warehouse Cloud solution.
Gathering Statistics in the Autonomous Data Warehouse Cloud
Optimizer statistics are essential for good execution plans and fast performance of the SQL queries. Of course, this is also the case in the Autonomous Data Warehouse Cloud. But the handling of gathering statistics is slightly different from what we know from other Oracle databases.
External Tables in Autonomous Data Warehouse Cloud
In Oracle Autonomous Data Warehouse Cloud, External Tables can be used to read files from the cloud-based Object Storage. But take care to do it the official way, otherwise you will see a surprise, but no data.
Using Temporary Tables for Complex Reports
Global temporary tables (or private temporary tables, if you use Oracle 18c) are a practical feature to be used for SQL queries with multiple steps, for example complex reports in a data warehouse. If you want to use them, some details must be considered.
Direct-Path INSERT and NOLOGGING with Oracle 12.2
Direct-Path INSERT is a very efficient way to load data into data warehouse tables. The load performance can even be improved with NOLOGGING on the target table. In Oracle 12c Release 2, this is even possible for Oracle Data Guard environments. But is it a good idea?
10 Tips to Improve ETL Performance
In summer time, the nights are very short. For some Data Warehouses, this is the case all year round, but not because of late sunset and early sunrise. The night is not long enough to finish all the ETL jobs. Long-running load jobs that run for several hours are not a seldom situation. Here some tips how this can be avoided.
Partition Exchange in Oracle 12.2
There are many new extensions for Partitioning in Oracle 12c Release 2 (12.2). One of them is the CREATE TABLE FOR EXCHANGE statement. Some years ago, I wished to have this feature for a particular problem with hidden columns.