Converting an Accumulating Fact Table to a Transaction Table (Calculating Peak Call Volumes)

An accumulating fact table has rows that represent a process and typically has dates (amongst other keys) that represent the time of completion of a stage in the process. For some calculations they are also a pain to deal with and where possible I like to convert them into transactional tables as well which makes solving these a lot simpler. Transaction tables represent a single measurement at a point in time which is immutable – it will never change. To take a very simple example consider a call logging table that contains the start and end of a call.

create table calls
	call_id int
,	start_on datetime
,	end_on datetime

With the data in this structure questions like what is the maximum number of concurrent calls are difficult to answer without iterating over the data set. Converting it to a transaction table will make these queries trivial.

The data in the table implies two types of transaction: Call Started and Call Ended. So we can create a transaction table with a record for each call start and call end event and include a measure that represents the change to the number of calls this event represents: that is 1 or -1.

create table call_transactions
	call_transaction_id int identity(1,1) primary key clustered
,	call_id int
,	transaction_type varchar(10)
,	transaction_date datetime
,	calls int

To populate this we need two records for each call record. This can be done by cross joining to a table with two rows (I’ll use the trusty integers table for this). One of the records will become the call start record and one the call end record.

insert into call_transactions
select	c.call_id
,		case when i.number = 1 then 'Call_Start' else 'Call_End' end transaction_type
,		case when i.number = 1 then c.start_on else c.end_on end transaction_date
,		case when i.number = 1 then 1 else -1 end calls
from	calls c
		cross join mis.mst.integers i
where	i.number <= 2

Producing a time series of concurrent calls now becomes a simple matter of creating a running sum of calls ordered by the transaction date. In SQL:

select	transaction_date
,		sum(calls) over (order by transaction_date asc) concurrent_calls
from	call_transactions
order by transaction_date

Or in DAX and plotted with some sample data in Power BI:

        ISONORAFTER('call_transactions'[transaction_date], MAX('call_transactions'[transaction_date]), DESC)

Converting an Accumulating Fact Table to a Transaction Table (Calculating Peak Call Volumes)

One downside here is that whatever window on the data we sum over must include both the start and end transaction of every call in that window. If we filter the data by day based on the transaction date a call which starts in one day and ends in the following day will be appear half in each day. This might cause the number of concurrent calls at the start of day two to be -1 which will not look good! This is easily solved by making an arbitrary choice about these boundary conditions – let’s say that any call belongs to the day that it started in – and apply these when we create the call transactions table in a new column that will be populated with the start date of the call.

,	call_date datetime

Filtering on this date rather than the transaction date will then ensure that “whole” calls are included when slicing the data.

An Alternative Approach

Something similar can be produced using the original calls table by sampling the data at specific intervals. Create a table with a set of datetimes to be the points to check the number of concurrent calls, in SQL:

select	i.interval_date
,		count(*)
from	calls c
		join intervals i
		on i.interval_date between c.start_on and c.end_on
group by i.interval_date

The issue here is that the performance of this approach is much worse and the accuracy of the results is dependent on the granularity of the intervals. Furthermore the more intervals you add to improve accuracy the more the performance is degraded.