Hierarchies with facts at every level

View previous topic View next topic Go down

Hierarchies with facts at every level

Post  friklubbe on Thu Aug 11, 2011 3:57 pm

Good day all.
We run a mobile telecoms DW storing network element related performance mgmt (PM) data.
There are various hierarchies with PM facts at almost (99% of the time) every level of the hierarchy.

Here is a simple example:
Hierarchy of elements from lowest level to highest level.

radio (rx) > cell (ci) > base transmit station (bts) > base station controller (bsc) > region > country

Each of these elements has its own attributes (40-60 attribs) which we should track and keep history of changes. SCD2 is sufficient.
Each of these elements has its own fact table/s for various PM stats.

Here is the challenge:
We need to be able to query each level of the hierarchy/element and its facts.
We need to 'roll-up' the facts to the parent level/element and higher.
We need to trend the performance over time at each level of the hierarchy.
Each of the child elements/levels can change parent element/level at least 3 times a day.
Fact records (transactions) are added every 15 mins.

Question:
Do we model each level of the hierarchy into its own dim and then join all the dims together with surrogate keys?
Do we join the facts at each level to its respective dim and join all the dims together in the correct hierarchy?
Do we create a single dim with the complete hierarchy and all attributes from all dims in a denormalized table?

Anybody with experience on mobile telecoms DW's?
Any suggestions would be great please.

Frik

friklubbe

Posts : 1
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Hierarchies with facts at every level

Post  ngalemmo on Thu Aug 11, 2011 4:55 pm

Do we model each level of the hierarchy into its own dim and then join all the dims together with surrogate keys?

Yeah, sort of. Use a subtype cluster with a general dim for all types and type specific dims with attributes. Both the general and specific dims share the same PK, so each type specific dim has a mutually exclusive set of keys. The general dim should hold common attributes and would be used to support the hierarchy. Since the keys are mutually exclusive you need only one FK on the fact to support both general and type specific queries. Join to the dim table that makes sense for the query.
avatar
ngalemmo

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

View user profile http://aginity.com

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