TFS Warehouse errors

TFS Warehouse errors

• kilis

  You can debug TFS if you follow these instructions:
  
  http://66.51.173.204/help/HowToDebugTFSWarehouseAdapters/howtodebugtfswarehouse.htm
  
  If you send the debug log to us we might be able to determine what is causing this.
  

• Richard Purchas

  I am getting a similar or exactly the same error. Also seems that because of this i can't see dates after October 7th 2005 in my reports :S
  
  Is there any solution to this
  
  P.S. My server is running on an Icelandic (is-IS) locale (we use dd.mm.yyyy there).
  

• mbudiman

  Partial success (in my case at least)!

  I think i found out what is wrong (partially) and it seems to indeed be a mismatch between Locale settings.

  Connect to your Analysis Service using the SQL Server Management Studio (be sure to connect to the Analysis Service and not the Database). Then right click the Server (not the database) and select "Properties". There should be "Language/Collation" settings there. There, the Language was set to "English (US)" when it should be set to "Icelandic" in my case. Set it to the correct Language (as to how your server is set up), and the Cube should now process when run through SQL Server Management Studio. The Warehouse Web Service still gives me the "char to valid datetime" error when i try to run the Process from there :(
  
  Hope this helps someone.

  Any specific reason as to why the Web Service would still be failing but a manual "Process" works I'm completely stumped now, since there don't seem to be any language settings in the Web Service part (unless its hardcoded which should be a no-no).

  
  

• SugarDaddy

  Hi,
  
  I also have errors in event-log from TSF Warehouse service - see:
  http://forums.microsoft.com/msdn/ShowPost.aspx PostID=101668
  In the moment I don’t have a clue why this service is crashing on my server.
  
  Otherwise - the starting and stopping of the TFS-(web)services is normal.
  If you take a look on the "TFS AppPool" in IIS the worker process will shutdown if the process is idle more then 20 minutes.
  

• Joel Bennett

  I am seeing this problem too - does anyone have any ideas

  
  
  

• Aselby11

  Thanks Dan,
  I'll email you the log and if we get to the bottom of it, we can repost the findings here.
  
  Cheers
  

• johnlaus

  It seems that it has something to do with the local settings.
  
  I installed the TFS with german settings (date format is also dd/mm/yyyy). After installation the warehouse process produces always error messages in event-log.
  
  I tried to set the local settings to en-US and after restarting the server the warehouse runs without errors.
  
  I got a hint from Dan Takacs to run the Warehouse manually on SQL Server 2005.
  You have to connect to the Analysis Service on SQL Server 2005 and select the "TFW Warehouse" Cube. Then choose "Process" and press "OK".
  You now see all cubes which are processed by the service.
  On my computer I had troubles with three cubes on german settings.
  
  
  
  

• Chris_Kavanagh

  We have identified this bug in beta 3.  We will address this issue for VSTS RTM.  For now the workaround is to do the following:
  
  Change the Web.Config file for the TeamFoundation Warehouse component to switch to Full processing.
              C:\Program Files\Microsoft Team Foundation 2005\Web Services\Warehouse\web.config:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    <appSettings>

      <add key="ProcessingType" value="Full" />

    </appSettings>
  
  

  
  

• GeoffP

  that would be great if you send me a backup of your cube & the datawarehouse database. 
  
  Also please include your machine configuration (what locale are you using for your Client & servers)
  
  Thanks,
  Dan
  Dan.Takacs(nospam)@microsoft.com
  

• fulgerica

  Won't work on some localized versions again. I'd suggest getting help from the real SQL server guys.
  
  German version of MSSQL (NOT english version on german system) for instance does NOT accept date format "yyyy-mm-dd", you'd have to use "yyyymmdd". This seems to be the "most compatible" text representation of a date value in the MSSQL world.
  
  Btw. why not getting around the whole fuss and using something like that "SELECT ... FROM ... WHERE date_value <= @p_date_value", and setting a parameter from a DateTime value. Nothing can go wrong with that.
  

• EmNat

  Hi,
  
  I tried to process manually all TFS-cubes with SQL Management Studio and the processing fails on following items:
  "Code Churn"
  "Work Item History"
  "Current Work Item"
  
  The errormessage looks similiar on all three items:
  
  <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Object>
        <DatabaseID>TFSWarehouse</DatabaseID>
        <CubeID>Team System</CubeID>
        <MeasureGroupID>Current Work Item</MeasureGroupID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Batch>
   Processing Cube 'Team System' failed.
    Start time: 10/6/2005 10:49:00 AM; End time: 10/6/2005 10:49:07 AM; Duration: 0:00:07
    Processing Measure Group 'Current Work Item' failed.
     Start time: 10/6/2005 10:49:07 AM; End time: 10/6/2005 10:49:07 AM; Duration: 0:00:00
     Processing Partition 'Current Work Item' failed. 1 rows have been read.
      Start time: 10/6/2005 10:49:07 AM; End time: 10/6/2005 10:49:07 AM; Duration: 0:00:00
      SQL queries 1
       SELECT [Current_x0020_Work_x0020_Item].[ID] AS [Current_x0020_Work_x0020_ItemID0_0],[Current_x0020_Work_x0020_Item].[ccMicrosoft_VSTS_Scheduling_RemainingWork] AS [Current_x0020_Work_x0020_Item0_1],[Current_x0020_Work_x0020_Item].[ccMicrosoft_VSTS_Scheduling_CompletedWork] AS [Current_x0020_Work_x0020_Item0_2],[Current_x0020_Work_x0020_Item].[ccMicrosoft_VSTS_Scheduling_BaselineWork] AS [Current_x0020_Work_x0020_Item0_3],[Current_x0020_Work_x0020_Item].[Work Item ID_Work Item ID_ID] AS [Current_x0020_Work_x0020_Item0_4],[Current_x0020_Work_x0020_Item].[State_State_ID] AS [Current_x0020_Work_x0020_ItemState_State_ID0_5],[Current_x0020_Work_x0020_Item].[Revision_Revision_ID] AS [Current_x0020_Work_x0020_ItemRevision_Revision_ID0_6],[Current_x0020_Work_x0020_Item].[Assigned To_Person_ID] AS [Current_x0020_Work_x0020_ItemAssigned_x0020_To_Person_ID0_7],[Current_x0020_Work_x0020_Item].[Changed By_Person_ID] AS [Current_x0020_Work_x0020_ItemChanged_x0020_By_Person_ID0_8],[Current_x0020_Work_x0020_Item].[Created By_Person_ID] AS [Current_x0020_Work_x0020_ItemCreated_x0020_By_Person_ID0_9],[Current_x0020_Work_x0020_Item].[Area_Area_ID] AS [Current_x0020_Work_x0020_ItemArea_Area_ID0_10],[Current_x0020_Work_x0020_Item].[Iteration_Iteration_ID] AS [Current_x0020_Work_x0020_ItemIteration_Iteration_ID0_11],[Current_x0020_Work_x0020_Item].[Team Project_Team Project_ID] AS [Current_x0020_Work_x0020_Item0_12],[Current_x0020_Work_x0020_Item].[System_Reason_System_Reason_ID] AS [Current_x0020_Work_x0020_ItemSystem_Reason_System_Reason_ID0_13],[Current_x0020_Work_x0020_Item].[System_WorkItemType_System_WorkItemType_ID] AS [Current_x0020_Work_x0020_Item0_14],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_Issue_Microsoft_VSTS_Common_Issue_ID] AS [Current_x0020_Work_x0020_Item0_15],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_ActivatedBy_Microsoft_VSTS_Common_ActivatedBy_ID] AS [Current_x0020_Work_x0020_Item0_16],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_ResolvedBy_Microsoft_VSTS_Common_ResolvedBy_ID] AS [Current_x0020_Work_x0020_Item0_17],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_ResolvedReason_Microsoft_VSTS_Common_ResolvedReason_ID] AS [Current_x0020_Work_x0020_Item0_18],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_ClosedBy_Microsoft_VSTS_Common_ClosedBy_ID] AS [Current_x0020_Work_x0020_Item0_19],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_Priority_Microsoft_VSTS_Common_Priority_ID] AS [Current_x0020_Work_x0020_Item0_20],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_Triage_Microsoft_VSTS_Common_Triage_ID] AS [Current_x0020_Work_x0020_Item0_21],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_Rank_Microsoft_VSTS_Common_Rank_ID] AS [Current_x0020_Work_x0020_Item0_22],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Build_FoundIn_Build_ID] AS [Current_x0020_Work_x0020_Item0_23],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Build_IntegrationBuild_Build_ID] AS [Current_x0020_Work_x0020_Item0_24],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_ExitCriteria_Microsoft_VSTS_Common_ExitCriteria_ID] AS [Current_x0020_Work_x0020_Item0_25],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Common_Discipline_Microsoft_VSTS_Common_Discipline_ID] AS [Current_x0020_Work_x0020_Item0_26],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Scheduling_StartDate_Date_ID] AS [Current_x0020_Work_x0020_Item0_27],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Scheduling_FinishDate_Date_ID] AS [Current_x0020_Work_x0020_Item0_28],[Current_x0020_Work_x0020_Item].[Microsoft_VSTS_Scheduling_TaskHierarchy_Microsoft_VSTS_Scheduling_TaskHierarchy_ID] AS [Current_x0020_Work_x0020_Item0_29],[Current_x0020_Work_x0020_Item].[VSTS_Common_QualityOfServiceType_VSTS_Common_QualityOfServiceType_ID] AS [Current_x0020_Work_x0020_Item0_30],[Current_x0020_Work_x0020_Item].[VSTS_Common_RoughOrderOfMagnitude_VSTS_Common_RoughOrderOfMagnitude_ID] AS [Current_x0020_Work_x0020_Item0_31]
    FROM [dbo].[Current Work Item] AS [Current_x0020_Work_x0020_Item]
      Error Messages 2
       Errors in the OLAP storage engine: The attribute key cannot be found: Table: Current_x0020_Work_x0020_Item, Column: State_State_ID, Value: 2. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute State of Dimension: State from Database: TFSWarehouse, Cube: Team System, Measure Group: Current Work Item, Partition: Current Work Item, Record: 37.
       Errors in the OLAP storage engine: The attribute key cannot be found: Table: Current_x0020_Work_x0020_Item, Column: State_State_ID, Value: 2. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute State of Dimension: State from Database: TFSWarehouse, Cube: Team System, Measure Group: Current Work Item, Partition: Current Work Item, Record: 37. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Current Work Item' partition of the 'Current Work Item' measure group for the 'Team System' cube from the TFSWarehouse database.
  Errors and Warnings from Response
   Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
   Errors in the OLAP storage engine: An error occurred while processing the 'Current Work Item' partition of the 'Current Work Item' measure group for the 'Team System' cube from the TFSWarehouse database.
   Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
   Errors in the OLAP storage engine: The attribute key cannot be found: Table: Current_x0020_Work_x0020_Item, Column: State_State_ID, Value: 2.
   Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute State of Dimension: State from Database: TFSWarehouse, Cube: Team System, Measure Group: Current Work Item, Partition: Current Work Item, Record: 37.
  
  
  If you like I can send you a backop of the cube.
  
  Regards,
  Johannes
  
  PS: If I debug my machine the resulting messages are the same like in the event-log...
  

• Walt Parkman

  My server is set with the en-AU locale, which also has its date formats dd/mm/yyyy, I wonder if this is part of the problem. It would certainly explain why not many people are seeing this problem.
  
  

• aahd1

  < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> 

  How & why this bug occurs:   the reporting adapters will suck data from the operational stores into the TFSWarehouse SQL db .  This data is then transported to the Analysis Services db (TFSWarehose db).  Our code that tries to update the TFSWarehouse Analysis services db has a bug in it.  We generate SQL statements with a date that localized to the Mid Tier locale.  i.e:

              “Select * from …. Where LastUpdatedTime <=” + dateTime.ToString()

  These kinds of SQL statements do not work on SQL databases that differ in locales.

   

  We should be generating dates that are “universially” acceptable by SQL.

              “Select * from …. Where LastUpdatedTime <=” + dateTime.ToString(“yyyy-MM-ddTHH:mm:ss.fff”)

   

  To turn off our “incremental processing code” you can specify the above flag in web.config.  This will bystep our smart cube update and fully process the Analysis Services db every hour.

  
  

• VaderPi

  Are you seeing data in the cube   We would love to take a look at your cube.  Would you be ok with backing it up and sending it to me
  
  Sam
  
• TFS Warehouse errors