Mark Streutker

Handling EAV-data: Timelines

2010-02-22T18:29:00Z

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

EntityID	Entity
1	Employee

Table: Attribute

AttributeID	Attribute
100	Name
101	Birthday
102	Nationality
103	Marital Status

Table: EAV_Data

ID	EntityID	AttributeID	Value	ValidFrom	ValidTo
1234	1	100	John	01-01-2000	31-12-2999
1234	1	101	12-12-1970	01-01-2000	31-12-2999
1234	1	102	English	01-01-2000	02-02-2009
1234	1	102	Dutch	02-02-2009	31-12-2999
1234	1	103	Single	01-01-2000	03-03-2010
1234	1	103	Married	03-03-2010	31-12-2999

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 query yields the following result:

ID	ValidFrom	ValidTo	Name	Birthday	Nationality	Marital_Status
1234	2000-01-01	2009-02-02	NULL	NULL	English	NULL
1234	2000-01-01	2010-03-03	NULL	NULL	NULL	Single
1234	2000-01-01	2999-12-31	John	12-12-1970	NULL	NULL
1234	2009-02-02	2999-12-31	NULL	NULL	Dutch	NULL
1234	2010-03-03	2999-12-31	NULL	NULL	NULL	Married

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:

ID	ValidFrom	ValidTo
1234	2000-01-01	2009-02-02
1234	2009-02-02	2010-03-03
1234	2010-03-03	2999-12-31

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:

ID	ValidFrom	ValidTo	Name	Birthday	Nationality	Marital_Status
1234	2000-01-01	2009-02-02	John	12-12-1970	English	Single
1234	2009-02-02	2010-03-03	John	12-12-1970	Dutch	Single
1234	2010-03-03	2999-12-31	John	12-12-1970	Dutch	Married

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.

Is ISNUMERIC numeric?

2009-10-30T19:14:00Z

In SQL, if you’re not sure if a value can be cast to a certain datatype there are some built-in functions that can help you out.
Take the following construction, where col_Value is a CHAR(4):

   1: SELECT CASE ISNUMERIC (col_Value)

   2:             WHEN 1 THEN CAST (col_Value AS INT)

   3:             ELSE NULL

   4:             END

At first glance, this solution seems to handle col_Value just fine. If col_Value contains a numeric value it will be cast to an INT and if it’s not, a NULL value will be returned.
The developers expectations seem to be met: in this scenario no typecasting errors can occur. Or can they?

Truth is, the ISNUMERIC function is often misinterpreted (at least, that’s my personal experience).
According to MSDN the definition of ISNUMERIC is: Determines whether an expression is a valid numeric type.
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type.

The catch is, a returnvalue of 1 means that the expression can be cast to at least one numeric datatype, rather than all available numeric datatypes supported by SQL Server.
The following queries illustrate this behavior:

   1: SELECT ISNUMERIC ('100')        -- Returns 1

2:

   3: SELECT CAST ('100' AS FLOAT)    -- Returns 100

   4: SELECT CAST ('100' AS NUMERIC)  -- Returns 100

   5: SELECT CAST ('100' AS MONEY)    -- Returns 100,00

   6: SELECT CAST ('100' AS INT)      -- Returns 100

7:

8:

   9: SELECT ISNUMERIC ('1e2')        -- Returns 1

10:

  11: SELECT CAST ('1e2' AS FLOAT)    -- Returns 100

  12: SELECT CAST ('1e2' AS NUMERIC)  -- Error converting data type varchar to numeric.

  13: SELECT CAST ('1e2' AS MONEY)    -- Cannot convert a char value to money. The char value has incorrect syntax.

  14: SELECT CAST ('1e2' AS INT)      -- Conversion failed when converting the varchar value '1e2' to data type int

15:

  16: SELECT ISNUMERIC ('€100')       -- Returns 1

17:

  18: SELECT CAST ('€100' AS FLOAT)   -- Error converting data type varchar to float.

  19: SELECT CAST ('€100' AS NUMERIC) -- Error converting data type varchar to numeric.

  20: SELECT CAST ('€100' AS MONEY)   -- Returns 100,00

  21: SELECT CAST ('€100' AS INT)     -- Conversion failed when converting the varchar value '€100' to data type int.

So in conclusion; if an expression has to be cast into a specific numeric datatype, checking ISNUMERIC may not be sufficient. This is of course heavily dependant on the possible values of the expression, but if this is unpredictable extra checks may be necessary

Handling EAV-data

2009-06-01T16:38:00Z

In my line of work I often have to develop reports on all kinds of environments, but most of the time the underlying databases are limited to relational or dimensional models.
A few months ago, however, I was faced with the tedious task challenge to build a couple of reports on a system that was entirely based on an EAV-architecture.
Even though I was familiar with the technique, I have never put it to use for reporting purposes.
After doing some research on the internet, looking for best practices and guidelines, I noticed that the amount of information about reporting on EAV-data was limited at best.
That’s why I decided to dedicate a few articles on my personal experiences (and pitfalls) with the wonderful world of EAV.

There are a lot of interesting challenges to overcome when working with EAV-data, but I will try to start out with a few simple examples.
In this first article I will focus on the selection and formatting of EAV-data, the more complex techniques (like handling timelines) will be addressed in future articles.

First of all, let’s dive into some of the basics concerning EAV-data (since this type of datamodelling isn’t very common in a reporting environment).
EAV is an abbreviation for Entity – Attribute – Value. Data stored in this type of model usually consists out of three ‘base’ columns:

