Hi all,
We have two star schemas built into our warehouse: one called vehicle
data and one called parts data. For some reason the warehouse was built
with these quite separate 'data marts' (not sure if that's the
right term)? Even though the parts will always relate to the vehicle.
How should these be merged? Do we connect via the dimension tables? Or
do we need a some other new type of tables to carry the keys?
Many thanks in advance for any pointers.
Sam

forgot to add. would another strategy be to place all the parts facts
into the vehicle fact table and then merge the dimension tables also?

If the two lots of data can be merged into one fact table that would
make it simpler; or you could build 2 cubes with different fact tables
and then merge them via a virtual cube - the dimensions need to match
up though if you want them all to work with all data.

You should always begin any of these analyses with an assessment of the
business requirements. What types of business questions are end-users
trying to answer about vehicles and parts? Are there facts that are
unique to vehicles and unique to parts? Are parts only interesting to
analyze in the context of vehicles, or vice-versa? Making data model
changes without understanding the end-user use cases is a recipe for
disaster.

However, in general, just to give you some food for thought, assuming
one vehicle can have many parts and one part can be associated to many
vehicles, you may be looking for a bridge table to model the
relationship between parts and vehicles, as this type of table is used
to model many-to-many relationships between entities. The book
entitled "The Data Warehouse Toolkit" has a number of case studies with
implemented data models that should be helpful.

Good Luck!

Many thanks - I have just bought the book. However the bridge table and
relationships you describe I thought was more appropriate to
transactional models rather than star schemas?

Dear Sam et al -

Sam> We have two star schemas [implemented as] quite separate
Sam> 'data marts'. How should these be merged?

Nenshad> You should always begin any of these analyses with an
Nenshad> assessment of the business requirements. [...] Making
Nenshad> data model changes without understanding the end-user use
Nenshad> cases is a recipe for disaster.

I agree wholeheartedly that the requirements are *the* critical thing
to consider. The intended business intelligence outcome should always
be first and foremost in one's mind.

It's useful (I find) when developing the requirements to focus on
strategic objectives. Your model is successful if and, only if, the
analytical work that's accomplished through its use forwards your
corporate objectives.

Rachel> If the two lots of data can be merged into one fact table
Rachel> that would make it simpler; or you could build 2 cubes
Rachel> with different fact tables and then merge them via a
Rachel> virtual cube - the dimensions need to match up though if
Rachel> you want them all to work with all data.

Rachel's point is dead on as well but, must remain secondary to the
acheivment of objectives.

I love the math part she brings up. Unfortunately, I find the tools
have idiosyncracies that impinge on model design.

So, my recommendation is to engage in the following steps:

o Recognize corporate objectives

o Identify your constituencies that effectively address those
objectives

(Ugghhh.... take politics into account.)

o Determine their analytical requirements

o Recognize the strength and weaknesses of the tools being
utilized

o Minimize your fact tables and conform the dimensions

o Rest on your laurels

Best of luck.

- Billy

================================================== ==========
William Goedicke goedicke (AT) brandeis (DOT) edu
Cell 617-510-7244 Office 781-736-4657
AIM goedsole
================================================== ==========

Lest we forget:

Everything is learned in the same way. Practice, practice, practice.

- William Goedicke