Warehouse Cube Won't Process - B3Refresh Upgraded OK

Question

Our B3Refresh install upgraded successfully to the release version without throwing any errors.  Unfortunately our Warehouse cube won’t process.

When the OLAP cube attempts to process, I'm getting Event 3000 errors in the event logs. 

Detailed Message: Processing OLAP failed
Exception Message: MdxScript(Team System) (64, 46) The '[Str]' function does not exist.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Team System) (66, 1) The END SCOPE statement does not match the opening SCOPE statement.
…

Exception Stack Trace:    at Microsoft.AnalysisServices.XmlaClient.SendExecuteAndReadResponse(Boolean skipResults, Boolean throwIfError)

   at Microsoft.AnalysisServices.AnalysisServicesClient.CommitTransaction()

   at Microsoft.AnalysisServices.Server.CommitTransaction()

   at Microsoft.TeamFoundation.Warehouse.OlapCreator.ProcessOlap(Boolean schemaUpdated, UpdateStatusStore updateStatus)

I've done quite a bit of reading on this forum and tried invoking a Run from the Wearhouse webservice. No luck.  I also tried adding the Full process directive to the Warehouse web.config.  All with no luck.

I had one project created in the B3Ref version, with quite a few work Items and areas created.  I didn't customize anything with workItems or the process templates. 

Something tells me the problems is coming from the TfsWorkItemTracking database.  Doing a schema comparison against my TfsWorkItemTracking database and a clean install verion of the same table does show some differences.  My version was the result of an upgrade from B3Refresh so I'm not entirely surprised the schemas didn't match exactly.  I'm not sure if this is the problem or if there is a bad workItem that's breaking things.

TfsWorkItemTracking.dbo.InstanceInfo lists InstanceID d8fe8f08-b253-47aa-ac09-416693471605 and SchemaVersionID 4

Tracing the Warehouse processing reveals it's failing while processing the Iteration dimension.

[5652] [DW] [Info, PID 5652, TID 4152, 02:15:37.744] Added CubeDimensions: vRelated Current Work Items.Microsoft_VSTS_Common_ResolvedDate
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:37.744] Added CubeDimensions: vRelated Current Work Items.Microsoft_VSTS_Scheduling_FinishDate
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:37.744] Added CubeDimensions: vRelated Current Work Items.Assigned To
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:37.744] Added CubeDimensions: vRelated Current Work Items.Team Project
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:37.744] Added CubeDimensions: vRelated Current Work Items.Microsoft_VSTS_Build_FoundIn
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:37.744] Added CubeDimensions: vRelated Current Work Items.Work Item
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:37.744] Added measure group: vRelated Current Work Items
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:44.713] Created OLAP.
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:44.729] Cube processing starting.
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:44.916] Found OLAP db: TFSWarehouse
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:45.354] Processed: Tool Artifact Display Url
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:45.432] Processed: Team Project
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:45.682] Processed: Today
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:45.948] Processed: Date
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.041] Processed: Person
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.135] Processed: Changeset
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.322] Processed: File
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.604] Processed: Work Item
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.697] Processed: Build
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.776] Processed: Platform
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.838] Processed: Build Flavor
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.916] Processed: Build Quality
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:46.979] Processed: Build Status
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.057] Processed: Assembly
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.135] Processed: Test Category
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.291] Processed: Load Test Counter Dimension
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.385] Processed: Outcome
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.479] Processed: Load Test Page Summary Dimension
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.588] Processed: Result
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.682] Processed: Run
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.760] Processed: Run Result
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.822] Processed: Load Test Scenario
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.901] Processed: Machine
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:47.979] Processed: Load Test Transaction Dimension
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:48.057] Processed: Area
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:48.151] Processed: Iteration
[5652] [DW] [Info, PID 5652, TID 4152, 02:15:51.838] Processed OLAP database: TFSWarehouse
[5652] [DW] [Warning, PID 5652, TID 4152, 02:15:52.026] MdxScript(Team System) (64, 46) The '[Str]' function does not exist.
[5652] The END SCOPE statement does not match the opening SCOPE statement.
[5652] MdxScript(Team System) (66, 1) The END SCOPE statement does not match the opening SCOPE statement.
[5652] MdxScript(Team System) (68, 46) The '[Str]' function does not exist.
[5652] The END SCOPE statement does not match the opening SCOPE statement.
[5652] MdxScript(Team System) (70, 1) The END SCOPE statement does not match the opening SCOPE statement.

The TfsWarehouse.dbo.Iteration table has 2 rows that hold info about deleted Iterations that were defined and then deleted prior to the upgrade.  Below is the data for the 2 suspect rows.

ID	Iteration	Iteration Uri	Parent Iteration	Iteration Path	Forwarding ID	Project Uri	__Parent_ID	__LastUpdated	__DimensionMemberActive
8	Deleted Nodes	Deleted Nodes		\Deleted Nodes			8	3/26/2006 20:18	TRUE
9	23abdf5a-480e-489e-88f5-9eb429178d6b	vstfs:///Classification/Node/23abdf5a-480e-489e-88f5-9eb429178d6b	Deleted Nodes	\Deleted Nodes\23abdf5a-480e-489e-88f5-9eb429178d6b	vstfs:///Classification/Node/0a07f99c-c4aa-454e-9300-128b09953144	NULL	8	3/26/2006 20:18	TRUE

(scroll to the right to see all row details)

I deleted these two rows and I was able to manually process the Iteration dimension.  So looks like the trick might be to somehow delete all rows in the related tables responsible for the two data rows showing up in Warehouse.dbo.Iteration table.  Yuck.  A quick scan shows Iteration data in TfsIntegration and TfsWorkItemTracking.  Humm.  Maybe we can undelete the problem Iterations....

