BI Blogs

It is official now - SQL Server 2005 is finally released. I am still trying to comprehend it, so far I am overwhelmed with all kinds of feelings. I kind of started to beleive that it is going to happen only at the beginning of September, when we pretty much stopped doing almost anything about Yukon and switched to SP1 work. Now our product begins its own life, outside of our labs. It is a little bit scary, but I do have full confidence, that it is going to succeed, and succeed in a big way….(read more)

Excellent speaker…very funny.

Key points:

Optimiser goal - Give the CBO a chance by telling it what kind of environment you have - ALL_ROWS or FIRST_ROWS_n

Gather system stats - Gives CPU, Multi Block Read Count and IO figures to help CBO work out cost of a query - or estimated time for completion.

Use DBMS_STATS to gather stats - not ANALYZE - 10g automatically gathers these stats via a job created when the database is created. (you can still use ANALYZE for validating indexstructures and listing chained rows - just not stats stuff).

AVG_DATA_BLOCKS_PER_KEY on DBA_INDEXES is a human readable restatement of the CLUSTERING_FACTOR column - CBO uses the latter in calculating cost but easier to read the former. Higher values for both indicate higher cost…and hence why a given index may, correctly, not be chosen by CBO.

CBO counts physical IO’s when determining cost - it assumes all IO’s are physical which is not always true of course.

There is a scaling of the DB_MULTIBLOCK_READ_COUNT value - when you ask for 8, for example it will use a value of 6.588. This number is fixed - there is a table of fixed values per different value of DMRC.

OICA Threat - don’t change OPTIMISER_INDEX_COST_ADJ to low values to persuade CBO to use indexes instead of full table scans - it makes all index access cheaper and may result in choosing a poor/inefficient index.

More interesting stuff in the paper if you’re interested.

Excellent - obviously. Yes Tom, I did find it interesting and I did learn something.

3 key points…

Schema is important - sometimes it’s not the SQL that needs tuning - look at the schema design - Tom gave an example of using an IOT to colocate data and improve performance of a specific simple query.

RTFM - features change - advice has a shelflife - Tom used the example of array processing being used automatically and transparently so that rewriting queries prior to 10g such that they use array processing is no longer necessary in 10g onwards.

Scalar subqueries - way cool feature for removing outer joins on fast return queries. Using SS to reduce query effort by PL/SQL function cacheing.

Ok - gotta do 4 - an honorable mention you might say…

Partitioned Outer Joins - nice new 10g feature but not in itself groundbreaking. The example Tom demonstrated was, however, very cool - given to him by mikito harohiri - a data generator which uses dual to get however many rows you want with - literally - no IO! Look out for the presentation - I couldn’t type quick enough to get the SQL…

Actually there were more highlights but I guess you figured as much!

Not heard Tom present before - thoroughly enjoyable and humorous to boot!

Fusion seems to be the big thing - the strategy going forward for dealing with the Peoplesoft,
J D Edwards, Retek and Oracle Apps products and their customers.

Protect - each product has loyal and happy customers due to inherent strengths - it would be foolish to jeopardise this.

Extend - enhancements to the individual products and their integration with each other, e.g. Retek to financials bridge.

Evolve - doing things better - building sustainable competitive advantage by blended business insight and process automation throught adaptive processes.

A kind of reassuring session for the various product customers that Larry raided the piggy bank for this year!

As you already know, the DTS migration wizard of SSIS 2005 doesn’t migrate Data Transformation Task in a Data Flow component, but instead it uses to encapsulate each Transformation Task into a legacy embedded DTS2000 package.

Now, you can imagine my surprise when I tried to migrate a DTS this morning and I’ve got a Data Flow task into a SSIS package. But wait, it’s not so beautiful as we could hope!

This is the starting situation: I had a simple DTS generated by Import/Export Wizard of SQL Server 2000. It copies two tables from Northwind (Customers and Categories) into another database. One of those transformations has been migrated to a Data Flow task, and another to an inner DTS package execution. It deserves to investigate!

The task migrated to a data flow has only “direct row transformations”, while the other has a “ActiveX Script Transformation”, even if this transformation is a simple “copy column” script (it’s the default of SQL2000 Import wizard if you choose the script transformation). Probably I changed the default transformation on one of those task at the time I generated the task (it was a demo I realized while teaching a DTS course some months ago).

