How to model a complex region dimension

[Hugo]

Hi,

I'm trying to model a territory dimension but I'm having some doubts on the best way to accomplish this, due the frequent changes that take place on the company.

The base of the dimension is (country -> region -> sub region), this is a trivial dimension.
The company is divided into departments and some departments want to see data by “regions” and other departments want to see data by “sub region” this is also easily accomplished.

Every department has several sales teams, and each sales team divides the country in areas and there are no correlation between areas. Some examples:

* Department D1 defines areas for team T1 as a group of regions, for example:
1. Area D1T1-A1 is created by grouping regions R1, R2 and R3;
2. Area D1T1-A2 is created by grouping regions R4 and R9;
3. …


* Department D2 defines areas for team T1 as a group of sub regions, for example:
1. Area D2T1-A1 is created by grouping sub regions R1-SR1, R1-SR2 and R2-SR1;
2. Area D2T1-A2 is created by grouping sub regions R2-SR2 and R1-SR9;
3. …


* Department D3 defines areas for team T5 as a group of sub regions, but with sub regions belonging to several areas, for example:
1. Area D3T5-A1 is created by grouping sub regions R1-SR1, R1-SR2 and R2-SR1;
2. Area D3T5-A2 is created by grouping sub regions R1-SR1 and R2-SR1;
3. ...


I believe this may be modeled as several hierarchies inside a single dimension, for example
* H1: country -> region -> sub region
* H2: country -> Areas D1-> region -> sub region
* H3: country -> Areas D2 -> sub region
* H4: country -> Areas D3 -> sub region

(We are using SSAS 2008)
Since H4 may duplicate values I was considering splitting H1, H2, H3 into one dimension and H1 and H4 into another dimension and handle the fact that some metrics are not addictive over H4.

The final issue is that the structure of the sales teams changes frequently.
We can assume that the structure changes one time per year for every sales team and that every year several temporary sales teams are created to handle new products or new promotions.

My question is how to create the DB and the AS structure to handle this?

Making the design very simple, I have one fact table with sales and dimensions time and product.

• How would you model the dimension(s) for territory?
• In the DB should I have 1 table for territory and 1 extra table for each team? (making it a snowflake dimension)
• Any other general thoughts about this?

Many thanks,
Hugo

[VHF]

What about creating a Team dimension with one record for each regular or temporary team? This dimension would allow you to "name" each team and supply any other general attributes (department affiliation, etc.)

You would then create a bridge table to connect sub-regions and teams. A sub-region could appear in more than one team.

SSAS should handle this correctly (not double count) following same pattern as when a bank account has multple owners.