I plan to provide a database that links several parent nodes in an E / R mode as an OLAP cube. My model is considering a tree, which should be dynamic, customizable.
To get a dimensional representation of a database, I need to convert my E / R database into a star schema, and the natural way to represent my tree is to map it to a dimensional hierarchy.
My problem arises when this dynamic tree needs to be set up, because now I can only conclude that every time this tree changes, the entire measurement table must be denormalized in accordance with the new tree diagram
**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 value ...)? Do I need to resize the dimensioning again to define new fields and hierarchies?
In addition, sometimes I am asked to provide several logical trees that display sets of common leaves. This means that either I create a new dimension for a new logical aggregation, or I denormalize it in DIM_TABLE. In both cases, I need to change the scheme, and I break the granularity, since the value is counted as many times as the aggregation trees refer to it. I'm right?
Dimensioning / OLAP is appropriate for these requirements? If so, what is the best approach besides modeling the db schema for each aggregation tree and changing hierarchy / taxonomy?