BI Blogs

Investigation of collecting Cost Base Optimizer statistics : "This
article looks at some of the questions and challenges you need to consider when
you are planning statistics collection for Oracle’s Cost Based Optimizer (CBO).
Many businesses require 24 by 7 operations which often put constraints on the
available timeframe and hardware resources for collecting CBO statistics. The
purpose of this investigation is to:

• Highlight how Oracle’s statistics collection methods dbms_stats and
  analyze work.
• Test and analyse ways to produce quality statistics, within a
  reasonable time-frame."

An excellent article by Peter Bach on the various statistics collection
strategies available with Oracle 9i and 10g. See also Jonathan Lewis’

"Gathering
Statistics: How Often and How Precise".
 

A paper that caught my eye at the recent
OTDUG Desktop Conference was
the one by Kent Graziano on
Agile Methods
and Data Warehousing. You have to be a conference attendee to view the
presentations, but I got hold of Kent’s email address and dropped him a line and he
was kind enough to send me a review copy of the conference paper. I subsequently
noticed that Kent delivered the paper again at the RMOUG Training Days and you
can actually download the
powerpoint slides that came with the presentation (and can get the paper if
you purchase the conference proceedings, which are a bargain I hear). I’ve had a look through
the paper and presentation now, and I thought it worth making a few notes on.

The presentation abstract was stated as:

"Most people will agree that data warehousing and business
intelligence projects take too long to deliver tangible results. Often by
the time a solution is in place, the business needs have changed. With all
the talk about Agile development methods and Extreme Programming, the
question arises as to how these approaches can be used to deliver data
warehouse and business intelligence projects faster. This paper will attempt
to look at some of the principles behind the Agile Manifesto and see how
they might be applied in the context of a traditional data warehouse
project. We will also examine a new approach call the Data Vault to see if
that methodology helps. The goal is to determine a method or methods to get
a more rapid (2-4 week) delivery of portions of an enterprise data warehouse
architecture."

Agile methods are a bit of a hot topic at the moment and I took a look at
them, in the context of data warehousing, earlier last year in
two
articles. My interest
at the time was sparked by the promise of a more flexible, responsive and
customer-driven approach to data warehousing projects, as a way of giving us a
competitive edge over competitors who were stuck with methodologies that
discouraged requirements changes and only delivered benefits right at the end of
the project. At the time there were a couple of astute comments, particularly by
Andy Todd and
Guy Mortenson, with
Guy making the observation:

"for context: my interest is the boundry conditions between, say, XP
and datawarehousing.

