Configuration

The basics

To set up DSE you configure it with your clients, entities, attributes, etc. This is currently done with SQL scripts. To show how this is done, we will use an example scenario and show how it is configured in SQL step-by-step. After each step we present what the result would look like in the corresponding database tables. These tables are heavily simplified to just give you an idea of the result. You can run the sql scripts in a real DSE database to see what the result actually would look like.

Our example consists of two systems that need to have their data synchronized; A system named "CRM" and a system named "ERP". After analyzing the data models for the two systems, we have come up with the following normalized model, mapping and access rights:

The ERP Customer entity is a denormalized model where a person can be represented several times, but as different customers. The normalized model therefore consists of two entities.

During the analyze we mark the normalized attributes with the type of attribute according to the following table:

We have marked each client attribute with the access rights that the client will have to the corresponding normalized attribute.

Options

--- Set the configuration level to one of [Debug, IntTest, SysTest, AccTest and Release]
EXEC sp_Option_SetType2 'All', 'ConfigurationLevel', 'Configuration', 'Debug'

Clients

We will configure which clients that take part in the synchronization with one or more entities. There is one preconfigured client, "XlentDSE" that refers to DSE itself.

Example: The clients that are taking part in the data synchronization are a CRM system named "CRM" and an ERP system named "ERP.

---
--- Create clients
--- 

--- Set up the clients with the endpoint where the entity resources resides for datasync GET, PUT, POST
EXEC sp_Client_Create 'CRM', 'http://common.nexus.local/DSE.Test.MockAdapterRest/smoke-testing-company/local/CRM', NULL
EXEC sp_Client_Create 'ERP', 'http://common.nexus.local/DSE.Test.MockAdapterRest/smoke-testing-company/local/ERP', NULL

Result in the Client table:

Entities

We will configure the normalized entities and the client entities. The normalized entities have their name prefixed with Md by convention.

Example: The CRM system has a Contactentity that corresponds to the MdPerson entity. The ERP system has a Customer object that is a denormalized version of MdCustomer and MdPerson. We want the Person entity in the CRM system to be automatically created when a new MdPerson object has been established in DSE.

---
--- Create entities
--- sp_Entity_Create ClientName, EntityName, IsMd, DoCreate=0
---

--- The normalized entities
EXEC sp_Entity_Create 'XlentDSE', 'MdPerson', 1
EXEC sp_Entity_Create 'XlentDSE', 'MdCustomer', 1

--- The client entities
EXEC sp_Entity_Create 'CRM', 'Contact', 0, 1
EXEC sp_Entity_Create 'ERP', 'Customer', 0

Result in the Entity table:

Key types

The client entities must have a primary key. They should be configured as key types in DSE. The key type specifies which normalized entity that the key is associated to and if we can send get and update requests to the client. We should not send updates to a "deaf" client.

Example: We will set up the key type for the primary key for the CRM Contact entity and the ERP Customer entity.

---
--- Create key types
--- sp_KeyType_Create MdEntityName, ClientName, ClientEntityName, KeyTypeName, IsSendGetAllowed=1, IsSendUpdateAllowed=1
---

EXEC sp_KeyType_Create 'MdPerson', 'CRM, 'Contact', 'CrmContactKeyType'
EXEC sp_KeyType_Create 'MdCustomer', 'ERP', 'Customer', 'ErpCustomerKeyType'

Result in the KeyType table:

Codes

If you have enumerations that you want translated between systems, then you will need to configure code types and specify which valid values that code type has. We configure one code type that is used internally in DSE and one code type per collection of values.

There are preconfigured codes and code types for boolean values, countries and currencies.

Example: The CRM system stores the gender of a person as "M" (Male), "F" (Female) or "U" (Unknown). The ERP system uses integer values; 1 (Male), 0 (Female), 9 (Unknown). Internally in DSE we will use the values "Male", "Female", "Unknown".