My test at this point has been to change the script transformation into a direct one: both Transformation Task has been correctly migrated to a data flow task (one for each original task)!

At this point I’m really excited: why on the earth this wizard still doesn’t migrate some more complex package full of simple Transformation Task which simple map columns directly between source (got with a SQL query) and destination? To make the story short, here are my results (works means successfully data flow migraton, doesn’t work mean a DTS inner package migration):

• Use of Exception file: partially works (package is migrated successfully, but you lose log settings - BTW I can live with this due to new log provider architecture)
• Use of Table Lock: works
• Use of Batch Size (in SQL Server fast load mode): works
• Use of Max Errors Count: doesn’t work (I have to reset my Max Errors Count to 0 and to handle errors modifying “Error Output” behavior of data flow)
• Use of UDL file connections: doesn’t work (I can write a script that consolidates my connections in a DTS package removing UDL reference in DTS object model)

Until today I never found a similar schema of what are Data Transformation Task supported features to allow a Data Flow migration instead than a legacy Inner Package execution. I’d like to have an official one by MS.

Birmingham has been suffering powercuts this morning and the conference centre has been affected so registration is running late and the keynote isn’t going to start for at least another 20 minutes!

Perhaps this is an example of the powercuts the utility companies are warning of in the near future in the UK ?

Met fellow blogger, Pete Scott this morning and had a coffee whilst we’ve been waiting - he only recognized me cos I was the wally wearing the name badge! He’s a nice chap.

…well, I guess it could have started yesterday with the oak table event but I couldn’t make it.

Anyway, i’ve done my usual trick of building in way too much time contingency in my travel schedule so i’m sitting in a pretty empty Birmingham International Convention Centre with time to kill…

My plan as far as blogging goes is to post something after each session via my Orange SPV M1000 PDA and a GPRS connectivity - but we’ll have to see if I get time for it. A friend of mine who I met at university was of the opinion that you could potentially boil a presentation down to 3 key points - he’s a sharp cookie so I might try and follow that approach in my blog posts over the next few days.

Now, where’s the café…I need some caffeine!

It’s Monday morning at about ten to seven, and for the first time in three
years at the UKOUG, I’ve actually had a good night’s sleep. My plan to go out on
Sunday night and get trashed has obviously worked, so now it’s just a chance to
catch up with emails and update the website before getting some breakfast and
taking a look at the stand.

Yesterday was the Oak Table day, and it was surprising just how many people
would be prepared to be at the ICC for 9.30 for a full day’s worth of full-on
technical presentations. I went to the two Jonathan Lewis ones in the morning -
the second of which, on PGA memory management, was particularly relevant for
myself given some of the work we’ve being doing on very large ETL batch
processes - but for me the best one of the day was by Graham Wood, on the
evolution of time-based tuning from Oracle V2.3 through to 10g.

I’ve never been to a Graham Wood talk before and he’s the guy behind much of
the new diagnostics stuff (ADDM, AWR and so on) in Oracle 10g. The talk took us
through the various Oracle versions since 2.3 - with additional info and
corrections from Mogens and Anjo at the back - and how the approach to tuning
has changed over time as more instrumentation has been made available. I know
for some people this might seem a bit of a lightweight topic but for me it was
pretty interesting actually, kind of what I came to the event for, a bit of
history mixed in with some technical stuff. Very worthwhile.

Following on from the Oak Table presentations
Jon Mead
and I popped into town for an hour and then it was down to All Bar One for the
SIG Chairs and volunteers’ free bar. As I mentioned before, this fitted in with
my plans quite well as I thought the only way I could get some sleep last night
was to have a few drinks and stop worrying about getting some sleep (if you know
what I mean) so in the end I spent a very good few hours with Jon, Doug Burns,
Andy Johnston (SIG Deputy), Andrew Clarke, Chris Dunscombe, Tim Dexter (the XML
Publisher product manager) and, later on in the evening, a few guys (and
Charlie) from the company.

That’s it for now. I’m chairing Sten Vesterli’s PL/SQL talk at about two-ish
this afternoon, other than that I’ll be at a couple of talks and then on the
stand for the rest of the day. I’ll post again tomorrow after we’ve had the
bloggers’ dinner.

