Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to design tables to hold cube data?
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.
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