Any assistance would be greatly appreciated.

-Nathan

Answer 1

 

Nathan,

This might be due to a known Analysis Services issue that can be fixed by applying the SQL Server 2005 KB914595 HotFix from http://msdn.microsoft.com/vstudio/teamsystem/downloads/ 

If this does not work, I would recommend deleting the cube and recreating the warehouse by following the following steps:

Step 1:
Stop the SQL Server Analysis Services – Use Management Studio - Connect to Analysis Services - Right click on the Analysis Server and select Stop.
Make a copy of the Analysis Server “data” folder for backup.  Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data
Delete the Data Folder
Restart Analysis Services.
 
Step 2
Run the setupWarehouse command from the application tier:
 C:\Program Files\Microsoft Visual Studio 2005 Team Foundation Server\Tools\Setupwarehouse.exe -o -mturl http://<APP_TIER>:8080 -c warehouseschema.xml -s <DATA_TIER> -d TfsWarehouse -a <DOMAIN>\<SERVICE_ACCOUNT> -ra <DOMAIN>\<REPORTING_ACCOUNT>

<APP_TIER> is the name of the app tier server.
<DATA_TIER> is the name of the data tier server
<DOMAIN>\<SERVICE_ACCOUNT>  is the account used for TFS Services (set in your domain)
<DOMAIN>\<REPORTING_ACCOUNT is the account used for TFS Reporting (set in your domain)

Wait for this process to complete.

Step 3:
From the application tier server
Open http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx.
Click Run and then click Invoke.
Open http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx.
Click GetWarehouseStatus, and then click Invoke.
Wait for the service to idle. The service should return Running Adapters, ProcessingOlap, and then idle. The four possible return states are:
Idle:  The warehouse is not processing.
RunningAdapters:  The adapters are applying schema changes or pulling data into the warehouse.
ProcessingOlap:  The warehouse is processing schema changes or data changes in the OLAP cube.
Blocked:  The warehouse is blocked.

Step 4:
Process the TFSwarehouse in Analysis Service using Management Studio.

Thanks,
Othmane

 

Answer 2

Bingo!

I followed your steps, top to bottom, and the cube is now successfully processing.

Thanks for your time and help with a successful resolution.

-Nathan

Answer 3

In step 2, please rebuild the cube by using this command instead:

setupwarehouse -o -s ServerName -d TFSWarehouse -c warehouseschema.xml -a TFSServiceAccount -ra TFSReportAccount

 

The -rebuild option (in the original proposal) will drop the whole relational warehouse and in case of a big warehouse it may take a long time to rebuild.  The -o option will only drop the cube and rebuilding that is very fast. 

The -rebuild option should be used only as the very last resort.

Answer 4

Hi,

 

Thanks, but it did not solve my issue.

 

I was unable to install the hotfix KB914595 as it is service pack 2, see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1217827&SiteID=1 (albeit SP1 was installed on Rob_p's server on that thread)

 

I recieved errors in running the setupwarehouse command in Step 2 of rebuilding the cube

 

I tried the recommended method:-

 

setupwarehouse -o -s ServerName -d TFSWarehouse -c warehouseschema.xml -a TFSServiceAccount -ra TFSReportAccount

 

and recieved the following error:-

C:\Program Files\Microsoft Visual Studio 2005 Team Foundation Server\Tools>setup
warehouse -o -s vsts-tfs -d TFSWarehouse -c warehouseschema.xml -a TFSService -r
a TFSReports
An unexpected error occurred when connecting to the Analysis Server. Check that
your Analysis Server is up and you have sufficient privileges on it: File system
 error: Error opening file; \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OL
AP\Data\TFSWarehouse.0.db\Team System.0.cub.xml is not a disk file or file is no
t accessible.

I tried the orignal post's method:-

 

Run the setupWarehouse command from the application tier:
 C:\Program Files\Microsoft Visual Studio 2005 Team Foundation Server\Tools\Setupwarehouse.exe -o -mturl http://<APP_TIER>:8080 -c warehouseschema.xml -s <DATA_TIER> -d TfsWarehouse -a <DOMAIN>\<SERVICE_ACCOUNT> -ra <DOMAIN>\<REPORTING_ACCOUNT>

<APP_TIER> is the name of the app tier server.
<DATA_TIER> is the name of the data tier server
<DOMAIN>\<SERVICE_ACCOUNT>  is the account used for TFS Services (set in your domain)
<DOMAIN>\<REPORTING_ACCOUNT is the account used for TFS Reporting (set in your domain)

 

and recieved the following:-

 

C:\Program Files\Microsoft Visual Studio 2005 Team Foundation Server\Tools>Setupwarehouse.exe -o -mturl http://vsts-tfs:8080 -c warehouseschema.xml -s vsts-tfs -d TfsWarehouse -a teamdev\TFSService -ra teamdev\TFSReports An unexpected error occurred when connecting to the Analysis Server. Check that your Analysis Server is up and you have sufficient privileges on it: Authentication failed.

 

Both occured while I was logged in as TFSSETUP (the setup account). I believe my accounts are correctly setup.

 

I tried running cmd as Administrator as a test and recieved

 

C:\Program Files\Microsoft Visual Studio 2005 Team Foundation Server\Tools>Setup
warehouse.exe -o -mturl http://vsts-tfs:8080 -c warehouseschema.xml -s vsts-tfs
-d TfsWarehouse -a teamdev\TFSService -ra teamdev\TFSReports
An unexpected error occurred: TF30063: You are not authorized to access VSTS-TFS.

 

Please advise

 

Thanks,

 

Kalim