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: How to design tables to hold cube data?

Re: How to design tables to hold cube data?

From: Michael Carton <MikeC_at_erols.com>
Date: Sun, 28 Jan 2001 15:06:27 -0500
Message-ID: <3A747BC3.EDC47876@erols.com>

lawr99_at_hotmail.com wrote:
>
> Here is the problem, you have clients who will need to input revenue
> information into the database. Some might just want to break this up
> into years, but others may want to break revenues further into
> regions. Some might even want to break it down into seasons. There is
> no telling up front how many different ways this data will be looked
> at. The cube has to be able to go to the nth level.
>
> How can you design a table structure to handle this?

If it is a lot of data (tens or hundreds of millions of rows) or if it is going to be used enterprise wide or if it is going to be used by the big shots, you'd be better off doing a proper data warehouse solution. Use Hyperion's Essbase or something similar.

Alternately,

Design your table as you would design any good transaction table in a relational database. The primary key will be a composite of the IDs for the dimensions in a cube. It might comprise the year, month (or week), Region ID, Dept. ID etc. The data item will be revenue amount. Have clear and clean cut master tables for each of the fields in the primary key. A regions_tbl with Region ID, Region Name etc. In addition you'd need table like a seasons_tbl with month and season_name as columns.

I have done this sort of thing in the last 3 years.

  1. Simpler stuff in Oracle following my own advice above. Each dimension has a tree. Defining roll ups. For example Austria, ... UK rolling up into Europe and so on and all the continents rolling up into the <Company> Worldwide. Similar trees exist for each dimension like time etc. The query interface allowed user to choose a node on each tree and showed the data which was summed up as required by the user choices using dynamically constructed SELECT statements.

   BTW, the users get to define one or more hierarchies (trees) for each    dimension, themselves. I use the TList control from Bennett-Tech for this    and for allowing users to choose nodes in the queries.

   If you know what you're doing, you can satisfy a lot of the simpler    warehousing requirements like this very well. Depends on how well    the server m/c hosting Oracle is specified, though.

   This can be a lot of work. But it is deeply satisfying when you see the    user querying the data to get real information.

2. In only one case, in my current application, I'm faced with huge amounts

   of data, enterprise wide, mission critical use and visibility to    corporate big wigs.

   We are going with Hyperion Essbase date warehouse product. It uses the    cube architecture.

-- 
MikeC

Please reply to the group.
Received on Sun Jan 28 2001 - 14:06:27 CST

Original text of this message

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