Managing Team Development with SSAS, TFS, & BIDS

I am currently a single BI developer over a corporate datawarehouse and cube. I use SQL Server 2008, SSAS, and 开发者_StackOverflow中文版SSIS as my basic toolkit. I use Visual Studio +BIDS and TFS for my IDE and source control. I am about to take on multiple projects with an offshore vendor and I am worried about managing change. My major concern is manging merges and changes between me and the offshore team. Merging and managing changes to SQL & XML for just one person is bad enough but with multiple developers it seems like a nightmare. Any thoughts on how best to structure development knowing that sometimes there is no way to avoid multiple individuals making changes to the same file?

SSIS, SSAS and SSRS files are not merge-friendly. They are stored in an xml file that is changed drastically - even with minor changes (such as changing a property) - so it becomes really impossible to merge.

So stop thinking about parallel development on one file. You need to think how you can achieve that people are not need to do parallel development on one file. So start with disabling the multiple checkout of a file. You might even want to consider to enable the option to get the latest version on a checkout.

Then start thinking how you can achieve that people can work independent. This is more in the way you structure the work and files:

• Give people their own area they can work on. One SSIS package is only developed by person X at any given moment in time.
• Make smaller files so the change that two people need to work in the same file is small.

I have given feedback to the product team of the imcompatability of BIDS to merge. It is a known issue, but will be hard to tackle. They don't know when it will be possible to really do parallel development on these files. Until then keep away from parallel development.

As Ewald Hofman mentioned, SSAS and SSIS is not merge-friendly.

In one environment I worked solved the problem as follows:

• do only use SSIS when you have to (fuzz algorithm or something similar). Replace SSIS packages as often as you can with SQL code (see Linked Server for datasync. and MEARGE Command for dimension/fact-table-creating for instance).
• build your data warehouse structure as follows:
• build 2 databases, one for the "raw source data" from the source systems and one (the "stage" database) for the dimension and fact views and tables
• use procedures that can deploy the whole "stage" database
• put the structure for the "stage" database into your Repository

• build a C# application that build your dimensions and cubes via the AMO API (I know, that's a tough job at the beginning but it is it worth - think on what you gain - Look at the Pros below )

• add the stage database and the C# application to your Repository (TFS/Git etc.)

Pros of that structure:

• you have a merge-able structure you can put in your Repository
• you are using the AMO API witch has
• you can automate the generation of new partitions
• you can use procedures to automate and clone measure groups to different cubes (what I think is sometimes a big benefit!)
• you could outsource your translation and import it easily (the cube designer is probably not the best translator)

Cons:

• the vendor would probably not adapt that structure
• you have to pay more (because of either higher skill requirements or for teaching him your individual structure)
• you probably need knowledge over a new language C# - if you don't already have

Conclusion:

• there are possibilities to get a merge-friendly environment
• you will get lost of nice click-and-run tools f.e. BIDS - but will get into process of high automation functionality
• outsourcing will be maybe unprofitable because of high individualization

http://code.google.com/p/support/wiki/DVCSAnalysis

maybe a better tag is DVCS? https://stackoverflow.com/questions/tagged/dvcs

As long as both teams are using bids and TFS this should not be a problem.

assuming that your tsql code is checked in to source control in a single file per object, merging TSQL code is straight forward since it is text based. I have found the VSTS Database projects help with this.

Merging the XML based source files of SSIS and the MSAS can be cumbersome as you indicate below. to alleviate some of the pain, I find that keeping each package limited to a single dataflow or logical unit of work helps reduce developer contention on packages. I then call these packages from one or more master packages. I also try to externalize all of my tsql source queries using sprocs, view or udfs so that the need to edit the package is further reduced. using configuration files and variables also helps to a smaller extent.

MSSAS cubes are a little bit tougher. My best suggestion is to look into a 3rd party xml differencing tool. I have been able to successfully merge small changes use the standard text based tools but it can be a daunting task.