Post reply

Conformed Customer Dimension design for an Orders business process

  • January 17, 2008 at 9:19 am

    #378079

    Thanks in advance for reading this.

    I'm designing a dimensional model for an orders process (the first in a several business process warehouse implementation). I'm stuck on the customer dimension. I'm trying to figure out how I should arrange things.

    The Question

    Should I create one dimension that includes all combinations of bill-to/ship-to, or create a customer ship-to dimension separate from a customer bill-to dimension?

    Or...

    Should I completely separate shipping from the orders process into its own business process, which will then have its own "customer ship-to" dimension?

    A Little Background

    We have both direct and wholesale customers. While most direct customers have only one ship-to, many of our wholesale customers have multiple ship-to addresses (up to 1300 at this point, maybe more in the future).

    Our company may also allow multiple ship-tos in one order (think Amazon).

    The grain of the fact table is the order detail line (quantity, price being the measurements).

    I'm sure I'm leaving something out, but this should provide enough for a head start. 😀

  • January 17, 2008 at 1:23 pm

    #769421

    Hi,

    I would begin with 1 dimension for the customer. The ship to and bill to adresses are attributes of the customers and it is possible to build more than 1 hierarchy in one dimension in SSAS 2005.

    What could be a problem to modulate are the multiple ship to adresses of 1 customer. But how do you generally know to which ship to adress an order has been delivered? One way to do this could be to use not the original customer key as dimension grain but a combination of the customer key and the ship-to/bill to adress. Something like this 0888888_Ship_ZipCode_44444 , 0888888_Ship_ZipCode_44467 .

    Maybe this helps you a step forward.

    Kindest Regards,

    SK

  • January 17, 2008 at 1:30 pm

    #769425

    Thanks Stefan.

    I've decided that the ship-to data isn't part of the customer at all. It's a part of the order. I'll be creating a new dimension table for the ShipTo addresses.

    Thanks for your input.

  • January 17, 2008 at 1:49 pm

    #769432

    My company had wholesalers and retailers and we had two different dimension tables since our customers required different information for wholesalers and retailers.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply