Transaction with Multi level Hierarchy

Hi,

Im trying to design a data mart for Procurement domain, especially the contract process. Following are the data points for your reference -
1. Category -

2. Region -

3. Business Unit -

4. Contracts -


In OLTP the mapping gets stored at the level mapped and to all its children in the hierarchy. The way this information is stored in OLTP is -
1. Contract - Region Mapping

2. Contract - Category Mapping

3. Contract - Business Unit Mapping

4. Contract Details


The problem here are multiple -
1. The Contract can get mapped at any level in the Hierarchy (Both Category and Region)
2. The Contract can be mapped to multiple categories (Contract 3 in above example)

The reporting Requirement  is -
1. Category Wise Contract Value -
   a. Level 1 -

Category	Contract Count	Contract Value
Hardware	4	$50200

  b. Level 2 -

Category	Contract Count	Contract Value
Computer	3	$50200
Laptops	2	$0

  c. Level 3 -

Category	Contract Count	Contract Value
Desktop	4	$40200
Office Equipments	2	$35000

  d. Level 4 -

Category	Contract Count	Contract Value
Mouse	1	$200
Keyboard	0	$0
Monitor	0	$0
CPU	0	$0

Im trying to figure out the best way to model this kind of OLTP's into a proper dimensional model.

Regards
V.S.Purushothaman

Just to give you guys the way i have tried to model these tables are -

1. Category Dimension

Category	Level	Parent Category
Hardware	1	Hardware
Computer	2	Hardware
Office Equipments	2	Hardware
Desktop	3	Computer
Laptop	3	Computer
Mouse	4	Desktop
Keyboard	4	Desktop
Monitor	4	Desktop
CPU	4	Desktop

2. Region Dimension

Region	Level	Parent Region
Americas	1	Americas
North America	2	Americas
Carribbean	2	Americas
South America	2	Americas
Cuba	3	Carribbean
Bahamas	3	Carribbean
Barbados	3	Carribbean
Jamaica	3	Carribbean
Asia	1	Asia
Central Asia	2	Asia
Eastern Asia	2	Asia
Southern Asia	2	Asia
South Eastern Asia	2	Asia
Bhutan	3	Southern Asia
Nepal	3	Southern Asia
India	3	Southern Asia
Maldives	3	Southern Asia

3. Business Unit Dimension

Business Unit

HR

Legal

Marketting

Merchandising

Operations

4. Contract Category Bridge


5. Contract Region Bridge


6. Contract Business Unit Bridge


7. Contract Fact (It had other dimensions like Contract Type, Issue Date, Issuing Authority etc)

A few thoughts/suggestions...

1. Make the grain of your Fact table at least Contract and Category - so you don't have a multi-valued Category issue

2. How you define a data structure to support hierarchical reporting is normally driven by the type of hierarchy (fixed, ragged, etc.) and the requirements of your BI Tool and is not really a Dimensional modelling issue. For example, OBIEE has a wizard that will build and populate the hierarchy table it requires, based on the parent-child relationships in your Dimensional model that you feed into the wizard

3. You can't drill down a hierarchy to a lower level than that for which you have data. So in your example, if a contract is assigned to Southern Asia I assume you are not expecting to be able to drill-down to Bhutan/Nepal/India/Maldives? You can only drill up

If your hierarchies are fix level ones, you can flatten them.
For the CATEGORY in the following way:

Key Name            1.Level         2.Level.        3.level     4.level
2    Computers     Hardware      Computers
3    Office eqv.    Hardware      Office eqv.
4    DEsktop        Hardware      Computers    Desktop
5    Mouse          Hardware      Computers    Desktop     Mouse
…..

So you will be able to drill down.
You can make it for the the REGION as well.
There will be a simple star with the following dimensions:
DATE, CONTRACT, CATEGORY, REGION, BUSINESS UNIT and 1 fact table:
CONTRACT_FACT

The model doesn't make much sense.

What is the purpose of the contract/region bridge?  According to your own data, the region for contract 1 is Caribbean.  Why would there be other relationships in this bridge?  Why would there be a bridge at all?

The problem here is you are trying to relate events to hierarchies rather than relate the hierarchy to itself.  

The contract has only one region associated to it.  It is the region that has the hierarchy.  You model the hierarchy as parent-child relationships between the regions.  For a structured hierarchy you can use the model suggested by gvarga.  For a recursive or unstructured hierarchies, you would use a hierarchy bridge table based on the parent-child relationship maintained in the region dimension.  You associate the contract with other regions by joining the region it belongs to to the child region in the bridge (You would have a contract fact table that includes region as a dimension).  The bridge would contain all possible parents as well as the level of the child and parent (along with other things such as distance, leaf flag, sort key, etc…).  The bridge would also contain an 'identity' row, where a child points to itself (levels are the same) in order to include itself if reporting is done at that level.

By containing the hierarchy within itself (either as a flat structure or a bridge) you can apply the hierarchy to any fact that references a member of that hierarchy. Another huge advantage is if the hierarchy changes, you do not need to re-associate contracts with regions. You simply replace the bridge which represents the region-region relationships.