Modeling Invoice Level Sales With a Volatile Sales Org

So I'm trying to model invoice level sales, and I'm struggling with the Rep/Territory portion of it.

So here's some of the knowns -

1. Sales territories change from time to time
2. We have multiple businesses in our corporation who have different sales territory hierarchies (different number of levels)
3. We often want to see sales by the original territory alignment, the current territory alignment, and a proposed future territory alignment
4. Reps can make sales outside of their territory (it's unfortunate), and I'm not sure if we only need to look at the rep that booked the sale or the current/proposed as well.
5. Accounts are typically aligned to a territory by a business (the other businesses can also service the same account but typically sell distinct products)
6. We have master data for customer (and will use an inferred dimension member model where we don't have them in master data yet, and then update it once the master data is available)


So my first instinct is Territory and Rep are definitely distinct dimensions, the one I'm unsure on is whether territory is part of the customer hierarchy or it's own dimension.

The second piece I'm not sure about is how to handle the territory realignments. The first option I came up with was:

Have the territory dimension and a currentTerritoryId, originalTerritoryId, and proposedTerritoryId on the fact table and write an ETL package that updates that to realign the sales when necessary. It seems to be a very flexible model and works with our different businesses being very different. However my colleagues are concerned that this approach doesn't scale well as the fact table grows.

The second idea that I came up with that would work would be to make the territory a "Type 6" dimension which could potentially cause this dimension to become quite large (especially if we opted to combine it with customer).

The option my colleagues suggested is to have a "realignment table" however since not all businesses work the same way I'm not sure this is a practical approach (their response was we could use this to drive the business to change their processes and unify them... but I don't think that will work either.

So I'm kind of at an impasse on what the right approach is here. :-) Anyone?

This is a tough one...here are a few questions (you may have already asked yourself), that may help drive to a solution?

1. When a "sale" is transacted it is assigned to a territory at that time. What is the "dim natural key" used to assign it a territory? Is territory # carried on the source data?

2. If territory is not carried on the source, what other value is used to lookup the territory dimension key? Can this be used on the fact instead of territory? If so, then you could build a bridge table between that key and the many possible territory alignments.

For example, if territories are assigned by account (i.e. on load use the account to find the appropriate territory), your bridge would have the following for 2009 (assuming a yearly territory alignment):

Account, Territory, Territory Alignment Year
=================================
Acct-A, Terr-#1, 2009
Acct-B, Terr-#2, 2009
Acct-C, Terr-#3, 2009

Then, if the territories were realigned/renamed in 2010, you may have the following rows added:

Account, Territory, Territory Alignment Year
=================================
Acct-A, Terr-#1, 2009
Acct-B, Terr-#2, 2009
Acct-C, Terr-#3, 2009
Acct-A, Terr-WEST, 2010
Acct-B, Terr-WEST, 2010
Acct-C, Terr-EAST, 2010

note: that this would also support combining or splitting territories over time as shown.

If you had multiple business units wanting their own territory, then you could add that to the bridge as well:

Account, Territory, Territory Alignment Year, Biz Unit
=======================================
Acct-A, Terr-WEST, 2010, Retail
Acct-B, Terr-WEST, 2010, Retail
Acct-C, Terr-EAST, 2010, Retail
Acct-A, Terr-HiGrade, 2010, Internet
Acct-B, Terr-MidGrade, 2010, Internet
Acct-C, Terr-LowGrade, 2010, Internet

From your post, it is the customer account that drives where a sale is reported in the sales organization, and an account is assigned to a region, correct?

To handle accounts, you need an account/region bridge table. This can include effective date ranges and a current flag to allow you to store historical relationships. This also serves as the entry point into the sales organization hierarchy.

The sales organization hierarchy can be implemented as a flat row containing all the levels associated with the region or as an exploded hierarchy bridge. If you use a bridge for the hierarchy, you would use both bridges to roll facts into the hierarchy. You could also use effective data ranges and a current flag on the hierarchy table (flat or bridge) to retain relationship history.

So for about 60% of our divisions sales are algined to territory by customer (region is a parent of territory)

Other areas in the business do things like for a combination of zip code and product line

Sounds like you'll need two bridges. On the fact store the following keys:

customer_skey
zip_skey
product_skey

Then build two bridges, one for each type of rollup...

Bridge #1 (Division Sales Bridge)

customer_skey, Territory, Territory Alignment Year
=================================
cust-a, Terr-#1, 2009
cust-b, Terr-#2, 2009
cust-c, Terr-#3, 2009


Bridge #2 (Other Sales Bridge)

zip_skey, product_skey, Territory, Territory Alignment Year
=================================
21100, prod-X, Terr-ALPHA, 2009
43032, prod-X, Terr-BETA 2009
75115, prod-X, Terr-GAMA, 2009

note: example attempts to show one product across three zips, each assigned to a diff territory, for the year 2009 (see previous notes about versioning of the hierarchy)