Handling EAV-data: Timelines

As promised in my previous post about working with EAV-data this post would about handling multiple timelines (or periods for that matter).
While this blog is specifically written for an EAV-environment, the technique explained in this post can be useful for other situations as well.

The problem I’m discussing in this article is about working with multiple, overlapping periods of time and the way they affect the outcome of PIVOT statements.
Because there is so little to find about this subject, I decided to make a write-up of the solution me and my colleagues came up with.

The Problem

Before diving into the technique I would like to position the problem, so first of all I’d like start off with a simple example of what’s going wrong.
Take a look at the following tables:

Table: Entity

Table: Attribute

Table: EAV_Data

They have a striking similarity with the tables I’ve used in my previous post, but this time the EAV_Data table is a bit more complex due to the added ValidFrom and ValidTo columns.
Because of these two columns, it is now possible to have multiple values  for different periods of time.

Meet John.
John is an Englishman who chose the green wetlands of Holland to make his new home. He liked it so much that he even acquired the Dutch nationality at some point.
His actions did not go unnoticed and John conquered the heart of the girl of his dreams, who he married a year later.

See underneath for a graphic representation of the situation:

Now let’s say we ignore the time-axe and just start pivoting, just like we did last time:

   1: SELECT * FROM   

   2: (  

   3:     SELECT ID  

   4:     , ValidFrom

   5:     , ValidTo 

   6:     , [100] AS Name   

   7:     , [101] AS Birthday   

   8:     , [102] AS Nationality   

   9:     , [103] AS Marital_Status   

  10:     FROM     

  11:     (       SELECT ID, EntityID, AttributeID, Value, ValidFrom, ValidTo 

  12:             FROM EAV_Data     

  13:     ) p  

  14:     PIVOT     

  15:     (     

  16:         MAX (Value)  

  17:         FOR AttributeID IN ([100], [101], [102], [103])     

  18:     ) AS pvt  

  19: ) as result

This result introduces a few new problems.

First of all, the periods overlap each other.  This is a bad thing, because now I don’t have one version the truth.

Second of all, most columns are blank (NULL). This means that in order to get a correct selection we have to combine the right periods AND merge the right values to get a complete, correct visualization of the record on a specific moment.

But what is causing this behavior in the first place? Because the PIVOT operator now ‘rotates’ the data around the ID, ValidFrom and ValidTo columns, data is only presented in a row if it matches the exact same period. This is incorrect behavior, because the name (John) is valid during the entire period and therefore should be present in every row.

What we need, is what I’d like to call an Effective Timeline.

The Solution

An effective timeline is a timeline that consist out of multiple consecutive periods, rather than multiple parallel/overlapping periods.

This way, a specific moment belongs to one period and one period only.

If we merge all the periods present in our example and remove the duplicates we get something like this:

See how this graph only has one line for every period?

But how do you achieve this programmatically?

In my project we came up with the following solution;

• We simply gather all unique start-and enddates of every period in one big collection.
• Next, we sort this collection from oldest to newest, ignoring the fact if the current record is a start- or enddate.
• Now we have a temporary collection with all possible dates, conviently ordered by date.
• Finally we join the collection with itself (one record shifted), combine all dates and effectively create a whole new set of consecutive periods.

See the sample UDF below for clarity:

   1: CREATE FUNCTION [dbo].[fn_EffectiveTimelines]

   2: (

   3: )

   4: RETURNS @l_ResultTable TABLE

   5: (

   6:  

   7:          ID INT

   8:         , ValidFrom DATETIME

   9:         , ValidTo DATETIME

  10: )

  11: AS

  12: BEGIN    

  13:  

  14:     WITH EffectiveTimelines AS

  15:     (

  16:         SELECT ID, Date, Type, ROW_NUMBER() OVER (ORDER BY  ID, Date) as Number 

  17:         FROM 

  18:         (

  19:             SELECT DISTINCT ID, Date, Type

  20:             FROM

  21:             (

  22:                 SELECT ID

  23:                     , ValidFrom AS Date

  24:                     , 1 as Type

  25:                 FROM EAV_Data

  26:                 UNION

  27:                 SELECT ID

  28:                     , ValidTo AS Date

  29:                     , 0 as Type

  30:                 FROM EAV_Data

  31:             ) as TimeSet

  32:         ) as TimeSetOrdered

  33:     )

  34:  

  35:     INSERT INTO @l_ResultTable

  36:     SELECT

  37:     t1.ID,

  38:     CASE

  39:         WHEN t1.Type = 0 THEN DATEADD(dd, 1, t1.Date)

  40:         ELSE t1.Date 

  41:     END

  42:     ,

  43:     CASE

  44:         WHEN t2.Type = 1 THEN DATEADD(dd, -1, t2.Date)

  45:         ELSE t2.Date 

  46:     END

  47:     FROM EffectiveTimelines t1

  48:     INNER JOIN EffectiveTimelines t2

  49:     ON t1.Number + 1 = t2.Number

  50:     AND t1.ID = t2.ID

  51:     AND DATEADD(dd, 1, t1.Date) < t2.Date

  52:  

  53:     RETURN;

  54: END

Running this function will give the following result:

Now that we have an unigue, effective timeline, we can now join the rest of the data to get a complete overview. See the following example:

   1: SELECT * FROM   

   2: (  

   3:     SELECT ID  

   4:     , ValidFrom

   5:     , ValidTo 

   6:     , [100] AS Name   

   7:     , [101] AS Birthday   

   8:     , [102] AS Nationality   

   9:     , [103] AS Marital_Status   

  10:     FROM     

  11:     (       SELECT eav.ID, eav.EntityID, eav.AttributeID, eav.Value, t.ValidFrom, t.ValidTo 

  12:             FROM EAV_Data eav

  13:             INNER JOIN fn_EffectiveTimelines() t

  14:             ON eav.ID = t.ID

  15:             AND eav.ValidFrom < t.ValidTo

  16:             AND eav.ValidTo > t.ValidFrom  

  17:     ) p  

  18:     PIVOT     

  19:     (     

  20:         MAX (Value)  

  21:         FOR AttributeID IN ([100], [101], [102], [103])     

  22:     ) AS pvt  

  23: ) as result

In this step the data is assigned to every period is has overlap with. Because some values are longer valid than others, it can happen that one value is assigned to multiple periods, as shown in the graph below:

 

Because the periods are now neatly aligned, the PIVOT statement will come up with the following result:

This results is a more accurate representation of the data. The NULL fields are gone and the data is now correctly divided amongst the periods.

With this technique you can pivot data with multiple timelines with little effort, it doesn’t matter if you have 3 periods or 3000: this technique will always calculate the effective timeline (albeit a bit slower ;)).

Conclusion

By making use of effective timelines working with EAV is a bit less complex.

And like I said in the beginning of this blog, calculating an effective timeline by merging periods can be useful in other situations as well. For example, if you have multiple periods that have overlap and you need to count the unique days over all periods, having a effective timeline can really speed up the process.

Share this