Solution: DSP
Version: 6.5 and 6.6
Component: Design and Map
1. Objective
Prior to version 6.5, all Source Tables had to exist under the same database to be able to use them in Map. Since the version 6.5, release this approach has been enhanced.
According to the Release Notes: “the process for establishing relationships has been updated. External sources, Add row sources (formerly primary source), and Update Row sources (formerly Secondary sources) can now be used when creating relationships.”
I am going to cover what External, Add Row and Update Row sources are and how to use them.
Please note that currently there is a bug in 6.6.0 and a workaround is needed (also covered in this post). It is planned to be fixed in 6.6.2.
2. Procedural Steps Involved
2.1 Design
2.1.1 Add Row source
An Add Row source is the source table where records are migrated from and inserted into the target table. Records in an Add Row source table are used for reconciliation.
[Example] we want to migrate Warehouse object and the source of data is coming from a table that is in sdbFlatFile database. Please note that here we talk about Insert data, therefore the source is going to generate your unique entries. You can use additional tables to retrieve some additional information for your field in the next section.
2.1.2 Update Row source
If data must originate from a source other than the Add Row source table, add the Update Row source and then specify the Update Row source connection to the Add Row source table using relationship joins (in Map).
An Update Row source must have a relationship with an Add Row source.
An Update Row source can be either a view or a table.
An update table will not insert data into the target table, it is used only to update fields with information that you do not have in your main table.
Update Row source is not defined in this section (Design), it is done in Map (covered in the section 2.1.2).
2.1.3 External source
An External source is an Update Row source that is external to the system used in the Add Row source when establishing a relationship.
When an External source is used, the External Source ID field on the Field Mappings page must be populated with the source ID.
An External source is not treated as an Add Row source, and is not synced from Target Design when the target is synced. It must be added on the Target Source (Update Row) page and configured manually.
You can add multiple External Sources if you need to read data from multiple databases.
[Example] we need to retrieve additional information from a table that is not in the same data base than the one of our Add Row source (this example: sdbMDM_Location). In this case we need to use an External source to be able to use this table later on in our DSW:
2.2 Map
2.1.1 Add Row source
Once the Source data source (source database) has been defined in Target Design, select the source Table in Map. You can add as many tables as you need from the same database.
[Example] Our source table is BRANCH
Note that SourceID is Flatfile which is the one that has been defined in Design as Add Row and it cannot be edited.
Note if you see that SourceID is blank, you need to Sync from Target Design
2.1.2 Update Row source
Update Row tables are always associated to a specific Add Row table by creating a relationship between them.
You can use an Update Row source without using an External source when the table exists in the Add Row data base.
[Example] We need extra information from the table Accounts_Mapping.
Click on Update Row sources.
Then, add as many tables as you need.
You can see that the SourceID allows two databases (Add Row and External Source databases selected in Design).
2.1.3 External source
Because an External Source is a type of Update Row source, a relationship must be defined for it.
To be able to select the table from an External database, you have to have added it in Design as an External source.
[Example] We need 3 tables: Contacts, CostCentres and CostCentreManagers.
2.1.4 Relationships
Relationships are a powerful feature of DSP that is used to create complex relationships between tables. Those relationships are identical to Inner Joins.
[Example] We want to retrieve data from Contacts but there is not a direct relationship between BRANCH and Contacts. We need to use an intermediate table. A recommended method is to start with SQL and build the relationship in a graphical way:
Then, go to DSP and apply the Relationship.
NOTE: The tables in SDB* have different names than those created in DSW*.
IMPORTANT:
Every time you create a new Relationship, you will need to click the validation button for the relationship to be generated.
Once it is created, I recommend to check the join in SQL. Click in the vertical view to see it:
2.1.5 Source Rules
The configuration steps above are required to make these tables and their fields available in the mapping section. When we want to create either an action Copy or Rule, there are two approaches:
1. Data is coming from Update Source (same database as Add Row)
In this scenario there is no need to do anything else, the tables are available in the dropdown:
2. Data is coming from External Source (different database)
In the previous screenshot, there are only two tables available in the drop-down, the Add Row and the Update Row. The External source is not an option. In the Vertical View of the Field Mappings page, select the External Source ID and the Target Relationship ID, to display the External source.
Please note that currently there is a bug in 6.6.0 and a workaround is needed (also covered in this post). It is planned to be fixed in 6.6.2.
In 6.5:
[Example] in the below image you can see that Primary source is coming from FlatFile, then we need to select External source ID = MDM_location (external source):
In 6.6.0 the issue is that External ID has been hidden, there is only a Target Relationship ID:
Refer to the next section to check the current workaround.
3. Appendix – External Source in 6.6.0 workaround
Please follow the steps described by Support:
It will be necessary to update the cMap.dbo.ttTargetFieldMapping.ExternalSourceID value directly from SSMS.
When doing so:
- It will probably be best to filter on the dbo.ttTargetFieldMapping.SourceDatabaseObjectIDvalue. This will restrict the 'Field Mapping' resultset to a single 'Source' in Map.
- The easiest way to retrieve the SourceDatabaseObjectID value is to click the 'Source' record in Map and then hover your mouse over the Target Sources page title. After a couple seconds, the page keys will appear in the bottom-left corner:
Finally, you can take a look at the allowable options for the ExternalSourceID value by executing this query: SELECT SourceID, Source FROM cMap.dbo.webTargetSource_ParentSourceIDList WHERE ParentSourceDatabaseObjectID = '<your SourceDatabaseObjectID>'
- [SourceID] holds the allowable options for ExternalSourceID
- <your SourceDatabaseObjectID>should be replaced with the GUID of the particular 'Source' record in Map that you're working with (from item 2)
In addition to this I usually use a script to make it easier and quicker. Using the following query you have to pass the SourceDatabaseObjectID (described before) and the name of the field that you need to use in Map. Then, with the result, update the table cMap.dbo.ttTargetFieldMapping.SourceDatabaseObjectID. I would have automated the process but I prefer do it manually.
The code for the query:
DECLARE @SourceDatabaseObjectID NVARCHAR(50),
@Field Nvarchar(50)
SET @SourceDatabaseObjectID='0c750bde-5612-4820-9e4b-790054c75824'
SET @Field = 'MWPHNO'
SELECT SourceID, Source FROM cMap.dbo.webTargetSource_ParentSourceIDList
WHERE ParentSourceDatabaseObjectID = @SourceDatabaseObjectID
SELECT Field,ExternalSourceID,* FROM cMap.dbo.ttTargetFieldMapping INNER JOIN
cMap.dbo.ttTargetField ON cMap.dbo.ttTargetFieldMapping.TargetFieldID = cMap.dbo.ttTargetField.TargetFieldID
WHERE SourceDatabaseObjectID = @SourceDatabaseObjectID and Field = @field
I hope you have found this post interesting.
Please do not hesitate to ask any question.
Comments
1 comment