How to model Customer in data warehouse

[Nisse]

Hi!

I have a datawarehouse that contains facts for store sales (FactStoreSalesTransactionLine).

I´m now about to create a new Fact for our order sales (internetorder, phoneorder etc).

I have som questions regarding how to handle the customer (and addresses) of the order.
In the future we will also have customer-information on the store sales (receipts) so I´m thinking about having a conformed customer dimenssion for both facts and for any other customerrelated facts
that might come up in the future.

Q1) Should I add customers to DimCustomer when they are created in ERP (we might have several erp´s containg differnt type of customers, like invoice customers and loyalty customers) or should I add them when they appears on an Order or a Receipt?

Q2) What is the best (most common) way to handle addresses (geographical data). An order might have both a customer address a billing address and a shipping adress. The Customer may also have addresses of different type in the ERP system and may have several shipping addresses. What I know is that the business want to know which city, zipcode and country an order was placed to.
Should the geo-info be added to the orderfact or to the customer or perhaps both? Is it common to track both shipping and customeraddresses? One line for every unique customer in dimcustomer or different versions deping on there current status/address etc?

Example of possible modelling:

Model1
*******
FactOrderSales
-----------------
dimX
dimY
dimZ
dimCustomer
dimCustomerGeograhy (links to dimgeography) - The customeradress (city, zip, country) on the order
dimShipToGeography (links to dimgeography) - The shippingadress (city, zip, country) on the order


DimCustomer
-------------
CustomerKey
-CustomerId
-CustomerName
- ...

DimGeography
-------------
GeoKey
-City
-ZipCode
-Country

Model2
******
FactOrderSales
-----------------
dimX
dimY
dimZ
dimCustomer

DimCustomer
------------
CustomerKey
-CustomerId
-CustomerName
-GeographyKey (Current customer address in the ERP)

DimGeography
-------------
GeoKey
-City
-ZipCode
-Country


Thanks in advance
/ Nisse

[ngalemmo]

Generally customer or any other sourced dimension is maintained separately from the facts. There would be a process to maintain the customer data from the source system's master data, regardless of wether or not facts exist.

As far as address information goes, I've seen it done both ways... either a part of the customer dimension or a separate dimension. I prefer the latter, but it all depends. If you do implement separate dimensions, you may want to supplement it with a customer/address fact table if you need to keep track of the relationship.

[Nisse]

Thank you.

Can you please give an example of how the customer/address fact table can be modeled.
If I go with your preffered solution the customer-dim and order-fact will look something like in Model 1?

What if the customer isn´t created in the ERP before I got the Order, but i think it will arrive some time later. Should I create a new customer from the customer-info of the order and later update it with data from ERP when the customer arrives there?

[ngalemmo]

Something like model 1 would be preferred. As far as tracking address history, you would have an fact table with customer key, address key, maybe a role flag (ship to, bill to ect...) and an effective date range.

[Sponsored content]