---
--- Create code types and codes
--- sp_CodeType_Create ClientName, CodeTypeName, ReferenceName=NULL
--- sp_Code_Create ClientName, CodeTypeName, Value, ReferenceValue=NULL
---

--- Internal gender code
EXEC sp_CodeType_Create 'XlentDSE', 'GenderCode'
EXEC dbo.sp_Code_Create 'XlentDSE', 'GenderCode', 'Male'
EXEC dbo.sp_Code_Create  'XlentDSE', 'GenderCode', 'Female'
EXEC dbo.sp_Code_Create  'XlentDSE', 'GenderCode', 'Unknown'

--- CRM gender code
EXEC sp_CodeType_Create 'CRM', 'CrmGenderCode', 'GenderCode'
EXEC dbo.sp_Code_Create 'CRM', 'CrmGenderCode', 'M', 'Male'
EXEC dbo.sp_Code_Create  'CRM', 'CrmGenderCode', 'F', 'Female'
EXEC dbo.sp_Code_Create  'CRM', 'CrmGenderCode', 'U', 'Unknown'

--- ERP gender code
EXEC sp_CodeType_Create 'ERP', 'ErpGenderCode', 'GenderCode'
EXEC dbo.sp_Code_Create 'ERP', 'ErpGenderCode', '1', 'Male'
EXEC dbo.sp_Code_Create  'ERP', 'ErpGenderCode', '0', 'Female'
EXEC dbo.sp_Code_Create  'ERP', 'ErpGenderCode', '9', 'Unknown'

Result in the CodeType table:

Result in the Code table:

Attributes

We will configure the attributes for each entity. Each of the attribute types have their own create method (Normal, Key, Code and Reference):

Example: We have small set of attributes which are rather self explanatory.

---
--- Create attributes
--- sp_Attribute_Create ClientName, EntityName, AttributeName
--- sp_Attribute_CreateKey ClientName, EntityName, AttributeName, KeyTypeName
--- sp_Attribute_CreateReference ClientName, EntityName, ReferenceEntityName, AttributeName
--- sp_Attribute_CreateCode ClientName, EntityName, AttributeName, CodeTypeName
---

--- XlentDSE.MdPerson
EXEC sp_Attribute_CreateKey 'XlentDSE', 'MdPerson', 'CrmContactId', 'CrmContactKeyType'
EXEC sp_Attribute_Create 'XlentDSE', 'MdPerson', 'GivenName'
EXEC sp_Attribute_Create 'XlentDSE', 'MdPerson', 'Surname'
EXEC sp_Attribute_Create_Code 'XlentDSE', 'MdPerson', 'GenderCode', 'GenderCode'

--- XlentDSE.MdCustomer
EXEC sp_Attribute_CreateKey 'XlentDSE', 'MdCustomer', 'ErpCustomerId', 'ErpCustomerKeyType'
EXEC sp_Attribute_CreateReference 'XlentDSE', 'MdCustomer', 'MdPerson', '_Person'

--- CRM.Contact
EXEC sp_Attribute_CreateKey 'CRM', 'Contact', 'Id'
EXEC sp_Attribute_Create 'CRM', 'Contact', 'FirstName'
EXEC sp_Attribute_Create 'CRM', 'Contact', 'LastName'
EXEC sp_Attribute_Create_Code 'CRM', 'Contact', 'Gender', 'CrmGenderCode'

--- ERP.Customer
EXEC sp_Attribute_CreateKey 'ERP', 'Customer', 'Id', CrmContactKeyType
EXEC sp_Attribute_Create 'ERP', 'Customer', 'Name1'
EXEC sp_Attribute_Create 'ERP', 'Customer', 'Name2'
EXEC sp_Attribute_Create_Code 'ERP', 'Customer', 'Sex', 'ErpGenderCode'

Result in the Attribute table:

Attribute mappings

We will configure how the client attributes maps to the normalized attributes.

