SSAS Cube for tracking changes in parent child relationship over time

Question

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!

Answer 1

Parent-child relationship in SSAS isn't straight forward as we might wish. From my experience in SSAS 2008 linking them both together wasn't simple at all. In those type of cases the most recommended solution would be to flatten the relationship with a (SQL) view in which you link the parent to the child with the date attribute. That view becomes your dimension and on which you check the changes over time. you can find more data about this in here:
http://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/
and here:
http://www.kimballgroup.com/2012/05/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/
Alternatively: If you need both the Parent id and the Child in the fact table (because you need to query on both) then in that case I would go on a different direction and include both id's in my fact table. This way you can monitor changes across time as you would for any other dimension.