Ggtcf

I am new to dwh and creating data warehouse for my company. We have a customer dimensions which we want to create.However finance team customer list Id and name are different from the CRM list ID and name.
We created a mapping table but sometimes for same finance ID we have multiple crmid How should I handle this...do I have to create two customer dimension

 CRM Name
 CRMID1 XXXX
 CRMID2 YYYYY
 CRMID3 ZZZ

Mapping table

 CRMID FINID
 CRMID1 FINID1
 CRMID2 FINID1
 CRMID3 FINID2
 CRMID4 FINID3

FinTable

 FINID1 XXXX
 FINID2 YYYY
 FINID3 ZZZ

When I create the Dimension and join these three tables

Final Dimension

 ClientKey CRMID FINID
 1 CRMID1 FINID1
 2 CRMID2 FINID1
 3 CRMID3 FINID2
 4 CRMID4 FINID3

This causes duplication when I join the dimension table with Fact table and add the ClientKey in the Fact table.

Should I create Two Dimension for Client or Is their any other way I can handle it in one dimension.

As a Data Warehouse you must have a unique customer ID.

A solution involving your operational systems is best (i.e - Master Data Management) but if this is too hard to drive (and it usually is) at least create a unique customer ID in the DWH Customer dimension.

Both the CRMID and the FINID are attributes of a customer in the Customer dimension regardless of the current relation between them (1:1 or 1:M or even M:M).

As mmarie mentioned your unique customer ID can be a combination of business keys but I strongly advise creating a separate surrogate key since as Ralph Kimball states production keys are unstablevolatile.

Another reason is that your source systems change over time (like you mentioned) even when your customers don’t so you need the ability to track and report on a customer over multiple systems and periods.

Assuming you will go for a SCD type 2 – you will want something like this:

• PK (Surrogate, time variant)


• CustomerID (Surrogate, permanent, DWH generated, unique per customer)


• CRMID (CRM business key)


• FINID (business key)


• ATTRIBUTE_1


• ATTRIBUTE_2


• START_DATE


• END_DATE

If your mapping is always 1:1 or M:1 CRM Customer:Finance Customer, then you shouldn't have to create two customer dimensions. But everyone involved will have to understand that there are multiple levels/views of customers and that this can complicate things. It is a somewhat common situation to have a difference between the marketing customer/purchase decision customer and the billing customer. These customer attributes are combined in master data management and data warehouses for a coherent combined view called a conformed dimension.

In the reporting system/data mart, you just need to define your mapping table and ultimately your single customer dimension at the level of granularity of the CRM customer ID. Just treat the FINID as a nonunique attribute of the customer

You may end up having to identify a "parent customer" so that facts at the granularity of the financial system don't duplicate data across all CRMIDs with matching FINIDs. If you can flag the parent customer (One CRMID per FinID), then you can attribute all financial facts to that parent customer CRMID and all marketing/sales facts to any applicable CRMID.

There is no problem mapping all described relations in a central DIM_CUSTOMERS, see 2 last records in the below sample

1. CRMID FINID


2. CRMID1 FINID1


3. CRMID2 FINID1


4. CRMID3 FINID2


5. CRMID4 -1


6. -1 FINID2

It seems to me your problem is more with driving cooperation between different parts of the company and its quite a common challenge in DWH projects

The DWH key job is to integrate separate silos of the organization and it takes time for different departments to understand how they affect each other.

The best way to encouragepush your finance team & CRM owner to sync data between them (after giving them a heads up regarding the problem) is simply to reportreflecting garbage data (customers not existing in the new CRM or not mapped by finance).

This will either

1. Put management pressure on both players to sync entities – In which case you only need to update the -1 keys in your dimension, or


2. Reflect the problem is not as important as you fill – in which case your work is done