Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Transaction with Multi level Hierarchy

4 posters

Go down

Transaction with Multi level Hierarchy  Empty Transaction with Multi level Hierarchy

Post  VS.Purushothaman 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 -
Transaction with Multi level Hierarchy  Catego11
2. Region -
Transaction with Multi level Hierarchy  Region10
3. Business Unit -
Transaction with Multi level Hierarchy  Bu_hie10
4. Contracts -
Transaction with Multi level Hierarchy  Contra10

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
Transaction with Multi level Hierarchy  Contra11
2. Contract - Category Mapping
Transaction with Multi level Hierarchy  Contra12
3. Contract - Business Unit Mapping
Transaction with Multi level Hierarchy  Contra13
4. Contract Details
Transaction with Multi level Hierarchy  Contra14

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 : 40

Back to top Go down

Transaction with Multi level Hierarchy  Empty Re: Transaction with Multi level Hierarchy

Post  VS.Purushothaman 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
Transaction with Multi level Hierarchy  Contra15

5. Contract Region Bridge
Transaction with Multi level Hierarchy  Contra16

6. Contract Business Unit Bridge
Transaction with Multi level Hierarchy  Contra17

7. Contract Fact (It had other dimensions like Contract Type, Issue Date, Issuing Authority etc)
Transaction with Multi level Hierarchy  Contra18

VS.Purushothaman

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

Back to top Go down

Transaction with Multi level Hierarchy  Empty Re: Transaction with Multi level Hierarchy

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Transaction with Multi level Hierarchy  Empty Flattened hierarchy

Post  gvarga 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

Back to top Go down

Transaction with Multi level Hierarchy  Empty Re: Transaction with Multi level Hierarchy

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

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

http://aginity.com

Back to top Go down

Transaction with Multi level Hierarchy  Empty Re: Transaction with Multi level Hierarchy

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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