Peter Scott - Rittman Mead (Page 7)

Upgrading Warehouse Builder

I have mentioned here before that one of our customers has a BI system based on an Oracle 9.2 Data warehouse and uses OWB 9.2.0.8 for the majority (but not all) of the ETL processes. For various reason linked with vendor certifications the system was frozen

More on fraud and money laundering

Yesterday I mentioned a small analytic project we are doing for a retail customer around money laundering. Joel Gary commented [http://pjsrandom.wordpress.com/2007/02/23/end-of-week-catch-up/#comment-5381] and linked to an article in his local press about the rise of anti-fraud analytic companies in his neck of California.

Technical

End of week catch up

A somewhat slow week blogwise; loads of events that are not blog worthy, or just unusable for confidentiality reasons. Some may be written about later, but others will be locked away for ever. At long last one of my customers is going to upgrade the Oracle version of there multi-terabyte

Tales of woe: Query performance

The nuts and bolts of putting together a decent data warehouse are not that hard. You just need to get the I/O right ;-) OK, in reality there is a little more to it: you need to make sure you store the correct granularity of data, you have all

Technical

Materialized Views - 2

The other day I was asked [https://www.rittmanmead.com/blog/2007/02/07/working-with-materialized-view-summaries/#comment-5140] about the approach I took to refreshing materialized views. To be honest I use my own techniques - others [http://oraclesponge.wordpress.com/2006/04/12/a-quick-materialized-view-performance-note/] do similar things. In a data warehouse

Technical

Working with materialized view summaries

Materialized views (mviews) have been part of Oracle for along time, before Oracle 8 they went under the name snapshots and indeed some of the current dictionary tables refer to the old name. Common uses of mviews are to replicate data and to summarise data. It's this second

Summary table design

Most data warehouses used for reporting and built on conventional database technology such as SQL Server, Oracle and DB2 use some form of summarisation to boost query performance. In principle we doing once in the batch load and refresh process what would need to be done for each query at

Sending test cases to support

My heart sinks when someone in support asks us to send over the datafiles involved in some SR or other we have running with support. People seem to forget that there are some large systems out there. In our case our problem was with a 9 way join in a

Technical

Not such good ideas

One of our consultants dropped by my desk to ask me some Oracle questions around data life cycle management. One of his customers is moving a store based system to a central system and like a lot of systems in the UK needs to keep 7 years of transactional data

Just to reinforce the idea...

... that the hot topic in BI is "intelligence to the masses". Cognos have just acquired [http://www.cognos.com/news/releases/2007/0117.html] a dashboard and operational reporting vendor, Celequest [http://celequest.com]. Interestingly, Celequest also market a BI appliance product, a bit of a departure from

Technical

Database problems

One of our customers is on Oracle 9.2, we would dearly love to move them to 10g but for certification reasons with their chosen query tool we can't upgrade them until the tool is also upgraded which in turn is linked to the version of JVM associated

Generic problems

Drag and drop ETL tools sometimes encourage the use of a generic approach. Load a flat file to stage, validate the data, apply some transforms and publish to a data warehouse is after all standard fare within a data warehouse. But what when there is some subtle difference in the

Technical

Thoughts on extremely large databases and searching the unstructured

Nuno Souto posts an interesting set of thoughts on Extremely Large Databases [http://dbasrus.blogspot.com/2007/01/no-moore-part-2.html]. As usual, it is a well thought through post from someone who is probably scarred for life from actually working with large databases. In a data warehouse (or even a

Technical

Nothing is black or white on a foggy day

Sometimes people write to me or post questions on the blog along the lines of "I'm new to data warehouses, which xxxx is best?" where xxxx could be tool, database, hardware platform, design methodology, in fact, xxxx could be almost anything. Well, nothing is clear-cut, the

Technical

Index organized tables in data warehouses

Like a lot of newfangled stuff, IOTs are perhaps less commonly used in DW systems than they might be. In part this is an extension of the "I've always done it like this, so why change" attitude, and in part it identifying a suitable use where

More statistics

Following from yesterday and the comment about 5th January being the most popular day to seek new work. January is also a popular time to think of divorce. Just like NCR & Teradata [http://www.ncr.com/en/media_information/2007/jan/Jan%208%20NCR%20strategic%20announcement.pdf] ! opps -

Data Warehouse speed

I see that David Aldridge is buying a new laptop [http://oraclesponge.wordpress.com/2007/01/04/new-laptop-required-suggestions/], now that is a dilemma! Do you go for the biggest, fastest CPU, the most RAM, the best display or choose something more modest?. Of course it depends what you are going

Technical

Data quality for success

The final installment on the ingredients for successful BI is about data quality. For some strange reason, if users do not trust the quality of data in a BI system the system will fall into disrepute and be seen as a failure. Now I said "strange reason" and

Easy and complete access - part 3 of 4

Let's be honest here, the BI world is changing; it is no longer the exclusive domain of an elite group of data analysts (the sort of people where being an extrovert is to look at someone else's shoes whilst talking). BI is now (or should be)

Timely access - part 2 of 4

So what is timeliness? Again this a purposely vague term. I suppose that "knowing something when you need to know it" is timely; and the sooner you know something the more options you have to react to the information. But in reality this depends on the nature of

What stops a BI implementation from being a success?

Now that is a good interview question - the simplicity of the question hides a vast richness of answers; it can differentiate between the "been there, done it" and the "read a book"; it can tease out the roundedness of a candidate - are they reporting

Technical

Last time I mentioned in passing...

... our use of an index organized table in our ETL process and I promised to jot a quick note on it. So here goes. There are various combinations of database functionality that I do not like mixing in a data warehouse. For instance I impose rigorous RI checking in the

Slowing queries

The Oracle Sponge makes a timely comment [http://oraclesponge.wordpress.com/2006/12/12/the-importance-of-partition-key-statistics/] on statistics on partition keys. Having the right (and not just some!) statistics on a data warehouse table is often the factor that tips the balance between an efficient query and one that causes abject

Technical

Time for dates (again)

One of the themes I keep retuning to is the time dimension in Data Warehouses. I use the word "time" in the physics sense of something continuous and not the cyclical "clock" sense. Time (and in my sense, time could be measured in units far more

Technical

More on moving data

One of my pet hates is moving shed-loads of data from the disk to aggregate it away to resolve a user's query. Experience tells me that the very thought of doing this imposes a massive strain on a data warehouse. Ok, millions of people tell me that I