Slowly Changing Dimension handling

1

To use Fibery as a KM tools at scale, a typical problem that arises in those systems/datastores is how to handle data and relationships that changes over time. Slowly changing dimension - Wikipedia

Let’s take example with type Contacts (representing people), Organizations and Events (conference, webinar, etc).
For simplicity let’s say that a Contact has only 1 current Organization at time, ie his employer. But to hold information on past employer, I’ve added another relation to Organization (many to many) called “Past Organizations”.

  • Fields grouped In a Contact Entity the field “position title” and relationship “Organization” are linked, since they represent my role at the current employer. Ideally when I change job, my past position title should be saved with the “Past Organizations”. Best way to solve that should be to have attributes on the relationship itself (or “grouped” fields). If we want to record the change of an attribute on a relationship as a first class element that is visible as a “Past …” field and queryable in lookups, we could imagine a checkbox “create associated historical field” for each field/relationship definition.

  • Lookup when underlying relationship value change: easier with a real example. I have an Event “Conf A” that has a relationship with the Contact type (representing “Attendees”). Let’s say that “Conf A” has been linked to Contacts “Jeff” and “Bob”. Being Contacts, they are linked to their current Organization, “Amazon” for “Jeff” and “Google” for “Bob”. Since I would like to list all the companies that have attended the conf, I’ve added a lookup field “Organization Attendees” to Event, that take the Organization of the Contacts. So I can see that “Amazon” and “Google” where there. A month pass and someone in my team updates “Bob” entity (even better automatically using clearbit or linkedin data!) since he moved to “M$”. Now my “Conf A” will show “Amazon” and “M$” as Organization Attendees…
    Usually

Big part to solve those is to record the intent when editing:

  • is the new value fixing a previous error (we wrongly assigned “Bob” to “Google”, he was working all that time at “M$”) => we don’t want to record the old value as a “Past Organization” just update the value and where it is linked (keep the change visible in the audit trail, indicating this is a fix to a data entry error)
  • or is it a change: “Bob” moved to a new company. We want then to move to add a “Past Organization” entry (with the corresponding start/end dates).

Usually, event sourcing is used to record those intent.

That’s something all CRM are doing wrong, hard to keep the history of companies someone worked with, keeping the linked Opportunity to the Contact in the context of his previous company.

@mdubakov any thoughs on those topics?

1 Like
2

Lots of good ideas/suggestions here.
I can think that there are (at least) two ways of looking at it.

  1. It can be useful for relationships to have properties themselves. So for example, a relation to an organisation could have a checkbox to indicate that it is ‘current’.
    See here:
    Relationship properties
    although this did not explicitly mention the exact use case being discussed here.

  2. An entity has a history, and sometimes it’s useful for other items to link to a specific historical version of an entity rather than the current one.
    See here:
    [APPROVED] Versioning in Rich Text Fields (and entities as a whole, too) - #6 by Chr1sG

3 Likes