Search code examples
data-warehousedimensional-modeling

Dimensional Modeling - Outrigger dimension for geography


Currently, I'm working on dimensional modeling and have a question in regards to an outrigger dimension. The company is trading and acts as a broker between customer and supplier.

For a fact table, "Fact Trades", we include dimCustomer and dimSupplier. Each of these dimensions have an address.

My question is if it is correct to do outrigger dimensions that refer to geography. This way we can measure how much we have delivered from an origin and delivered to a city. dimensional model

I am curious to what is best practice. I hope you can help to explain how this should be modelled correctly and why.

Hope my question was clear and that I have posted it the correct place.

Thanks in advance.


Solution

  • I can think of at least 3 possible options; your particular circumstances will determine which is best for you:

    1. If you often filter your fact by geography but without needing company/person information (i.e. how many trades where between London and New York?) then I would create a standalone geography dimension and link it directly to your fact (twice - for customer and supplier). This doesn't also stop you having geographic attributes in your customer/supplier Dims, as a dimensional model is not normalised
    2. If geographic attributes change at a significantly more frequent rate than the customer/supplier attributes, and the customer/supplier Dims have a lot of attributes, then it may be worth creating an outrigger dim for the geographical attributes - as this reduces the maintenance required for the customer/supplier Dims. However, given that most companies/people rarely change their address, this is probably unlikely
    3. Keep the geographical attributes in the customer/supplier Dims. I would probably do this anyway even if also picking option 1 above

    Just out of interest - do customer and supplier have significantly different sets of attributes (I assume they are both companies or people)? Is it necessary to create separate Dims for them?