I am planning to render a database which relates several nodes parenthood in an E/R fashion as an OLAP cube. My model contemplates a tree which needs to be dynamic, configurable.
In order to end up with a dimensional representation of the database, I have to convert my E/R database to an star schema, and the natural way to represent my tree is by mapping it to a dimensional hierarchy.
My problem comes when that dynamic tree needs to be configurable, as for now, I can only conclude that every time that tree changes, the whole dimension table needs to be denormalized according to the new tree schema
**FACT_TABLE** **DIM_TABLE**
DIM_TREE METRIC NODE_LEVEL1 NODE_LEVEL2 NODE_LEVEL3
dim_id leaf1_value dim_id1 root_1 intermediate_1 leaf_1
dim_id2 leaf2_value dim_id2 root_1 leaf_2
What if I want to change the tree structure (i.e, add a level, change its semantic meaning...)? Do I have to change dimensional schema again to define new fields and hierarchies?
In addition, sometimes I am asked to provide several logical trees that map sets of common leafs. That means that either I create a new dimension for the new logical aggregation or I denormalize it within DIM_TABLE. In both cases, I need to modify schema and I break the granularity, as the value is counted as many times as aggregation trees reference it. Am I right?
Is dimensional/OLAP modeling suitable for those requirements? If so, what's the best approach aside from modeling a db schema for every aggregation tree and changing hierarchy/taxonomy?