Customers from 2 sources on different granulaties

[mchojnacki]

Hello,

I'm having a design problem. I've figured out a solution, however I would like to ask for your opinion.
My model is very similar to a "communication industry" model.
We have a sale fact table and customer dimension (apart from other dims of course).
Customer dimension is having multiple natural keys. I have created a surrogate key for joining with fact table.
For a natural keys I have something like:
-- SourceCustomerId (Id taken from source sales systems)
-- SourceCustomerRegion (some systems enumerate customers per region, so I had to add a second natural identification)
-- Tax Payer Id (This is Taxpayer Identification Number)

Now, we have received a Change Request to include further customers data from CRM system.
Problem Is that CRM customer's grain is basically equal to Taxpayer Identification Number.
Customer from sales system per system per region may have multiple same entities for same Taxpayer Identification Number.
This is cause by the way how those source systems handles customers information, rather a fact that they didn't handle it at all.

Apart from CRM customers list I need to introduce an information about customer's declaration about their "usage".

I thought about it for some time and figured out that those "customers" cannot really be merged, but I can join then through a Natural Id(Taxpayer Identification Number), but keep them in a separate tables. I would say it could be a physical conformed dimension.

My model would be something like this:

Fact_DeclaredUsage (DimCustomerCRMKey (FK)) --> DimCustomerCRM (CustomerCRMKey (SK), TaxPayerId (NK)) --> DimCustomer (CustomerKey (SK),
CustomerCRMKey (FK), etc.) --> FactSales (CustomerKey (FK))

I hope I made it clear. Please let me know whether I'm going into right directions.
Regards,
Maciej

[Jeff Smith]

The Business is using the same term to describe 2 different things. The entity on the CRM is not the same thing as the Entity that is used for Sales. The CRM has decided to use the TIN as it's base. Since in the sales table, multiple entities can share the same TIN, then the TIN is a rollup of customer - sort of. You may end up with the same customer in Sales with more than 1 TIN.

You may have a CRM FACT table which pulls all the atribute from the CRM database together. And you can have a Sales fact table. The Sales fact table can have the CRM related dimensions assigned to it. You can also rollup the Sales Fact to the CRM (TIN Level).

You can also assign each sales customer to a Primary TIN based on some business rules that you can derive. You can assign this TIN to the Customer Dimension as a Primary TIN. This might come in handy when trying to measure the effectiveness of marketing campaigns using TINs from the CRM system.