How Not to Build A(n Autonomous) Data Warehouse

My day job involves investigating and resolving performance problems, so I get to see a lot of bad stuff.  Often, these problems have their roots in poor design.  It is not surprising. but is nonetheless disappointing, that when I point this out I am told that the system is either delivered this way by the vendor, or it has already been built and it is too late to change.
In the last couple of years, I have worked on several data warehouse applications that have provided the inspiration for a new presentation that I am giving at the DOAG and UKOUG conferences this year.
The presentation and this series of related blogs have several objectives:

• Partly, it is an attempt to get some advice out in the hope that some of these poor design decisions are not made in the first place. 
• I get to have a bit of a rant about how to design a data warehouse properly, and I will feel better at the end it.
• I get to attend two really good conferences where I will learn (or be reminded of) much.

This particular blog post is an index to various topics within the presentation, that I have turned into separate blog postings.

Oracle Sales History (SH) Sample Schema

I cannot illustrate issues with actual examples from client systems, no matter how well anonymised they are. So, I have recreated some of the most egregious mistakes using the Oracle Sales History example schema, SH.  Everything I am going to show you does have an origin in the real world!  The SH sample schema is a simple and very typical star schema.  It is well designed and implemented, and represents good practice. I encourage you to install and play with it for yourself.

My examples use a single fact table (SALES), that has five dimensions around it (CUSTOMERS, CHANNELS, PRODUCTS, PROMOTIONS and TIMES), although I will only use three. COUNTRIES is a dimension on the CUSTOMERS dimension, sometimes called a 'snowflake'.

• The schema described in the Oracle documentation.
• Installation scripts are available from Github.
• It is also available by default in the Autonomous Data Warehouse Cloud but in a slightly different form.

In some of my examples, I have deliberately broken the data model in exactly the same way that I have seen it broken in real life. So, if you find yourself saying "nobody would actually do that!", let me assure you that they did!

Common Mistakes in Data Warehouse Design and Build

As each blog post is published, I will add a link to them in this section.

1. Lack of Foreign Keys
2. Effective Dated Dimensions
3. Dates that are not Dates

Other Topics

• To Index or Not: Star Transformation -v- Full Scan-Bloom Filter
• Snowflakes and Lost Skew
• Engineered System Considerations
• A Brief Look Inside Oracle's Autonomous Data Warehouse Cloud