Rittman Mead Blog - Data Warehousing

Tagged

Data Warehousing

A collection of 205 posts

Data Warehousing

Rewrite problems

One of the Oracle 9.2 data warehouses my team manages uses nested materialized views for the aggregation strategy. Nesting of aggregates can reduce the workload on the batch as ‘expensive’ roll-ups only need happen once. The downside to nesting is that in order to make query re-write work for

Data Warehousing

More slow cases

Yesterday [https://www.rittmanmead.com/blog/2006/02/16/what%e2%80%99s-that-about-then/] I had a small rant about the use of case statements in where clauses. In a reply to this parkerpg suggested this was perhaps generated by the use of query environment such as Cognos. In this case

Data Warehousing

What’s that about then?

At the risk of Tom Kyte telling me that I have given up thinking… Any ideas why this appeared as part of a WHERE predicate (or more to the point why write it!) > … AND ( case when T.V_STATUS = 'A' then 'Active' when T.V_

Data Warehousing

Real-life parallels

If you read AskTom you will probably know that there is no such thing as a universal go-faster switch in Oracle. Everything is “maybe faster, maybe slower and your mileage may vary”. Over the past week or so we have been user-testing a revised parallelism setting on a 3 TB

Data Warehousing

Collecting statistics

For a while we have used DBMS_STATS.GATHER_SCHEMA_STATS with the gather stale option to collect statistics on one of our Oracle 9.2 data warehouses. It was perhaps a little slower than our old approach but it did the job and was also code not written (and

Data Warehousing

Going for OLAP

To be honest, I have never implemented a data warehouse using Oracle’s OLAP option. True, I used to dabble with Oracle Sales Analyzer and Express but that was a couple of years ago and the technology has moved on. In those days we used the ROLAP model to map

Data Warehousing

Over the past few days life has been full of little distractions.

Recently we implemented some changes on a customer’s DW to improve query performance. As usual, all changes were fully tested before implementation on the production system, but because of the nature of the changes and the fact that it is difficult to adequately emulate user workload we also adopted

Data Warehousing

Average for a week

In most data warehouses there is some form of aggregation going on, sometimes in the presentation of database summary tables, sometimes in the query tool. Often this aggregation is additive over time: how many times did this IP address connect to that web page in a month, what is the

Data Warehousing

Accidents waiting to happen

A while back I blogged [https://www.rittmanmead.com/blog/2005/11/07/design-matters/] about a data warehouse review we are doing for a customer. At the time I was less than impressed by the consistency of the approach to DW design and the lack of knowledge of how Oracle

Data Warehousing

Customers do odd things

You may have read in a previous Blog that I am a great believer in the use of constraints (not null, primary key and, of course, foreign key) in a data warehouse. In an Oracle data warehouse these constraints inform the query optimiser about relationships between dimension and fact data.

Data Warehousing

Pre-built materialized views

In a recent Oraclesponge posting [http://oraclesponge.blogspot.com/2005/12/ora-12034-materialized-view-log.html] David Aldridge writes about a way to get around the “ORA-12034: materialized view log younger than last refresh” error. Of course his method relies on you knowing what it is about the MV that needs to be

Data Warehousing

Modelling from JDeveloper

In a few idle moments I downloaded the CWD4ALL [http://www.cwd4all.com/cwd4all4jdeveloper.cfm] extension to JDeveloper – I guess it is the developer in me that wants to give modelling tools a trial and as the vendor web page mentioned words and phrases such as OLAP, multi-dimensional modelling and

Data Warehousing

Design matters

One of our customers has asked us to review the performance of one of their legacy data warehouses. The daily data load takes far too long. There seems to be a lot we can suggest here to improve things - for a start, we need move to an incremental load

Data Warehousing

We know where you live

No, not a threat from a bunch of gangsters, but a reminder about a, perhaps, under used Oracle feature in data warehousing A little while back Mark Rittman reminded me of a piece he wrote on GIS enabling a data warehouse. [http://www.dbazine.com/datawarehouse/dw-articles/rittman6] People may

Data Warehousing

Materialized view usage

For one of my DW customers we adopted a materialized view-based aggregation strategy. Only the base level summaries are exposed to the query tool and the Oracle database uses query rewrite to select the 'best fit' aggregation. This was the customers first use of query rewrite, their previous

Data Warehousing

Warehouse design rant

An anonymous poster to Tom Kyte’s [http://tkyte.blogspot.com/2005/09/digging-stuff.html]Blog shared a tale of woe about data warehouse performance. Well, I felt for that poster – how can a developer put their hand on their heart (or wallet, if they are an external consultant) and

Data Warehousing

Know who your customers are - part 4

In follow-ups to recent [https://www.rittmanmead.com/blog/2005/08/31/know-who-your-customers-are-%e2%80%93-part-1/] posts [https://www.rittmanmead.com/blog/2005/09/05/know-who-your-customers-are-%e2%80%93-part-2/] some people raised a point about using basket analysis in “reverse” to segment customers. The legend that young men buy beer

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

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

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

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