Entity : An Entity is a ‘thing’, and object. Examples could be ‘Person’, ‘Employee’, ‘Contract’.
Attribute : An Attribute describes a part of the Entity. If we take the Entity ‘Person’ as an example, attributes could be ‘Name’, ‘Birthday’, ‘Nationality’, etc.
Value : The Value of the Attribute, i.e. ‘John’, ’12-12-1970’, ‘English’.

In a relational datamodel, an Entity would most likely be a table, a Attribute would correspond with a column and a Value would match a field.
Following table is a representation of a very simple EAV dataset.

Table: Entity

EntityID	Entity
1	Employee
33	Contract

Table: Attribute

AttributeID	Attribute
100	Name
101	Birthday
102	Nationality
360	Type
361	Startdate

Table: EAV_Data

ID	EntityID	AttributeID	Value
1234	1	100	John
1234	1	101	12-12-1970
1234	1	102	English
5678	33	360	Fulltime
5678	33	361	01-01-2009

In the above example, we notice a few things.
First of all, in a relational datamodel we probably would have had a ‘Employee’ table and a ‘Contract’ table.
In our EAV example, most the data is stored in 1 single table. Both the tables (Entities) and columns (Attributes) are stored in a row-wise manner, therefore there is no ‘fixed’ datastructure. Second, all the ‘Values’ are stored in the same column, which implicates that we have 1 datatype to store everything (varchars, datetimes, ints, etc.).
To keep this example simple, ill ignore this ‘datatype-problem’ for now.

EAV-modelling works exceptionally well for systems that need a great amount of flexibility.
Both ‘tables’ and ‘columns’ can be dynamically added without changing the underlying model.
Systems that need this amount of flexibility are i.e. clinical applications.
So instead of having several (fixed) smaller tables, you end up with one (flexible) huge table.

While this is all fun and games for systems that storing information, things can get a bit challenging when you need to build reports.
First challenge: How do you retrieve EAV data?
To demonstrate the differences between a relational model and a EAV model when building queries, I’ll try to make an easy selection on all employee’s with the name ‘John’ that have the ‘English’ nationality.

The relation employee table in this example has the following columns: ID, Name, Last Name, Birthday, Nationality and Marriagal status.

Example 1: Relational table

        1: SELECT * 

       2: FROM Employee 

       3: WHERE Name = 'John' 

       4: AND Nationality = 'English'

Result

ID	Name	Last name	Birthday	Nationality	Marriagal Status
1234	John	Doe	12-12-1970	English	Married
365	John	Cook	01-08-1983	English	Single

All the information from an Employee is stored in 1 record, which makes it a very easy task to select all the information we need.
In contrary to the Employee table, the EAV table has every attribute (column) stored in a new record.

Example 2: EAV table

       1: SELECT * 

       2: FROM EAV_Data

       3: WHERE Attribute = 'Name'

       4: AND Value = 'John'

Result

ID	EntityID	AttributeID	Value
1234	1	100	John
365	1	100	John

Hmm, not exactly what we wanted to see. Even though this is a correct result, the other Attributes (like Last Name, Birthday, etc) are not returned by our query.
If we were to make a report on the Employees, we have to figure a way out to present the Attributes on the horizontal axe, as if they were columns. In short, we want the rows to be converted to columns.
One way to achieve this, is to access the same table several times by means of selfjoins.

Example 3: EAV table with selfjoins

       1: SELECT  t1.ID as 'ID'

       2:       , t1.Value AS 'Name' 

       3:       , t2.Value AS 'Nationality'

       4:       , t3.Value AS Birthday

       5: FROM EAV_Data t1

       6: LEFT JOIN EAV_Data t2

       7: ON t1.ID = t2.ID

       8: LEFT JOIN EAV_Data t3

       9: ON t1.ID = t3.ID

      10: WHERE t1.Attribute = 'Name'

      11: AND t1.Value = 'John'

      12: AND t2.Attribute = 'Nationality'

      13: AND t2.Value = 'English'

      14: AND t3.Attribute = 'Birthday'

Result

ID	Name	Nationality	Birthday
1234	John	English	12-12-1970
365	John	English	01-08-1983

While this works to some extent, you’ll notice that the query for the EAV-data is much more complex that the relational one. On top of that, in order to get the same amount of columns as the relational query, we need even more selfjoins! Needless to say, using lots and lots of selfjoins is stressfull for the database and has a negative impact on the performance.
A better (and more elegant) solution is to ‘rotate’ the table by making use of the PIVOT operator that was introduced with SQL Server 2005.

Example 4: EAV table with PIVOT

       1: SELECT * FROM

       2: (

       3:     SELECT ID

       4:            , [100] AS Name

       5:            , [101] AS Birthday

       6:            , [102] AS Nationality

       7:     FROM 

       8:     (

       9:            SELECT ID, EntityID, AttributeID, Value

      10:            FROM EAV_Data

      11:     ) p

      12:     PIVOT

      13:     (

      14:            MAX (Value)

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

      16:     ) AS pvt

      17: ) AS result

      18: WHERE Name = 'John'

      19: AND Nationality = 'English'

Result

ID	Name	Birthday	Nationality
1234	John	12-12-1970	English
365	John	01-08-1983	English

By making use of the PIVOT operator we are capable of ‘rotating’ the table, which makes it possible to query the data as if it was stored in a relational table.
Of course this is a very simple example, but combined with metadata it can be a powerful technique.

Systems that make extensive use of EAV-modelling are usually backed up with a great amount of metadata, which is used to describe properties like the datatypes of attributes, the attributes that belong to a certain entity, the relation between entities, etc.
In my current project we use the metadata to generate an extra layer in the database, based on certain combinations of attributes.
This way it’s like we have a couple of hundred different (relational) tables, while everything is effectively stored in 1 big table.

I hope this was an interesting read, in my next article I’ll elaborate on the subject of handling multiple timelines