Create an OLTP Version

A Report That Queries the Blackbaud CRM OLTP Database

For information about reports in Blackbaud CRM, see Reports Guide. For more information about creating reports for Blackbaud CRM, see Infinity Reports.

In many cases, you will get better performance from your reports if you report off of the data warehouse database. But we are going to compare a report off of the OLTP database with reports off of the data warehouse. So we will build both kinds.

Begin by creating a Report Spec. Once loaded, the Report Spec will connect the report to the application interface. For information about how to create a Report Spec, see Create a Report Spec.

Use SQL Server Business Intelligence Development Studio (Visual Studio 2008) to create the project because there is an editor for RDL files.

Logic

Here is a query which returns the stage description (Identification, Cultivation, Negotiation, Solicitation, etc.), the prospect plan name, and the actual start datetime for completed plan steps (interaction records). The query presents the results in order by prospect plan name and, within the prospect plan name, by actual start datetime.

select PROSPECTPLANSTATUSCODE.[DESCRIPTION],
	PROSPECTPLAN.[NAME],
	INTERACTION.[ACTUALSTARTDATETIME]
from INTERACTION
inner join PROSPECTPLANSTATUSCODE on INTERACTION.[PROSPECTPLANSTATUSCODEID] = PROSPECTPLANSTATUSCODE.[ID]
inner join PROSPECTPLAN on INTERACTION.[PROSPECTPLANID] = PROSPECTPLAN.[ID]
where (INTERACTION.[COMPLETED] = 1)
group by PROSPECTPLANSTATUSCODE.[DESCRIPTION],
	INTERACTION.[ACTUALSTARTDATETIME],
	PROSPECTPLAN.[NAME]
order by PROSPECTPLAN.[NAME],
	INTERACTION.[ACTUALSTARTDATETIME]

This gives us a starting place for the problem. If we create a plan in Blackbaud CRM called Test and enter the example steps from the design, the query returns these rows:

If there were other prospect plans, there would be rows for those as well. But the results would be grouped by prospect plan name.

For each prospect plan, we want to identify the first instance of consecutive occurrences of a stage description in these results. Those rows will give us much of what we need to address the consecutive and nonconsecutive perspectives.

For each prospect plan, we want to identify the first instance of a stage description in these results. Those rows will give us much of what we need to address the overlapping perspective. For example:

The difference is that with the overlapping perspective, we are not concerned with when a new set of consecutive occurrences of a stage appear. In the consecutive and nonconsecutive perspectives, we need to identify occurrences of consecutive stage descriptions. In the overlapping perspective, we need to identify only the first occurrence of a stage description. But it turns out that we need information from adjacent rows to complete the picture.

For the first stage occurrence, the start time is also the start time of the first step in the plan. The end time is the end time of the last step in the occurrence. When there is a subsequent stage occurrence, the end datetime is the same as the start datetime of the first step in the subsequent occurrence. When there is no subsequent stage occurrence, the end datetime is the end datetime of the plan, which is the same as the end datetime of the last step in the stage occurrence. Since we are paring down rows to represent either a stage in the case of the overlapping perspective or a stage occurrence in the other perspectives, we need to pick which rows.

