Hello,

When many datamarts need the same dimensions and facts that are
conformed and sitting in the data warehouse, is it best practice for
those marts to each take copies of what they need locally and have the
data stored twice, or instead is it better to have the tables reside
in 1 place only (which would force a star schema that is effectively
across 2 different databases/systems) ?

Thanks,

Jack

Well, Mr. Posemsky,

I think the answer is the one so often heard, "...that depends..."

Some reasonable questions that come to mind are...

1. How many is "many datamarts" ?
2. Why are there STILL "datamarts" if the fact data they need is in
the EDW ? Is the EDW too slow? Is the EDW too read-only ? Is the
EDW data too stale ?
3. Is the philosophy of the organization to source the datamarts from
the EDW or does each datamart "role their own" ETL ?
4. What mechanism(s) are in use for information delivery ? Can
it/they seamlessly support multi-database joins or is/are it/they why
there are still datamarts ?

A best practice by definition would seem to imply a de-facto, generic,
industry guideline for doing something...which unfortunately depends
heavily on not only the industry, but the reality of raw materials
from which a particular endeavor must derive a credible and livable
solution with which their CFO can live.

Your truly,
Rufus T. Fuddpucker, Esq. III
"Livin' large in Pleasant View, TN...where the deer are crazy and the
coyotes are scared."



jack_posemsky (AT) yahoo (DOT) com (Jack) wrote in message news:<209b7e58.0404021541.76f8a064 (AT) posting (DOT) google.com>...

dmcmunn (AT) yahoo (DOT) com (D.McMunn) wrote in message news:<87665175.0404071935.163ba88f (AT) posting (DOT) google.com>...
We are talking 50-100 datamarts pulling from various EDW's (subject
based)

There are specialized types of analysis that is needed by different
processes - with an EDW one size doesnt fit all. Thats why there is
such a thing as a datamart layer.

Each datamart rolls ETL that is specific to their needs most of the
time.
The EDW does also.
Can you please answer the original question posed ?

"Best practice" is nothing more than the consultant's paper trail du
jour.

I suggest #2 below for your organization.

At the end of the practical day it depends on your organization's
goals and resources...unless your organization is the US
government...a printer of money.

1. using the single copy of "the same dimensions and facts that are
conformed and sitting in the data warehouse" will simplify ETL, but
may lead to complex and conflicting user query roles / permissions and
their cross-platform management; and single point of failure
availability concerns for the datamarts...if EDW is off-line...your
datamarts are worth so much milk-toast. Given the proliferation of
datamarts, my guess is a corporate datamart-centric culture would not
be happy customer.

2. copying those same conformed dimensions/facts from the EDW to a
datamart provides much more operational EDW-independence to the
datamarts, keeps security single platform. However, like many other
layered operational systems used in years gone by...reconciliation
becomes a concern where datamart subject areas overlap and can lead to
competing versions of a business truth and therefore consume a few
more resources. Ask CFO re:Sarbanes-Oxley.
However, I posit that's what datamarts are meant to address...the
different business views/meanings of an organization's data.