Customer Dimension Modeling

[CaptainMo]

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.

 

[avjoshi]

Question is, do you have revenue data for each customer record in CRM/sales. What you are looking at is different grain of different fact tables. DW can't give somthing you don't have.
Could you please elaborate more on your design or did I miss somthing?


Anand

 

[CaptainMo]

No. Each customer record in CRM only has pipeline data. Each of these CRM customer record is rolled up to the parent, which exist only in the revenue system.

I was thinking about creating a cube for revenue and a cube for pipeline, and then a virtual cube using the shared customer dimension.

 

[johnherman]

I think you are right about a shared dimension. It should be conformed, too, as best as possible. It is possible that one system will have greater levels of detail (pipeline) than the other. This should work in both a cube and table back-end.
--------------

-------------------------
Sometimes the grass is greener on the other side because there is more manure there !

 

[entaroadun]

Create a new Customer entity that has a 1:M relationship to Customers on each system. When you build your queries, you will have to aggregate revenues from each system into this new Customer entity independently into temp tables, then join the two temp tables together to avoid the join multiplication issue.

The drawback is that the new Customer entity has to be at a higher level (lower granularity) than both of your source systems. This may or may not be an issue.