|
|
I got a message from the user Sergejus, but his personal Live settings won't let me reply to him. Therefore I hope he reads this blog instead as an answer to his mail. Sergejus wrote:
Hi Tommie,
Some time has passed since your last post, but currently I'm
trying to write custom adapter for external DB. Because you've done you
MSc, could you please share source code or at least provide some
snippets for connecting to external DB and pushig data from it to TFS.
Thanks,
Sergey My answer:Hi Sergey! You can find our final report with all code we produced in our master thesis document, at this address: Feel free to read, spread and use the information held in our report - as long as you give credit where credit is due and don't alter the content. Good luck with your work! Best regards, Tommie We've completed the first preliminary draft of the report and sent it to our examiner Mr. Sandahl and our opponents; Daniel Ek and Börje Granberg. It was not a completed draft since we know it contains many errors; all chapters weren't even complete. But to give the opponents and the examiner a chance to glance through what the report is about, we decided to send it to them anyway. Magnus has been proofreading the report and done some changes. Now it is on my table. I've read about 60 pages of approximately 110, so there is some work left to do. I plan to redo some chapters completely and also combine some chapters into one. But, since it is Christmas time now there won't be so much time for me to do this. I hope to get it completed sometime between Christmas and new years eve, so that a complete draft can be sent to those it concerns. The report will be published when it's been thoroughly read and presented at the university. Merry Christmas to you all! (This post is just for documentation purposes). Mr. Ericson at Microsoft has been a valuable help to me trying to figure out what is wrong with the deployment of my adapter. I really appreciate that he's taking his time trying to help me. I haven't made any progress though. I tried what Mr. Ericson suggested me to do the following shown in this thread. Here's a summary of what has been done and what exceptions I get. I removed the adapter, and reverted the schema changes:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -rebuild -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a ORCASBETA2_TFS
V\TFSSERVICE -ra ORCASBETA2_TFSV\TFSREPORT -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully.
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>
I processed the warehouse by going to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx and choosing Run. I got these information (not error) messages in the Event Viewer: Warehouse controller application stopped.
The application is being shutdown for the following reason: BinDirChangeOrDirectoryRename
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
and
Warehouse controller application started.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Went to this page you told me about: http://localhost:8080/Warehouse/v1.0/tftrace.aspx?All=Verbose. This gave me just a blank page (I suppose that it is not supposed to show anything special like a confirmation message or anything?). Launched DbgView and went to the warehousecontroller.asmx page again and chose Run. I still get the same sql error messages in Event Viewer about not finding the 'Team Project' column: Event Type: Error
Event Source: TFS Warehouse
Event Category: None
Event ID: 3000
Date: 12/20/2007
Time: 2:04:38 AM
User: N/A
Computer: ORCASBETA2_TFSV
Description:
TF53010: The following error has occurred in a Team Foundation component or extension:
Date (UTC): 12/20/2007 10:04:38 AM
Machine: ORCASBETA2_TFSV
Application Domain: /LM/W3SVC/1747463155/Root/Warehouse-7-128426185442245744
Assembly: Microsoft.TeamFoundation.Warehouse, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
Process Name: w3wp
Process Id: 2596
Thread Id: 1316
Account name: ORCASBETA2_TFSV\TFSSERVICE
Detailed Message: TF51209: A run-time error System.Data.SqlClient.SqlException: Invalid column name 'Team Project'.
Invalid column name 'Team Project'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.SqlExecuteNonQuery(SqlCommand cmd)
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreatePrc_Fact_X_AddUpdate(SqlConnection cn, SqlTransaction tr)
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreateStoredProcedures(SqlConnection cn, SqlTransaction tr)
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.ProcessNewConfig(WarehouseConfig config)
at Microsoft.TeamFoundation.Warehouse.AdapterDataStore.Add(WarehouseConfig newConfig)
at CodeMetricsAdapter.CMAdapter.MakeSchemaChanges()
at Microsoft.TeamFoundation.Warehouse.AdapterWrapper.MakeSchemaChanges() occurred on adapter CodeMetricsAdapter.CMAdapter.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
(and I get the other one too) :
TF53010: The following error has occurred in a Team Foundation component or extension:
Date (UTC): 12/20/2007 10:04:38 AM
Machine: ORCASBETA2_TFSV
Application Domain: /LM/W3SVC/1747463155/Root/Warehouse-7-128426185442245744
Assembly: Microsoft.TeamFoundation.Warehouse, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
Process Name: w3wp
Process Id: 2596
Thread Id: 1316
Account name: ORCASBETA2_TFSV\TFSSERVICE
Detailed Message: Schema change failed. \r\nConfig:\r\n<?xml version="1.0" encoding="utf-16"?>
<WarehouseConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Facts>
<Fact> [... more xml but nothing on my fact Code Metrics ... ] This is really annoying since I do not make any SQL query at all asking for any 'Team Project' column. It should work, shouldn't it?
DebugView didn't really give me any more information (or perhaps someone may be able to read something helpful from this log):
00000000 2:04:08 AM [2596] [DW] [Info, PID 2596, TID 284, 10:04:08.744] Application Request Processing Started
00000001 2:04:08 AM [2596] [DW] [Verbose, PID 2596, TID 284, 10:04:08.744] Request authentication type: NTLM
00000002 2:04:09 AM [2596] [DW] [Info, PID 2596, TID 284, 10:04:09.686] Application Request Processing Ended
00000003 2:04:27 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:04:27.882] Application Request Processing Started
00000004 2:04:27 AM [2596] [DW] [Verbose, PID 2596, TID 1316, 10:04:27.882] Request authentication type: NTLM
00000005 2:04:27 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:04:27.992] Application Request Processing Ended
00000006 2:04:34 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:04:34.692] Application Request Processing Started
00000007 2:04:34 AM [2596] [DW] [Verbose, PID 2596, TID 1316, 10:04:34.692] Request authentication type: NTLM
00000008 2:04:35 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:04:35.333] Application Request Processing Ended
00000009 2:04:35 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:04:35.333] ProcessWarehouse entered.
00000010 2:04:35 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:04:35.343] Starting run on Adapter CodeMetricsAdapter.CMAdapter.
00000011 2:04:38 AM [2596] [DW] [Error, PID 2596, TID 1316, 10:04:38.137] Schema change failed. \r\nConfig:\r\n<?xml version="1.0" encoding="utf-16"?>
00000012 2:04:38 AM [2596] <WarehouseConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
00000013 2:04:38 AM [2596] <Facts>
00000014 2:04:38 AM [2596] <Fact>
00000015 2:04:38 AM [2596] <Name>Code Churn</Name>
00000016 2:04:38 AM [2596] <FriendlyName>Code Churn</FriendlyName>
00000017 2:04:38 AM [2596] <PerspectiveName>Code Churn</PerspectiveName>
00000018 2:04:38 AM [2596] <IncludeCountMeasure>true</IncludeCountMeasure>
00000019 2:04:38 AM [2596] <Fields>
00000020 2:04:38 AM [2596] <Field>
00000021 2:04:38 AM [2596] <Name>Lines Added</Name>
00000022 2:04:38 AM [2596] <FriendlyName>Lines Added</FriendlyName>
00000023 2:04:38 AM [2596] <Type>int</Type>
00000024 2:04:38 AM [2596] <Length>0</Length>
00000025 2:04:38 AM [2596] <Visible>true</Visible>
00000026 2:04:38 AM [2596] <AggregationFunction>Sum</AggregationFunction>
00000027 2:04:38 AM [2596] <RelationalOnly>false</RelationalOnly> .................. 00000734 2:04:38 AM [2596] <UseName>Changed By</UseName>
00000735 2:04:38 AM [2596] <FriendlyUseName>Changed By</FriendlyUseName>
00000736 2:04:38 AM [2596] <DimensionName>Person</DimensionName>
00000737 2:04:38 AM [2596] <RelationalOnly>false</RelationalOnly>
00000738 2:04:38 AM [2596] </DimensionUse>
00000739 2:04:38 AM [2596] <D
00000740 2:04:38 AM [2596] imensionUse>
00000741 2:04:38 AM [2596] <UseName>Creat
00000742 2:04:38 AM [2596] [DW] [Error, PID 2596, TID 1316, 10:04:38.157] TF51209: A run-time error System.Data.SqlClient.SqlException: Invalid column name 'Team Project'.
00000743 2:04:38 AM [2596] Invalid column name 'Team Project'.
00000744 2:04:38 AM [2596] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
00000745 2:04:38 AM [2596] at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
00000746 2:04:38 AM [2596] at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
00000747 2:04:38 AM [2596] at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
00000748 2:04:38 AM [2596] at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
00000749 2:04:38 AM [2596] at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
00000750 2:04:38 AM [2596] at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
00000751 2:04:38 AM [2596] at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
00000752 2:04:38 AM [2596] at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
00000753 2:04:38 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.SqlExecuteNonQuery(SqlCommand cmd)
00000754 2:04:38 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreatePrc_Fact_X_AddUpdate(SqlConnection cn, SqlTransaction tr)
00000755 2:04:38 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreateStoredProcedures(SqlConnection cn, SqlTransaction tr)
00000756 2:04:38 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.ProcessNewConfig(WarehouseConfig config)
00000757 2:04:38 AM [2596] at Microsoft.TeamFoundation.Warehouse.AdapterDataStore.Add(WarehouseConfig newConfig)
00000758 2:04:38 AM [2596] at CodeMetricsAdapter.CMAdapter.MakeSchemaChanges()
00000759 2:04:38 AM [2596] at Microsoft.TeamFoundation.Warehouse.AdapterWrapper.MakeSchemaChanges() occurred on adapter CodeMetricsAdapter.CMAdapter.
00000760 2:04:38 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:04:38.157] Skipping data for adapter CodeMetricsAdapter.CMAdapter since it failed to process schema changes
00000761 2:06:21 AM [1656] ReportingServicesService!library!3!12/20/2007-02:06:21:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
00000762 2:15:49 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:15:49.642] Application Request Processing Started
00000763 2:15:49 AM [2596] [DW] [Verbose, PID 2596, TID 1316, 10:15:49.642] Request authentication type: NTLM
00000764 2:15:49 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:15:49.692] Application Request Processing Ended
00000765 2:15:49 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:15:49.692] ProcessWarehouse entered.
00000766 2:15:49 AM [2596] [DW] [Info, PID 2596, TID 436, 10:15:49.692] Application Request Processing Started
00000767 2:15:49 AM [2596] [DW] [Verbose, PID 2596, TID 436, 10:15:49.692] Request authentication type: NTLM
00000768 2:15:49 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:15:49.702] Starting run on Adapter CodeMetricsAdapter.CMAdapter.
00000769 2:15:51 AM [2596] [DW] [Error, PID 2596, TID 1316, 10:15:51.685] Schema change failed. \r\nConfig:\r\n<?xml version="1.0" encoding="utf-16"?>
00000770 2:15:51 AM [2596] <WarehouseConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
00000771 2:15:51 AM [2596] <Facts>
00000772 2:15:51 AM [2596] <Fact>
00000773 2:15:51 AM [2596] <Name>Code Churn</Name>
00000774 2:15:51 AM [2596] <FriendlyName>Code Churn</FriendlyName>
00000775 2:15:51 AM [2596] <PerspectiveName>Code Churn</PerspectiveName>
00000776 2:15:51 AM [2596] <IncludeCountMeasure>true</IncludeCountMeasure>
00000777 2:15:51 AM [2596] <Fields>
00000778 2:15:51 AM [2596] <Field>
00000779 2:15:51 AM [2596] <Name>Lines Added</Name>
00000780 2:15:51 AM [2596] <FriendlyName>Lines Added</FriendlyName>
00000781 2:15:51 AM [2596] <Type>int</Type>
00000782 2:15:51 AM [2596] <Length>0</Length>
00000783 2:15:51 AM [2596] <Visible>true</Visible>
00000784 2:15:51 AM [2596] <AggregationFunction>Sum</AggregationFunction>
00000785 2:15:51 AM [2596] <RelationalOnly>false</RelationalOnly>
00000786 2:15:51 AM [2596] <CalculatedMembers /> ......... 00001498 2:15:51 AM [2596] imensionUse>
00001499 2:15:51 AM [2596] <UseName>Creat
00001500 2:15:51 AM [2596] [DW] [Error, PID 2596, TID 1316, 10:15:51.695] TF51209: A run-time error System.Data.SqlClient.SqlException: Invalid column name 'Team Project'.
00001501 2:15:51 AM [2596] Invalid column name 'Team Project'.
00001502 2:15:51 AM [2596] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
00001503 2:15:51 AM [2596] at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
00001504 2:15:51 AM [2596] at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
00001505 2:15:51 AM [2596] at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
00001506 2:15:51 AM [2596] at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
00001507 2:15:51 AM [2596] at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
00001508 2:15:51 AM [2596] at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
00001509 2:15:51 AM [2596] at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
00001510 2:15:51 AM [2596] at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
00001511 2:15:51 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.SqlExecuteNonQuery(SqlCommand cmd)
00001512 2:15:51 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreatePrc_Fact_X_AddUpdate(SqlConnection cn, SqlTransaction tr)
00001513 2:15:51 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreateStoredProcedures(SqlConnection cn, SqlTransaction tr)
00001514 2:15:51 AM [2596] at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.ProcessNewConfig(WarehouseConfig config)
00001515 2:15:51 AM [2596] at Microsoft.TeamFoundation.Warehouse.AdapterDataStore.Add(WarehouseConfig newConfig)
00001516 2:15:51 AM [2596] at CodeMetricsAdapter.CMAdapter.MakeSchemaChanges()
00001517 2:15:51 AM [2596] at Microsoft.TeamFoundation.Warehouse.AdapterWrapper.MakeSchemaChanges() occurred on adapter CodeMetricsAdapter.CMAdapter.
00001518 2:15:51 AM [2596] [DW] [Info, PID 2596, TID 1316, 10:15:51.705] Skipping data for adapter CodeMetricsAdapter.CMAdapter since it failed to process schema changes
00001519 2:15:51 AM [2596] [DW] [Info, PID 2596, TID 436, 10:15:51.735] Application Request Processing Ended
00001520 2:16:21 AM [1656] ReportingServicesService!library!3!12/20/2007-02:16:21:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
00001521 2:26:21 AM [1656] ReportingServicesService!library!3!12/20/2007-02:26:21:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams I'm out on advice here. Mr. König, my supervisor, said he will try to install the adapter on the 2005 version of the VPC. It may be some differences between the 2008 and 2005 version which causes these problems. We shall see what his results will be from that try. Meanwhile I will try to fix this on the 2008 version, somehow... (Summary of my newly added post in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2524723&SiteID=1&mode=1). I'm having serious problems deploying the adapter. First of all, I've commented all content of MakeDataChanges() method so that it just looks like this now: public DataChangesResult MakeDataChanges()
{
return DataChangesResult.NoChanges;
}
This is just temporary so it will be easier for me to analyze the errors made. Got some tips on the MSDN forum to add this part to MakeSchemaChanges, just to make sure I'm not adding a Fact twice (the code below is added in the beginning of the method, after getting your warehouse config variable):
//Check if the fact exists
Fact myFact = config.GetFact("Code Metrics");
if (myFact != null)
return SchemaChangesResult.NoChanges;
(After this part, the rest of MakeSchemaChanges() follows). Furthermore I've altered my try-catch statements to look something like this:
try
{
m_dataStore.Add(config);
m_dataStore.CommitTransaction();
}
catch
{
try
{
m_dataStore.RollbackTransaction();
}
catch (Exception ex)
{
m_dataStore.LogEvent(AdapterEventLevel.Error, ex.ToString());
return SchemaChangesResult.NoChanges;
}
throw;
}
That is; rethrowing the same exception that I catch, and not hiding the exception if RollbackTransaction also throws. So far so good. But this is recorded in the Event Viewer when I try running the adapter:
TF53010: The following error has occurred in a Team Foundation component or extension:
Date (UTC): 12/8/2007 4:17:57 PM
Machine: ORCASBETA2_TFSV
Application Domain: /LM/W3SVC/1747463155/Root/Warehouse-11-128416042576020816
Assembly: Microsoft.TeamFoundation.Warehouse, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
Process Name: w3wp
Process Id: 2972
Thread Id: 1504
Account name: ORCASBETA2_TFSV\TFSSERVICE
Detailed Message: TF51209: A run-time error System.Data.SqlClient.SqlException: Invalid column name 'Team Project'.
Invalid column name 'Team Project'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.SqlExecuteNonQuery(SqlCommand cmd)
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreatePrc_Fact_X_AddUpdate(SqlConnection cn, SqlTransaction tr)
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.CreateStoredProcedures(SqlConnection cn, SqlTransaction tr)
at Microsoft.TeamFoundation.Warehouse.WarehouseSchemaCreator.ProcessNewConfig(WarehouseConfig config)
at Microsoft.TeamFoundation.Warehouse.AdapterDataStore.Add(WarehouseConfig newConfig)
at CodeMetricsAdapter.CMAdapter.MakeSchemaChanges()
at Microsoft.TeamFoundation.Warehouse.AdapterWrapper.MakeSchemaChanges() occurred on adapter CodeMetricsAdapter.CMAdapter.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
And also this:
TF53010: The following error has occurred in a Team Foundation component or extension:
Date (UTC): 12/8/2007 4:17:57 PM
Machine: ORCASBETA2_TFSV
Application Domain: /LM/W3SVC/1747463155/Root/Warehouse-11-128416042576020816
Assembly: Microsoft.TeamFoundation.Warehouse, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
Process Name: w3wp
Process Id: 2972
Thread Id: 1504
Account name: ORCASBETA2_TFSV\TFSSERVICE
Detailed Message: Schema change failed. \r\nConfig:\r\n<?xml version="1.0" encoding="utf-16"?>
<WarehouseConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Facts>
<Fact>
<Name>Code Churn</Name>
<FriendlyName>Code Churn</FriendlyName>
<PerspectiveName>Code Churn</PerspectiveName>
<IncludeCountMeasure>true</IncludeCountMeasure>
.... [some more of warehouseschema.xml but nothing on code metric]...
<SourceField>_Microsoft_V
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
So I guess the schema change fails, probably because the config.add(...) fails. But why? Now I've tried to install the adapter and make it change the schema. Installing the adapter is not a problem. But making it update the schema is troublesome. You can see my source code in the previous post. It is expected that the Version Control Adapter will throw an exception when updating the warehouse. However, it is not expected that my own exceptions will be thrown. Here's what is happening in the event viewer when trying Run is performed from the warehouse web service: Event Type: Error
Event Source: TFS Warehouse
Event Category: None
Event ID: 3000
Date: 12/6/2007
Time: 7:21:07 AM
User: N/A
Computer: ORCASBETA2_TFSV
Description:
TF53010: The following error has occurred in a Team Foundation component or extension:
Date (UTC): 12/6/2007 3:21:07 PM
Machine: ORCASBETA2_TFSV
Application Domain: /LM/W3SVC/1747463155/Root/Warehouse-4-128414278681842144
Assembly: Microsoft.TeamFoundation.Warehouse, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
Process Name: w3wp
Process Id: 3044
Thread Id: 3116
Account name: ORCASBETA2_TFSV\TFSSERVICE Detailed Message: TF51209: A run-time error System.Exception: Transaction failed. Could not make schema changes!
at CodeMetricsAdapter.CMAdapter.MakeSchemaChanges()
at Microsoft.TeamFoundation.Warehouse.AdapterWrapper.MakeSchemaChanges() occurred on adapter CodeMetricsAdapter.CMAdapter. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. As expected, there was nothing to be found at the URL provided (as it apparently is with most documentation on TFS). Here is the place where it fails in the MakeSchemaChanges() method: try
{
// This will add the new fact and new dimension to the warehouse.
// If user wants to add new fields or measures to existing dimensions or facts in
// warehouse, then use the IDataStore.Add* methods. IDataStore.Add(config) will not
// work in that case.
// Also note that the Add method only appends the new facts and dimensions from the
// modified config. The current existing facts and dimensions will not get added twice.
m_dataStore.Add(config);
m_dataStore.CommitTransaction();
}
catch
{
m_dataStore.RollbackTransaction();
throw new Exception("Transaction failed. Could not make schema changes!");
}
It is, I suppose, the Add method in the datastore object that fails. I do wonder why though.
I feel completely stuck. This should be pretty straight-forward. I'll have to do these steps: - Implement the adapter
- Implement Initialize()
- Implement MakeSchemaChanges()
- Implement MakeDataChanges()
- Install the adapter on the TFS VPC
- Navigate to the warehouse web service and invoke the Run command (which will make the warehouse read and execute the adapter)
That should be it. So, let's have a look at how the adapter looks like, method by method: - public void Initialize(IDatastore ds)
public void Initialize(IDataStore ds)
{
// Check the data store
m_dataStore = ds;
if (m_dataStore == null)
{
throw new Exception("Null data store");
}
// Connect to the TFS Server
String url = "ORCASBETA2_VSTS"; //or perhaps it should be http://orcasbeta2_tfsv:8080 ?
TeamFoundationServer tfs = TeamFoundationServerFactory.GetServer(url);
if (tfs == null)
{
throw new Exception("TF Server instance not obtained for TFS url:" + url);
}
}
- public SchemaChangesResult MakeSchemaChanges()
public SchemaChangesResult MakeSchemaChanges()
{
SchemaChangesResult result = SchemaChangesResult.NoChanges;
WarehouseConfig config = m_dataStore.GetWarehouseConfig();
Fact codeMetricsFact = new Fact();
//Set the name of the fact
codeMetricsFact.Name = "Code Metrics";
//Set friendly name. This will be used in the OLAP cube in SQL Enterprise editions-
codeMetricsFact.FriendlyName = "Code Metrics";
//Create the fields the Code Metrics fact will have
Field maintainabilityIndex = new Field();
maintainabilityIndex.Name = "Maintainability Index";
maintainabilityIndex.Type = "INT";
maintainabilityIndex.AggregationFunction = "sum";
Field cyclomaticComplexity = new Field();
cyclomaticComplexity.Name = "Cyclomatic Complexity";
cyclomaticComplexity.Type = "INT";
cyclomaticComplexity.AggregationFunction = "sum";
Field depthOfInheritance = new Field();
depthOfInheritance.Name = "Depth of Inheritance";
depthOfInheritance.Type = "INT";
depthOfInheritance.AggregationFunction = "sum";
Field classCoupling = new Field();
classCoupling.Name = "Class Coupling";
classCoupling.Type = "INT";
classCoupling.AggregationFunction = "sum";
Field linesOfCode = new Field();
linesOfCode.Name = "Lines of Code";
linesOfCode.Type = "INT";
linesOfCode.AggregationFunction = "sum";
//Add the fields to the newly created fact table.
codeMetricsFact.Fields.Add(maintainabilityIndex);
codeMetricsFact.Fields.Add(cyclomaticComplexity);
codeMetricsFact.Fields.Add(depthOfInheritance);
codeMetricsFact.Fields.Add(classCoupling);
codeMetricsFact.Fields.Add(linesOfCode);
//Create and add dimensionUse for Build to fact. This means that this fact can be sliced by Build.
DimensionUse dimUse = new DimensionUse();
dimUse.FriendlyUseName = "Build";
dimUse.UseName = "Build";
dimUse.DimensionName = "Build";
dimUse.IntermediateFactName = null; //I don't know what the hell this does, really...
codeMetricsFact.DimensionUses.Add(dimUse);
//Add the new fact to the config
config.Facts.Add(codeMetricsFact);
//Check if adapter is requested to stop before starting transaction.
if (m_stopRequested)
{
return SchemaChangesResult.StopRequested;
}
// Use transactions for safe operation
m_dataStore.BeginTransaction();
try
{
// This will add the new fact and new dimension to the warehouse.
// If user wants to add new fields or measures to existing dimensions or facts in
// warehouse, then use the IDataStore.Add* methods. IDataStore.Add(config) will not
// work in that case.
// Also note that the Add method only appends the new facts and dimensions from the
// modified config. The current existing facts and dimensions will not get added twice.
m_dataStore.Add(config);
m_dataStore.CommitTransaction();
}
catch
{
m_dataStore.RollbackTransaction();
throw new Exception("Transaction failed. Could not make schema changes!");
}
result = SchemaChangesResult.ChangesComplete;
return result;
}
- public DataChangesResult MakeDataChanges()
public DataChangesResult MakeDataChanges()
{
DataChangesResult result = DataChangesResult.NoChanges;
FactEntry metricsDataEntry = m_dataStore.CreateFactEntry("Code Metrics");
string connString = "Data Source=ORCASBETA2_TFSV;" +
"Database=CMDB;" +
"Integrated Security=true;";
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
}
catch (Exception e)
{
m_dataStore.LogEvent(AdapterEventLevel.Error, "Could not open CMDB.");
return DataChangesResult.NoChanges;
}
try
{
SqlCommand command = new SqlCommand("HarvestCodeMetrics", conn);
command.CommandType = CommandType.StoredProcedure;
using (SqlDataReader myReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
// Harvest data from CMDB. The indexes of metricsDataEntry must correspond
// to an existing field in the Code Metrics fact table, as created in
// MakeSchemaChanges() above.
while (myReader.Read())
{
metricsDataEntry["Maintainability Index"] = myReader.GetInt32(myReader.GetOrdinal("MaintainabilityIndex"));
metricsDataEntry["Cyclomatic Complexity"] = myReader.GetInt32(myReader.GetOrdinal("CyclomaticComplexity"));
metricsDataEntry["Depth of Inheritance"] = myReader.GetInt32(myReader.GetOrdinal("DepthOfInheritance"));
metricsDataEntry["Class Coupling"] = myReader.GetInt32(myReader.GetOrdinal("ClassCoupling"));
metricsDataEntry["Lines of Code"] = myReader.GetInt32(myReader.GetOrdinal("LinesOfCode"));
metricsDataEntry["Build"] = myReader.GetString(myReader.GetOrdinal("Build"));
}
myReader.Close();
}
}
catch (Exception e)
{
m_dataStore.LogEvent(AdapterEventLevel.Error, "Could not read from CMDB");
return DataChangesResult.NoChanges;
}
//Check if adapter is requested to stop
if (m_stopRequested)
{
return DataChangesResult.StopRequested;
}
// Not requested to stop? Great, start the transaction then.
m_dataStore.BeginTransaction();
try
{
m_dataStore.SaveFactEntry(metricsDataEntry, false); // If true, the save may result in updating an _existing_ row.
m_dataStore.CommitTransaction();
}
catch
{
m_dataStore.RollbackTransaction();
throw new Exception("Error! Couldn't save the fact entry");
}
//we're done, mark that we've done some changes.
result = DataChangesResult.ChangesComplete;
m_dataStore.LogEvent(AdapterEventLevel.Informational, "Updated warehouse");
return result;
}
That's it. Let's try to build the adapter and install it, following these instructions. I'll publish a new post when I've tried this out. Since the first solution didn't seem to work, I'll have to revert my changes and go for an update via the adapter instead. Here's what I've done: - Dropped my manually created Code Metrics table in the TfsWarehouse database:
USE [TfsWarehouse]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Code Metrics_Build_Build_ID]')
AND parent_object_id = OBJECT_ID(N'[dbo].[Code Metrics]'))
ALTER TABLE [dbo].[Code Metrics] DROP CONSTRAINT [FK_Code Metrics_Build_Build_ID]
GO
USE [TfsWarehouse]
GO
/****** Object: Table [dbo].[Code Metrics] Script Date: 12/06/2007 05:53:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Code Metrics]') AND type in (N'U'))
DROP TABLE [dbo].[Code Metrics]
- Returning warehouseschema.xml to its original state (i.e. copy the backed up version and replacing the new with the old one, or you could manually just delete the <Fact>...</Fact> added before)
- Run SetupWarehouse again, just in case:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -rebuild -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSREPORT -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully.
- Navigate to the warehouse web service and perform the Run command again.
- Verifying in Microsoft Excel that the cube and the perspectives still are there, intact.
Since I haven't managed to get the installation of the XML schema to work, I've come up with 2 different ways to hopefully solve the problem, until I get some answers from MSDN: - Try to manually create the Code Metrics table, insert the xml manually into warehouseschema.xml and the build the olap cube and database tables.
- Try to use the MakeSchemaChanges() method in the adapter to add my Fact table.
The first try was a failure. I added a "Code Metrics" table in the TfsWarehouse DB: USE [TfsWarehouse]
GO
/****** Object: Table [dbo].[Code Metrics] Script Date: 12/05/2007 11:32:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Code Metrics](
[__ID] [int] IDENTITY(1,1) NOT NULL,
[Maintainability Index] [int] NOT NULL,
[Cyclomatic Complexity] [int] NOT NULL,
[Depth of Inheritance] [int] NOT NULL,
[Class Coupling] [int] NOT NULL,
[Lines of Code] [int] NOT NULL,
[Build] [int] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Code Metrics] PRIMARY KEY CLUSTERED
(
[__ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Note that Build is of datatype int. This is because it seems like all references to a build in other tables are using the __ID column of build (not Build Artifact Moniker as I've incorrectly suggested in this post. I think that perhaps Build Artifact Moniker is used in a layer the application developer is not meant to use). The __ID and Build Artifact Moniker value are in most cases the same (except that the data type is different), but not always as this picture shows:
I verified this by publishing another test result. Since earlier I had 2 test results in the database, both referencing Build.__ID == 1. So I chose to associate my newly created test results with Build having __ID == 1 (Build Artifact Moniker == 7, as you can see above). The name of the Build was XjobBuild_20071203.3. And sure, the column "Build" in the table Test Result gives the value of 1 (i.e. it really is the Build.__ID that is used - not Build Artifact Moniker).
Then I added a foreign key referencing the Build table (supposed to represent the <DimensionUse> property):
USE [TfsWarehouse]
GO
ALTER TABLE [dbo].[Code Metrics] WITH NOCHECK ADD CONSTRAINT [FK_Code Metrics_Build_Build_ID] FOREIGN KEY([Build])
REFERENCES [dbo].[Build] ([__ID])
GO
ALTER TABLE [dbo].[Code Metrics] CHECK CONSTRAINT [FK_Code Metrics_Build_Build_ID]
So the foreign key depicts a mapping between TfsWarehouse.Code Metrics.Build and Build.__ID.
Next step was to add our XML into the warehouseschema.xml. I added it as the first fact encountered in the <Facts>....</Facts> property. Went back to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx, chose Run, waits for a while and then checks that I got my cube and perspective in Microsoft Excel.
The result: Nothing had changed. Tried to run SetupWarehouse again:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -rebuild -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSREPORT -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully.
Goes back to the URL mentioned above, chose Run again, checks in Excel - Nothing. The Code Metrics table is still in the TfsWarehouse database, the olap cube has probably been rebuilt successfully since I haven't got any error messages, and my Fact entry in warehouseschema.xml is intact. But still nothing whatsoever about code metrics in the cube!
I have although encountered a strange thing: If you feed SetupWarehouse with an incorrect xml file (e.g. wrong file name, referencing a file that does not exist), it still says that the warehouse updated successfully:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -rebuild -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c hma.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSREPORT -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully.
So perhaps my value passed to the -c parameter has been wrong all the time? Probably not because now my perspectives and cubes are gone - again. I think I would have noticed that before... Anyway, executing Run (from internet explorer) restores the perspectives and the cube. Even if you take away some parameters like -rebuild and -o, it still "updates successfully" if given a wrong name of the xml file:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -s ORCASBETA2_TFSV -d TfsWarehouse -c hema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSREPORT -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully.
Nothing, nothing, NOTHING seems to make the Code Metrics Fact table appear in the cube. WHY THE HELL NOT? Please tell me I'm stupid and I'm missing something obvious. Please! I didn't solve the XML import problem thingy tonight. I got to bed like at 5 in the morning, and here I am - a couple of minutes past 9 in the morning, and still trying to find a solution. I decided to post the problem on the MSDN Forums here. Hopefully some kind soul will be able to help me. I started by following the tips in this post. Although that one is for the 2005 edition, it still is pretty much applicable to the 2008 version of Team Foundation Server, as I've understood. So I browsed to C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools, copied my XML schema to that directory (CodeMetricsSchema.xml), and ran SetupWarehouse.exe with these parameters:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse.exe -s ORCASBETA2_TFSV -d TfsWarehouse -c CodeMetricsSchema.xml -a TFSSERVICE -ra TFSREPORT -mturl http://orcasbeta2_tfsv:8080 where: -s server name
-d database name of the warehouse
-c config XML file.
-a Access Account
-ra Reader Account
-mturl The url of the TFS domain; ex.: http://machine1:8080 The result was the following: SetupWarehouse: Warehouse updated successfully. I started Excel to try it out, but it seemed like all my cubes were gone! I was able to connect to the TFS database, but no cubes or perspectives was to be found. I realized it was because I had not issued a run command (or it had not been automatically triggered) that caused this. To remedy this, I browsed to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx where you can issue the Run command. I did that, and voila - I had my cubes and perspectives back. But I did not have any code metrics! Another resource on the net advised to delete the Data folder from the OLAP directory, and then running SetupWarehouse.exe again. No such luck though, I just got these error messages: C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a TFSSERVICE -ra TFSSERVICE -mturl http://orcasbeta2_tfsv:8080
An unexpected error occurred when connecting to the Analysis Server. Check that your Analysis Server is up and you have sufficient privileges on it: The following system error occurred: The system cannot find the path specified. .
File system error: Error occurred during the creation of directory: '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\TfsWarehouse.0.db\Team System.0.cub\Work Item History.0.det'.
The following system error occurred: The system cannot find the path specified. .
File system error: Error occurred during the creation of directory: '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\TfsWarehouse.0.db\Team System.0.cub\Code Churn.0.det'. I seemed to have the same problems as this guy. Unfortunately he didn't solve the problem either. I had my Analysis server online I had sufficient privilege (TFSSERVICE is an Admin account, and thus part of the admin group). I took a breather and checked the documentation. I tried to add the -l and -v parameters too so it would write to the log file. The output was empty. But I discovered this from the SetupWarehouse documentation that the -a and -ra parameters should have values in the format DOMAIN\USER - not just USER. Thus, I changed my input to this (plus I stopped the SQL server Analysis Services) and ran this again: C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSSERVICE -mturl http://orcasbeta2_tfsv:8080 -l log.txt The result in the log file: An unexpected error occurred: Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator. Trying to access http://orcasbeta2_tfsv:8080 gives me an authorization denied (Error 403). I wonder if it is supposed to be like that or not when I'm logged in with the TFSSERVICE account.
I also found this link, describing how to get the warehouse to update itself. It seems I was a bit to quick. After I've issued the Run command and waited for a couple of seconds, I got my cubes and perspectives back. But, as you could've guessed - no code metrics facts at all. So once again:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c CodeMetricsSchema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSSERVICE -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully. The result: The warehouse started to Idle almost directly. And my cubes were gone again. To recreate the cubes: ============== RECREATE CUBES ==================
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSSERVICE -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully.
============== RECREATE CUBES ================== Do a Run again (at http://orcasbeta2_tfsv:8080/Warehouse/v1.0/warehousecontroller.asmx) , wait a couple of seconds, and verify that you got your OLAP data back in Excel. When this was done, I tried to register my own schema again: C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c CodeMetricsSchema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSSERVICE -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully. Repeat the Run procedure as mentioned above, wait a couple of minutes and then check in Excel again. No code metrics at all. Nothing. Not even a code metrics table to be seen in SQL Server Management Studio Express. All this even though it reported "Warehouse updated successfully". I'm having my doubts. Whatever perspective or cube I choose in Excel, I don't see my code metrics facts anywhere. So I started thinking that perhaps you need to insert some data for the tables to appear. But all other tables have been cleared, and I still have the Build, Code Churn and everything else - except my code metrics. Is it necessary to register the adapter before the code metrics facts even show up? Getting more and more desperate, I tried to include 2 schemas in the -c parameter: C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c CodeMetricsSchema.xml warehouseschema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSSERVICE -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully. The result: Nothing. Next proposition: Alter warehouseconfig.xml manually (backing it up first, of course!). I added my <Fact>...</Fact> at the top after <!-- BEGIN: Hatteras -->. C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSSERVICE -mturl http://orcasbeta2_tfsv:8080 -l log.txt
SetupWarehouse: Warehouse updated successfully. Great, it should work now. Shouldn't it? I browsed to ControllerService again (http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx), executes the Run queary and everything, opening Excel and.... nothing. No code metrics. Not in SQL Server Management Studio either. NONE! There must be something I've missed. I add the -rebuild parameter (rebuilds the tables in the warehouse) to the call: C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools>SetupWarehouse -v -o -s ORCASBETA2_TFSV -d TfsWarehouse -c warehouseschema.xml -a ORCASBETA2_TFSV\TFSSERVICE -ra ORCASBETA2_TFSV\TFSSERVICE -mturl http://orcasbeta2_tfsv:8080 -l log.txt -rebuild
SetupWarehouse: Warehouse updated successfully. The result: Nothing. Nothing had changed. It seems impossible to insert my facts to the Warehouse. I've tried almost everything; all the variations above, rebooted, stopped & started the SQL Server Analysis Service... and still nothing. I really do need some help here. Additional Resources http://abadawi.blogspot.com/2007/01/tfswarehouse-cube-corruption.html http://forums.microsoft.com/msdn/showpost.aspx?postid=11686&siteid=1 http://scrumforteamsystem.com/cs/forums/196/ShowPost.aspx On this page you can read a bit about configuration of Builds. There are some important aspects to adhere to, when we shall try to map code metrics data to a specific Build. Here is how all relevant entities relate to eachother (I am not 100% sure on all of these. More like 90% sure) Team Project : Builds is related as ( 1 : N ) 
Builds : Solution is related as ( N : M ) 
Solution : Project is rleated as ( 1 : N ) (not 100% sure on this though. Can a project be part of several solutions? That is if you exclude the obvious situation where you raw-copy the source-code files from one directory to another...)
The above relationships means that there are some things to keep in mind when mapping our code metrics data. We know that we have two quantities that are supposed to be mapped together; Builds and Code Metrics. If we view all our five code metrics as a cluster of metrics, a unit, then we have the following relationships:
Project : Code Metrics is related as ( 1 : 1 ) 
Solution : Code Metrics is related as ( 1 : N )
Build : Code Metrics is related as ( N : M )
Team Project : Code Metrics is related as ( N : M ) 
Ramifications & Conclusion Because we're trying to map Code Metrics to Builds, we have a M : N relationship to take care of. I think the wisest thing to do is to leave it be as it is, and try to make the mapping + filtering in Microsoft Excel. There we should concentrate on mapping the code metrics build for build. It worried me at first, but it really doesn't matter that the same cluster of code metrics could belong to several builds. It is up to the user (or development team) to keep track of what code metrics data that should be mapped to what build. Now I'm off to try install the XML schema, as I said in my previous post. Wish me luck... I've been puzzled about what purpose the <FriendlyUseName> attribute serves when defining your XML Schema for your custom adapter. It is used when defining the <DimensionUses> (and in turn <DimensionUse>) attributes. <FriendlyUseName> is thus a member of <DimensionUse>. To sort this out, I once again studied the warehouseschema.xml file in the [install dir of TFS]\Tools directory. Under the <Facts> element, where all facts for the Warehouse is defined, I found this part: 1 <Fact>
2 <FriendlyName _locID="Test_Result_1">Test Result</FriendlyName>
3 <Name>Test Result</Name>
4 <PerspectiveName _locID="Test_Result_2">Test Result</PerspectiveName>
5 <IncludeCountMeasure>false</IncludeCountMeasure>
6 <!-- for measures -->
7 <Fields>
8 ....
9 </Fields>
10 <DimensionUses>
11 ....
12 <DimensionUse>
13 <FriendlyUseName _locID="Build_12">Build</FriendlyUseName>
14 <UseName>Build</UseName>
15 <DimensionName>Build</DimensionName>
16 </DimensionUse>
17 ....
18 </DimensionUses>
19 ....
20 </Fact>
(I've chosen to look at the Test Result fact, since I've been laborating with that one. Please read my post here and I think my reasoning will make sense). I've deleted some unnessecary information to save space and make the XML snippet more perspicuous.
The important part is lines 12-16. In the <DimensionUse> attribute, they define the <FriendlyUseName>. I don't know what the _locID is for, but it seems to be used as an internal thingy. There are 5 attributes you can define in the <DimensionUse></DimensionUse> attribute (links go to the API documentation at MSDN for respective property. Although the documentation doesn't give you any information at all...):
- <DimensionName> - My guess: The name of the Dimension in the warehouse you are referencing.
- <FriendlyUseName> - My guess: Sets friendly name. This will be used in the OLAP cube in SQL Enterprise editions, apparently. Since all examples I've encountered using this property when referencing Builds have had 'Build' as value, I'll do the same.
- <IntermediateFactName> - My guess: No idea what this property does. I won't include it.
- <RelationalOnly> - My guess: No idea.
- <UseName> - My guess: No idea. But as with <FriendlyUseName>, all examples have had 'Build' as value. I'll do the same here too.
So with these things in mind, the final XML schema (I'm 85% confident it is correct now, and that will have to do) looks like this for our adapter:
<?xml version="1.0" encoding="utf-8" ?>
<WarehouseConfig>
<Facts>
<Fact>
<Name>Code Metrics</Name>
<Field>
<Name>Maintainability Index</Name>
<Type>int</Type>
<AggregationFunction>Sum</AggregationFunction>
</Field>
<Field>
<Name>Cyclomatic Complexity</Name>
<Type>int</Type>
<AggregationFunction>Sum</AggregationFunction>
</Field>
<Field>
<Name>Depth of Inheritance</Name>
<Type>int</Type>
<AggregationFunction>Sum</AggregationFunction>
</Field>
<Field>
<Name>Class Coupling</Name>
<Type>int</Type>
<AggregationFunction>Sum</AggregationFunction>
</Field>
<Field>
<Name>Lines of Code</Name>
<Type>int</Type>
<AggregationFunction>Sum</AggregationFunction>
</Field>
<DimensionUses>
<DimensionUse>
<FriendlyUseName>Build</FriendlyUseName>
<UseName>Build</UseName>
<DimensionName>Build</DimensionName>
</DimensionUse>
</DimensionUses>
</Fact>
</Facts>
</WarehouseConfig>
Now I will try to register the schema in TFS. Let's see how this will go... I've been wondering if the Build Artifact Moniker really is the linking item or not. I have now confirmed that it really is so. As we got the tip from Mr. König to look at hos Test Results are published, I decided to try it out. I created a new project (under my newly created Team Project - keep in mind the difference between Project and Team Project!), and generated some unit tests for the project by using the built-in wizard in Visual Studio. 2 tests were created. I ran the tests by choosing Test --> Run --> All tests in Solution... Next up was to click the Publish button in the Test Results view that appears at the bottom. See the picture below (I didn't care to implement the tests - I just wanted tests to run, never mind that they don't pass!) 
...which takes you to the next screen, where the interesting stuff starts to happen: 
As you can see, you have to associate a build to your published test results. The text showing in the drop-down menu above is exactly the same as the one found in [dbo].[TfsWarehouse].[Build].[Build] field. (TfsWarehouse is the database, the first Build is the table, and the last Build is the column in the table). "Behind the scenes" the Build Artifact Moniker is hiding, mapped to each of these builds. How do I know that? Well, I had to do some experimentation. Here's how I did it. As in the picture above, I chose to associate build number "XjobBuild_20071203.1" to the test results. Keep in mind that I had not published any test results before, so the Test results table in the warehouse was empty. A sidenote: The Build names are generated automatically by the system to follow the standard of: [Name-of-build-type]_[date-when-build-is-created].[version-number]. version-number starts with 1 for the first build, and is incremented +1 every time you make a new build of the same type (on the same date). When I had published the test results, I ran this query in Microsoft SQl Server Management Studio Express (auto-generated by right-clicking on the [TfsWarehouse].[dbo].[Test Result] table and choosing Script Table as --> SELECT To --> New Query Editor Window. I know I didn't have to choose all elements, but it was faster than manually typing the statement ;-) ) SELECT [__ID]
,[Result Record Count]
,[Result Count]
,[Result Transition Count]
,[__LastUpdatedTime]
,[__LastUpdatedBy]
,[__TrackingId]
,[Result]
,[Build]
,[Run]
,[Area]
,[Iteration]
,[Date]
,[Outcome]
,[Finished Date]
,[Agent Machine]
,[Category]
,[Owner]
,[Run By]
,[Platform]
,[Flavor]
,[Team Project]
FROM [TfsWarehouse].[dbo].[Test Result]
Among the results I got, I could see that Build Artifact Moniker == 7. So I ran this query on the the [TfsWarehouse].[dbo].[Build] table, in the same way as above:
SELECT [__ID]
,[Build Artifact Moniker]
,[Build]
,[Build Type]
,[Drop Location]
,[Build Start Time]
,[__LastUpdatedTime]
,[__DimensionMemberActive]
FROM [TfsWarehouse].[dbo].[Build]
And voila. In the table there is a row which has Build Artifact Moniker == 7, and the build name is - exactly as predicted - XjobBuild_20071203.1. It resides on the drop location \\ORCASBETA2_TFSV\share\XjobBuild_20071203.1.
Conclusion: Build Artifact Moniker (B.A.M) is definitely the key identifier we should use to map our code metrics to a specific Build. Note that the B.A.M is a number, but the datatype in the SQL database is nvarchar(64) - it is not an int (!) I had problem making my builds not failing when trying to make them on the TFS VPC. Whatever I did, I always got this error message: 
"TF209011: Could not create drop location \\ORCASBETA2_TFSV\share\XjobBuild_20071203.1: The network path was not found. [strange square]. I began to wonder why I got the error message, since I had created the folder C:\share. But before you laugh in my face, let me just say that my computer network experience is almost non-existing. The fault was of course obvious. I had not shared my C:\share folder and given it an UNC name (duuh!). So after sharing the folder and giving it the UNC name of "share", it finally goes smoothly to make new builds. Not a big problem, but it had to be done so I could try out to publish the Test Results (which I will write about in next post). The code metrics doesn't really work in the way we thought. We thought the code metrics were calculated by an hierachical structure with the Solution as the top element. That is not the case. The top element is the (local) project. To verify this, I added a temporary project (deleteME) to my CodeMetricsAdapter project ang tried to generate the code metrics. The result: 
As you can see, there are two top nodes in the hierarchy - the projects. But I thinkthe builds (that we map our code metrics data to) are kept at solution level (correct me if I'm wrong, Mr. König). So, we must have a way to handle this awkward situation. There are some solutions to this matter. You could for example calculate a mean value for all your projects in your solution. Or you could calculate the sum of all metrics (although that will probably be pretty misleading). You have to aggreggate the values somehow. The question is just where you should do that. There are some options: - In the client
- At the web service
- In the operational store (SQL database)
- In the adapter
- In the Excel report
We can exclude the client and the web service. Take for example a regular Solution which contains, say, 5 projects. For each project, the user will have to type 5 code metric values. That means that the user will have to type 25 values before he can send the code metrics data to the operational store. It just won't happen, nobody is eager to manually input so many values at once. The web service is also excluded because the things you can accomplish at the web service, you could just as easily do the same in the operational store. Furthermore, the web service still has to end up inserting data into the operational store, so why do the overhead work at this level? We could extend the database to include some aggregate information, but there will be concurrency issues and keeping data up to date will probably be hard to accomplish. So, the options we have left is either the adapter or the Excel report. As of now, I think we'll stick to just using the Excel report. This is because first of all I'm eager to test the adapter if it works or not. Thus, I want it to be as simple as possible (I can always extend it later on!). Secondly, I want to see how the data is presented in Excel. Perhaps it's even easier to manipulate it there. From excel we can create a standard template which automatically calculate the mean value, the sum, the median or whatever we need from the code metrics. Please do give som feedback if you have any thoughts on this matter. As you could see in my previous post, we had a couple of solution proposals on how to keep track of the harvested data from the operational store. We chose Solution #1. It won't be too much of a hustle to extend it to Solution #3, since we will only append a DELETE statement after we've harvested our data from the operational store. So, here's the new structure of the operational store. A new column in SavedEntries has been created: - Name: Harvested
- Data type: bit
- Default value on creation: 0
Rule: Everytime the adapter harvests data from the operational store, the data collected must be marked with Harvested = 1. We first considered creating a trigger which will let you update the Harvested field on SELECT. But since triggers only react to ISERT / UPDATE / DELETE statements, it is impossible. Well, unless you do some workarounds but that's too much of a hustle. We decided to create another stored procedure instead which will take care of that part. The procedure collects all data unharvested at the time of call, changes the Harvested field on the collected data to 1, and then returns a SET collection (result from a SELECT statement) to the caller. We have tried this in a simple console application, and it works. The HarvestCodeMetrics procedure looks like this: USE [CMDB]
GO
/****** Object: StoredProcedure [dbo].[HarvestCodeMetrics] Script Date: 12/03/2007 04:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tommie Haag
-- Create date: 2007-12-03
-- Description: Harvests code metric data
-- =============================================
CREATE PROCEDURE [dbo].[HarvestCodeMetrics]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @findings TABLE (
ID int,
Build nvarchar(64),
Stamp datetime,
Harv bit )
-- First, select all non-harvested items
-- (We _will_ select all items not being harvested as of yet.)
INSERT INTO @findings (ID, Build, Stamp, Harv)
SELECT EntryID, Build, TimeAdded, Harvested
FROM SavedEntries
WHERE Harvested = 0
-- Now, as we have taken all items having harvested = 0,
-- it is time to mark these as harvested.
UPDATE SavedEntries SET Harvested = 1 WHERE Harvested = 0
-- Extract the metrics data from MetricsData and @findings table, and be sure
-- to get the exact data needed!
SELECT EntryID, Build, Stamp,
MaintainabilityIndex, CyclomaticComplexity,
DepthOfInheritance, ClassCoupling, LinesOfCode
FROM MetricsData, @findings WHERE EntryID = ID
END
Furthermore, the datatype for SavedEntries.Build has been changed from varchar(64) to nvarchar(64), to be totally compatible with the TfsWarehouse.Build column with the corresponding values. TimeStamp has also been renamed to TimeAdded to avoid conflicts in datatypes versus column names. The updated database structure looks like this now:
We have to have some way to know what code metrics data that already have been processed, and what data that is left to process.
Everytime MakeDataChanges() in the adapter is called, we have to have a way to collect the data from the CMDB, store it in the warehouse, and in some way mark the collected data in the CMDB as "used". We shall not process already processed data twice! There are a couple of solutions to this matter. I don't know as of yet if the so called "trackingID" (which I've written about in earlier posts) can help us here. I think not, because it seems to have with the TFS Warehouse databases to do - not with our own "user" DB. But I've included it as part of one of the solutions, for the future. - Solution #1 - Add an extra field to either the MetricsData or the SavedEntries table called "harvested" or something like that. Set the datatype to bit or something similar. Make it have a default value of 0. Everytime the adapter reads a row from the DB, set "harvested = 1" for that row. This means that when the adapter harvests data from the CMDB database, it should only read rows which has "harvested" set to 0. Pros: - Easy to implement
- Keeps historical data intact, since it does not delete any information
- Relations between the MetricsData and the SavedEntries table will be kept intact.
Cons: - The database will increase in size. Perhaps a bit too much when scaling to a larger project (?) (the extra field does not add so much overhead, but keeping track of all previous records may make the database grow beyond control).
- Solution #2 - "The cleanup solution". Do not change anything in the table structure. Change the procedure for harvesting data instead. Everytime the adapter reads a row from the database (and succeeds in storing the data in the TFS Data Warehouse), delete the corresponding row in both tables afterwards (none of the will serve a purpose anymore). Pros: - Keeps the size of the whole database table to a minimum.
- Very easy to keep track of what's inserted into the warehouse and what's not :-).
Cons: - Difficult to implement. I think. Just think about concurrency issues with first issuing a SELECT statement and then a DELETE statement from C# code. Perhaps it's easily solved, perhaps not.
- No historical data will be available in the operational store CMDB, but it isn't used when already added to the warehouse anyways.
- Solution #3 - (A combination of Solution #1 and Solution #2). Add that harvest field to the table, set "harvested = 1" every time you read data, and then delete all rows with harvested set to 1. It makes it easier to delete correct rows, I think. That is; first run your SELECT statement with a trigger automatically setting harvested = 1, and then runt a DELETE statement Pros: - Keeps the size of the whole database table to a minimum
- Very easy to keep track of what's inserted into the warehouse and what's not :-).
- Easier to implement than solution #2
Cons: - No historical data will be available in the local database CMDB, but it isn't used when already added to the warehouse anyways.
- Solution #4 - Use the trackingID somehow. Maybe the trackingID can be used to collect from the TFS Warehouse the latest row that has been inserted, and map it to our local operational store somehow. If that's the case, then this trackingID can be used to identify in the CMDB which row that was last inserted. Then you just have to harvest the rows inserted after this specific row. I'm sceptical about this solution though. Pros: - Keeps historical data intact, since it does not delete any information.
- Relations between MetricsData and SavedEntries will be kept intact.
- No changes has to be done to the database strucutre.
Cons: - The procedure of harvesting information will probably become more complex.
- Don't know yet if it is doable at all, and we're running out of time.
- I don't know how the interface between the adapter and the operational store should work. Too much fuzzyness and too little information for me to implement this solution as of now.
Any feedback on these solutions (or if you have another one in your backpocket) would be greatly appreciated.
Here's the SQL statement to alter the table: USE [CMDB]
GO
ALTER TABLE [dbo].[MetricsData] ADD CONSTRAINT entryID_unique UNIQUE (EntryID)
Alternatively, you can right-click on the Constraints folder for the dbo.MetricsData table in Object Explorer in Microsoft SQL Server Management Studio Express and add the constraint via the GUI. I've tried it out by trying to add a row which has the same EntryID as an already existing row. We get an exceptions like this:
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'entryID_unique'. Cannot insert duplicate key in object 'dbo.MetricsData'.
The statement has been terminated.
...which is exactly what we want. So I've dropped the primary key for the table (but kept the foreign key). Now, does it still work when the client tries to add data? A testrun gives me the answer YES'! So here is the new structure of the database:
One peculiar thing I've noted (and you can observe it in the screenshot above) is that the constraint just added does not show up under the folder "Constraints". Instead, it shows up under "Keys". I wonder why. I've done some changes to the SQL database (CMDB) and its design. It includes rewriting the stored procedure, and changing the tables' data structures. Now it looks like this: 
Note that it theoretically is enough to have the EntryID as primary key field in the MetricsData table. I have although chosen to insert (or rather, keep) an extra field (called ID) as the primary key for the table. This is because you are not allowed to manually enter data into a key field with an auto-incrementing value (duh). This way we are guaranteed to keep data integrity intact for the table. Anoher solution would be to add the UNIQUE constraint to EntryID and not giving the table a primary key at all. I'm not really sure it will work, but I will give it a try. That would save us some space in the database, as one redundant column will cease to exist then. I should also mentioned how the stored procedure looks like now (script generated as an ALTER statement, but you get the point): set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertCodeMetrics]
(
/*@Metric1 int = 5,*/
/*@parameter2 datatype OUTPUT */
@MI int,
@CyclComp int,
@DoI int,
@ClCoup int,
@LOC int,
@BuildArtifactMoniker varchar(64)
)
AS
/* SET NOCOUNT ON */
/* RETURN */
DECLARE @LatestEntry int
INSERT INTO SavedEntries (Build) VALUES (@BuildArtifactMoniker)
SET @LatestEntry = (SELECT @@IDENTITY)
INSERT INTO MetricsData (EntryID, MaintainabilityIndex, CyclomaticComplexity, DepthOfInheritance, ClassCoupling, LinesOfCode)
VALUES (@LatestEntry, @MI, @CyclComp, @DoI, @ClCoup, @LOC)
I'll get back in another post if I've succeded with the UNIQUE constraint contra the extra ID column. - Start SQL Server Management Studio Express (in the VPC)
- Go to [your server] --> Security --> Logins in the left pane
- Find NT AUTHORITY\NETWORK SERVICE and double click it to open the properties dialog
- Go to User Mappings in the left pane of the properties pane
- Identify your database in the table to the right (in this case: CMDB) and make sure the checkbox is marked under the column "Map" for you database. Do the same for the TfsWarehouse database.
- Under "Database role memebership for: CMDB check "db_owner". For TfsWarehouse check "db_datareader".
- Now it should work fine!
|
Tools 
