This is just a quick tip for Master Data Services, specifically around the setup of new entities. When creating new Master Data Services entities one of the settings that you can choose is the Staging Table Name, which is an optional setting:
As it says in the screenshot, if you do not complete the field, the entity name will be used. This might be ok in simple, single model environments, but you can easily get your staging tables into a mess if you don’t complete this setting. MDS will try and default the staging table name to the name of the entity. But if you’ve got several models that all have the same entity, then MDS will add an underscore and number to the end of your staging table name. For example, here there is a Region entity within the Customer model and within the Engineer model. But which staging table belongs to which model?
The approach that I tend to take in the entity setup is give the staging table an explicit name, which is the Model name prefixed with the Entity name. E.g. I would choose EngineerRegion and CustomerRegion. This results in a much clearer set of staging tables:
We now know which table belongs to which model. As the MDS Staging Tables all follow a particular pattern, we can now create a generic set of ETL packages using SSIS. I won’t go into this in huge detail, but by specifying the model name and the entity name as variables you can create yourself a template MDS package that can be easily copied and pasted to load other entities. For example below, the staging table load, staging stored procedure and (optionally) model validation all are controlled by the following variables:
The variables are used as expressions in the Control Flow and Data Flow components resulting in a generic MDS loading template:
profeExcellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites! professional training