关于Microsoft MDS中ETL Staging变更集审批及强制审批方法的咨询

Great question! Yes, you absolutely can enforce that changes submitted via Microsoft MDS staging tables go through the same approval workflow as manual edits. Here's a step-by-step breakdown of how to set this up:

Prerequisite #

First, confirm your MDS model already has an approval workflow configured (since you mentioned manual edits can use changesets, this is likely already in place—but double-check that the workflow is active for your target entity).

1. Configure Staging Tables to Route Changes to a Changeset #

By default, MDS applies staging table changes immediately unless you explicitly associate them with a changeset. Here's how to override that:

• For your target entity's staging table (e.g., stg.<YourModelName>_Leaf for leaf entities, stg.<YourModelName>_Consolidated for consolidated entities), populate the ChangeSetID column for every row you're importing.
  • You can generate a unique GUID for the changeset (use NEWID() in SQL if loading via queries) or create a named changeset via the MDS UI/API first and use its ID.
• Set the Status_ID column to 0 (Pending) for all staging rows—this tells MDS to hold the changes in the changeset instead of applying them immediately.

2. Enforce Permissions to Block Direct Commits #

To prevent users/ETL processes from bypassing approval, adjust role permissions:

• For the account running your ETL staging jobs, grant only the Create Changeset permission for the target model/entity. Do NOT grant Commit Changeset access.
• Ensure only designated approvers have the Approve Changeset permission—this way, only they can review and finalize the staging changes.

3. Automate Changeset Creation (For Automated ETL Pipelines) #

If your ETL process is automated, you can integrate with MDS's API to create changesets programmatically before loading data:

• Use the MDS Web API's CreateChangeset method to generate a new changeset, then retrieve its ID.
• Inject this ID into the ChangeSetID column of your staging table rows before triggering the staging batch.
• Example pseudo-code (C#):

  // Initialize MDS client and create a changeset
  var mdsClient = new MDSClient("https://your-mds-instance/api/service");
  var createRequest = new CreateChangesetRequest
  {
      ModelId = new Identifier { Name = "YourModel" },
      EntityId = new Identifier { Name = "YourEntity" },
      Name = $"ETL Staging Changeset - {DateTime.Now:yyyy-MM-dd HH:mm}",
      Description = "Automated changes from ETL staging pipeline"
  };
  var changesetId = mdsClient.CreateChangeset(createRequest).ChangesetId;
  
  // Update staging table with the new changeset ID
  using (var conn = new SqlConnection("YourStagingDBConnectionString"))
  {
      var updateCmd = new SqlCommand($"UPDATE stg.YourModel_Leaf SET ChangeSetID = @ChangesetId WHERE Status_ID = 0", conn);
      updateCmd.Parameters.AddWithValue("@ChangesetId", changesetId);
      conn.Open();
      updateCmd.ExecuteNonQuery();
  }
  

4. Validate the Workflow #

After setting up:

• Run your ETL pipeline to load data into the staging table and trigger the MDS staging batch.
• Log into MDS, navigate to your entity, and check the Changesets section—you should see your new staging changeset marked as Pending Approval.
• Only users with approval permissions can review, edit (if needed), and commit the changeset, at which point the updates will be applied to MDS's master data.

Key Notes #

• If you use MDS's staging stored procedures (e.g., stg.udp_<ModelName>_Leaf), make sure to pass the @ChangeSetID parameter instead of leaving it null.
• Always test the workflow with a small batch first to confirm changes are correctly routed to the approval queue.

内容的提问来源于stack exchange，提问作者arcee123