Recursive Hierarchy Data Modelling

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

www.dbmsmag.com/9809d05.html

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

Many to many relationships are never acceptable.

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

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.

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

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).

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,

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.

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

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