2

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!

0

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.

  • I think the use case here is different than the Kimball example because there is generally an order of magnitude difference between vertices and edges in a graph. Flattening a graph with a sql view that calculates the transitive closure is again many orders of magnitude beyond their ~5 to 1 large table join.... regardless, how would I configure such a hierarchy in SSAS after flattening the dimension? If I choose the parent child node ids, don't I end up with the same problem...having all days versions of one node being the parent of all days versions of children? – Jonny May 30 '16 at 5:49
  • Thanks for your comment. I've updated my answer. If it is "fast" moving dimension then obviously the answer should be different. – Hila DG May 30 '16 at 6:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.