Peter Scott - Rittman Mead (Page 12)

Know who your customers are – part 3

So you managed to go through all those hoops and have a way to tie (at least some) transactions to identifiable individuals. But can you be sure that each customer on your customer table is different person? Cleaning of customer data is notoriously difficult. True duplicates should not happen if

Data Warehousing

Know who your customers are – part 2

Thanks to the readers that pointed out that some stores ask for postal code or zip code at the point of sale. Postal codes can help refine customer identification to those in living in a specific locality (or even as in the UK a cluster of houses on a street)

Data Warehousing

Know who your customers are – part 1

Consider a supermarket data warehouse – you know what was sold, where it was sold and when it was sold, we can even identify what else the customer bought in the same basket. But do we really know who the customer was? Identifying retail customers at point of sale has always

Data Warehousing

Validation of dimensions

If you have an Oracle data warehouse and are using materialized views for aggregations then Oracle dimension objects would have almost certainly been defined. These objects describe the hierarchical, attribute and join relationships for dimensions and together with database constraints enable the query rewrite mechanism to successfully rewrite queries that

New Book

I see that Digital Press is about to publish a book on Oracle Data Warehouse Tuning for 10g [http://books.elsevier.com/us/digitalpress/us/subindex.asp?isbn=1555583350&country=United+States&community=digitalpress&ref=&] . I hope to pick up a copy of Gavin Powell'

Data Warehousing

Partition Pruning works!

In a recent post, David Aldridge [http://oraclesponge.blogspot.com/2005/08/execution-plans-for-partition-not.html] discussed an approach for efficiently rebuilding a table so that partition key migrated from one column to another. This is a somewhat specialised operation and one that most people need not consider (well not unless you

DW migration

Our sales people have asked us to look at migrating a small (less than 3TB) DW system from another vendor’s database to Oracle. The DW database migration is conceptually not too difficult – we keep the user layer logically the same as now so they do not have to make

Data Warehousing

Wrong results

One of our users raised a support call with my team – a query returned the wrong results. My guys check it out and capture the explain plan… yes wrong results, and because the lower bound of a BETWEEN prediacte was being omitted. So a TAR is raised with support – the

Data Warehousing

Partition joins

A few weeks ago (OK, in June; time flys when you are old!) I posted a piece of time series aggregation [https://www.rittmanmead.com/blog/2005/06/21/time-series/] One technique I did not mention (and nobody reminded me of it ;-) ) was “partition joins”. This was a new

Tuning

I have a love hate relationship with query tuning. Part of me enjoys the challenge, the solving of a puzzle, but on the other hand there is that naked fear of failing to meet a customer’s expectations. I prefer the type of problem where I am told the business

Disasters

We are currently putting the final stages of a DW migration for one of our customers to bed – they have a small DW (just over half a terabyte) on old and unsupportable equipment – it was good stuff when it is was new, but now… Like all conscientious developers we insist

Data Warehousing

Research

I’ve been asked by one of our sales guys to give a presentation of data warehousing strategy to a potential customer... talk about open ended. I suspect they want to know if what they already do is still sound, if they could do it better, and whether their current

Data Warehousing

Data sources

“The data source: Traditionally, this is one (or quite possibly more) remote system(s)” I wrote in a recent Blog and followed that up with a promise to expand on that statement later Firstly, note the emphasis on the word “remote”; I am a strong believer that in order to

A data warehouse recipe book - recipe 1, the basic mixture

Ingredients: 1 data source 1 database to store the data warehouse 1 reporting layer (optional) Method: Add data source to database, mix well. Decorate with optional reporting layer. Sounds simple, and not a Kimball or Inmon in sight! But as with all great recipes it’s the quality of the

Time series

Of all dimensions, time is perhaps the most common. In fact I would be hard pressed to think of a data warehouse that did not encompass time. Inventory, sales, phone calls, web click-throughs, energy usage or whatever, they all hold measures (quantities) along a time line. So you’d think

Surrogate keys

June 17 - I was not too happy about the way this post was written - I will be revising parts of this - I will use italics to highlight changes. I must admit my view on surrogate keys in a data warehouse is far from the orthodox. For me

Data Warehousing

Added dimensions

Some dimensions in a data warehouse are easy to understand… for a retail DW, stock might be composed of three dimensions: when, where and what; sales on the other hand, might also have a customer dimension (if customers are known, say, in a loyalty scheme or by delivery address) It

Partition Exchange Dataloading

Revision history - minor change to clarify one note 2-jun-05 At the end of 2003 I took on a project to redesign a multi-terabyte DW for one of our customers - changes to their data feeds required the replacement of the majority of interfaces. I also took the opportunity to

It’s a mater of integrity

In a data warehouse there are typically two ‘chunks’ of data – the public stuff (such as reference or dimensional data, raw fact data, and the aggregated fact data.) The other chunk is the ‘backroom’ layer that is used to build the public side of the warehouse. For me, the backroom

Starting out in DW

A little while back I posted a piece on how I got into databases in general and data warehouses in particular. One reader asked if I could reveal how to start out in data warehousing. Rather than posting a direct reply to the question, I thought that I would write

Data Warehousing

Query rewrite (again)

One of our customers uses Oracle materialized views and query rewrite as the key component of their summarisation strategy. The front-end query tool is Business Objects (or rather its web version) The great thing about rewrite is that the BO universe designer only needed to include the base level summaries

Well I am feeling a bit smug

Rang the user who created last week's query from hell... he still needed to find that mystery transaction. It turns out the police were investigating a credit card fraud and knew the amount but no other details (strange they did not know the date (even to the nearest

Poor queries

One of the things I do on the DWHs I manage is to periodically review the nature of queries being run so that we can come up with performance improvement plans. When a data warehouse is put in the designer (me) guesses the aggregates and indexes that will be needed

A good end to the week

Working for an IT services company I tend to get quite a bit of variety in the work that passes my way. Although (for me) it is always data warehouses, the works ranges from completely new systems, through data warehouse migrations, enhancements, to steady-state maintenance and performance tuning. This week

Data warehouses, a multi-dimensional being (2)

Keen eyed readers would have observed that the original two “part ones” of this article merged and slightly downsized, as part of this process the name ‘1b’ was deleted from history. This posting is dedicated to the words and memory of that fallen ‘b-ing’ Attributes and Hierarchies Attributes [http://dictionary.