Sample Header Ad - 728x90

SSAS Cube for tracking changes in parent child relationship over time

2 votes
1 answer
614 views
I would like to build an SSAS cube which tracks how objects in a graph who's edges represent a "belongs to" relationship change over time (daily). There are two components to the change: 1. which object belongs to which 2. attributes of each object. Here is my schema: fact.Edge: the_date date parent_id int child_id int fact.Vertex: the_date date id int attribute1 int attribute2 int ... attributen int dim.attribute{1...n}: id int value1 nvarchar(64) value2 nvarchar(64) ... valuem nvarchar(64) These tables get new data once daily. If nothing changes, then there are two copies of the exact same data in the two fact tables with sequential dates. I would like to know if it is possible to define a parent child hierarchy in SSAS based on the fact.Edge table referencing itself (via child_id->parent_id) but also only when the_date = the_date. I am new to SSAS, but it seems only one attribute can be the parent attribute. Are there any workarounds? Additionally, is it possible to treat the vertex table as two "fact" related dimensions -- ie parent_vertex and child_vertex? Or else do I need to include edges with either a null parent_id or null child_id and choose the other to have the only vertex reference? If my questions don't quite make sense (likely due to my limited SSAS experience), is there an example cube definition that demonstrates best practices for this case? I'd appreciate any insights you might have!
Asked by Jonny (121 rep)
Sep 29, 2015, 11:31 PM
Last activity: Feb 14, 2025, 12:01 PM