DIM_SOURCE

This describes all data sources being fed into the Data Warehouse. By default there is only 1 which is BBEC and this pulls some info from the INSTALLATIONINFO table.

Primary Key

Primary Key	Field Type
SOURCEDIMID	int

Fields

Field	Field Type	Null	Notes	Description
SOURCESYSTEMID	nvarchar(100)	yes		dbo.[INSTALLATIONINFO].[CLARIFYSITEID]
SOURCENAME	nvarchar(100)	yes		dbo.[INSTALLATIONINFO].[PRODUCT]
SOURCESHORTNAME	nvarchar(20)	yes		Derived in SSIS Package; Set to 'BBEC' for BBEC Data Source
SOURCEVERSION	nvarchar(25)	yes		Custom field to describe the Source application version
ORGNAME	nvarchar(100)	yes		dbo.[INSTALLATIONINFO].[INSTALLATIONNAME]
FISCALYEARDAYMONTHSTART	nvarchar(5)	yes		Derived from dbo.[INSTALLATIONINFO].[FISCALYEARFINALMONTH] with 1 added (or set to 1 if 12) and day added - e.g. 6 would be 7/1, 12 would be 1/1, etc
FISCALYEARMONTHSTART	tinyint	yes		Derived from dbo.[INSTALLATIONINFO].[FISCALYEARFINALMONTH] with 1 added (or set to 1 if 12)
MAINTRANSACTIONTYPE	nvarchar(25)	yes		Custom field to describe the Start/End Date data. For BBEC this would be 'REVENUE'
MAINTRANSACTIONSTARTDATE	datetime	yes		First transaction date for MAINTRANSACTIONTYPE
MAINTRANSACTIONENDDATE	datetime	yes		Last transaction date for MAINTRANSACTIONTYPE
EARLIESTREPORTDATE	datetime	yes		Custom field used to restrict reporting and date ranges
LATESTREPORTDATE	datetime	yes		Custom field used to restrict reporting and date ranges
CURRENTFISCALYEARSTARTDATE	datetime	yes		Start Fiscal Year Date based on LASTREFRESHDATE
CURRENTFISCALYEARENDDATE	datetime	yes		End Fiscal Year Date based on LASTREFRESHDATE
LASTREFRESHDATE	datetime	yes		Set to Refresh date
ISINCLUDED	bit	yes		Flag indicating when data should be included in results.
ETLCONTROLID	int	yes		ID generated through the ETL process

Indexes

Index Name	Fields	Unique	Primary	Clustered
PK_DIM_SOURCE	SOURCEDIMID	yes	yes	yes