This will be a living document and as we troubleshoot issues, we will add to the query and document. It will culminate in a consistency checker tool which automates the checking and ultimately other functions like kicking the warehouse etc... Feedback is welcome and I will update this data.
Symptoms:
The data in the reports is stale and doesn’t seem to ever get updated.
The reports error out.
An error has occurred during report processing. (rsProcessingAborted)
…
Background:
Data in the operational stores (Work Item Tracking, Version Control, Team Build, Integration Services) is pulled into a relational database (TfsWarehouse) and then into an OLAP cube on a scheduled basis. The reports are rendered by querying the cube. There is a Windows Service names TfsServerScheduler which calls the warehouse web service every hour (by default) to initiate the processing of the warehouse. The cube and reports should be available while this process continues in the background.
Troubleshooting:
Preparation Steps:
You should have admin access to the application tier and data tier. The database and cubes are on the data tier. In Beta 3, the reporting services site and reports are on the data tier but in the final RTM version of Team Foundation, it is on the application tier.
On the application tier, turn off IE friendly error handling so you can see messages. In IE, Tools, Internet Options, Advanced, Uncheck HTTP Friendly Errors
Step 1:
Is the TFSServerScheduler windows service runningRight click on My Computer, Manage. Select Services and Applications and then Services. Scroll down to the TFS Server Scheduler and ensure it is running.
This windows service is what signals the warehouse web service to go aggregate data and process the cube. In the final RTM version of Team Foundation, there is one other case where the warehouse is signaled - the completion of the project creation wizard. That means that within a few minutes, the warehouse should be processed and the reports should be available.
To find out when was the last time the scheduler called the warehouse to process, browse to %ProgramFiles%\Microsoft Team Foundation Server 2005\TfsServerScheduler. The xml file in that directory will contain the last time warehouse processing was attempted.
Step 2: Did processing pull data over into the warehouse
Data is pulled from all of the operational systems into a database named TfsWarehouse. From there, it is processed into the cube.
To find out what amount of data in the operational systems made it into the TfsWarehouse database, open up SQL Manager and execute:
use
TfsWarehouseselect Setting as [Last Cube Processing] from _WarehouseConfig where ID = 'LastProcessedTime'
-- total revisions in the relational warehouse
select top 1 __LastUpdatedTime as [LastVCWHWrite] from dbo.[Code Churn] with (nolock) order by __LastUpdatedTime desc
select top 1 __LastUpdatedTime as [LastWITWHWrite] from dbo.[Work Item History] with (nolock) order by __LastUpdatedTime desc
use
TfsWarehouseselect count(*) as [WH Revisions] from [dbo].[Work Item History] with (nolock)
where [Record Count] <> -1
use
TfsWorkitemTrackingselect count(*) as [WIT Revisions] from [dbo].[WorkitemsLatestAndWere] with (nolock)
use
TfsWarehouseselect max ([Changeset ID]) as [WH Changeset] from [dbo].Changeset with (nolock)
where DimensionMemberActive = 1
use
TfsVersionControlselect max(ChangeSetId) as [VC Changeset] from tbl_Changeset with (nolock)
-- identities
select Property_Value as [Warehouse Identity Id] from _PropertyBag
where Property_Key = 'CSS Identity Sequence Id'
use
TfsIntegrationselect max(sequence_Id) as [Integration Identity Id] from tbl_security_identity_cache
-- structure
select Property_Value as [Warehouse Structure Id] from _PropertyBag
where Property_Key = 'CSS Structure Sequence Id'
use
TfsIntegrationselect max(sequence_Id) as [Integration Structure Id] from tbl_nodes with (nolock)
This should let you how up to date your warehouse is. If no data is present, or data is severely latent, either the scheduler is not signaling processing or there is a problem in the warehouse aggregating data. Please refer to Gathering Error Data below.
So, let's go over the output. Here's sample output from our dogfood server with comments after each output block:
-- This shows the last time the cube was successfully processed. If
-- the scheduler is initiating processing successfully,
-- it should be less than an hour
Last Cube Processing
-----------------------
11/18/2005 11:45:33 AM
-- Last code churn entry written in the warehouse
LastVCWHWrite
-----------------------
2005-11-22 11:23:15.803
-- Last workitem revision written into the warehouse
LastWITWHWrite
-----------------------
2005-11-19 22:13:44.047
-- The number of workitem revisions in the workitem tracking
-- operational store
WH Revisions
------------
463754
-- The number of workitem revisions that made it into the warehouse
WIT Revisions
-------------
463754
-- The last Changeset that was processed into the warehouse
WH Changeset
------------
12702
-- The last changeset in the Source Control operational store
VC Changeset
------------
12702
-- The user/group change number that made it into the warehouse
-- (each change which can be one or more user/group changes gets a
--sequence id)
Warehouse Identity Id
----------------------
5667
-- The user/group change number in the operational store
Integration Identity Id
-----------------------
5667
-- The area/iteration change number in the warehouse
Warehouse Structure Id
-----------------------
990
-- The area/iteration change number in the operational store
Integration Structure Id
------------------------
990
Step 3:
Manually processing the warehouseIf there are problems, it is possible to manually process the cube. After manually processing the cube, you can repeat step 2 to validate data was moved over and then if there's problems, refer to the Gathering Error Data section below for the time range of the processing.
Log onto the application tier. Browse to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx
Click on GetWarehouseStatus, and then click the Invoke button. Wait for it to be idle
.It should return ProcessingAdapters (pulling data), ProcessingOlap (processing the cube), or Idle. If you have the final RTM version of Team Foundation, you may see it repeat since it does ProcessingAdapters (schema modifications), ProcessingOlap (schema modifications), ProcessingAdapters (pulling data), ProcessingOlap (processing the cube), and then idle
.Browse to
http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmxClick on Run, and then click the Invoke button. This will start the processing of the warehouse. In Beta3, you will see a blank screen - in RTM, it will return true or false as to whether is started processing (asynchronously).
You can repeat GetWarehouseStatus to monitor the processing.
To verify, you can repeat step 2.
Step 4: Browse to the reports
Each report has the time the warehouse last processed. It should be a recent time.
Report Generated: 2005/11/23 12:55:37 by domain\user; Last Warehouse Update: 2005/11/23 12:15:51
The data should be current and less than an hour.
For troubleshooting, you will likely want to disable caching for the report:
click Properties -> Execution,
Select "Do not cache temporary copies of this report",
Apply
Click View
**In Beta3, <server> is the data tier - in RTM it is the application tier.
Gathering Error Data
Go to the Application Event Log (right click my computer, manage) and see if there are any errors (red x) from the source 'Warehouse'. The error and error stack in that event log can help identify the problem.
If all of the steps are leading to errors, gathering a trace which a support specialist or product team member can analyze will help diagnose the issue.
- Use a tool to capture debug output
dbmon is another popular windows tool
- Enabling tracing
%ProgramFiles%\Microsoft Team Foundation Server 2005\Web Services\web.config:
Change: <add name="General" value="1" /> to <add name="General" value="4" />
Do Step 3 above
Known Beta 3 Issues:
1. Getting this Are you on a non-english locale
at Microsoft.TeamFoundation.Warehouse.OlapCreator.ProcessOlap(Boolean schemaUpdated, UpdateStatusStore updateStatus)
at Microsoft.TeamFoundation.Warehouse.AdapterScheduler.RunCubeProcess()
See:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=139746&SiteID=1
2. Are iterations or area nodes not showing up in reports Did you happen to delete a project and then recreate it by the same name
The following query will help you identify if you have a deleted project in the warehouse:
SELECT TP.*
FROM [TFSWarehouse].[dbo].[Team Project] as TP
JOIN
(SELECT
'vstfs:///Classification/TeamProject/' + CAST(project_id AS VARCHAR(36)) AS Uri
FROM [TFSIntegration].[dbo].[tbl_projects_tombstone]) as dTP
ON (TP.[Project Uri] = dTP.[Uri])
UPDATE [TFSWarehouse].[dbo].[Team Project]
SET [Team Project] = ('Deleted Project - ' + TP.[Team Project] + ' (' + CAST(dTP.id AS VARCHAR(36)) + ')')
FROM [TFSWarehouse].[dbo].[Team Project] as TP
JOIN
(SELECT
'vstfs:///Classification/TeamProject/' + CAST(project_id AS VARCHAR(36)) AS Uri,
project_id as Id
FROM [TFSIntegration].[dbo].[tbl_projects_tombstone]) as dTP
ON (TP.[Project Uri] = dTP.[Uri])
3. Scenario Details and Related Workitem Reports show no data: The data should be in the warehouse if you run the queries above but there was an issue in the Beta 3 code which caused the data from time to time not to get into the cube during incremental processing. The workaround is to process the cube fully:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=139746&SiteID=1
4. OLAP Schema can become out of sync: Because of the processing order and not persisting a schema dirty bit, the OLAP schema could become out of sync.
Symptoms when viewing reports from the AT (The dimension 'xxx' was not found in the cube):
similar to ...
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'dsWorkItemTypeParam'. (rsErrorExecutingCommand)
Query (1, 452) The dimension '[System_WorkItemType]' was not found in the cube when the string, [System_WorkItemType].[System_WorkItemType].[System_WorkItemType], was parsed.
Resolution: Rebuild the OLAP schema and cube
1) Back up all data
2) Delete the TFSWarehouse database in *Analysis Services* (I don't believe this is absolutely necessary but doesn't hurt if you back it up).
3) From a command prompt on the TFS Application Tier machine, use the following command (substituting the correct values for your installation in place of the following values: ServerName, Domain\TFSService, Domain\TFSReports):
%ProgramFiles%\Microsoft Visual Studio 2005 Team Foundation Server\Tools\setupwarehouse.exe -o -s ServerName -d TfsWarehouse -c Warehouseschema.xml -a Domain\TFSService -ra Domain\TFSReports
Note: -o is the important param here which causes the OLAP cube to get rebuilt.
Feedback welcome. I will post a tool that will automate all of this by RTM.
Thanks
Bryan MacFarlane
Continued Errors after troubleshooting guide
slavalit
Thanks a lot for this.
I went through these steps for this issue (http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=145329&SiteID=1) and it looks like the data warehouse is doing everything it is supposed to do and without error.
This leads me to suspect that our reports for the scenario details and related work items are corrupt. We selected the CMMI process on beta 3 refresh.
What would be the correct set of steps to obtain clean versions of these reports and upload these to our report server so that we can take advantage of these helpful reports
Thanks,
Barton
Akash69
When there's a project name collision, the reports will not be able to find the proper project by name. One symptom is the iteration and area nodes for that project don't show up in the reports.
The following query will help you identify if you have a deleted project in the warehouse:
SELECT TP.*< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
FROM [TFSWarehouse].[dbo].[Team Project] as TP
JOIN
(SELECT
'vstfs:///Classification/TeamProject/' + CAST(project_id AS VARCHAR(36)) AS Uri
FROM [TFSIntegration].[dbo].[tbl_projects_tombstone]) as dTP
ON (TP.[Project Uri] = dTP.[Uri])
Renaming the deleted project will fix your problem. You can run the following to patch your warehouse:
UPDATE [TFSWarehouse].[dbo].[Team Project]
SET [Team Project] = ('Deleted Project - ' + TP.[Team Project] + ' (' + CAST(dTP.id AS VARCHAR(36)) + ')')
FROM [TFSWarehouse].[dbo].[Team Project] as TP
JOIN
(SELECT
'vstfs:///Classification/TeamProject/' + CAST(project_id AS VARCHAR(36)) AS Uri,
project_id as Id
FROM [TFSIntegration].[dbo].[tbl_projects_tombstone]) as dTP
ON (TP.[Project Uri] = dTP.[Uri])
-Jim
harkat1
I notice that the _Warehouse Config table contains some fields which can be set - notably the following:
- FirstStartDelaySeconds
- RunIntervalSeconds
How do these settings work
Do they operate on TFSWarehouse (OLTP) or on the cube
Kind Regards
Jean-Pierre
fulga
This is great. The fix is working well.
Thanks for the quick response.
Regards,
Gary Russo
klem182
Regarding item 2 of the beta 3 issue list: "Are iterations or area nodes not showing up in reports".
Can you please provide a quick fix for this
As noted, I deleted a previous Team Project and then recreated it again using the same name.
jeevankumar
thx
Bob Walter
Bryan,
Thanks for your response.
- So the _WarehouseConfig values are the same as those manipulated by the warehousecontroller.asmx
- The TFSWarehouse (*relational*) database is always going to be slightly behind the actual/raw data used to track work items.
- I suppose there are some stored procedures - or perhaps other logic - to draw the raw data into TFSWarehouse. Is there a way to force updates of just the relational data warehouse (TFSWarehouse)
Jean-Pierre
Bary
saar
The workaround is to fully process the cube:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=139746&SiteID=1
I am updating the troubleshooting steps with this as a known issue.
thx
GabeOSM
I went through the entire troubleshooting guide, but some reports still have this error:
I haven't found a clue yet. I also have this possibly unrelated (but who knows) error in windows system log:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {BA126AD1-2166-11D1-B1D0-00805FC1270E} to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
Checking with regedit, this belongs to "Network Connection Manager Class", but I can't find/fix it in Component Services.
Anybody knows how to fix this
Regards,
Eric
Dennis_K
Thomas Irvine
The Report Server log gives more detail. Any help would be appreciated:
w3wp!library!1!14/12/2005-18:37:57:: i INFO: Call to RenderFirst( '/TAPSDEV/Project Velocity' )
w3wp!processing!9!12/14/2005-18:37:58:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException' was thrown., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException' was thrown.
w3wp!processing!9!12/14/2005-18:37:58:: e ERROR: An exception has occurred in data source 'dsWorkItemHistoryChart'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The Group expression for the grouping 'Date' contains an error: Argument 'DateValue' cannot be converted to type 'Date'. at Microsoft.ReportingServices.ReportProcessing.ReportRuntime.RegisterRuntimeErrorInExpression(VariantResult& result, Exception e, IErrorContext iErrorContext, Boolean isError)
Kev Matthews
The RunInterval seconds is the latency of the warehouse. In my write up above I refered to the TfsServerScheduler NT service - that's the interval it kicks the warehouse to process.
Processing includes pulling data from many data sources into warehouse and processing the cube.
Note, that the process of pulling data and processing the cube isn't always cheap which is why we ship with an hour latency. The warehouse should be a source of aggregated latent data for historical purposes (trending over time, stats etc...) and the granularity is typically a day on trending but you want the latest date to be relatively recent.
Edman Zayzay
Hi,
Great post! Thank you.
I do the following:
1. Add a new bug to work item s using Team Explorer
2. Rerun your query (SQL)
I find that the changes appear after about a minute. Why is this
Is it perhaps becuase TE saves the data to another table/database and it is copied to TFSWarehouse at alater time
I have looked at a query on [Current Work Item] - same result!
How can I access the data immediately
Kind Regards
Jean-Pierre