Hi,

What is the best way to design if I have multiple fact tables? Rather
than having multiple cubes, I would like to see one cube that consists
of all fact tables.

I would really appreciate if someone could show me an example or
websites.

Many thanks in advance.


--
Posted via http://dbforums.com

Hi,

to design all fact tables within one fact table usually only makes sense if
these fact tables are a kind of partitioning. If so look in your db manuals
how to partition data. If your db doesn't support partitioning a view could
help. If the dimensionality of your fact tables is different I would
strongly recommend to not put them into one giant fact table otherwise you
will have to do a very good job to describe your business users how to use
the data warehouse.

Kind regards,

Joerg
"dbdev" <member46101 (AT) dbforums (DOT) com> schrieb im Newsbeitrag
news:3534523.1067398522 (AT) dbforums (DOT) com...

Hi Joerg,

Thanks for your reply.

If a giant fact table isn't a problem to users, and these tables share
some common dimensions, will a view affect the star shema model?

Partitioning does not work because these fact tables are different. And
because of the unique way each table is loaded, each of the fact table
will stay the way it is.

Do I have any other options?



Thanks!



Originally posted by Joerg Narr


--
Posted via http://dbforums.com

A problem I've seen too often in Data Warehousing initiatives that are
driven by IT rather than by business requirements....

"We have one warehouse, so why can't we have just one cube"


Each fact table and its related dimensions represents one business
unit, or one business problem. It is meaningless to collect multiple
unrelated facts into a single cube.

Users will get lost trying to find meaningful data, and your
aggregations will have little/no/confusing meaning. The larger the
cube (size as well as attributes) the worse your performance will be,
and the less relevant to any particular business pain.

Bite the bullet... accept the fact that each fact table will be
one/more cubes.


MichaelR


On Tue, 28 Oct 2003 22:35:22 -0500, dbdev <member46101 (AT) dbforums (DOT) com>
wrote:

This depends of what system you are using. As Joerg points out you can
have have multiple fact tables on some OLAP systems such as MSAS 2000
by partitioning your OLAP system. In that case you need the MSAS
Enterprise edition.

In order to take advantage of the partitioned database just create a
new partition in your database editor (not inside the cube editor) the
new partiotion can be different from the existing ones by using MOLAP,
HOLAP and ROLAP storage mode - however standard MOLAP is the most
frequent used one.

Unfortenately using this techinque all fields in ALL partitions have
to be IDENTICAL, so if you are using two tables with a slightly
different table layout you need to rebulid them.

In many cases using the partition technique is very smart such as
having very large OLAP databases with data organized in partitions
representing years, or versions such as budgets versus actuals.

RE
Bjørn T