locked
Versioning MDS model within a Visual Studio solution RRS feed

  • Question

  • Hello,

      I am curently developing a DW solution utilizing MDS for reference/dimension data. There are several developers on the team, each working with their local SQL instance and local MDS instance. We use TFS to version the Visual Studio solution containing SSDT database project as well as SSIS and SSAS projects for loading and presenting the data. Now, there is no obvious way to add MDS model into this mix - we would like to have the model, export views and few rows of data in each entity - {0, Unknown} member as well as potentially small amount static members which are not loaded by any other ETL process (Yes/No, etc.) I thought of versioning PKG file itself, but it's sort of awkward to have to export it and deploy it all the time and it won't create the export views as those seem to be tied to the MDS instance itself. Also we want ability for developers to work independently without breaking each others data.

      I am thinking that writing a C#-based tool that will connect to MDS via web service API and create/populate all the necessary entities and views is probably the most viable way - all developers on the team have at least some experience with C#, it's easily versioned in TFS and can do basically all operations that we need including deploying the views. Before we embark on this path, I would like to ask if there are any other ways to achieve what I described so far - MDS model under TFS version control?

     Thanks in advance,
    Alex

    Wednesday, October 24, 2012 4:37 PM

Answers

  • Hi Roman,

      We decided against a full-blown service-based project to create the models auto-magically as it would be a fairly resource intensive project by itself to do. Right now we basically rely on individual MDS development instances, internal MDS versioning and MDSModelDeploy.exe to export\import model onto different machines. This still means that a single person should be responsible for the overall model structure (or rather single person at a time).

      The nice thing about MDS 2012 is that the package creation includes the Subscription Views - so that been a great help as we do not have to recreate all the views manually after import as in the previous version. Within the model we have a locked down latest "stable" version and open "development" version. A version flag is utilized to mark a version "Current" and all the subscription views are set up to show data from the version marked with that flag. During the ETL, I have some code that queries the version marked "Current" and loads data into it. If I'm testing loading into MDS - I simply delete the "development" version using [mdm].[udpVersionDelete] and then clone it using [mdm].[udpVersionCopy] from the "stable" version. Periodically I need to modify the [LargestCodeValue] within [mdm].[tblCodeGenInfo] to avoid gaps in the auto-generated Code fields as I delete and clone the versions. And conversely if I am testing ETL process that uses MDS as a reference, I can switch the flag to either the "stable" or "development" version depending on the situation.

      This is not ideal of course, but make the development process somewhat bearable. I am able to load data into MDS and rollback changes on my development instance as needed, and share the "stable" version of the model with other developers. It is a manual step to generate and check-in the latest model XML files into version control - which I do periodically when structural and base-set changes are applied.

      Feel free to reply if this is not very clear or you need some code samples.

    Thanks,
    Alex

    Thursday, March 21, 2013 10:24 PM

All replies

  • Hello Alexandr!

    Did you find a solution for MDS model développment and versionning ?

    Thanks,

    Roman

    Thursday, March 21, 2013 8:33 AM
  • Hi Roman,

      We decided against a full-blown service-based project to create the models auto-magically as it would be a fairly resource intensive project by itself to do. Right now we basically rely on individual MDS development instances, internal MDS versioning and MDSModelDeploy.exe to export\import model onto different machines. This still means that a single person should be responsible for the overall model structure (or rather single person at a time).

      The nice thing about MDS 2012 is that the package creation includes the Subscription Views - so that been a great help as we do not have to recreate all the views manually after import as in the previous version. Within the model we have a locked down latest "stable" version and open "development" version. A version flag is utilized to mark a version "Current" and all the subscription views are set up to show data from the version marked with that flag. During the ETL, I have some code that queries the version marked "Current" and loads data into it. If I'm testing loading into MDS - I simply delete the "development" version using [mdm].[udpVersionDelete] and then clone it using [mdm].[udpVersionCopy] from the "stable" version. Periodically I need to modify the [LargestCodeValue] within [mdm].[tblCodeGenInfo] to avoid gaps in the auto-generated Code fields as I delete and clone the versions. And conversely if I am testing ETL process that uses MDS as a reference, I can switch the flag to either the "stable" or "development" version depending on the situation.

      This is not ideal of course, but make the development process somewhat bearable. I am able to load data into MDS and rollback changes on my development instance as needed, and share the "stable" version of the model with other developers. It is a manual step to generate and check-in the latest model XML files into version control - which I do periodically when structural and base-set changes are applied.

      Feel free to reply if this is not very clear or you need some code samples.

    Thanks,
    Alex

    Thursday, March 21, 2013 10:24 PM