Extend the Warehouse with a Table to Extend the Fact

The tables used by our queries were described in Find Data in the Data Warehouse Database. Those tables are insufficient for our design requirements because they do not include actual start datetime and actual end datetime. We don't want to alter that table because it is possible the table will be updated in a subsequent release of Blackbaud Data Warehouse. Instead, we are going to create a new table with the actual start datetime and actual end datetime which we can join to the existing fact table in queries.

Note: Another approach would be to replicate the FACT_INTERACTION table with the addition of those columns. This would have the advantage of avoiding an extra join in the report queries. But it would require more complex database revisions and ETL logic in the SSIS packages. Also, it increases risk because so many INTERACTION columns now require updates in two tables.

File for Revisions

If you don't already have a file for revisions, create an XML file with these contents:

<?xml version="1.0" ?>
<DBRevisions xmlns="bb_appfx_dbrevisions">
	<DBRevision ID="1">
		<Comment>Extended Database Schema</Comment>
	</DBRevision>
	
	<DBRevision ID="5">
		<ExecuteSql>
			<![CDATA[	
if not exists (
		select *
		from sysobjects so
		where so.type = 'P'
			and so.name = 'RESETETL_EXT'
		)
	exec sp_executesql N'create procedure BBDW.[RESETETL_EXT] as set nocount on;'
			]]>
		</ExecuteSql>
	</DBRevision>
	
</DBRevisions>

Name the file according to the naming convention for revisions extensions.

CREATE TABLE Revision

We will call the table FACT_INTERACTIONACTUALTIMES_EXT. One of the revisions we need is the revision which actually creates the table. Since we are replicating some of the FACT_INTERACTION table, we can grab some of that from SQL Server Management Studio. After opening SSMS, we can connect to the database and filter the tables for FACT_INTERACTION. To filter the tables for a database, right-click the Tables node for the database in Object Explorer and select Filter > Filter Settings.

In the Name field of the Filter Settings screen that appears, enter FACT_INTERACTION and click OK.

From the filtered list, right-click FACT_INTERACTION and select Script Table as > CREATE To > New Query Editor Window.

The part we need is the CREATE TABLE statement.

Copy this into the query editor.

