Extend the Data Warehouse with New Tables and Views

The goal of these extensions is to create structures which represent prospect plan stages and prospect plan stage occurrences. The overlapping perspective and the nonconsecutive perspective aggregates can be calculated from a table which contains rows which each represent a stage in a prospect plan. The consecutive perspective aggregates can be calculated from a table which contains rows which each represent a stage occurrence. Each row should include duration information for the perspectives in addition to the start and end times and dimension IDs for prospect plans and constituents.

Note: It is possible to implement the stored procedures for the reports through revisions also. The sample implements them from the Report Spec. The deciding factor was the ability to update the stored procedure through loading the spec as opposed to deploying the data warehouse. However, loading the stored procedures through revisions would overcome the issue caused when the spec loading mechanism overrides the data source defined in the RDL file. So if you want to use data sets with different data sources in your RDL file, loading the stored procedures accessed by those data sets through revisions is a way to avoid configuring the data sources in Reporting Services after loading the spec.

Create Table Revision for Stage Occurrence (Used by Consecutive Perspective)

  <DBRevision ID="10">
    <ExecuteSql>
      <![CDATA[
create table [BBDW].[FACT_PROSPECTPLANSTAGEOCCURRENCE_EXT] (
	[PROSPECTPLANSTAGEOCCURRENCEFACTID] [int] IDENTITY(1, 1) not null,
	[STARTDATETIME] [datetime] null,
	[STARTDATEDIMID] [int] null,
	[ENDDATETIME] [datetime] null,
	[ENDDATEDIMID] [int] null,
	[STAGEOCCURRENCEDURATION] [datetime] null,
	[PROSPECTPLANSYSTEMID] [uniqueidentifier] null,
	[PROSPECTPLANDIMID] [int] null,
	[CONSTITUENTSYSTEMID] [uniqueidentifier] null,
	[CONSTITUENTDIMID] [int] null,
	[PROSPECTPLANTYPESYSTEMID] [uniqueidentifier] null,
	[PROSPECTPLANSTATUSSYSTEMID] [uniqueidentifier] null,
	[PROSPECTPLANSTATUSDIMID] [int] null,
	[ISINCLUDED] [bit] null,
	[ETLCONTROLID] [int] null,
	[SOURCEDIMID] [int] null,
	constraint [PK_FACT_PROSPECTPLANSTAGEOCCURRENCE_EXT] primary key clustered ([PROSPECTPLANSTAGEOCCURRENCEFACTID] asc)
	) on [BBRPT_FACTGROUP]
]]>
    </ExecuteSql>
  </DBRevision>

Create Table Revision for Stage (Used by Overlapping and Nonconsecutive Perspectives)

  <DBRevision ID="15">
    <ExecuteSql>
      <![CDATA[
create table [BBDW].[FACT_PROSPECTPLANSTAGE_EXT] (
	[PROSPECTPLANSTAGEFACTID] [int] IDENTITY(1, 1) not null,
	[STARTDATETIME] [datetime] null,
	[STARTDATEDIMID] [int] null,
	[ENDDATETIME] [datetime] null,
	[ENDDATEDIMID] [int] null,
	[STAGEDURATIONOVERLAPPING] [datetime] null,
	[STAGEDURATIONNONCONSECUTIVE] [datetime] null,
	[PROSPECTPLANSYSTEMID] [uniqueidentifier] null,
	[PROSPECTPLANDIMID] [int] null,
	[CONSTITUENTSYSTEMID] [uniqueidentifier] null,
	[CONSTITUENTDIMID] [int] null,
	[PROSPECTPLANTYPESYSTEMID] [uniqueidentifier] null,
	[PROSPECTPLANSTATUSSYSTEMID] [uniqueidentifier] null,
	[PROSPECTPLANSTATUSDIMID] [int] null,
	[ISINCLUDED] [bit] null,
	[ETLCONTROLID] [int] null,
	[SOURCEDIMID] [int] null,
	constraint [PK_FACT_PROSPECTPLANSTAGE_EXT] primary key clustered ([PROSPECTPLANSTAGEFACTID] asc)
	) on [BBRPT_FACTGROUP]
]]>

Other Items to Support the Stage and Stage Occurrence Tables

As with other data warehouse revisions which create tables, there should be a staging table, stored procedures for indexes, table truncation, and MS_Description comments for mapping. The revisions file should include each of these. Also, a view of each table is desirable.

But the bulk of the work comes with the SSIS packages to perform the ETL. The ETL process for these tables will be more complex than with the previous example. This is because the stage and stage occurrence tables have rows which are based on multiple rows in the Interaction table in the OLTP database. For the previous versions of the report in this document, these transformations were performed by the stored procedures used by the report. The idea behind these extensions is to transfer that workload for the report's stored procedures to the ETL process. The report stored procedures will then be simplified to calculating the aggregates and not performing the prerequisite transformations of the Interaction table.

Fortunately, we have already worked through this logic in the course of building the previous report version's stored procedures. So to create the SSIS packages for the Stage and Stage Occurrence tables, we can transfer that logic to the SSIS packages.