Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Fetching from a hierarchical data structure?

Re: Fetching from a hierarchical data structure?

From: Christoph Pfrommer <Christoph.Pfrommer_at_oracle.com>
Date: Mon, 09 Apr 2001 23:15:43 +0200
Message-ID: <3AD2267E.4219C2EA@oracle.com>

Maury Markowitz wrote:

> "Christoph Pfrommer" <Christoph.Pfrommer_at_oracle.com> wrote in message
> news:3AD14E79.7CD7461B_at_oracle.com...
> > A few words on typical DW architecture: The well known star schema
 consists of a
> > large fact table (as in your application) and some lookup tables, one for
 each
> > (hierarchical) dimension. Since lookup tables are having a different
> > representation of hierarchies - one column for each level - you are coding
 your
> > SQL with some joins
>
> Now this certainly sounds like a good idea in performance terms, but
> doesn't this limit the depth of the hierarchy?
>
> Maury

Maury,
that's true. (Even if you add one or two extra levels your argument remains valid).

However, data warehouses are mostly dealing with static or slowly changing dimensions and hierarchies. ('Cause people want to analyze their data with well-known structures.) If you talk to the right people, you should know of those changes in advance.

And if you want to go to extremes (in terms of coding): You can write load procedures in Oracle PL/SQL, that add new columns dynamically, e.g. statements like:
  execute immediate ('alter table my_table add column level' || i); where i stands for the depth of your tree.

Hope this helps.
Christoph. Received on Mon Apr 09 2001 - 16:15:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US