Pete Scott’s random notes

For various reasons I have gained a reputation in data warehousing – whether it is from my older blog posts, speaking at conferences, or my role in a high profile BI consultancy, I am not sure.

One outcome of this is that I get asked to take part in various activities to promote new products; PR companies write (perhaps under the mistaken impression that I am a real journalist) to invite me to interview some CEO or CTO of a software company to discuss a new product; I also get invited to try new software, either at launch or as part of a beta program.

Recently, a software company (NOT Oracle, hence I write here on my personal blog, and not the company one) invited me to try their beta release BI database. There are a lot of innovative features but one really caught more eye as a one of boosting performance without breaking the hardware budget: the UNGROUP BY clause.

Readers of my postings on my employers blog, or attendees of my DW design courses will know that I am strong advocate of the principle that IO throughput is the biggest constraint on DW performance. You need the right capacity of disk, the right number of IO channels working at fast enough speeds (fibre channel, InfiniBand etc.), some vendors move some processing close to the disk so that smaller (skinny?) result sets are passed over the wire to the CPUs. Other database vendor-specific techniques centre on the use of segment compression and partition elimination to reduce the number of bytes brought from disk to answer the query. But for all database vendors the traditional way of reducing IO is to fetch back “summarised” data that answers the user’s query. But until now there was no way back from the aggregated data to detail. BUT, for this database vendor’s product. Is no longer the case.

Suppose we have a table of total sales by year for our international company and we need to get the yearly results for each country – simple we just execute:
SELECT country, sum(sales_value) from GLOBAL_SALES UNGROUP BY country
need those sales broken down by quarter as well then:
SELECT country, quarter, sum(sales_value) from GLOBAL_SALES UNGROUP BY country, quarter ORDER BY country, quarter.
“This looks magic, can we use it for any query?” Well there are some restrictions; the column names used must match the key column names defined in dimension objects (the next release will also support unique attribute matching), there must be a column in the table to be ungrouped for the parent key – that is, we we need an ALL_GEOGRAPHY column in table, we can’t rely on the fact that the column is not there to assume the total level. There is also a practical limitation on the number of levels you can drill to using this query – two levels. So ALL_GEOGRAPHY can drill to region and country, but not to state or city.
Under the covers we use a novel kind of composite “index” to assign a “proportionality” to the dimensional components, this is the really clever part as the index uses bitmap semantics to provide a compressed array of proportions.
Early days in testing this feature but I am impressed.

Late breaking news – Just been told that this is an April Fools posting and should not be used as part of a functional specification

I am very conscious that I have not posted much on this blog recently. In part the technical stuff has been surfaced through the company blog – I get in to trouble if I say Mark’s blog because it is the company one now, an in part because of a load of domestic events have stifled my available creative time.

Still, on-site with a customer in Greece has given me an unexpected amount of online-in-the-hotel-in-the-evening time which I can fill with research or just writing this stuff.

I had always wanted to visit Greece but as I am not a sand-n-sea person it would not be for the islands and beaches, but more the ancient history. So working here and having a weekend in Athens (and not one where I am scheduled to fly to London or San Francisco – which is the case for all the other weekends before Christmas) gave me the ideal chance to see the Acropolis and other historic sites nearby. Put simply, I was stunned by it all. The scale of things, the age of things… the sophistication of the art and the science. It makes you wonder why or how Western Civilisation did nothing from before the fall of Rome until, well, 160 years ago.

I think I can understand the concept of the Grand Tour now!

Last Sunday was the end of the daylight saving scheme that goes in these parts by the name of British Summer Time, today 2 days past the end of summertime it snows!

I don’t recall snow so far south in October. Oh well it gives me something to write about!

Seemed to have spent a lot of August travelling. A short-term engagement in a Nordic country and the family vacation let me put on more than my fair share of air miles. The final flight (I hope!) of the month was yesterday when I took my 17-year old to Aberdeen to visit the university. It seems so tiring to get the 7:00 AM flight and then spend most of the day walking around before the 8 PM flight home. Still it was a great day out and nice to get back to Scotland. My Daughter loved the University, so who knows, Aberdeen may become a regular destination for me!

Not written much for a while – or so a dear friend tells me in an email

I have been busy with some consultancy for a non-BI customer and then several days learning to deliver a training course that a colleague produced; well at least build my virtual machines for the exercises and making sure that I can get the examples to work. I also learned how to shrink a VMWare Fusion virtual machine when there is not enough free diskspace on the host to use the shrink tool!

So, what goes on at Scott Towers? The elder girl is looking at colleges for 2009 and like all teenagers wants to get as far as possible from home – in the UK that is not that far, but she has ambitions – OK, she has now decided against going to Canada to study on cost grounds, but her potential choices in Cornwall, Scotland and North Wales are all a long drive from here. Be glad when the last open day is done!!!

Before she goes to university there is that final year of school. She has started to ask me for help checking her work for chemistry (I knew that degree of mine would be useful one day) and asking for help on her database project for IT… think though she is asking the wrong person for help on normalisation (I said that to prevent others – Doug – from doing so!)

It’s a holiday weekend in England and that means our local shopping centre is hosting yet another collector’s fair. Unlike the others there will not be a Dalek, Wookie, Blake’s-7 cast member in sight, and probably none of those autograph hunters that actually look like Vogons, Klingons or extras from the Lord of the Rings. Instead, it is a collection of geriatric retired sportsmen (but probably still fitter than me!)

I guess my colleague, Mr R, will be impressed that some of Spurs UEFA Cup winning team will be there (was 1963 really the last time Spurs won anything ) it impresses me too as Jimmy Greaves’ aunt used to live a couple of houses from me and Terry Dyson used to teach sport at my old school – maybe I’ll go and look them up… maybe not.

I am currently in Paris getting ready to participate in a training course – makes a change to be trained, usually I am the one doing the training.

For me, getting to Paris is simple, I walk to my local station (less than ten minutes), catch a train to London (about 30 minutes) then the short walk to the Eurostar terminal and catch the Paris train. Less than two and a half hours later I would be walking in Parisian streets on my way to the hotel. The same journey by air would be a lot more convoluted with driving, parking and airport shuttle buses added into the mix. And if I go with a budget carrier no food – unlike my ticket today which gave me a meal and wine on the journey and way too much coffee.

So why do I need a new passport, well it’s got a couple of years to go before it expires, it is machine readable so I can get in the USA on a visa waver but the significant thing is the photograph – today the French border police took one look and smirked…

If only we were in Schengen zone then I would not have such hassle!