Recursive Hierarchy Data Modelling

View previous topic View next topic Go down

Recursive Hierarchy Data Modelling

Post  jaiveeru on Thu Apr 16, 2009 10:14 am

Hiiii

I am pretty new to data warehousing, now after reading a little literature on Data Modelling from our Guru's book Data Warehousing Toolkit I am guessing what could be done to create a data model for my problem.
I have a recursively hierarchical database which means it contains tree structure. And hence I have challenges like

1. How to model such a relationship.
2. How to effectively let the business user select a piece of that tree structure etc.

Any directions or hints will be appreciated.


Regards,
Manish

jaiveeru

Posts : 14
Join date : 2009-04-16

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  BoxesAndLines on Thu Apr 16, 2009 6:32 pm

www.dbmsmag.com/9809d05.html
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  jaiveeru on Fri May 01, 2009 9:48 am

Hiiii

Thanks for the response.

I did implement my dimensions using a bridge in between but if you see the relationship between tables
i.e. Bridge table to fact table

are coming many to many... I am not sure if this is going to be acceptable !!!

Even the solution you gave me doesn't take that matter into consideration (may be it's not worth considering :p)
Whole thing is would I still be able to publish a model with a many to many relationship between dimension and fact?

Please help

Regards,
Manish

jaiveeru

Posts : 14
Join date : 2009-04-16

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  BoxesAndLines on Sat May 02, 2009 11:14 am

Many to many relationships are never acceptable.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  radsampath on Wed May 06, 2009 9:35 am

Can you explain how its becoming many to many ,please provide some example

radsampath

Posts : 8
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  ngalemmo on Mon May 18, 2009 2:28 pm

Technically it is many to many (between the fact and the bridge), but in practice it doesn't work out that way. The many-to-many relationship is the result of exploding the heirarchy which maps out every possible parent-child relationship at all levels of the hierarchy. In actual practice a query is only going to look at a child and its parents or a parent and its children for any given fact row.

The bridge works really well. It is just a matter of framing the query so it makes sense.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  jaiveeru on Fri May 22, 2009 4:05 am

radsampath wrote:Can you explain how its becoming many to many ,please provide some example

Straight explaination is we have got groups and groups have tree structure. Meters can be anywhere in the groups. An example could be:


Group A
Group B M1
Group C M2
M3
Group X M4
M5

Since this makes a recursive hierarchy between groups hence I exploded the group as follows

My bridge is:

Group A --- Group B --- M1
Group A --- Group C --- M2
Group A --- Group C --- M3
Group A --- Group X --- M4
Group A --- Group X --- M5
Group B --- Group B --- M1
Group B --- Group C --- M2
Group B --- Group C --- M3
Group C --- Group C --- M2
Group C --- Group C --- M3
Group X --- Group X --- M4
Group X --- Group X --- M5


My fact in this case might look like

M1 --- 01/01/2004 --- 00:00:00 --- 10
M1 --- 01/01/2004 --- 00:00:01 --- 12
M1 --- 01/01/2004 --- 00:00:02 --- 19
M1 --- 01/01/2004 --- 00:00:03 --- 87
.
.
.
You can see along M1 can have nultiple entries in both table. BoxesAndLines please comment...


Last edited by jaiveeru on Fri May 22, 2009 4:11 am; edited 1 time in total

jaiveeru

Posts : 14
Join date : 2009-04-16

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  jaiveeru on Fri May 22, 2009 4:08 am

ngalemmo wrote:Technically it is many to many (between the fact and the bridge), but in practice it doesn't work out that way. The many-to-many relationship is the result of exploding the heirarchy which maps out every possible parent-child relationship at all levels of the hierarchy. In actual practice a query is only going to look at a child and its parents or a parent and its children for any given fact row.

The bridge works really well. It is just a matter of framing the query so it makes sense.

I had this response from Boxes and Lines above that many to many is not never acceptable, is it really not? Now I doubt.
I had believed him because in my visual studio Report Model Project I was not able to publish such a relationship in the model.
This was the initial design that I had come up with. If this is the design I understand completely but I wonder how in Visual Studio's Report Model project some one can publish a many to many relationship (across meters).

jaiveeru

Posts : 14
Join date : 2009-04-16

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  BoxesAndLines on Fri May 22, 2009 7:50 am

A many to many is not acceptable. Take for example two entities, StockBroker and Stock. A StockBroker can trade many Stocks. Stocks can be traded by many Stockbrokers. Where do you store how many stocks a StockBroker trader? It doesn't fit in either of the existing entities. The solution for 3NF modeling is called an associative entity. The solution for dimensional modeling is called a bridge table.
Good luck,
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

It works !!!

Post  jaiveeru on Mon Aug 24, 2009 5:48 am

ngalemmo wrote:Technically it is many to many (between the fact and the bridge), but in practice it doesn't work out that way. The many-to-many relationship is the result of exploding the heirarchy which maps out every possible parent-child relationship at all levels of the hierarchy. In actual practice a query is only going to look at a child and its parents or a parent and its children for any given fact row.

The bridge works really well. It is just a matter of framing the query so it makes sense.

Sorry for the delay in response. But I think it was important to share with all that what is quoted above holds good.

A many to many relation like ngalemmo suggested can exist and work well at the same time. I still have concerns related to query execution time etc. but the answer I was looking for was found by doing exactly what was suggested above. ( In other words, A many to many relationship at compile time becomes a one to many at runtime:)

Thanks so much ngalemmo for your deep understanding and suggestion.


Last edited by jaiveeru on Mon Aug 24, 2009 5:52 am; edited 1 time in total (Reason for editing : typo)

jaiveeru

Posts : 14
Join date : 2009-04-16

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  revdpoel on Fri Aug 26, 2011 5:32 pm

A pity that this link, www.dbmsmag.com/9809d05.html, doesn't work anymore
Is there another link explain clearly and simple the principle of recursive dimensions?

thanks
Ron

revdpoel

Posts : 24
Join date : 2010-06-11

View user profile

Back to top Go down

Re: Recursive Hierarchy Data Modelling

Post  John Simon on Sun Aug 28, 2011 7:01 pm

Try my blog: http://jsimonbi.wordpress.com/2011/01/14/sql-hierarchies-parent-child/

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Recursive Hierarchy Data Modelling

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