Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Fetching from a hierarchical data structure?
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