Warehouse table: StaffAssignment

The StaffAssignment table holds a single field of data from the CRM Opportunity view which changes frequently enough that it is not considered "slow-changing" with the rest of the opportunity data (most opportunity data is "slow-changing" and warehoused in the AnalyticsMaster table).

Staff Assignment records are joined to the parent record by the OpportunityGUID.

This table is made available in Stage II to clients via the Advanced Export under the name StaffAssignment_Local. In Stage IIa, this data is flattened into the OpportunityPerson_Local table instead.

Stage II: 

 columntype required extract sample dataexport analytics 
OpportunityGUIDuniqueidentifieryes join[guid]join join
StaffAssignmentGUIDuniqueidentifier yes generated, not from CRM[guid] primary key
StaffAssignednvarchar(100) yes Opportunity. OwnerIdName Nick TorresXport. StaffAssignedvw__Master. Staff_Assigned 
StaffEffectiveDatedatetime yes effective dated by extract 2010-02-24 00:00 used to calc. IsCurrent 
IsCurrentbit yes calculated 1 filter = 1 

 

There is a unique index on OpportunityGUID + StaffEffectiveDate: only one staff assignment change per day is recorded.

There is a search index on OpportunityGUID + IsCurrent + StaffAssigned: this retrieves the current staff assignment for an opportunity for use in the analytics view vw_DescAnalytics_Master.

This table is populated from Xport used as a staging table, not directly from the Opportunity view in the CRM database.

 


Comments