It seems to me there are two different ways of approaching ‘agile’ and data
warehousing projects. One way is to say ‘this agile stuff looks interesting
(http://agilemanifesto.org/principles.html), let’s see if we can apply it to
DW projects, or the other is to say, what was the epipheny that grew into
agile and go through the same process to arrive at ’son of agile’, or
‘brother of agile’? Kent Beck, I have it on good authority, went through a
process of ‘this isn’t working, there has to be a better way. What is the
minimum we need to build software? We need source code. We need to know it
works. We need design. We need to know what to build.’ Thus, we arrive at
continuous integration, test first, refactoring, customer onsight’(etc).
(Thanks to Alan Francis [http://www.twelve71.com/]for the insight).

In the datawarehousing world, I believe we’re at a kind of ‘this (often)
doesn’t work, there has to be a better way’ stage. Which path is then taken
depends on the appetite for people to "embrace change" in the first place
and then to either adapt agile or take agile to its extreme and start agile
anew."

In the presentation, Kent examines the
twelve key principles behind
the agile manifesto and looks at how they can be applied to data warehousing
projects. Looking first at principle number one, "Our highest priority is to
satisfy the customer through early and continuous delivery of valuable
software", Kent notes that we really need to consider the valuable software as
being the ETL interfaces, data structures and reports, and customers as being
end users or "knowledge workers", otherwise all we’d be talking about the
process of delivering reports and dashboards to the final end users and ignoring
all the work that has to go into putting the infrastructure in place. The second
principle, around being able to welcome change, is the one that is most
intererest to me, and Kent proposes that the best way of being able to
accommodate this is to build your warehouse in a normalized fashion, and to do
so with a code generation tool such as Oracle Designer or Oracle Warehouse
Builder.

I won’t try and paraphrase the entire presentation, but one other principle
that I was keen to get Kent’s opinion on is the one around delivering software
frequently, from a couple of weeks to a couple of months, with a preference to
the shorter timescale. In the conference paper Kent suggests that deliverables
could be defined as:

"1.      
A fact table for a star schema
2.      
A dimension table
3.      
A complete star (fact and all dimensions)
4.      
One piece of ETL code that populates a fact table
5.      
A function needed by the ETL code
6.      
A new report or query"

and then argues that:

"Even if the original intent of Agile did not consider database and
ETL type development efforts, can we not apply the principles anyway? The
intent of this paper is to propose some new best practices for data
warehouse development. So my proposition is that we can apply the concepts
and principles of Agile as a means of organizing our work efforts and our
teams to be more efficient and deliver something sooner rather than later.
Is that not a good thing? Remember that Principle #3 indicates delivery in a
couple of weeks to a couple of months, so a two week interaction, while
desirable, is not mandatory to be considered Agile. Perhaps we (the Oracle
and data warehouse community) need to be broader in our thinking and our
interpretation of Agile methods. Why can’t we use Agile methods and concepts
to deliver a database structure quickly? Do we have to follow the letter of
the law to reap benefit from Agile thinking? I think not."

The idea of using agile techniques when building databases is not new, with
Martin Taylor and Pramod Sadalage publishing
"Agile Database Design"
early in 2003 and a whole book,

"Agile Database Techniques", written on the subject. OTN also published an
article last year by Scott Ambler, the book author, on

"Agile Development
and the Developer/DBA Connection", and if you read any of the books
by Ralph Kimball on dimensional data warehousing, what he’s actually suggesting
is an agile method that delivers a data warehouse based on incremental, short
steps, based around subject areas.

So what are my thoughts? I’m all for it actually, tempered however by the
reality that on most projects I get to engage on, the client wants to define
requirements up front (indeed, has often defined them by the time we get there)
and isn’t too keen on what they might consider to be an open-ended approach to
development, especially if they see it as leading to a price tag (or delivery
date) they can’t predict in advance. I noticed that in the paper, Kent suggests
that a normalized data warehouse design lends itself well to agile development,
and so I dropped him an email to clarify this - what Kent is actually saying is
not that dimensional warehouses aren’t suited to agile development (indeed they
may be faster to develop than normalized ones) - it’s just that he feels that
with dimensional, star schema warehouses you end up transforming the data
multiple times to feed multiple fact tables, and this additional work makes it
difficult to be agile. I guess I’d disagree with this, as it’s been my
experience that well designed dimensional warehouses don’t necessarily have lots
of duplicated data, but I’ll defer to Kent’s experience on this one. Certainly, I can certainly see why a code generating tool, coupled with
repeatable processes and integrated regression testing, would make the process
of implementing data model changes much more manageable. All in all, an excellent
bit of analysis and I’ll be interested to hear at a later date how he
got on with this approach.

The powerpoint presentation can be
downloaded from the
RMOUG Training Days site, and if you were a training days attendee or you
"attended" the Desktop 2005 conference, you should be able to get hold of the
paper as well.

Some more interesting interesting articles and papers.

"EII - Dead On Arrival" by Andy Hayler for
DMReview looks at the current buzz around Enterprise Information Integration
- "lightweight BI" based around the synthesis of XML feeds - and finds it comes
up lacking:

"Some people who should know better have swallowed this EII mirage
hook, line and sinker, and a number of start-up companies have been funded
flaunting "EII for business intelligence" messages. The only problem with
this new futurist approach is that it is absolutely and utterly flawed.

Let’s consider the problem again. You have data in dozens of incompatibly
structured source systems. Your new EII software is somehow going to build a
presumably fairly complex set of distributed queries that will zip off to
the source transaction systems, interrogate them and bring back a result set
that will somehow produce a consistent answer.

The first problem is: how exactly does the EII software know what the
linkages are between the differently coded source system structures?
Somewhere it is going to have a catalog which will translate the
differences, rather like a dictionary to translate words from one language
to another. This sounds suspiciously like a metadata dictionary of the type
that data warehouses have to construct, but let’s leave that aside for the
moment.

What exactly happens when those distributed queries make their way through
to the source systems? For a start, the unpredictable nature of queries will
upset the careful load balancing done by operations departments to optimize
online throughput. Or rather, it won’t, because no systems managers are
going to allow this technology anywhere near their delicately balanced
systems, at least not after the first time it brings the ordering system to
a grinding halt.

The next problem with the EII approach is that there is no history. For
transaction systems, you want to archive data quickly in order to maintain
high performance (there is no need to worry about what your account balance
was last year, just what it is now; last year’s balance can be archived).
However for an inquiry: show me the trend in account withdrawals over the
last year in the Southeast region, this does require historical data.

Next, do these vendors really think that all the analysis hierarchies needed
are embedded within the ERP systems? To take the example of marketing, there
are normally complex segmentation hierarchies for analysis purposes that are
usually held in entirely separate places from the core transaction systems
and are not stored along with each order or invoice.

Just as importantly, the EII tools entirely ignore the tedious problem of
data quality. It may be news to vendors who have more experience producing
PowerPoint slides than production code, but the quality of data lurking in
the transaction systems is not what it might be. This is why there is an
industry of products to assist with improving data quality, and why a
significant chunk of any data warehouse project budget is associated with
data quality. Oh that’s right; you don’t need a data warehouse any more, so
I guess you may as well ignore that pesky data quality problem as well."

I took a look at this concept late last year when I posted a link to a
colleagues’ presentation on
Executive BI Dashboards
With XML, XQuery And XDS and the issue around historic data was brought up
by Ferenc Palyi in the article comments. I think Andy Hayler has hit the nail on
the head with his article - although you also should notice that he works for
Kalido, who sell very expensive full
service packaged data warehouses, the antithesis of what EII vendors provide -
but my thinking nowadays is that EII systems are more of a point solution for
integrating disparate current data in real-time as opposed to a replacement for a
traditional data warehouse, which is probably what the originators of the idea
first had in mind before the vendors arrived on the scene.

There’s a
rather entertaining thread currently running on
Asktom around

Don Burleson’s challenge for someone to create "a reliable predictive
model that will suggest tables and indexes which will measurably benefit from
reorganization.". Don is suggesting that it may be possible to build a
model that can predict when a table or index would benefit from rebuilding, and
bases this model on a number of assumptions that Tom subsequently states are

fundamentally flawed. I won’t pretend to be an expert in this area or try
and paraphrase the argument, but take a look at the

original challenge, the

thread on Asktom, and the additional one’s on
Don’s Oracle DBA board
and Howard J. Rogers’s
Dizwell Forum.
Note also a couple of good postings,
one by
Robert Freeman and one by

Jonathan Lewis that discuss whether such a model is in fact possible, and
what sort of assumptions it would have to be built on. Fascinating stuff.

Update: Don Burleson asks
"Are All
Oracle Scientists Created Equal" whilst Jonathan Lewis’ Spanish
Correspondent, "Don Quixote", asks
"Can You Spend Too
Much Cash?". See also postings on the

AskTom thread by

Mike Ault,

Howard J. Rogers,

Tim Hall and Tom’s follow-up to

Mark Cunningham’s posting.

The RMOUG Training Days recently took place, and there’s a whole load of
presentations and papers available for download that’ll be of interest to BI&DW
developers. Some interesting ones that I noticed were:

• 
  Oracle 10g Wait
  Event Tuning with the Automated Session History Tables
• 
  Oracle
  Discoverer V4 The Basics Uncovered and Administration Setup
• 
  Quick Web
  Development using JDeveloper 10g
• 
  Oracle 10g SQL
  Tuning Secrets
• 
  Blowing the
  Whistle on REF Cursors, Implicit Cursors, and Native Dynamic SQL: When and
  Why Each of These PL/SQL Constructs Comes In Handy
• 
  Oracle JDeveloper for
  Database Developers and DBAs
• 
  Analytic SQL Functions-Teaching an Old Dog New Tricks
• 
  Oracle/Unix
  Scripting Tips and Techniques
• 
  Oracle9i PL/SQL
  Fundamentals
• 
  Oracle 10g/9i
  for Developers: What You Need to Know
• 
  Oracle Spatial and
  Location Technologies
• 
  Profiling Oracle: How
  It Works
• 
  PL/SQL Debugging: Going Beyond DBMS_OUTPUT
• 
  Data Warehousing 101
• 
  Agile Methods and
  Data Warehousing
• 
  Data Mining Options in
  Oracle 10g
• 
  Get the Bigger
  Picture with OLAP-Enabled Oracle BI 10g - Unified, Strategic, and Extensible
• 
  Bridging the Gap Between
  Structured and Unstructured Data - All
• 
  Using STATSPACK as a
  Performance DW
• 
  No Bikinis? Working
  with SQL’s Model - Intermediate
• 
  Materialized Views in
  Action
• 
  Supercharging Star
  Transformations
• 
  Data Vault-What’s
  the Combination?
• 
  Using the Oracle Metabase Plus Language to Build and Deploy Mappings and
  Workflows
• 
  XML Survival Skills
  for DBAs
• 
  Resource Mapping: A
  Wait Time-Based Methodology for Performance Analysis
• 
  Zeroing In on
  Performance in Oracle 10g
• 
  Gathering
  Statistics: How Often and How Precise
• 
  Index Organized
  Tables-Are They Right for You?
• 
  Speeding Up
  Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT
  EXISTS, IN, and NOT IN
• 
  Use EXPLAIN
  PLAN and TKPROF to Tune your Applications
• 
  BIS and Discoverer EUL
  in 11.5.9
• 
  Oracle Applications
  Reporting-How Can I Get What I Need?

Finally, Chris Lawson has
written a useful article on the performance issues that can sometimes arise when
adding PL/SQL functions to an SQL statement.
"How Functions Can Wreck
Performance" should be of interest to any ETL developer looking to use
functions as a way of getting around a tricky SQL problem, and looks in
particular at the effect of calling such PL/SQL functions repeatedly. Well worth
a five minute read.

All statistics on the visits to the posts in the AMIS Technology Weblog are gathered in a MySQL database that sist underneath WordPress. We would like to do some analysis on these statistics, for several reasons: to try out the Oracle Warehouse Builder 10gR2 Beta Paris Release we have currently …

Some more interesting interesting articles and papers.

"EII - Dead On Arrival" by Andy Hayler for
DMReview looks at the current buzz around Enterprise Information Integration
- "lightweight BI" based around the synthesis of XML feeds - and finds it comes
up lacking:

"Some people who should know better have swallowed this EII mirage
hook, line and sinker, and a number of start-up companies have been funded
flaunting "EII for business intelligence" messages. The only problem with
this new futurist approach is that it is absolutely and utterly flawed.

Let’s consider the problem again. You have data in dozens of incompatibly
structured source systems. Your new EII software is somehow going to build a
presumably fairly complex set of distributed queries that will zip off to
the source transaction systems, interrogate them and bring back a result set
that will somehow produce a consistent answer.

The first problem is: how exactly does the EII software know what the
linkages are between the differently coded source system structures?
Somewhere it is going to have a catalog which will translate the
differences, rather like a dictionary to translate words from one language
to another. This sounds suspiciously like a metadata dictionary of the type
that data warehouses have to construct, but let’s leave that aside for the
moment.

What exactly happens when those distributed queries make their way through
to the source systems? For a start, the unpredictable nature of queries will
upset the careful load balancing done by operations departments to optimize
online throughput. Or rather, it won’t, because no systems managers are
going to allow this technology anywhere near their delicately balanced
systems, at least not after the first time it brings the ordering system to
a grinding halt.

The next problem with the EII approach is that there is no history. For
transaction systems, you want to archive data quickly in order to maintain
high performance (there is no need to worry about what your account balance
was last year, just what it is now; last year’s balance can be archived).
However for an inquiry: show me the trend in account withdrawals over the
last year in the Southeast region, this does require historical data.

Next, do these vendors really think that all the analysis hierarchies needed
are embedded within the ERP systems? To take the example of marketing, there
are normally complex segmentation hierarchies for analysis purposes that are
usually held in entirely separate places from the core transaction systems
and are not stored along with each order or invoice.

Just as importantly, the EII tools entirely ignore the tedious problem of
data quality. It may be news to vendors who have more experience producing
PowerPoint slides than production code, but the quality of data lurking in
the transaction systems is not what it might be. This is why there is an
industry of products to assist with improving data quality, and why a
significant chunk of any data warehouse project budget is associated with
data quality. Oh that’s right; you don’t need a data warehouse any more, so
I guess you may as well ignore that pesky data quality problem as well."

I took a look at this concept late last year when I posted a link to a
colleagues’ presentation on
Executive BI Dashboards
With XML, XQuery And XDS and the issue around historic data was brought up
by Ferenc Palyi in the article comments. I think Andy Hayler has hit the nail on
the head with his article - although you also should notice that he works for
Kalido, who sell very expensive full
service packaged data warehouses, the antithesis of what EII vendors provide -
but my thinking nowadays is that EII systems are more of a point solution for
integrating disparate current data in real-time as opposed to a replacement for a
traditional data warehouse, which is probably what the originators of the idea
first had in mind before the vendors arrived on the scene.

There’s a
rather entertaining thread currently running on
Asktom around

Don Burleson’s challenge for someone to create "a reliable predictive
model that will suggest tables and indexes which will measurably benefit from
reorganization.". Don is suggesting that it may be possible to build a
model that can predict when a table or index would benefit from rebuilding, and
bases this model on a number of assumptions that Tom subsequently states are

fundamentally flawed. I won’t pretend to be an expert in this area or try
and paraphrase the argument, but take a look at the

original challenge, the

thread on Asktom, and the additional one’s on
Don’s Oracle DBA board
and Howard J. Rogers’s
Dizwell Forum.
Note also a couple of good postings,
one by
Robert Freeman and one by

Jonathan Lewis that discuss whether such a model is in fact possible, and
what sort of assumptions it would have to be built on. Fascinating stuff.

The RMOUG Training Days recently took place, and there’s a whole load of
presentations and papers available for download that’ll be of interest to BI&DW
developers. Some interesting ones that I noticed were:

• 
  Oracle 10g Wait
  Event Tuning with the Automated Session History Tables
• 
  Oracle
  Discoverer V4 The Basics Uncovered and Administration Setup
• 
  Quick Web
  Development using JDeveloper 10g
• 
  Oracle 10g SQL
  Tuning Secrets
• 
  Blowing the
  Whistle on REF Cursors, Implicit Cursors, and Native Dynamic SQL: When and
  Why Each of These PL/SQL Constructs Comes In Handy
• 
  Oracle JDeveloper for
  Database Developers and DBAs
• 
  Analytic SQL Functions-Teaching an Old Dog New Tricks
• 
  Oracle/Unix
  Scripting Tips and Techniques
• 
  Oracle9i PL/SQL
  Fundamentals
• 
  Oracle 10g/9i
  for Developers: What You Need to Know
• 
  Oracle Spatial and
  Location Technologies
• 
  Profiling Oracle: How
  It Works
• 
  PL/SQL Debugging: Going Beyond DBMS_OUTPUT
• 
  Data Warehousing 101
• 
  Agile Methods and
  Data Warehousing
• 
  Data Mining Options in
  Oracle 10g
• 
  Get the Bigger
  Picture with OLAP-Enabled Oracle BI 10g - Unified, Strategic, and Extensible
• 
  Bridging the Gap Between
  Structured and Unstructured Data - All
• 
  Using STATSPACK as a
  Performance DW
• 
  No Bikinis? Working
  with SQL’s Model - Intermediate
• 
  Materialized Views in
  Action
• 
  Supercharging Star
  Transformations
• 
  Data Vault-What’s
  the Combination?
• 
  Using the Oracle Metabase Plus Language to Build and Deploy Mappings and
  Workflows
• 
  XML Survival Skills
  for DBAs
• 
  Resource Mapping: A
  Wait Time-Based Methodology for Performance Analysis
• 
  Zeroing In on
  Performance in Oracle 10g
• 
  Gathering
  Statistics: How Often and How Precise
• 
  Index Organized
  Tables-Are They Right for You?
• 
  Speeding Up
  Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT
  EXISTS, IN, and NOT IN
• 
  Use EXPLAIN
  PLAN and TKPROF to Tune your Applications
• 
  BIS and Discoverer EUL
  in 11.5.9
• 
  Oracle Applications
  Reporting-How Can I Get What I Need?

Finally, Chris Lawson has
written a useful article on the performance issues that can sometimes arise when
adding PL/SQL functions to an SQL statement.
"How Functions Can Wreck
Performance" should be of interest to any ETL developer looking to use
functions as a way of getting around a tricky SQL problem, and looks in
particular at the effect of calling such PL/SQL functions repeatedly. Well worth
a five minute read.

Building BI Dashboards with Oracle Database 10g, Oracle Discoverer, and
Oracle Portal : "If you’ve been
following the release of

Oracle Business Intelligence 10g or keeping an eye on
industry trends, you’re probably aware that everyone is talking about business
intelligence (BI) dashboards. BI dashboards bring together reports and graphs
from several data sources and present information to users in a
simple-to-understand, unified manner. The demand for BI dashboards from the user
community has therefore led to numerous new features and improvements in
Oracle’s business intelligence tools, and the simplified creation of BI
dashboards is one of the key new features of Oracle Business Intelligence 10g.
This article explains the features of a BI dashboard, discusses the benefits to
users, and steps through the creation of a dashboard with Oracle Database 10g;
Oracle Business Intelligence Discoverer (Oracle Discoverer), a component of
Oracle Business Intelligence 10g; and Oracle Portal."

This is my first article for OTN, and looks at how you can create
the above BI dashboard using Discoverer "Drake", Oracle Portal and the
Videostore demo data that comes with Discoverer Administrator. Writing for OTN
is a bit of an eye-opener (as it’s Oracle, you have to be "spot-on" with
references to product names and technical details) but it’s nice to see an
article up on the OTN front page. Take a look if you’re looking to set a
dashboard like this up.

I just read the new SQL Server 2005 Features Comparison.
Bad news on the last line: ActiveViews (officially named SQL Server 2005 Reporting Services Report Builder, or simply Report Builder) will be delivered only within the SQL Server 2005 Enterprise Edition.

Please, Microsoft, don’t do that. At least, give a choice to users and sell Report Builder as a single product.

A lot of customers could evaluate different reporting products and maybe different platforms just for this reasons. Expecially small and medium companies (I live in Europe, a market really different than US).

Report Builder as a separate product for SQL2K5 Standard Edition users should be really considered.

Thanks to Chris Webb I discovered this useful webcast about MDX tips & tricks (rightly named Common MDX mistakes & solutions). It’s available even the whole transcript, so you can read it instead to listen after midnight