Transaction with Multi level Hierarchy

View previous topic View next topic Go down

Transaction with Multi level Hierarchy

Post  VS.Purushothaman on Tue Feb 03, 2015 2:22 pm

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 -
CategoryContract CountContract Value
Hardware4$50200
  b. Level 2 -
CategoryContract CountContract Value
Computer3$50200
Laptops2$0
  c. Level 3 -
CategoryContract CountContract Value
Desktop4$40200
Office Equipments2$35000
  d. Level 4 -
CategoryContract CountContract Value
Mouse1$200
Keyboard0$0
Monitor0$0
CPU0$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


Last edited by VS.Purushothaman on Tue Feb 03, 2015 11:05 pm; edited 1 time in total

VS.Purushothaman

Posts : 5
Join date : 2015-02-03
Age : 33

View user profile

Back to top Go down

Re: Transaction with Multi level Hierarchy

Post  VS.Purushothaman on Tue Feb 03, 2015 2:49 pm

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

1. Category Dimension


CategoryLevelParent Category
Hardware1Hardware
Computer2Hardware
Office Equipments2Hardware
Desktop3Computer
Laptop3Computer
Mouse4Desktop
Keyboard4Desktop
Monitor4Desktop
CPU4Desktop
2. Region Dimension


RegionLevelParent Region
Americas1Americas
North America2Americas
Carribbean2Americas
South America2Americas
Cuba3Carribbean
Bahamas3Carribbean
Barbados3Carribbean
Jamaica3Carribbean
Asia1Asia
Central Asia2Asia
Eastern Asia2Asia
Southern Asia2Asia
South Eastern Asia2Asia
Bhutan3Southern Asia
Nepal3Southern Asia
India3Southern Asia
Maldives3Southern 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)

VS.Purushothaman

Posts : 5
Join date : 2015-02-03
Age : 33

View user profile

Back to top Go down

Re: Transaction with Multi level Hierarchy

Post  nick_white on Wed Feb 04, 2015 8:15 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Flattened hierarchy

Post  gvarga on Fri Feb 06, 2015 4:40 pm

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
1    Hardware       Hardware
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

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: Transaction with Multi level Hierarchy

Post  ngalemmo on Sat Feb 07, 2015 5:47 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Transaction with Multi level Hierarchy

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum