SQL Server Analysis Services Projects with Multiple Developers

A topic that often comes up when discussing enterprise level development with SSAS is how to have multiple developers work on the same project at the same time.  This issue doesn’t come up for many installations… a lot of teams get away with just having a single person working on their OLAP capabilities.  However, for a decent sized implementation, you’re going to want to have more than one person working on the solution at the same time.  I’ll be discussing some of the issues, workarounds, and tools you can use to make concurrent SSAS development easier.


Here’s a link to the source code from later in the post if that’s all you’re looking for.


Background


Analysis Services objects (cubes, dimensions, roles, etc.) are manipulated either programmatically or in Visual Studio (Visual Studio is the normal method). These objects are persisted by serializing them to XML. When you deploy an Analysis Services database, you either connect directly to an instance of Analysis Services, or you save off XMLA scripts/files that can be used to create the database on a remote server.


If you have a single person working on an AS project, you don’t have a problem.  If you’re using source control with exclusive locks (i.e., only one person can edit a file at a given time) you can have multiple people working on the same solution, but not on the same object at the same time.  This is somewhat complicated by the fact that modifying one object (such as a dimension) may require a change in associated objects (such as a cube where it is included).  You’re still fairly limited in the amount of work you can do concurrently.


The way to have multiple developers working concurrently is to use source control with non-exclusive check-outs, so multiple people can work on each file at the same time.  The down side is that you eventually have to merge the copies each person is working on back together.  Since the SSAS files are large, complicated XML documents this isn’t necessarily an easy task.  Most source control systems will attempt to automatically merge non-conflicting changes, but this usually doesn’t work very well with SSAS files (for reasons I’ll go into in just a minute).  There are, however, some things we can do to make the task a bit easier.


Challenges with Merging SSAS Files


When SSAS objects are persisted to XML, they contain structural information about the objects (which is required) as well as environmental and formatting metadata (which can be helpful in Visual Studio, but is not required for the solution to work correctly when deployed). The environment and formatting metadata elements tend to be extremely volatile, and vary for each developer. Stripping the volatile fields from the XML files will make the merge process easier without affecting the cubes and dimensions that are deployed.


Ex. A developer checks out “Adventure Works.cube” , fixes an error in a Description field, then deploys the cube to test. When he checks the file in, he will have to merge large XML file. He has only changed one line, but large sections of the file will be different from copies checked out to other developers due to metadata capturing the state of Visual Studio and the local AS server. By stripping this metadata, the developer can focus on merging the one change that matters without having to verify every other change in the file.


SSAS Elements that can be Removed


The following represent the environment and formatting metadata elements that are persisted in Analysis Services files. These fields can all be safely stripped from Analysis Services files prior to merging to remove the large number of unimportant conflicts that normally occur.




























Element


Description


CreatedTimestamp


When the object was created.


LastSchemaUpdate


When the schema was last pushed to a SSAS DB. Updated when an object is deployed using the development environment.


LastProcessed


When the object was last processed. Updated when an object is processed using the development environment.


State


The state (processed, unprocessed) of the object. Updated based on actions in the development environment.


CurrentStorageMode


The current storage mode of the object. Updated based on actions in the development environment.


Annotations


Annotations and metadata around the positioning of various objects (such as tables) on the canvas. This data is usually updated every time an object is opened. This element does have a user impact. The annotations section is where the layout of DSV objects is stored, and there is value in arranging those objects. However, this is where most conflicts occur, so it is often worth removing this section and losing custom positioning.


design-time-name


A GUID assigned to each object. It is generated when an object is created (either by a user in BIDS or by reverse engineering an existing database.


Programmatically Removing SSAS Elements


I’ve create a PowerShell function ‘Clean-SsasProject’ that will iterate over all writable SSAS objects in a directory and remove the volatile elements by manipulating the XML.  The function will make a copy of every file it modifies.  It is written using using PowerShell v2 CTP3, but should be easy to back port if you need to.  I’ve included a commented out section that will process the .ASDatabase file as well… this is used for a particular scenario on our team, just including it in case it is handy for anybody.  Use the $WhatIf and $Debug flags to know what the function will do before you do it for real.  This code is geared to the project I’m working on currently, and you may want to modify it to meet your precise needs. 


I would recommend creating a backup of your solution before you try this script, just in case.  I’ve been using this for awhile with no ill effects, but you could have a scenario I never dreamed about, so…


***DO THIS AT YOUR OWN RISK.  IT WORKS ON MY MACHINE.  ***


Consider comparing the cleaned XML side by side with the original to make sure this process works for you… it’s worked fine for every project I’ve used it on, but better safe than sorry.


You can download the source here.


Using Clean-SsasProject (for an individual)


I have my environment configured to load all files with the pattern ‘*Library.ps1’ when PowerShell loads via the following script in my ‘Profile.ps1’ file:



   1: $powerShellScriptsDirectory = “c:\PowerShellScripts\”
   2: if (!$powerShellScriptsDirectory.EndsWith(“\”)) { $powerShellScriptsDirectory += “\” }
   3:  
   4: Write-Host Welcome $Env:Username
   5:     
   6: foreach($filename in Get-ChildItem $powerShellScriptsDirectory* -Include “*Library.ps1″)
   7: {
   8:     & $filename 
   9: }

I store the .ps1 file with Clean-SsasProject and the other functions it depends on in my PowerShell scripts directory, so it’s loaded every time the PowerShell environment loads.  You can then just run ‘Clean-SsasProject’ from the PowerShell prompt.  I also have a .Cmd file in my path to automatically clean my normal SSAS project.  It just uses the following commands:



   1: SET SSAS_PROJECT_LOCATION=C:\Source\MyProject
   2:  
   3: PowerShell -Command “Clean-SsasProject %SSAS_PROJECT_LOCATION%”

Running that command file will strip the volatile fields out of any file in the directory that is writable (i.e., checked-out of my source control system).


Using Clean-SsasProject (for a team)


This tool is designed to work when every team member does the following:



  1. Check-out all files required for a change.  Remember that modifying one object may require that another object be updated, so make sure and check out all objects that can possibly be affected).

  2. Make the change.

  3. Clean the files.

  4. Merge/Resolve conflicts.

  5. Build project output (if required for your solution… I’ll be posting on how to easy project builds/deployments in a few days)

  6. Check-in all files required for a change.

General Best Practices


There are some other general things you can do to make concurrent development a little bit easier (most of these go for software development in general, not just Analysis Services).  If you’ve attempted to have multiple developers work on a project, you’re probably doing all these things already.  Remember that it is always faster and easier not to have to merge when you don’t have to.


Do Separate AS Cubes and Databases by Subject Area


Including only related objects in a cube/database is a standard best practice. This approach avoids potential performance issues, increases manageability and maintainability, and improves the presentation and understandability for the end user. This design pattern also lessens the chance that multiple developers will need to be working on the same object at the same time.


Don’t Combine Unrelated Attributes in a Single Dimension


Including unrelated attributes in a single dimension causes problems with performance, maintainability, and general use of the solution. Including unrelated attributes also promotes conflicts by increasing the chance that developers working on unrelated areas will need to work on the same file.


Do Communicate and Schedule Work for Minimum Conflicts


Make sure to communicate with other developers to avoid working on the same objects when possible. If you need to work on the same object, ensure the design changes are compatible and that there is no way to optimize the work.


Major changes that will dramatically affect source merging should be performed with an exclusive lock on the file.


Ex. A developer wants to re-order the 200 calculated members in the calculate script. The developer should wait until everyone else has submitted their changes, then make the change and submit it.


Do Check-out late and Check-in Early


Minimize the time you keep AS files checked out. While it may take some time to develop new functionality for AS (modifying the source database, creating an ETL to load the database from a source system, etc.) the work in AS is typically fairly quick to do if properly designed and prepared for. Complete the design and other development before checking out the Analysis Services files.


Do Use Tools to Help Merge


Use a side-by-side differencing tool to compare and merge different versions of Analysis Services files. A good diffing tool will have features to make this operation significantly easier. Consider using a tool such as Beyond Compare for this task.  You can use this process to verify that Clean-SsasProject works for your solution the first time you it.


 


Next Steps


Modify the provided source/process to meet your needs and environment.  There is no 100% “right way” to handle development like this… everyone’s situation will be just a little bit different, and require a little bit of customization.  I’m just trying to give you the tools to make it a little bit easier.


Conclusion


That’s all there is.  If you use the tools, techniques, and approach above it should make developing Analysis Services solutions with multiple developers a bit easier for for you.  You’ll still have some of the headaches normally associated with this type of work, but hopefully you’ll have an easier time of it.


Cheers,


David

5 Comments

  1. drjohn says:

    Thanks for a great post! I can see your code be useful to anyone using source code control systems with SSAS, even when not working in a team. I always find it fustrating that I cannot easily see what elements have changed when comparing SSAS files with the previous version. Your code will help tremendously here.

  2. furmangg says:

    Smart Diff in BIDS Helper also lets you do a diff on SSAS project files:
    http://bidshelper.codeplex.com/Wiki/View.aspx?title=Smart%20Diff

  3. Pavan says:

    Hello,
    This is very interesting and handy. Tried it out of curiosity on one of my local projects, but ran into the following error:
    ————
    The term ‘Clean-SsasProject’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
    At line:1 char:18
    + Clean-SsasProject <<< powershell -Command “Clean-SsasProject C:\SampleSSASProjectFolderPath”

    Am i missing something? Looking forward to some help.

    Thanks

    • ddarden says:

      You will need to make sure the entire script is executed first in your session (i.e., defining the function) before you can use it. You can do that by running the file as the first statement in your command, or by running it as part of your Profile.ps1.

  4. Pavan says:

    Hello ddarden,
    I haven’t updated the default Microsoft.PowerShell_profile.ps1 profile file of powershell with the code for loading the ps1 files. I got that fixed by basically replacing the contents of that file with Profile.ps1. Now it is identifying the Clean-SsasProject method and the clean operation works very well. This was much needed for us to save lot of time in my currrent project implementation. I have done some custom changes as per our project cleanup requirements.
    Thanks a lot for this.

    One problem that I found is that when the schema contains Unicode characters other than ASCII, then the cleaned file has some non unicode characters.
    Example: ‘Costo Estándar’ is replaced with ‘Costo Estándar’. This can be problematic if the schema contains any Translations which are non ASCII characters.

    I guess this problem is coming because of the way powershell handles the file content. When powershell’s Get-Content function loads the text from the file to [/xml] object, it loads in ASCII format, so it is not able to identify characters like á, thus it changes it to some junk characters á.

    To get away from this problem, I tried changing the file format to Unicode using
    Get-Content Product.dim | Out-File -Encoding UTF8 Product_ANSI.dim, but it is not able to change those junk characters.

    What do you think about this? If you could give me directions on this, it would be great.

    Also, it would be great help to BI community if you can add your project to Codeplex.

    Cheers !!
    Pavan

Leave a Reply