Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
 Posted - 2007-10-31 : 07:24:06
|
i guess really i'm looking for opinions and / or experiences of modelling organisational hierarchies.... anyone?i'm in the process of creating a logical model (very early stages) of a new datawarehouse. The current OLTP schema stores it as a self referencing key (i.e. parent_group_id). The performance problems involved in aggregating to different levels of the hierarchy causes no end of complaints from customers, as i'm sure you can imagine. So, now we are modelling a datawarehouse for their reporting requirements, i obviously want it to be better  so far i've considered...1) keeping it as it is2) allocating a 'node' id and storing the left / right nodes in the hierarchy tree. we've used before when reporting over the OLTP system with report developers building it 'on the fly' as part of their report. (better explanation than mine... http://www.dbpd.com/vault/9811/kamfn.shtml )3) an intermediate table storing its 'position' in the hierarchy? not really played with this yet but mr kimball seems to like it  see... http://www.dbmsmag.com/9809d05.html4) denormalising it completely. i.e. level_1, level_2, level_3, Level4.... for every fact? the advantage of this is that it makes aggregation at any level really easy. the problem is that each of our customers has a different organisation hierarchy, with a different number of levels, and given the OLTP schema there is no limit to the number of levels in a hierarchyso... thoughts / experiences anyone?Em |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-31 : 09:26:53
|
| Celko had a lot to say about storing hierarchies.For simplicity I like to try and get away with something like this which works on the parent-child model - I will maintain the work table in real time for efficiency (use varchar(max) for the level.http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.htmlReally depends on the reporting needs.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-10-31 : 09:53:02
|
| cheers Nigelrequirements are 'yet to be defined' (lol). so far i'm using existing reports as the starting point though. we know that they will need to 'roll up' aggregations to differant / multiple levels of the hierarchy. for example... give me the total number of emails sent grouped by department and business unit (level3 and level1)i'll be playing with this for the next few weeks so work through your example tooEm |
|
|
|
|
|
|
data modelling of hierarchies