Hierarchies with facts at every level
2 posters
Page 1 of 1
Hierarchies with facts at every level
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
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
Re: Hierarchies with facts at every level
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.
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.
Similar topics
» Modelling help for multiple level facts
» How to model Facts with Conformed Dims on different grain level in BO XI universe
» Coupons At the Order level not the Product Level
» Allocating Header Level FACT table Dimensions FKs into Line Level Fact
» How best to model Timesheet facts against Sales Order facts
» How to model Facts with Conformed Dims on different grain level in BO XI universe
» Coupons At the Order level not the Product Level
» Allocating Header Level FACT table Dimensions FKs into Line Level Fact
» How best to model Timesheet facts against Sales Order facts
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum