By: Siddharth Mehta
Overview
Any data hosting and/or management system generally needs to integrate with other source systems. We built a model and entities in our last chapter, but we manually added data to it. In real life scenarios, manual data entry is not a preferred option. Ideally the expected architecture would be importing data on a regular basis from external systems and merging the data into existing data repositories. In this chapter we would look at how to import data into Master Data Services (MDS) and enable integration with external source systems.
Explanation
Assuming that we have built a new model with entities, attributes and members similar to the ProductSample model we installed, we can use the same for the exercise we are going to perform in this chapter. In case you have not developed a similar model, you can use the sample ProductSample model itself. Follow the steps below to understand how to import data and enable integration.
In Master Data Manager, navigate to the Color entity in the ProductSample model. Whenever you create a new entity, two attributes are available by default - Name and Code. If you explore the Color entity in the ProductSample model, you should be able to see the data and attributes as shown below.
Whenever we create an entity, a corresponding staging table gets created in the MDS database. In case you opted to have the name of the staging table as that of entity, then you should be able to find the table with the same name in the MDS database. All staging tables are part of the stg schema. As the attributes in Color entity are leaf level attributes, the table would have the suffix _leaf. Open SQL Server Management Studio (SSMS), navigate to MDS database and find the table named stg.Color_leaf. If you study the schema of this table carefully, you would be able to find the Code and Name fields along with other import specific fields. Query this table and you would see that the table contains no records.
The staging architecture expects that data would be loaded in staging tables by external systems. For example, SQL Server Integration Services (SSIS) can be used to extract the required data from source systems and load the data into the staging table. MDS would detect the data along with the intended operation and then merge the changes into the existing data. Consider reading this article to understand the schema and the expectation of data in each of the fields of the staging table.
Lets say that we intend to add a new product color to the color entity with the code "vio" and name "violet". Let's add a new record in the staging table as shown below. In real world project scenarios, this data would be loaded into staging tables using some ETL tools like SSIS.
In Master Data Manager, open Integration Management and you should be able to find the record that we just added to the staging table.
Select the record, and click on the Start Batches button to import the record into the Color entity. You will be prompted to select the version of the model against which you want to add this record. If you do not have any version created, you will be able to see a default version Version 1. Select this and click OK. This will queue your record for importing and merging into the entity. Make sure that SQL Server Agent is running and the MDS_MDS_Sync job is enabled. Once this job runs the merge process will happen. You can also run this job manually to merge the staging record immediately.
After the merge is successful, you should be able to see the below status. Explore the Color entity now and you should be able to find the new member in the entity.
We studied the staging architecture and performed an exercise in which we imported data into an entity using staging tables. Having understood different ways of storing and importing data, in the next chapter we will look at some of the more complex MDS constructs to organize data in a more advanced manner.
Additional Information
- In the above exercise, we added a member into the entity. Try our different options like updating and deleting the entity. Also try to create different batches using different Batch names and learn how batch tags group records.