Example: For the CRM system the mappings are one-to-one to the MdPerson entity. For the ERP system that is denormalized, we will map most fields to MdPerson to ensure that all customers that are the same person should have the same values for these fields. The only exception is the ERP Customer Id that is mapped to MdCustomer so that we get a unique MdCustomerfor each unique ERP Customer. If the ERP Customer have other customer fields that should be synchronized with other systems with customer data those would also be mapped to the MdCustomerentity. Examples of such fields would be the number of orders that the customer has ordered or the delivery address to the customer.

---
--- Create attribute connections
--- sp_AttributeConnection_Create MdEntityName, MdAttributeName, ClientName, ClientEntityName, ClientAttributeName
---

-- CRM.Contact
EXEC sp_AttributeConnection_Create 'MdPerson', 'CrmContactId',  'CRM', 'Contact', 'Id'
EXEC sp_AttributeConnection_Create 'MdPerson', 'GivenName',  'CRM', 'Contact', 'FirstName'
EXEC sp_AttributeConnection_Create 'MdPerson', 'Surname',  'CRM', 'Contact', 'LastName'
EXEC sp_AttributeConnection_Create 'MdPerson', 'GenderCode',  'CRM', 'Contact', 'Gender'

-- ERP.Customer
EXEC sp_AttributeConnection_Create 'MdCustomer', 'ErpCustomerId',  'ERP', 'Customer', 'Id'
EXEC sp_AttributeConnection_Create 'MdPerson', 'GivenName',  'ERP', 'Customer', 'Name1'
EXEC sp_AttributeConnection_Create 'MdPerson', 'Surname',  'ERP', 'Customer', 'Name2'
EXEC sp_AttributeConnection_Create 'MdPerson', 'GenderCode',  'ERP', 'Customer', 'Sex'

Result in the AttributeConnection table:

Attribute access

We will configure the access rights that each client has to the normalized entity attributes.

Example: The CRM system can change all fields and is dominant when it comes to a persons name and gender. It is not allowed to clear the value of the surname or the gender. The ERP can create all fields, but it can only update the given name and it can't clear any fields.

---
--- Create attribute connections
--- sp_EntityAccess_Update ClientName, MdEntityName, AccessRights
--- sp_AttributeAccess_Update ClientName, MdEntityName, MdAttributeName, AccessRights
--- C = Create, R = Read, U = Update, D = Delete, ! = Dominant
--- (R) = Will not get updated values until they have been approved for this client
--- (U) = The value will not be accepted into the golden record until the value from this client has been approved
---

--- CRM
EXEC dbo.sp_EntityAccess_Update 'CRM', 'MdPerson', 'CRU-'
EXEC dbo.sp_AttributeAccess_Update 'CRM', 'MdPerson', 'CrmContactId', 'CR--'
EXEC dbo.sp_AttributeAccess_Update 'CRM', 'MdPerson', 'GivenName', 'CRUD!'
EXEC dbo.sp_AttributeAccess_Update 'CRM', 'MdPerson', 'Surname', 'CRU-!'
EXEC dbo.sp_AttributeAccess_Update 'CRM', 'MdPerson', 'GenderCode', 'CRU-!'

--- ERP
EXEC dbo.sp_EntityAccess_Update 'ERP', 'MdCustomer', 'CRU-'
EXEC dbo.sp_AttributeAccess_Update 'ERP', 'MdCustomer', 'ErpPersonId', 'CR--'
EXEC dbo.sp_EntityAccess_Update 'ERP', 'MdPerson', 'CRU-'
EXEC dbo.sp_AttributeAccess_Update 'ERP', 'MdPerson', 'GivenName', 'CRU-'
EXEC dbo.sp_AttributeAccess_Update 'ERP', 'MdPerson', 'Surname', 'CR--'
EXEC dbo.sp_AttributeAccess_Update 'ERP', 'MdPerson', 'GenderCode', 'CR--!'

Result in the EntityAccess table:

Result in the AttributeAccess table:

Advanced

• Pseudo-id
• Validation
• Transformation
• Linked fields