Here's the assumption:

I have 2 systems, revenue and CRM. Let's assume in the revenue
system, there's 1 customer record, IBM HQ. All previous billings to
IBM are issued using this record. The CRM system tracks only sales
pipeline, and has 10 customer record, all IBM subsidiaries whose
parent is IBM HQ.

I would like to compare what's in the pipeline against what was
revenued in the past. What is the best way to model the customer
dimension? Ralph Kimball suggested using a hierchrary bridge table
that stores the parent/child relationship. I tried it, but the
revenue number is repeated for every child.

Any suggestion is appreciated. Thanks.

"mowksi" <chairman_mowski (AT) yahoo (DOT) com> wrote

I assume something like this exists:

revenue table record: cust1
crm table record: cust1_a, cust1_b, cust1_c, etc. (same customer but
different subsidiaries)

Remember that Kimball says that every incoming production key must be
replaced with a meaningless integer (aka surrogate key). So, you will have
to maintain a seperate cross reference table (that hierarchy bridge table
you mention) and use it during your ETL. For example, your customer
dimension table could be structured like this:

create table dim_customer (
surrogate_key
prod_key_rev
prod_key_crm
etc.
)

Yes, the revenue key is repeated for every child in this case. If you don't
like that, you can alter your hierarchy bridge table to be something like:

create table xref_rev_crm (
surrogate_key --meaningless integer
prod_key_revenue
prod_key_crm
)

thus leading to the customer dimension table looking like:

create table dim_customer (
surrogate_key
sk_xref_rev_crm --surrogate key from the xref table
etc.
)

in this case, you will not get repeating value. I don't see the problem
with the first method though...