CREATE TABLE [BBDW].[FACT_INTERACTION](
	[INTERACTIONFACTID] [int] IDENTITY(1,1) NOT NULL,
	[INTERACTIONSYSTEMID] [uniqueidentifier] NULL,
	[CONSTITUENTDIMID] [int] NULL,
	[CONSTITUENTSYSTEMID] [uniqueidentifier] NULL,
	[FUNDRAISERDIMID] [int] NULL,
	[FUNDRAISERSYSTEMID] [uniqueidentifier] NULL,
	[INTERACTIONDATEDIMID] [int] NULL,
	[INTERACTIONDATE] [datetime] NULL,
	[INTERACTIONDIMID] [int] NULL,
	[EVENTDIMID] [int] NULL,
	[PROSPECTPLANDIMID] [int] NULL,
	[PLANOUTLINESTEPDIMID] [int] NULL,
	[PROSPECTPLANSTATUSDIMID] [int] NULL,
	[FUNDINGREQUESTDIMID] [int] NULL,
	[FUNDINGREQUESTOUTLINESTEPDIMID] [int] NULL,
	[INTERACTIONLOOKUPID] [nvarchar](100) NULL,
	[INTERACTIONOBJECTIVE] [nvarchar](100) NULL,
	[ISINCLUDED] [bit] NULL,
	[ETLCONTROLID] [int] NULL,
	[SOURCEDIMID] [int] NULL,
 CONSTRAINT [PK_FACT_INTERACTION] PRIMARY KEY CLUSTERED 
(
	[INTERACTIONFACTID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [BBRPT_FACTGROUP]
) ON [BBRPT_FACTGROUP]

Firstly, we already have a FACT_INTERACTION table, so we need to change that name. And we don't need the contents of the WITH clause. But we should leave the CONSTRAINT but change the name. and the ON for the fact group. Let's also reformat those keywords to lower-case since that is how the rest of our Transact-SQL looks. As for the columns:

Add the columns for actual start datetime and actual end datetime.
Change INTERACTIONFACTID and INTERACTIONSYSTEMID to INTERACTIONACTUALTIMESFACTID and INTERACTIONACTUALTIMESSYSTEMID.
Retain ISINCLUDED, ETLCONTROLID, and SOURCEDIMID.
Remove all other fields.

  <DBRevision ID="10">
    <ExecuteSql>
      <![CDATA[
create table [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT] (
	[INTERACTIONACTUALTIMESFACTID] [int] IDENTITY(1, 1) not null,
	[INTERACTIONACTUALTIMESSYSTEMID] [uniqueidentifier] null,
	[ACTUALSTARTDATETIME] [datetime] null,
	[ACTUALSTARTDATEDIMID] [int] null,
	[ACTUALENDDATETIME] [datetime] null,
	[ACTUALENDDATEDIMID] [int] null,
	[ISINCLUDED] [bit] null,
	[ETLCONTROLID] [int] null,
	[SOURCEDIMID] [int] null,
	constraint [PK_FACT_INTERACTIONACTUALTIMES_EXT] primary key clustered ([INTERACTIONACTUALTIMESFACTID] asc)
	) on [BBRPT_FACTGROUP]
]]>
    </ExecuteSql>
  </DBRevision>

Drop and Create Indexes Revision for Extension Table

There should be nonclustered indexes on the date dimension columns. These need to be dropped and added as a part of the ETL process. This creates a stored procedure for that.

  <DBRevision ID="15">
    <ExecuteSql>
      <![CDATA[
create procedure [BBDW].[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_INDICES] @CREATE_OR_DROP bit --1 to create, 0 to drop.
as
set nocount on;

if @CREATE_OR_DROP is null
	raiserror (
			'@CREATE_OR_DROP must be 1 or 0 in [BBDW].[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_INDICES]',
			16,
			10
			);

if @CREATE_OR_DROP = 1
begin
	--create
	if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALSTARTDATETIMEDIMID') = 0
		create nonclustered index [IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALSTARTDATETIMEDIMID] on [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT] ([ACTUALSTARTDATEDIMID]) on [BBRPT_DIMIDXGROUP]

	if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALENDDATETIMEDIMID') = 0
		create nonclustered index [IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALENDDATETIMEDIMID] on [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT] ([ACTUALENDDATEDIMID]) on [BBRPT_DIMIDXGROUP]
end
else
begin
	--drop
	if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALSTARTDATETIMEDIMID') = 1
		drop index [IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALSTARTDATETIMEDIMID] on [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT];

	if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALENDDATETIMEDIMID') = 1
		drop index [IX_FACT_INTERACTIONACTUALTIMES_EXT_ACTUALENDDATETIMEDIMID] on [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT];
end
]]>
    </ExecuteSql>
  </DBRevision>

CREATE TABLE Revision for Staging Table

The staging table has the same columns. Copy the revision for the main table and change the DBRevision ID and add _STAGE where the table name is used. This includes the constraint.

  <DBRevision ID="20">
    <ExecuteSql>
      <![CDATA[
create table [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT_STAGE] (
	[INTERACTIONACTUALTIMESSYSTEMID] [uniqueidentifier] null,
	[ACTUALSTARTDATETIME] [datetime] null,
	[ACTUALSTARTDATEDIMID] [int] null,
	[ACTUALENDDATETIME] [datetime] null,
	[ACTUALENDDATEDIMID] [int] null,
	[ISINCLUDED] [bit] null,
	[ETLCONTROLID] [int] null,
	[SOURCEDIMID] [int] null
	) on [BBRPT_STAGEGROUP]
]]>
    </ExecuteSql>
  </DBRevision>

Drop and Create Indexes Revision for Staging Table

There should be an index on the INTERACTIONACTUALTIMESSYSTEMID. These need to be dropped and added as a part of the ETL process. This creates a stored procedure for that.

  <DBRevision ID="25">
    <ExecuteSql>
      <![CDATA[
create procedure [BBDW].[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INDICES] @CREATE_OR_DROP bit --1 to create, 0 to drop.
as
set nocount on;

if @CREATE_OR_DROP is null
	raiserror (
			'@CREATE_OR_DROP must be 1 or 0 in [BBDW].[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INDICES]',
			16,
			10
			);

if @CREATE_OR_DROP = 1
begin
	--create
	if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INTERACTIONACTUALTIMESSYSTEMID') = 0
		create nonclustered index [IX_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INTERACTIONACTUALTIMESSYSTEMID] on [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT_STAGE] ([INTERACTIONACTUALTIMESSYSTEMID]) on [BBRPT_STAGEGROUP]
end
else
begin
	--drop
	if [BBDW].[UFN_INDEXEXISTS]('IX_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INTERACTIONACTUALTIMESSYSTEMID') = 1
		drop index [IX_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INTERACTIONACTUALTIMESSYSTEMID] on [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT_STAGE];
end
] ]>
    </ExecuteSql>
  </DBRevision>

Truncate Tables and Drop Indexes Revision

  <DBRevision ID="30">
    <ExecuteSql>
      <![CDATA[
alter procedure BBDW.[RESETETL_EXT]
as
set nocount on;

truncate table BBDW.[FACT_INTERACTIONACTUALTIMES_EXT];

exec BBDW.[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_INDICES] 0;

truncate table BBDW.[FACT_INTERACTIONACTUALTIMES_EXT_STAGE];

exec BBDW.[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INDICES] 0;
]]>
    </ExecuteSql>
  </DBRevision>

CREATE VIEW Revision

Since the data warehouse creates a star schema using views, it is helpful to add a view to support that.

  <DBRevision ID="35">
    <ExecuteSql>
      <![CDATA[
create view [BBDW].[v_FACT_INTERACTIONACTUALTIMES_EXT]
as
select [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT].[INTERACTIONACTUALTIMESFACTID],
	[BBDW].[FACT_INTERACTIONACTUALTIMES_EXT].[INTERACTIONACTUALTIMESSYSTEMID],
	[BBDW].[FACT_INTERACTION].[CONSTITUENTDIMID],
	[BBDW].[FACT_INTERACTION].[CONSTITUENTSYSTEMID],
	[BBDW].[FACT_INTERACTION].[FUNDRAISERDIMID],
	[BBDW].[FACT_INTERACTION].[FUNDRAISERSYSTEMID],
	[BBDW].[FACT_INTERACTION].[INTERACTIONDATEDIMID],
	[BBDW].[FACT_INTERACTION].[INTERACTIONDATE],
	[BBDW].[FACT_INTERACTIONACTUALTIMES_EXT].[ACTUALSTARTDATETIME],
	[BBDW].[FACT_INTERACTIONACTUALTIMES_EXT].[ACTUALSTARTDATEDIMID],
	[BBDW].[FACT_INTERACTIONACTUALTIMES_EXT].[ACTUALENDDATETIME],
	[BBDW].[FACT_INTERACTIONACTUALTIMES_EXT].[ACTUALENDDATEDIMID],
	[BBDW].[FACT_INTERACTION].[INTERACTIONDIMID],
	[BBDW].[FACT_INTERACTION].[EVENTDIMID],
	[BBDW].[FACT_INTERACTION].[PROSPECTPLANDIMID],
	[BBDW].[FACT_INTERACTION].[PLANOUTLINESTEPDIMID],
	[BBDW].[FACT_INTERACTION].[PROSPECTPLANSTATUSDIMID],
	[BBDW].[FACT_INTERACTION].[FUNDINGREQUESTDIMID],
	[BBDW].[FACT_INTERACTION].[FUNDINGREQUESTOUTLINESTEPDIMID],
	[BBDW].[FACT_INTERACTION].[INTERACTIONLOOKUPID],
	[BBDW].[FACT_INTERACTION].[INTERACTIONOBJECTIVE]
from [BBDW].[FACT_INTERACTION]
left join [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT] on [BBDW].[FACT_INTERACTION].[INTERACTIONSYSTEMID] = [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT].[INTERACTIONACTUALTIMESSYSTEMID]
]]>
    </ExecuteSql>
  </DBRevision>

Map Source to Target Revision

This revision adds MS_Description comments to the table.

  <DBRevision ID="40">
    <ExecuteSql>
      <![CDATA[
exec BBDW.USP_SCHEMA_TABLE_SETTABLECOMMENT 'FACT_INTERACTIONACTUALTIMES_EXT',
	'The Interaction Actual Times fact contains actual start and end datetimes for interactions.
	 The table can be joined to the Interaction fact which relates information to constituent interactions.
	  The v_FACT_INTERACTIONACTUALTIMES_EXT view does this.';

exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description',
	'FACT_INTERACTIONACTUALTIMES_EXT',
	'INTERACTIONACTUALTIMESFACTID',
	'Surrogate key for Interaction fact.';

exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description',
	'FACT_INTERACTIONACTUALTIMES_EXT',
	'INTERACTIONACTUALTIMESSYSTEMID',
	'dbo.[INTERACTION].[INTERACTIONID]';

exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description',
	'FACT_INTERACTIONACTUALTIMES_EXT',
	'ACTUALSTARTDATETIME',
	'dbo.[INTERACTION].[ACTUALSTARTDATETIME]';

exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description',
	'FACT_INTERACTIONACTUALTIMES_EXT',
	'ACTUALENDDATETIME',
	'dbo.[INTERACTION].[ACTUALENDDATETIME]';

exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description',
	'FACT_INTERACTIONACTUALTIMES_EXT',
	'ISINCLUDED',
	'Flag indicating when data should be included in results.';

exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description',
	'FACT_INTERACTIONACTUALTIMES_EXT',
	'ETLCONTROLID',
	'ID generated through the ETL process';

exec BBDW.USP_SCHEMA_TABLE_SETCOLUMNCOMMENT 'MS_Description',
	'FACT_INTERACTIONACTUALTIMES_EXT',
	'SOURCEDIMID',
	'Source system used';

]]>
    </ExecuteSql>
  </DBRevision>

Create the Project and Compile the Revisions to a DLL

If you don't already have a project for revisions extensions, create one.

Create the ETL for the New Table

Open Business Intelligence Development Studio.
Open the template DTSX file, BBDW_FACT_TEMPLATE.
1. Click File > Open > File.
2. Browse to C:\Program Files\Blackbaud\bbappfx\MSBuild\Datamarts\BBDW\SSIS\BBDW_FACT_TEMPLATE.dtsx.
  
  Note: The location may be different for your installation.
3. Click Open.
Save a copy in the Extend\SSIS folder.
1. Click File > Save Copy of BBDW_FACT_TEMPLATE.dtsx As...
2. The Save Copy of Package screen appears.
3. From Package location, select File System.
4. Click the ellipses button next to the field for Package path. The Save Package To File screen appears.
5. Browse to C:\Program Files\Blackbaud\bbappfx\MSBuild\Datamarts\BBDW\Extend\SSIS.
  
  Note: The location may be different for your installation.
6. Change the name to BBDW_FACT_INTERACTIONACTUALTIMES_EXT.
7. Click Save.
Change the Truncate Staging task.

On the Control Flow tab for the package designer, double-click the Truncate Staging task in the Load Rows sequence. The Execute SQL Task Editor screen appears.

From General > SQL Statement > SQLStatement, change the query to:
truncate table BBDW.[FACT_INTERACTIONACTUALTIMES_EXT_STAGE]; exec BBDW.[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INDICES] 0;
Click OK.
Change the Load Changed Rows task.

Note: If you see a red x, it may be because the Connection Mangers are not configured for your databases. To fix, this click the BBETL_DB_CONN_DW and BBETL_DB_CONN_OLTP connection managers on the Connection Managers tab and reconfigure them.
1. On the Control Flow tab for the package designer, double-click the Load Changed Rows task. The Data Flow appears.
  
  Double-click New and changed rows from OLTP. The OLE DB Source Editor appears.
  
  Change the SQL command text to:
  select i.[ID] as [INTERACTIONACTUALTIMESSYSTEMID], i.[ACTUALSTARTDATETIME] as [ACTUALSTARTDATETIME], i.[ACTUALENDDATETIME] as [ACTUALENDDATETIME], 1 as [ISINCLUDED] from dbo.[INTERACTION] as i where (i.[DATECHANGED] > ? and i.[DATECHANGED] <= ? )
2. Double-click the Date Dims Data Flow Component. The Restore Invalid Column References Editor appears.
  
  Remove all of the invalid references and click OK.
  
  Double-click the Date Dims Data Flow Component again. The Derived Column Transformation Editor appears.
  
  Copy the Expression in the grid:
  ISNULL(INTERACTIONRESPONSEDATE) ? 0 : YEAR(INTERACTIONRESPONSEDATE) * 10000 + MONTH(INTERACTIONRESPONSEDATE) * 100 + DAY(INTERACTIONRESPONSEDATE)
  Add two new derived columns:
  ACTUALSTARTDATEDIMID ACTUALENDDATEDIMID
  Adjust the expression you copied for each of these and paste the revised expressions into the Expression fields.
  ISNULL(ACTUALSTARTDATETIME) ? 0 : YEAR(ACTUALSTARTDATETIME) * 10000 + MONTH(ACTUALSTARTDATETIME) * 100 + DAY(ACTUALSTARTDATETIME) ISNULL(ACTUALENDDATETIME) ? 0 : YEAR(ACTUALENDDATETIME) * 10000 + MONTH(ACTUALENDDATETIME) * 100 + DAY(ACTUALENDDATETIME)
3. Remove the Check Dates Data Flow Component. Click Check Dates and press Delete.
4. Remove the Lookup Response Data Flow Component. Click Lookup Response and press Delete.
5. Remove the invalid column references in Stage Rows from the template.
  
  Double-click the Stage Rows Data Flow Component. The Restore Invalid Column References Editor screen appears.
  
  Delete the template columns. Select all of the rows and from Column mapping option for selected rows, select <Delete invalid column reference>.
  
  Click OK.
6. Double-click the Stage Rows Data Flow Component again. The OLE DB Destination Editor appears.
  
  Ensure the BBETL_DB_CONN_DW connection manager is selected under OLE DB connection manager.
  
  From Name of the table or the view, select [BBDW].[FACT_INTERACTIONACTUALTIMES_EXT_STAGE].
  
  Click Mappings. The mapping should be established for every column except INTERACTIONACTUALTIMESFACTID.
  
  Click OK.
7. Save the package.
Change the Adding Staging Indices task.
1. Return to the Control Flow tab.
2. Double-click the Adding Staging Indices task.
3. From General > SQL Statement > SQLStatement, change the query to:
  exec BBDW.[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_STAGE_INDICES] 1;
4. Click OK.

Change the Upsert task.

Connect Adding Staging Indices task to Upsert task
Double-click Upsert. The Execute SQL Task editor screen appears.

From General > SQL Statement > SQLStatement, change the query to:

declare @COUNTS table (
	[ACTION] varchar(28),
	[INSERTED] int,
	[UPDATED] int
	);

merge BBDW.[FACT_INTERACTIONACTUALTIMES_EXT] as t
using (
	select i.[INTERACTIONACTUALTIMESSYSTEMID],
		i.[ACTUALSTARTDATETIME],
		i.[ACTUALSTARTDATEDIMID],
		i.[ACTUALENDDATETIME],
		i.[ACTUALENDDATEDIMID],
		i.[ISINCLUDED],
		i.[ETLCONTROLID],
		i.[SOURCEDIMID]
	from BBDW.[FACT_INTERACTIONACTUALTIMES_EXT_STAGE] as i
	) as s
	on (t.[INTERACTIONACTUALTIMESSYSTEMID] = s.[INTERACTIONACTUALTIMESSYSTEMID])
when not matched by target
	then
		insert (
			[INTERACTIONACTUALTIMESSYSTEMID],
			[ACTUALSTARTDATETIME],
			[ACTUALSTARTDATEDIMID],
			[ACTUALENDDATETIME],
			[ACTUALENDDATEDIMID],
			[ISINCLUDED],
			[ETLCONTROLID],
			[SOURCEDIMID]
			)
		values (
			s.[INTERACTIONACTUALTIMESSYSTEMID],
			s.[ACTUALSTARTDATETIME],
			s.[ACTUALSTARTDATEDIMID],
			s.[ACTUALENDDATETIME],
			s.[ACTUALENDDATEDIMID],
			s.[ISINCLUDED],
			s.[ETLCONTROLID],
			s.[SOURCEDIMID]
			)
when matched
	then
		update
		set t.[INTERACTIONACTUALTIMESSYSTEMID] = s.[INTERACTIONACTUALTIMESSYSTEMID],
			t.[ACTUALSTARTDATETIME] = s.[ACTUALSTARTDATETIME],
			t.[ACTUALSTARTDATEDIMID] = s.[ACTUALSTARTDATEDIMID],
			t.[ACTUALENDDATEDIMID] = s.[ACTUALENDDATEDIMID],
			t.[ISINCLUDED] = s.[ISINCLUDED],
			t.[ETLCONTROLID] = s.[ETLCONTROLID],
			t.[SOURCEDIMID] = s.[SOURCEDIMID]
output $action,
	case 
		when deleted.[ETLCONTROLID] is null
			then 1
		else 0
		end,
	case 
		when deleted.[ETLCONTROLID] is not null
			then 1
		else 0
		end
into @COUNTS;

select count(*) as [TOTAL],
	isnull(sum([INSERTED]), 0) as [INSERTED],
	isnull(sum([UPDATED]), 0) as [UPDATED]
from @COUNTS

Click OK.

Change the Adding Indices task.
1. Double-click Adding Indices. The Execute SQL Task editor screen appears.
2. From General > SQL Statement > SQLStatement, change the statement to:
  exec BBDW.[CREATE_OR_DROP_FACT_INTERACTIONACTUALTIMES_EXT_INDICES] 1;
Adjust the package properties.
1. Go to the Package Explorer tab.
2. Right-click the package and select Properties.
3. From ID, click the drop-down and select Generate New ID.
4. Change the name to BBDW_FACT_INTERACTIONACTUALTIMES_EXT.
5. Save the package file.

Copy the package to the SSIS folder for extensions:

C:\Program Files\Blackbaud\bbappfx\MSBuild\Datamarts\BBDW\Extend\SSIS

Update the package manifest (BBDW_PackageList_EXT.txt):

C:\Program Files\Blackbaud\bbappfx\MSBuild\Datamarts\BBDW\Extend\SSIS

"Enabled","Package"
"1","BBDW_FACT_INTERACTIONACTUALTIMES_EXT.dtsx"

Deploy and Refresh the Warehouse

For information about how to deploy and refresh the warehouse, see the online guides at Blackbaud Data Warehouse.

Change the Report

As discussed in Create a Data Warehouse Version, the query on which the report relies can follow the same structure as the transactional database version. The difference here is the data warehouse version queries the a view created through data warehouse extensions and the stored procedures used by the RDL dataset are created through these revisions instead of a through the Report Spec.