Entity_based_Staging - TechNet Gallery
Download
Report
Transcript Entity_based_Staging - TechNet Gallery
Agenda
Staging Overview
New Benefits of EBS
Staging Tables structure
Import Types and Batch Tags
Error Management
• SQL Server 2008 R2 Staging Framework
• 3 Tables – tblStgMember,
tblStgMemberAttribute,
tblStgRelationship
• Data was loaded as name-value pairs
• SQL Server 2012 – Entity Based Staging
• Staging Table is created per Entity
• Hierarchy enabled entities will have 2
additional tables – Consolidated
Members and Relationships
•
•
•
•
More intuitive interface
Ability to secure staging at the entity level
Significant Performance improvements
Easy integration with SQL Server
Integration Services
Table
Description
stg.<Entity>_Leaf
Create, update, and delete leaf members and their
attributes.
stg.<Entity>_Consolidated Create, update, and delete consolidated members
and their attributes.
stg.<Entity>_Relationship Move members in an explicit hierarchy.
Import ID
Name
Description
0 or blank
Merge Optimistic
All populated record information will loaded into the Entity when either the member does not exist or the
member exists. Nulls will be ignored.
1 Insert
Only new member records and their attributes will be loaded into the MDS entity all existing records will be
flagged as member code already exists. No attributes on pre-existing records will be updated.
2 Merge Overwrite
All populated record information will loaded into the Entity when either the member does not exist or the
member exists any blank element in the EBS table will overwrite values within the MDS entity.
3 Delete
Only the member code will be evaluated and these records will be soft deleted from the MDS entity
4 Purge
Only the member code will be evaluated and these records will be removed from the MDS entity version that
these records are staged into.
5
Delete Override
6
Purge Override
Only the member code will be evaluated and these records will be soft deleted from the MDS entity. This will set
any Domain Based Attribute references to Null to complete the operation.
Only the member code will be evaluated and these records will be removed from the MDS entity version that
these records are staged into. This will set any Domain Based Attribute references to Null to complete the
operation.
• 50 character tag to allow multiple
simultaneous loads through entity staging.
• Trigger stored procedure with the provided
batch tag.
• System batch Id will be assigned in the
import process.
• In SQL Server 2008 R2 1 error per row
• In SQL 2012 all errors are now logged
• Error views can be reviewed to correct
issues.
•
Stg.viw_<entity staging name>_MemberErrorDetails