Customer Ship to Vs Customer Dimension

Hi Everyone,

I'm producing an invoice fact and SSAS model that can have several different customers and addresses for various reasons. For example, there is the Invoice Customer, Delivery Customer and also the End Customer. These customers will always be companies, not individuals. I'm very open to making these role playing dimensions, if applicable....

The Delivery Customer will often be just a location owned by the Invoice Customer. E.g. the Head office places an order, and the goods are then delivered to a particular 'site' or location owned by the Invoice customer. E.g. XYZ Limited may be a large company with 20 locations. XYZ Limited Head Office places several orders and the delivery is to each of its 20 locations across the country. In this case the Customer attributes such as Industry/Sales Person etc will be the same for the locations as they are for the head office.

Sometimes the Delivery Customer will be a completely different company, not just a location/address owned by the company that is paying.

In another fact table I also need to record the stock at these customer locations.

There are a few things I'm unsure of (this is all relating to the user perspective rather than the physical storage in the DW):

Do you think I should put these locations/addresses inside the Customer dimension? Or should I have separate Delivery Customer and Delivery Address dimensions?

My thinking is that its 1 dimension. The only attributes for the customer apart from the address are Industry and Customer Name/Number. Presumably when you have some sort of Address/ShipTo dimension, you have to include the name of the customer (as Kimball does in the Customer Ship To dim), hence my thinking that it should all be in 1 dimension.

Any help would be appreciated
Thank you

Hi - isn't "head office" effectively just another location? Why not treat every location (including the head office) as a separate Customer record - which I assume is what you mean when you suggest going down the route of having 1 dimension?
Attributes that are defined at the Head Office level can be cascaded (duplicated) down to the other records. If you keep a hierarchy between Head Office records and Location records then you can aggregate up to the Head Office level as required. You could also have a Customer Type field (with values of "Head Office" and "Location") if you need to differentiate between between the different types of Customer record.

This then gives you a single conformed dimension that you can use anywhere in your model

I like carrying an Address dimension and role play the relationships as needed. You can either dup the address information in the customer dim or just carry the address FK (i.e. snowflake).

Thanks for the replies,

Nick - you're right that's exactly why I'm saying to go down the route of 1 dimension. Then for using the same 1 dimension, I can record the invoices against the head office customer, then for my other fact table I can record the stock against the locations, using the same dimension.

What I'm essentially worried about is the semantics for the user Vs creating redundant dimensions. If I have a dimension called Customer then perhaps another one called Customer Location/Address then the semantics are perfect. But as I have 3 customer dimensions I will probably end up with 6 dimensions to cover the address and I don't want to have more dimensions than is necessary.

With the 1 dimension approach, the only true customer would be a legal entity, e.g. XYZ Limited, which has a head office address. The other 'rows' are just addresses, different locations that that company owns. So I'm worried that by calling the dimension Customer, its not semantically accurate, as the rows are not all customers.

One option is to go with one dimension, but call the dimension Customer Location with the attributes Customer Name, Address, Country, Postal Code, Customer Type etc. But then it feels weird creating an Invoice model and not having a dimension called 'Customer'. That would always be one of the first dims I would put in an Invoice model.

What does everyone think?

As, I assume, you are not exposing the raw tables to users you can call the same dimension different things depending on the context where it is being used - whether that is by using DB Views or the metadata in your reporting tool. Your users never need to be aware that the data is coming from a single table. Also, if it helps clarity for your users, you can expose different attributes depending on the context - so don't make location attributes visible where the context is "Head Office"

Regards,