locked
Workflow for managing daily changes to Customer Dimension RRS feed

  • Question

  • Been using Master Data Services for relatively static data for a while now, but I'm having trouble defining the architecture for a more "rapidly-changing" dimension, viz. Customer.

    Suppose I have a dozen or so different Systems of Entry, each of which generates new customers every day.

    I can create an SSIS package to poll all of these and load new customers into the MDS staging tables. Let's say we do this every night.

    The question is, what do I do next? I want to get this data into subscription views so that it can be used by downstream systems.

    Do I programmatically create a new version, load the data with the staging stored procedure, and commit the new version, all from the SSIS package? Meaning I will have one new version per day?

    Or am I missing something here?


    (Twitter | Blog)

    Wednesday, January 30, 2013 3:50 PM

Answers

  • You might want to consider overwriting the existing tables by doing Insert,Update and soft Deletes for a more rapidly changing entity. Creating new versions will create copies of the complete model.

    Instead you can create separate versions for Test and Production, edit the Test version and copy the model into Production version once the validations are successfull.

    If daily versioning is needed, there are MDS APIs available in form of services and SQL stored procedures that you can call using SSIS packages.

    Monday, February 4, 2013 12:01 PM
  • What is the problem, hypothetical or not, you are trying to address? Do you want the subscriber systems to know which new customers have been added to the MDS database since the previous load? Do you want to address an type 2 SCD scenario - i.e., when the details of a customer change but the business key/customer number stays the same?

    This are 2 very different problems that require 2 different solutions. The former is easier to solve than the later.

    Kind regards,

    M

    Wednesday, February 13, 2013 8:28 AM

All replies

  • You might want to consider overwriting the existing tables by doing Insert,Update and soft Deletes for a more rapidly changing entity. Creating new versions will create copies of the complete model.

    Instead you can create separate versions for Test and Production, edit the Test version and copy the model into Production version once the validations are successfull.

    If daily versioning is needed, there are MDS APIs available in form of services and SQL stored procedures that you can call using SSIS packages.

    Monday, February 4, 2013 12:01 PM
  • What is the problem, hypothetical or not, you are trying to address? Do you want the subscriber systems to know which new customers have been added to the MDS database since the previous load? Do you want to address an type 2 SCD scenario - i.e., when the details of a customer change but the business key/customer number stays the same?

    This are 2 very different problems that require 2 different solutions. The former is easier to solve than the later.

    Kind regards,

    M

    Wednesday, February 13, 2013 8:28 AM