See also : Doug Burns on UKOUG Day 0

Just a brief update after I’ve arrived up in Birmingham for the
UKOUG Conference. I came up by train
this time rather than drive, as the drive up from Brighton is on the one hand
either really tedious - around the M25 then an hour up the M40 - or a nightmare
- a hair-raising final couple of miles as you come off the motorway in
Birmingham and then try to negotiate the one-way systems. I’ve come up a day
early and therefore actually got to eat in the hotel - the Hyatt, next door to
the ICC, very nice if I say so myself - which I’ve never done before, as we
always go out during the conference nights to the local restaurants.

Anyway, you’ll be glad to know that I actually slept last night - sort of,
after a fashion - and I’ve just come back from Breakfast (spotted Gary Goodman
and some people from Miracle coming in, although you can’t say anything as
that’s getting into "stalker" territory) and in half an hour it’s the Oaktable
day which starts around 9.30.

Aside from the conference itself, I couldn’t help noticing all the references
to "Oracle Express" cropping up on Orablogs, which made me sit up a bit as of
course Express is the OLAP server that Oracle bought from IRI and which then
became the basis of the OLAP Option. Headlines such as

"Oracle Express - EDB Killer" and comments such as

this got me thinking for a minute that the world has finally caught on to
the benefits of multidimensional analysis, but what it is of course that
everyone’s referring to is the new

"Express Edition" of Oracle Database 10g that’s been launched as an answer
to Microsoft’s SQL
Server Express Edition.

Whilst I won’t go into the why’s and wherefores of the logic behind the
release - except to point out now that all our little home-grown web application
projects - like Orablogs, I guess - will now be able to use Oracle as the
backend database rather than having to base them on mySQL - it’s pretty obvious
that "Oracle Express" is going to be the shorthand way of referring to this new
version. I’ve already had several confusing conversations with people that think
that Analytic Workspace Manager is actually

Oracle Workspace Manager - the tool that you use to version tables, rather
than the OLAP admin tool - so in all I guess this is going to get pretty
confusing. Now when people look at my CV, they’ll think I’ve had five years
experience with the freeware version of Oracle rather than their standalone OLAP
server, so here goes my campaign - It’s called Oracle Database 10g Express
Edition, not Oracle Express, as that name’s already taken! Take your cut down
starter database and don’t darken our multidimensional doorstep again…

Right, that’s it for now, off to register and then for the first round of
presentations.

Well, I had intended to post something technical this week, but work and other activities intervened. I managed to get some free time to work on a book synopsis - maybe more about that later - but other that it was a week of solid work.
One of the work things I am actively involved in is setting up a global BI practice within my company; As part of this effort I was in the Netherlands on Friday

OK, sorry for all the repeat entries from Intelligent Enterprise in the last few days. I’ve removed them from the aggregator for now until I get a handle on what was going on there.

In totally unrelated news, you should notice that we’ve picked up several new feeds lately and all of the personal blog variety. I seems like more people in the BI/DW space are starting blogs and sharing their knowledge and insight.

And as always, if you know of a feed you’d like to see aggregated here, just let me know.

-Scott

Solution provider supports performance management decision-making for operations.

The Darwin Information Typing Architecture is finally pushing aside roadblocks that have stood in the way of adopting XML-based publishing.

The latest “patch” (and you’ll see why this is in quotes shortly) to OWB 10g Release 1 has just been posted in Metalink (patch number 4703215).

It’s a slightly odd patch, seeing that it requires installation into a new Oracle home and the patch installation notes read more like a major upgrade. It’s a 528MB patch… That’s a full installation mind you! Paul Narth posted in the OTN forums that there is an in place repository upgrade so you can patch from 10x to 10.1.0.4 for the repositories.

Other than just a bunch of bug fixes, why patch to 10.1.0.4?

Two reasons:
• Paris is “wicked late” pushed out to Calendar Year 2006
• It will run on Oracle DB 10gR2 which 10.1.0.3 and prior DO NOT

RapidResponse helps operational activities and decisions keep up with change.

Solution provider supports performance management decision-making for operations.

// Sponsor //

sor //

Top retailers choose RedPrairie to increase productivity and reduce labor costs.