We will hold on to the last row of each occurrence or stage. For the overlapping perspective, we have what we need in those rows with the exception of the start datetime of the plan and the end datetime of the plan. We can use MIN and MAX partitioned by plan and status code to find the results we need.

	select i.[PROSPECTPLANSTATUSCODEID],
		i.[ACTUALENDDATETIME],
		min(i.[ACTUALSTARTDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [FIRSTSTEPINSTAGEDATETIME],
		max(i.[ACTUALENDDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [LASTSTEPINSTAGEDATETIME]
	from [INTERACTION] as i
	where i.[COMPLETED] = 1

For the Test plan data (1D85EEC8-5205-4205-A5D6-9A31F4C78EA5 is the ID for Test):

We are going use aggregate calculations with these rows. We can set up a common table expression and select from that.

with [STEPS]
as (
	select i.[PROSPECTPLANSTATUSCODEID],
		i.[ACTUALENDDATETIME],
		min(i.[ACTUALSTARTDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [FIRSTSTEPINSTAGEDATETIME],
		max(i.[ACTUALENDDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [LASTSTEPINSTAGEDATETIME]
	from [INTERACTION] as i
	where i.[COMPLETED] = 1
	)
select *
from [STEPS] as s

This returns the same results. We want the friendly name for the stage rather than the GUID. So we will use INNER JOIN to get the prospect plan status code description from the PROSPECTPLANSTATUSCODE code table.

from [STEPS] as s
inner join [PROSPECTPLANSTATUSCODE] as p on s.[PROSPECTPLANSTATUSCODEID] = p.[ID]

We also want to reduce the rows to only the last row in a stage. Also, we are going to include the stage description. So we will GROUP BY that.

where (s.[ACTUALENDDATETIME] = s.[LASTSTEPINSTAGEDATETIME])
group by p.[DESCRIPTION]

For every stage in a plan, we now have the stage description, start datetime, and end datetime. We can use the datetimes to calculate a duration and find the average, minimum, and maximum of those durations.

with [STEPS]
as (
	select i.[PROSPECTPLANSTATUSCODEID],
		i.[ACTUALENDDATETIME],
		min(i.[ACTUALSTARTDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [FIRSTSTEPINSTAGEDATETIME],
		max(i.[ACTUALENDDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [LASTSTEPINSTAGEDATETIME]
	from [INTERACTION] as i
	where i.[COMPLETED] = 1
	)
select p.[DESCRIPTION] as [STAGENAME],
	avg(cast((s.[LASTSTEPINSTAGEDATETIME] - s.[FIRSTSTEPINSTAGEDATETIME]) as float)) as [AVGSTAGEDURATION],
	min(cast((s.[LASTSTEPINSTAGEDATETIME] - s.[FIRSTSTEPINSTAGEDATETIME]) as float)) as [MINSTAGEDURATION],
	max(cast((s.[LASTSTEPINSTAGEDATETIME] - s.[FIRSTSTEPINSTAGEDATETIME]) as float)) as [MAXSTAGEDURATION]
from [STEPS] as s
inner join [PROSPECTPLANSTATUSCODE] as p on s.[PROSPECTPLANSTATUSCODEID] = p.[ID]
where (s.[ACTUALENDDATETIME] = s.[LASTSTEPINSTAGEDATETIME])
group by p.[DESCRIPTION]

For the full CREATE PROCEDURE code to be used in the Report Spec, see Overlapping Perspective Stored Procedure.

Now we have our overlapping perspective. The consecutive and nonconsecutive perspectives are more complex. The reason is those perspectives look at stage occurrences rather than just stages. A plan can go back and forth between stages. So there can be more than one stage occurrence for a given stage. For example, the plan can move from Identification to Solicitation and back to Identification. We can't partition this effectively in one query. To overcome that, we can use row comparisons. To perform row comparisons, we join a table to itself on a sequence. But we stagger the sequence.

We are still going to use a common table expression. But we maintain some additional information and create a sequence number for the steps. To create a sequence number, we use the ROW_NUMBER function. We also maintain the start time, prospect plan ID, and prospect plan status ID. We will discuss the parameter @PROSPECTPLANTYPECODEID in another section.

with [STEPS]
as (
	select row_number() over (
			order by i.[PROSPECTPLANID],
				i.[ACTUALSTARTDATETIME]
			) as [ALLSTEPSEQUENCENUMBER],
		i.[ACTUALSTARTDATETIME],
		i.[ACTUALENDDATETIME],
		i.[PROSPECTPLANID],
		max(i.[ACTUALENDDATETIME]) over (partition by i.[PROSPECTPLANID]) as [LASTSTEPINPLANENDDATETIME],
		i.[PROSPECTPLANSTATUSCODEID]
	from [INTERACTION] as i
	inner join [PROSPECTPLAN] pl on i.[PROSPECTPLANID] = pl.[ID]
	where i.[COMPLETED] = 1
		and pl.[PROSPECTPLANTYPECODEID] = @PROSPECTPLANTYPECODEID
	),

For the Test data:

We want to grab some information from the preceding rows before we filter and create granularities for the other two perspectives. In particular, we want the prospect plan ID and prospect plan status of the preceding steps:

[STAGEOCCURRENCESFIRSTPASS]
as (
	select s.[ACTUALSTARTDATETIME],
		s.[ACTUALENDDATETIME],
		r.[PROSPECTPLANSTATUSCODEID] as [PREVIOUSSTEPPROSPECTPLANSTATUSCODEID],
		s.[PROSPECTPLANSTATUSCODEID],
		r.[PROSPECTPLANID] as [PREVIOUSSTEPPROSPECTPLANID],
		s.[PROSPECTPLANID],
		s.[LASTSTEPINPLANENDDATETIME]
	from [STEPS] as s
	left join [STEPS] as r on s.[ALLSTEPSEQUENCENUMBER] - 1 = r.[ALLSTEPSEQUENCENUMBER]
	),

For the consecutive perspective, the [STEPS] granularity is used to create a [STAGEOCCURRENCES] granularity which is used by the [STAGEDURATIONS] common table expression:

[STAGEOCCURRENCES]
as (
	select row_number() over (
			order by sofp.[PROSPECTPLANID],
				sofp.[ACTUALSTARTDATETIME]
			) as [ALLSTAGEOCCURRENCESSEQUENCENUMBER],
		sofp.[ACTUALSTARTDATETIME],
		sofp.[ACTUALENDDATETIME],
		sofp.[LASTSTEPINPLANENDDATETIME],
		sofp.[PROSPECTPLANID],
		sofp.[PROSPECTPLANSTATUSCODEID]
	from [STAGEOCCURRENCESFIRSTPASS] as sofp
	where sofp.[PROSPECTPLANID] <> sofp.[PREVIOUSSTEPPROSPECTPLANID]
		or (
			(sofp.[PROSPECTPLANSTATUSCODEID] <> sofp.[PREVIOUSSTEPPROSPECTPLANSTATUSCODEID])
			and (sofp.[PROSPECTPLANID] = sofp.[PREVIOUSSTEPPROSPECTPLANID])
			)
	),
[STAGEOCCURRENCEDURATIONS]
as (
	select so1.[ACTUALSTARTDATETIME] as [STARTDATETIME],
		so1.[ACTUALENDDATETIME] as [ENDDATETIME],
		"STAGEOCCURRENCEDURATION" = case 
			when so1.[ACTUALENDDATETIME] <> so1.[LASTSTEPINPLANENDDATETIME]
				then cast(so2.[ACTUALSTARTDATETIME] - so1.[ACTUALSTARTDATETIME] as float)
			when so1.[ACTUALENDDATETIME] = so1.[LASTSTEPINPLANENDDATETIME]
				then cast(so1.[ACTUALENDDATETIME] - so1.[ACTUALSTARTDATETIME] as float)
			end,
		so1.[PROSPECTPLANID],
		so1.[PROSPECTPLANSTATUSCODEID]
	from [STAGEOCCURRENCES] as so1
	left join [STAGEOCCURRENCES] as so2 on so1.ALLSTAGEOCCURRENCESSEQUENCENUMBER + 1 = so2.ALLSTAGEOCCURRENCESSEQUENCENUMBER
	)

The information gleaned from the row comparison is used to filter out unneeded rows in order to establish the new granularity of stage occurrence. So rather than discrete steps in a plan, the rows represent unbroken periods of time in a particular plan stage.

	where sofp.[PROSPECTPLANID] <> sofp.[PREVIOUSSTEPPROSPECTPLANID]
		or (
			(sofp.[PROSPECTPLANSTATUSCODEID] <> sofp.[PREVIOUSSTEPPROSPECTPLANSTATUSCODEID])
			and (sofp.[PROSPECTPLANID] = sofp.[PREVIOUSSTEPPROSPECTPLANID])
			)

In [STAGEOCCURRENCEDURATIONS] information from the next row for a stage occurrence is used to calculate stage occurrence durations. The case statement determines qualities about the stage occurrence represented by the row and bases the duration calculation on those qualities.

		"STAGEOCCURRENCEDURATION" = case 
			when so1.[ACTUALENDDATETIME] <> so1.[LASTSTEPINPLANENDDATETIME]
				then cast(so2.[ACTUALSTARTDATETIME] - so1.[ACTUALSTARTDATETIME] as float)
			when so1.[ACTUALENDDATETIME] = so1.[LASTSTEPINPLANENDDATETIME]
				then cast(so1.[ACTUALENDDATETIME] - so1.[ACTUALSTARTDATETIME] as float)
			end,

Notice that for the original sequence number (ALLSTEPSEQUENCENUMBER), we decremented the row in the comparison and for the second one (ALLSTAGEOCCURRENCESSEQUENCENUMBER), we incremented the row. We used ALLSTAGEOCCURRENCESSEQUENCENUMBER to create a join to compare the subsequent row, we will use ALLSTEPSEQUENCENUMBER to compare the preceding row. But also notice that when we decremented, we made this provision:.

	where sofp.[PROSPECTPLANID] <> sofp.[PREVIOUSSTEPPROSPECTPLANID]
		or (
			(sofp.[PROSPECTPLANSTATUSCODEID] <> sofp.[PREVIOUSSTEPPROSPECTPLANSTATUSCODEID])
			and (sofp.[PROSPECTPLANID] = sofp.[PREVIOUSSTEPPROSPECTPLANID])
			)

The calculation of average, minimum, maximum, and average count of occurrences is performed in a similar way as the overlapping perspective. But the average times here are for stage occurrence durations not stage durations. Also, there is an extra calculation for average times in stage.

select p.[DESCRIPTION] as [STAGENAME],
	avg(cast([STAGEOCCURRENCEDURATIONS].[STAGEOCCURRENCEDURATION] as float)) as [AVGSTAGEOCCURRENCEDURATION],
	min(cast([STAGEOCCURRENCEDURATIONS].[STAGEOCCURRENCEDURATION] as float)) as [MINSTAGEOCCURRENCEDURATION],
	max(cast([STAGEOCCURRENCEDURATIONS].[STAGEOCCURRENCEDURATION] as float)) as [MAXSTAGEOCCURRENCEDURATION],
	cast(count([STAGEOCCURRENCEDURATIONS].[PROSPECTPLANID]) as float) / cast(count(distinct ([STAGEOCCURRENCEDURATIONS].[PROSPECTPLANID])) as float) as [AVGTIMESINSTAGE]
from [STAGEOCCURRENCEDURATIONS]
inner join [PROSPECTPLANSTATUSCODE] as p on [STAGEOCCURRENCEDURATIONS].[PROSPECTPLANSTATUSCODEID] = p.[ID]
group by p.[DESCRIPTION] 

It is tempting to find the average number of times in a stage using the AVG function on a count created in the previous granularity. But remember there is a row for each stage occurrence. So the average would be weighted. This average calculation avoids that:

cast(count([STAGEOCCURRENCEDURATIONS].[PROSPECTPLANID]) as float) /
cast(count(distinct ([STAGEOCCURRENCEDURATIONS].[PROSPECTPLANID])) as float) as [AVGTIMESINSTAGE]

For the full CREATE PROCEDURE code to be used in the Report Spec, see Consecutive Perspective Stored Procedure.

The main difference between the consecutive and nonconsecutive perspective is the totaling of the stage occurrence times. We add another layer of granularity for durations and total the durations each stage occurrence.

[STAGEDURATIONS]
as (
	select sum(cast(sod.[STAGEOCCURRENCEDURATION] as float)) over (
			partition by sod.[PROSPECTPLANID],
			sod.[PROSPECTPLANSTATUSCODEID]
			) as [STAGEDURATION],
		RANK() over (
			partition by sod.[PROSPECTPLANID],
			sod.[PROSPECTPLANSTATUSCODEID] order by sod.[ACTUALSTARTDATETIME]
			) as [FIRSTOCCURRENCEROWINSTAGE],
		sod.[PROSPECTPLANSTATUSCODEID]
	from [STAGEOCCURRENCEDURATIONS] as sod
	)

The average count of times in stage is not necessary for the nonconsecutive perspective:

select p.[DESCRIPTION] as [STAGENAME],
	avg(cast([STAGEDURATIONS].[STAGEDURATION] as float)) as [AVGSTAGEDURATION],
	min(cast([STAGEDURATIONS].[STAGEDURATION] as float)) as [MINSTAGEDURATION],
	max(cast([STAGEDURATIONS].[STAGEDURATION] as float)) as [MAXSTAGEDURATION]
from [STAGEDURATIONS]
inner join [PROSPECTPLANSTATUSCODE] as p on [STAGEDURATIONS].[PROSPECTPLANSTATUSCODEID] = p.[ID]
where [FIRSTOCCURRENCEROWINSTAGE] = 1
group by p.[DESCRIPTION]

For the full CREATE PROCEDURE code to be used in the Report Spec, see Nonconsecutive Perspective Stored Procedure.