Org_Warehouse table definitions

Specific tables
Edit section

Note: the definitive table structures are given in the document Warehouse Column Dictionary. They are repeated here for convenience, but if there is a conflict then the Warehouse Column Dictionary should be assumed to hold the correct version.

See also: Tables for use in Advanced Export


General considerations 
Edit section

Each of the current warehouse tables corresponds roughly to a view in the CRM database, and this view is usually present in the CRM  database in two forms, filtered and unfiltered:  in addition to security considerations, the filtered views are slightly more flattened and contain more fields.

Versioning
Edit section

As of September 30, 2010, the 3.1 upgrade is in production and version information is now available from the Versionstable. There are several warehouse versions which have been in prior use, referred to in this documentation as "Stage I" "Stage II", "Stage IIa" and "Stage III".

Prior to 3.1 schema there was no versioning within the database, but here is a quick guide to identifying version by table presence:

  1. Contains a StaffAssignment_Local table: Stage II
  2. Contains no StaffAssignment_Local and no OpportunityPerson_Fast: Stage IIa
  3. Contains OpportunityPerson_Fast: Stage III
  4. Contains a Versions table: 3.1 schema or higher, with version information given there 


UTC/local dates 
Edit section

In the Stage I databases, many date fields are duplicated, one ending in -utc and one not; these match the date fields which are available in the filtered views. In the unfiltered views, the single date field (which does not end in -utc for the unfiltered views) corresponds to the date fields which end in -utc in the first two sources. In the Stage II database, only UTC fields are present. The "local" time represented by the non-UTC date field is the local time for the account which loads the filtered view, and for the executive account which runs the extract this always corresponds to the local time of the Atlanta office (United States Eastern Standard Time, or UTC -5 with daylight savings applied).

For example, the following three fields contain the same data:

Org_Warehouse.dbo.Interests.modifiedonutc
Org_MSCRM.dbo.A422_Interest.modifiedon 
Org_MSCRM.dbo.FilteredA422_Interest.modifiedonutc 

 The following two fields do not have a corresponding field in an unfiltered view:

Org_Warehouse.dbo.Interests.modifiedon (not present in the Stage II warehouse)
Org_MSCRM.dbo.FilteredA422_Interest.modifiedon

However, the local date can be calculated from the UTC date using that data, the fn_UTCToTzSpecificLocalTime andfn_FindUserGuid functions defined by CRM in the CRM database, and data from the SystemUserBase and UserSettingsBasetables in the CRM database, as in the following example:

SELECT 'modifiedon' = Org_MSCRM.dbo.fn_UTCToTzSpecificLocalTime(
		s.modifiedonutc,
		us.TimeZoneBias,us.TimeZoneDaylightBias,us.TimeZoneDaylightYear,us.TimeZoneDaylightMonth,
		us.TimeZoneDaylightDay,us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute,us.TimeZoneDaylightSecond,0,
		us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias,us.TimeZoneStandardYear,us.TimeZoneStandardMonth,
		us.TimeZoneStandardDay,us.TimeZoneStandardHour,us.TimeZoneStandardMinute,us.TimeZoneStandardSecond,0,
		us.TimeZoneStandardDayOfWeek)
FROM Org_Warehouse.dbo.Interests AS s
     LEFT JOIN Org_MSCRM.dbo.SystemUserBase u on (u.SystemUserId = Org_MSCRM.dbo.fn_FindUserGuid() and u.IsDisabled = 0)
     LEFT JOIN Org_MSCRM.dbo.UserSettingsBase us on us.SystemUserId = u.SystemUserId

State and status codes
Edit section

The unfiltered views contain two numeric fields, StateCode and StatusCode, which together hold the state of the record.  In the filtered views and the warehouse tables, these fields are expanded to include the StateCodeName and StatusCodeName fields which give the text of the state.

  • Note that these are not the same as the DeletionStateCode, a field found in the unfiltered views, which is 0 for undeleted records.  Records with a nonzero DeletionStateCode are excluded entirely from filtered views and warehouse tables.  (It is presumed that records with a nonzero DeletionStateCode are in the process of being removed from the database.)

The text of the state is read from the FilteredStatusMap table in the CRM database;  states range from the simple Active/Inactive to nine possible states for an Opportunity (Open: In Progress, Open: On Hold, and seven different closed states).  It can be retrieved with a simple join statement, as in this example:

SELECT a.statecode, st.StateCodeName, a.statuscode, st.StatusCodeName
FROM Org_MSCRM.dbo.A422_Award AS a
INNER JOIN Org_MSCRM.dbo.FilteredStatusMap AS st
  ON a.StateCode = st.StateCode AND a.StatusCode = st.StatusCode 
  AND st.FilteredViewName = 'FilteredA422_Award'

which is equivalent to:

SELECT a.StateCode, a.StateCodeName, a.StatusCode, a.StatusCodeName
FROM Org_MSCRM.dbo.FilteredA422_Award AS a