Import Data from Tables (Master Data Services)

APPLIES TO: yesSQL Server (Windows only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

You can add data and make data changes to a model in Master Data Services, in bulk.

Prerequisites

  • You must have permission to insert data into the stg.<name>_Leaf, the stg.<name>_Consolidated, stg.<name>_Relationship table in the Master Data Services database.

  • You must have permissions to execute either the stg.udp_<name>_Leaf, stg.udp_<name>_Consolidated, or the stg.udp_<name>_Relationship stored procedure in the Master Data Services database.

  • The model must not have a status of Committed.

To add, update, and delete data in the Master Data Services database

  1. Prepare the members for import into the appropriate staging table in the Master Data Services database, including providing values for the required fields. For an overview of staging tables, see Overview: Importing Data from Tables (Master Data Services)

  2. Open SQL Server Management Studio and connect to the Database Engine instance for your Master Data Services database.

    For more information, see SQL Server Management Studio.

  3. Import data into the staging tables by using the SQL Server Import and Export wizard.

    For more information, see SQL Server Import and Export Wizard.

  4. Load the data from the staging tables to the Master Data Services tables, by doing one of the following

    • Run the staging stored procedure that corresponds to the staging table that you want to move data to.

      For an overview of staging stored procedures and staging tables, see Overview: Importing Data from Tables (Master Data Services). For more information about parameters for staging stored procedures, and a code example, see Staging Stored Procedure (Master Data Services).

    • Use the Integration Management functional area of Master Data Management.

      On the Staging Batches page, select the model to which you're adding data to, in the drop-down list, and then click Start Batches. The status of the batch processing is indicated in the Status field. For more information about the statuses, see Import Statuses (Master Data Services).

      Staging Batches Page in Master Data Manager

      The staging process is started at intervals determined by the Staging batch interval setting in Master Data Services Configuration Manager. For more information, see System Settings (Master Data Services).

  5. View errors that occurred during staging. For more information, see View Errors that Occur During Staging (Master Data Services) and Staging Process Errors (Master Data Services).

  6. Validate the data against business rules.

    In Master Data Manager, navigate to the Explorer functional area for your model, and then apply business rules to validate the data. For more information , see Validate Specific Members against Business Rules (Master Data Services). You can also use a stored procedure to validate the data. For more information, see Validation Stored Procedure (Master Data Services).

    When you load data by from the staging tables, the data is not automatically validated against business rules. For more information on what validation is and when it occurs, see Validation (Master Data Services).