Skip to Content.
Sympa Menu

pcdb - [pcdb] OLTP verses OLAP design strategies

pcdb@lists.ibiblio.org

Subject: Permaculture Database

List archive

Chronological Thread  
  • From: Sean Maley <semaley@yahoo.com>
  • To: pcdb <pcdb@lists.ibiblio.org>
  • Subject: [pcdb] OLTP verses OLAP design strategies
  • Date: Tue, 3 Apr 2007 07:22:21 -0700 (PDT)

----- Original Message ----
From: Paul d'Aoust <paul@heliosville.com>
To: pcdb <pcdb@lists.ibiblio.org>
Sent: Friday, March 30, 2007 6:37:34 PM
Subject: Re: [pcdb] jedd's, lonnie's, and sean's requirements

> hopefully you won't mind my being lazy and asking
> you one quick question: were you arguing for or
> against star schemas? I know you mentioned OLAP;
> is that denormalised approach what you mean by star
> schemas? (I've never had any experiences with OLAP
> methodologies before. They frighten me.)

It has been difficult to know where to start in such a discussion. Database
design is more than organizing information. It's great that some understand
the importance of avoiding duplicates, as in the normalization concept.
However, sooner or later, the data must both be collected and/or retrieved.
If the database usage will be along the lines of collecting data, the
strategy would be to keep small easily updated and highly normalized tables
(OLTP; On-Line Transaction Processing). On the other hand, each data
retrieval that must join two or more tables to get the target information
would benefit from having those tables pre-joined in a manner of speaking, or
de-normalized (OLAP; On-Line Analytical Processing).

A star schema would be an example of a de-normalization design strategy. It
simply means that you avoid "snow flaking" by calling data dictionary like
information a dimension (time, location, "object" -species, rock, etc-, and
whatever level of roll-up you have collected data) and specific measures a
"fact" (yields, labor inputs, population, soil test measures, etc). The
dimensions never connect directly, as that produces many to many taboo
relationships. The facts provide the relationships.

The PFAF data set would feed part of one dimension. Other applications would
allow the recording (OLTP) of guild results, or measures. New information
can then be "mined" from the consolidated data mart. A collection of data
entry systems and corresponding data marts would be called a data warehouse.
I imagine we would de-industrialize the terminology into
composer/herbivore/swale db (OLTP) and de-composer/compost/predation db
(OLAP) with a collection of applications called a data guild.

A vegetarian data set would clearly be inadequate, always collecting
information, but never meaningfully able to share. The PFAF data makes a
good example of making it's information difficult to share, where three
categories of uses inconsistently collect data. A second system will now be
required (an herbivore/secondary data collection) to extract from PFAF to
make that information more consistent and usable for upstream systems
(predators/data marts). Lets be careful to observe patterns, rather than be
obsessed with size (good design makes a good remedy).

If you can forgive my attempt to avoid industrializing the discussion with
"data factory", etc.,


-Sean.






____________________________________________________________________________________
Get your own web address.
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL



  • [pcdb] OLTP verses OLAP design strategies, Sean Maley, 04/03/2007

Archive powered by MHonArc 2.6.24.

Top of Page