Posted by Doug on 09/25/07 11:38

Hello,
It sounds like you are creating a "data warehouse." Basically, there
is transactional data, where you are adding, updating, and deleting
data. For this a more normalized data schema is vastly preferred.

However, someday someone wants to pull that data out, and the users are
NOT very sophisticated about learning inner and outer joins to get
their data out.
Mostly they are looking at querying the data and reporting.

If you have a billion rows, you probably have fairly worthwhile
reporting requirements. How would you like to take your 18 minute
queries, and turn them into sub second response time? How about turning
ALL queries against a billion rows into sub second response? Look into
OLAP.

OLAP requires a different mind set. It does not solve all business
requirements, but it can take the vast majority and make them REALLY
fast.
I've implemented several OLAP solutions for various companies. It takes
a large data set to be worthwhile, and at a billion rows you are
probably approaching critical mass where you can't provide data
solutions to your data customers without it.

For grins, create a cube off your main table. You will have something
along the lines of "names of stuff." in your de normalized table. Make
3 or 4 of these "dimensions." Hopefully these "names of stuff" have no
more then 200-300 variations. Human understood Product names, or
countries or something. Dates are another great Dimension.

You will have numbers. These are frequently dollars, or counts, or
something like that. Make no more then two of these "measures."

It will process once every time you change something, so it takes some
patience to learn how to set it up.
Once you have it set up, it is a LOT faster to query.

Remember how slow it was to do table queries before you had any
indexes? OLAP is a LOT bigger step towards efficiency and speed then
indexes were over raw tables.

drop me a note if you want some help getting it going. I havent' done
one in a while, and i'm getting a little rusty, and would appreciate
being able to help someone get it going.
drmiller 100 at hotmail com

remove spaces!

[Back to original message]