How I built a Team Foundation Server custom data warehouse adapter

-
As mentioned in a previous blog I would like to explain some of the steps I took to get my custom data warehouse adapter to work.  I am going to start from the beginning just so that I have an easy starting point.  The code I show here was based upon my POC and written in C#.
  1. 1. Create a new C# Class Library project in Visual Studio. Then add references at least to the following assemblies: Microsoft.TeamFoundation.dll, Microsoft.TeamFoundation.Client.dll, Microsoft.TeamFoundation.Common.dll, Microsoft.TeamFoundation.Warehouse.dll and System.Web. As I need to connect to the build server I added Microsoft.TeamFoundation.Build.Common.dll and Microsoft.TeamFoundation.Build.Client.dll
  2. 2. Then specify the use of the IWarehouseAdapter interface.
  3. 3. Implement IWarehouseAdapter.RequestStop which only sets a stop flag to true which can then be tested by the other methods periodically.
  4. 4. Implement the IWarehouseAdapter.Initialize. This method this where I need to store objects that will need to communicate to the all systems that you adapter needs to talk to i.e.: 
   1: string url;
   2:  TeamFoundationServer tfs;
   3:  _DataStore = ds;
   4:   
   5:  if (_DataStore == null)
   6:  {
   7:     throw new Exception("Null data store.");
   8:  } 
   9:   
  10:  url = Microsoft.TeamFoundation.Server.TeamFoundationApplication.TfsNameUrl;
  11:  tfs = TeamFoundationServerFactory.GetServer(url);
  12:   
  13:  if (tfs == null)
  14:  {
  15:     throw new Exception("TF Server instance not obtained for TFS url: " + url);
  16:  } 
  17:   
  18:  _BuildServer = (IBuildServer)tfs.GetService(typeof(IBuildServer)); 
  19:   
  20:  if (_BuildServer == null)
  21:  {
  22:     throw new Exception("Build Server instance not obtained for TFS url: " + url);
  23:  } 
  24:   
  25:  _CommonStructureService = (ICommonStructureService)tfs.GetService(typeof(ICommonStructureService));
  26:   
  27:  if (_CommonStructureService == null)
  28:  {
  29:     throw new Exception("Common Structure Service instance not obtained for TFS url: " + url);
  30:  }
  1. 5. Implement the IWarehouseAdapter.MakeSchemaChanges. This method is called to see if there are schema changes that would need to be made to the data warehouse.  This is recommended to be where you actually register your changes you need to make to the warehouse i.e.:  
   1:  SchemaChangesResult result = SchemaChangesResult.NoChanges; 
   2:   
   3:  //Get the current data warehouse configuration  
   4:  WarehouseConfig warehouseConfig = _DataStore.GetWarehouseConfig(); 
   5:   
   6:  //As the IWarehouseAdapter.MakeSchemaChanges can be called many times in one sessions 
   7:  //I needed to see if your fact was already created 
   8:  Fact MyNewFact = warehouseConfig.GetFact("MyNewFact");
   9:  if (MyNewFact == null)
  10:  { 
  11:      //Create My New Fact
  12:      MyNewFact = new Fact(); 
  13:      MyNewFact.Name = "MyNewFact";
  14:   
  15:        //Adding a link to the Team Project – I had to do this otherwise my save would not work
  16:      MyNewFact.DimensionUses.Add(CreateDimensionUse("Team Project", "Team Project"));
  17:   
  18:      //Adding a link to the Build dimension also when use the dimension I needed to 
  19:        //find out the dimension key attribute was for when saving my data. 
  20:      MyNewFact.DimensionUses.Add(CreateDimensionUse("Build", "Build"));
  21:      MyNewFact.Fields.Add(CreateField("measure1","int",0,"Sum"));
  22:      MyNewFact.Fields.Add(CreateField("measure2", "int",  0, "Sum"));
  23:   
  24:      //Setting which perspective it would show under if enterprise edition of AS was in use.
  25:      MyNewFact.PerspectiveName = "Code Churn";
  26:      MyNewFact.IncludeCountMeasure = true; 
  27:   
  28:      if (!_StopRequest)
  29:        {
  30:             //Starting a Transaction to rollback my changes if they fail 
  31:             _DataStore.BeginTransaction();
  32:            try
  33:            {
  34:                   //Adding to the data warehouse configuration xml file and saving the changes 
  35:                   warehouseConfig.Facts.Add(MyNewFact);
  36:                   _DataStore.Add(warehouseConfig);
  37:                   _DataStore.CommitTransaction();
  38:                   result = SchemaChangesResult.ChangesComplete;
  39:             }
  40:             catch
  41:             {
  42:                   _DataStore.RollbackTransaction();
  43:                   throw;
  44:             }
  45:      } 
  46:      else
  47:      {
  48:          result = SchemaChangesResult.StopRequested; 
  49:      }
  50:   
  51:  }
  52:  return result; 
  53:  //The functions to create a new field and dimension link
  54:   
  55:  private Field CreateField(string FieldName, string FieldType, short FieldLength, string FieldAggregationFunction) 
  56:  {
  57:   
  58:      Field newField = new Field();
  59:      newField.Name = FieldName;
  60:      newField.Type = FieldType;
  61:      newField.Length = FieldLength;
  62:      newField.AggregationFunction = FieldAggregationFunction;
  63:      return newField;
  64:   
  65:  }
  66:   
  67:  private DimensionUse CreateDimensionUse(string DimensionName, string UseName) 
  68:  {
  69:   
  70:      DimensionUse dimensionUse = new DimensionUse();
  71:      dimensionUse.DimensionName = DimensionName;
  72:      dimensionUse.UseName = UseName;
  73:      return dimensionUse;
  74:  }
  1. 6. IWarehouseAdapter.MakeDataChanges which is where all the transferring and transforming of the data takes place. 
   1:  IEnumerator projectEnum = _CommonStructureService.ListAllProjects().GetEnumerator();
   2:  DataChangesResult result = DataChangesResult.NoChanges;
   3:  IEnumerator buildEnum;
   4:  IBuildDetail[] buildDetails;
   5:  ProjectInfo currentTeamProject;
   6:  DateTime buildStartSearch = LastMyNewFactPrcoessedDateTime;
   7:  DateTime buildEndSearch = DateTime.Now; 
   8:   
   9:  while (projectEnum.MoveNext())
  10:  { 
  11:      //checking to see if a stop request has happend if so then return the fact that a stop happen.
  12:   
  13:      if (_StopRequest
  14:      {
  15:          return DataChangesResult.StopRequested;
  16:   
  17:      } 
  18:   
  19:      //Just checking the state of the project as I am not interested in deleted projects
  20:   
  21:      currentTeamProject = projectEnum.Current as ProjectInfo;
  22:      if (currentTeamProject.Status != ProjectState.Deleting)
  23:      {
  24:   
  25:          //Get a list of builds and the filter the out builds that I have already processed.
  26:          buildDetails = _BuildServer.QueryBuilds(currentTeamProject.Name);
  27:          var filterBuildList = from bd in buildDetails
  28:          where bd.StartTime >= buildStartSearch
  29:          && bd.FinishTime < buildEndSearch
  30:          select bd;
  31:   
  32:          //From my new list of builds save my fact details
  33:          buildEnum = filterBuildList.GetEnumerator();
  34:   
  35:          while (buildEnum.MoveNext())
  36:          {
  37:   
  38:              if (_StopRequest)
  39:              {
  40:                  return DataChangesResult.StopRequested;
  41:              }
  42:              SaveMyNewFactEntry (buildEnum.Current as IBuildDetail, currentTeamProject); 
  43:          
  44:              //Update the last build started and date I have just covered.
  45:              LastMyNewFactPrcoessedDateTime = buildEndSearch;
  46:              result = DataChangesResult.ChangesComplete;
  47:          }
  48:   
  49:      }
  50:   
  51:  }
  52:   
  53:   
  54:  private void SaveMyNewFactEntry (IBuildDetail buildDetail, ProjectInfo currentProject)
  55:  {
  56:      Random rand = new Random();
  57:   
  58:      //Check to see if this build has been already added to fact table.
  59:      string buildURI = LinkingUtilities.DecodeUri(buildDetail.Uri.AbsoluteUri).ToolSpecificId;
  60:      if (_DataStore.GetFactEntry("Build Details", buildURI) != null)
  61:      { 
  62:          FactEntry newFactEntry = _DataStore.CreateFactEntry("MyNewFact "); 
  63:          // Had to add Tracking ID by not doing so cause an error.  The Tracking ID is used to find facts 
  64:          //So need make sure that method which repeatable for the same fact entry each time. 
  65:          newFactEntry.TrackingId = Guid.NewGuid().ToString("D", CultureInfo.InvariantCulture);
  66:          newFactEntry ["Team Project"] = LinkingUtilities.DecodeUri(currentProject.Uri).ToolSpecificId;
  67:          newFactEntry ["Build"] = buildURI; 
  68:          newFactEntry ["measure1"] = rand.Next(0,100);
  69:          newFactEntry ["measure2"] = rand.Next(0, 100);
  70:   
  71:          //Again create a transaction save the entry if successful commit the changes
  72:          _DataStore.BeginTransaction();
  73:          try
  74:          { 
  75:              _DataStore.SaveFactEntry(newFact, true);
  76:              _DataStore.CommitTransaction();
  77:          }
  78:          catch
  79:          { 
  80:              _DataStore.RollbackTransaction();
  81:              throw;
  82:          }
  83:   
  84:      }
  85:   
  86:  }
  • 7. I need to store the last date processed so I created a property which stores the data in the datastore property bag. 
   1:  // Last changeset that was populated.
   2:  private DateTime LastMyNewFactPrcoessedDateTime
   3:  { 
   4:      get
   5:      { 
   6:          String LastMyNewFactPrcoessedDateTimeStr = _DataStore.GetProperty("Last MyNewFact Prcoessed DateTime");
   7:          DateTime lastMyNewFactPrcoessedDateTime = DateTime.MinValue;
   8:          if (!String.IsNullOrEmpty(LastMyNewFactPrcoessedDateTimeStr))
   9:          { 
  10:              lastMyNewFactPrcoessedDateTime = DateTime.Parse(LastMyNewFactPrcoessedDateTimeStr);
  11:          }
  12:          return lastMyNewFactPrcoessedDateTime;
  13:   
  14:      }
  15:   
  16:      set
  17:      { 
  18:          _DataStore.BeginTransaction();
  19:          try
  20:          { 
  21:              _DataStore.SetProperty("Last MyNewFact Prcoessed DateTime", value.ToString());
  22:              _DataStore.CommitTransaction();
  23:   
  24:          }
  25:          catch
  26:          {
  27:              _DataStore.RollbackTransaction();
  28:              throw;
  29:          }
  30:      }
  31:  }
  1. 8. Build the adapter as a DLL.
  2. 9. Copy the build DLL into the warehouse plugins folder on the application tier.  In most cases will be C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Web Services\Warehouse\bin\Plugins.
  3. 10. Reset IIS.
  4. 11. On the application tier, navigate to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=Run and click Invoke.
  5. 12. On the application tier, navigate to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=GetWarehouseStatus and click Invoke. Continue doing this until the status is returned as Idle. Check the application event log, to see if an exception has been thrown.
  6. 13. Reset IIS.

If you wish to find out what your current warehouse configuration is then run the following query against your TFS warehouse:

SELECT CAST(wc.Setting AS XML) AS setting FROM dbo.[_WarehouseConfig] AS wc WHERE wc.ID = 'ConfigXML'

Comments

Post a Comment

Popular posts from this blog

SSIS File handle leak in For Each Loop

-
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.
Read more

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

-
Image
As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err
Read more

Challenges of move SQL Server data to Microsoft Azure SQL Server PaaS

-
This post covers one of the challenges we face when moving our infrastructure from on-prem to Microsoft Azure: Moving SQL Server data from on-premise to Microsoft SQL Server Paas. Moving our data was a challenge for the following reasons: Our company has tight controls and rules about what software/connectivity is allowed on our network The database that we need to migrate after compression was over 100GB in size. No information about how long other migration processes would take. As needed to ensure our migration was complete before the next Monday on the weekend of our migration. For most people migrating their data, the best option is to use Azure Database Migration Service , especially when transferring a database size that we were. We couldn't use the Azure Database Migration Service for the following reasons: Azure Database Migration Service requires software installed on a server within our network Connections to both databases
Read more