Analyzing Measures Between Two Dates

[intapiuser]

Analytical Need

In many use-cases, there’s a need to measure entities throughout their life cycle which spans from their ‘Start’ date to ‘End’ date.

For example insurance company wants to count how many active policies there are on any given day. The insurance policy has ‘start’ and ‘end’ date, and in between those dates is considered active.

Modeling Challenge

In most cases, entities are stored in the DB as one record with start-date and end-date.

When trying to measure the entities over time based on those dates, each entity will be measured only in those two dates, and won’t be measured in the dates that are in between.

In the example: if a policy started on 02/01/2018 and ended on 04/30/2018, it will be counted as active twice (in the above two days), but won’t be counted in the days between them, like 02/02/2018, 02/03/2018, … , 04/29/2018.

Solution

Option A

This option supports dynamic calculations in the Web Application but results in a larger table compared to option B.

Implementation steps:

1. Create a date dimension table by selecting the unique dates of the Start and End fields or import a date dimension table (you can use this date dimension file).

2. Create a custom SQL table that holds a row for every date between the Start and End date of each entity. SQL:

SELECT DISTINCT D.Date, F.EntityID 
FROM [Dim Dates] D 
JOIN [Fact] F 
ON  F.DateStart<=D.Date AND (F.DateEnd>=D.Date OR F.DateEnd is Null)

3. The JOIN in the above SQL considers both the Start and End date as valid dates for the entity, so those dates will be created as rows. If the lifecycle of the entity shouldn’t include one of these, remove the equal option (‘=’). For cases in which there isn't an 'End Date' (if it was not logged yet), the JOIN is also on Null values.

4. Example:

Original Table

New Table

Option B

This option aggregates the entities in the ElastiCube per date, so will result in a smaller table, but less dynamic in the Web Application compared to option A.

Implementation steps:

1. Create a date dimension table by selecting the unique dates of the Start and End fields or import a date dimension table (you can use this date dimension file).

2. Create a custom SQL table that aggregates the entities per day. The aggregation should be defined based on the business question (in the example the measure is ‘count’). SQL:

SELECT DISTINCT D.Date, count(F.EntityID) 
FROM  [Dim Dates] D 
JOIN [Fact] F 
ON F.DateStart<=D.Date AND (F.DateEnd>=D.Date OR F.DateEnd is Null)
GROUP BY D.Date

3. The JOIN in the above SQL considers both the Start and End date as valid dates for the entity, so those dates will be created as rows. If the lifecycle of the entity shouldn’t include one of these, remove the equal option (‘=’). For cases in which there isn't an 'End Date' (if it was not logged yet), the JOIN is also on Null values.

4. Example:

Original Table

New Table