Create a Data Warehouse Version

We could reuse many of the artifacts we created for the OLTP version. But to keep both versions, it may work better to create a new Page Spec, Task Spec, and Report Spec. Since the process is the same, you can refer to Wire up the Report. Firstly, we will show queries to the data warehouse database which are constructed in the same way as the OLTP version. There are three main issues for our prospect plan status example:

  1. We must query the tables rather than the views. This is because the views do not contain enough information.

  2. The tables themselves are missing a date column used in the transactional version. We can still create a useful metric without this column. But it would require revamping the approach. These sections highlight those locations in the queries: Overlapping Perspective That Mirrors the OLTP version and Create a Data Warehouse Version. In Extend the Warehouse with a Table to Extend the Fact, we describe the creation of an extension which allows us to mirror the OLTP version completely.

  3. An extra join is required because information is spread across a fact and dimension table. In another section, Create Another Data Warehouse Version and Extend the Data Warehouse with New Tables and Views, we will explore a more tailored approach.

Overlapping Perspective That Mirrors the OLTP version

The warehouse has analogous tables for INTERACTION, PROSPECTPLAN, and PROSPECTPLANSTATUS. For the overlapping perspective, it is possible to create a very similar query. But the actual end datetime does not exist. So, the closest we can get is with INTERACTIONDATE, in the OLTP database and the data warehouse database, this is created through a COALESCE of the actual and expected datetimes. We will describe extending the data warehouse to overcome this. But for reference, this is what the query would look like INTERACTIONDATE was used for actual start datetime and actual end datetime. Again, if we were limited to just that datetime, it would be better to revamp the query to only consider days or to extend the warehouse to include actual start datetime and actual end datetime.

with [STEPS]
as (
	select i.[PROSPECTPLANSTATUSDIMID],
		i.[INTERACTIONDATE],
		min(i.[INTERACTIONDATE]) over (
			partition by i.[PROSPECTPLANDIMID],
			i.[PROSPECTPLANSTATUSDIMID]
			) as [FIRSTSTEPINSTAGEDATETIME],
		max(i.[INTERACTIONDATE]) over (
			partition by i.[PROSPECTPLANDIMID],
			i.[PROSPECTPLANSTATUSDIMID]
			) as [LASTSTEPINSTAGEDATETIME]
	from [BBDW].[FACT_INTERACTION] as i
	inner join [BBDW].[DIM_INTERACTION] as j on i.[INTERACTIONDIMID] = j.[INTERACTIONDIMID]
	where j.[ISINTERACTIONCOMPLETED] = 1
	)
select p.[PROSPECTPLANSTATUS] 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 [BBDW].[DIM_PROSPECTPLANSTATUS] as p on s.[PROSPECTPLANSTATUSDIMID] = p.[PROSPECTPLANSTATUSDIMID]
where (s.[INTERACTIONDATE] = s.[LASTSTEPINSTAGEDATETIME])
group by p.[PROSPECTPLANSTATUS]