Cannot process the TFS 2010 Beta2 OLAP database

Question

Hi,

I'm trying to run the TFS 2010 Beta 2 with reports and the dashboard. But it seems that the OLAP database is not processing properly and I cannot run any reports or analysis webparts on Sharepoint dashboards due to it.
I tried everything - rebuilding the Warehouse database, OLAP database, running WarehouseControlService.asmx, etc. but no luck yet.
I have three servers with Windows 2008 R2 x64 in domain. One is for TFS, second with database (SQL 2008 SP1), third for Sharepoint (MOSS 2007).
In the TFS Administration Console I can see the Warehouse Database and Analysis Services Database are Configured and Online, Report Server is Configured. When I open any dashboard on Sharepoint instead of the proper web part I get error message:

Data Refresh Failed
Unable to retrieve external data for the following connections:
TfsOlapReport
The data sources may be unreachable, may not be responding, or may have denied you access.
Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set.

When I try to Process the OLAP Cube manually I get error message:

OLE DB error : OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'TfsAnalysisDataSource', Name of 'TfsAnalysisDataSource'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Build', Name of 'Build' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Build BK' attribute of the 'Build' dimension from the 'TfsAnalysis' database was being processed.
Internal error: The operation terminated unsuccessfully.

When I run the method GetProcessingStatus from WarehouseControlService.asmx I get error message:

< WarehouseProcessingStatus xmlns:xsd =" http://www.w3.org/2001/XMLSchema " xmlns:xsi =" http://www.w3.org/2001/XMLSchema-instance " xmlns =" http://schemas.microsoft.com/TeamFoundation/2005/06/Services/Controller/03 ">

< RequestTimeUtc > 2009-10-28T09:30:50.3068619Z </ RequestTimeUtc >

< WarehouseProcessingOnlineStatus > Started </ WarehouseProcessingOnlineStatus >

< AnalysisProcessingOnlineStatus > Started </ AnalysisProcessingOnlineStatus >

< JobProcessingStatus > FullAnalysisProcessing </ JobProcessingStatus >

< JobsRunning > 1 </ JobsRunning >

< JobsQueued > 0 </ JobsQueued >

< Instance Name =" TEAM FOUNDATION " JobProcessingStatus =" FullAnalysisProcessing " JobsRunning =" 1 " JobsQueued =" 0 ">

< Jobs >

< Job Name =" Full Analysis Database Sync " JobProcessingStatus =" FullAnalysisProcessing ">

< CurrentRun QueueTimeUtc =" 2009-10-28T09:30:38.55Z " ExecutionStartTimeUtc =" 2009-10-28T09:30:41.523Z " JobState =" Running " />

</ Job >

< Job Name =" Incremental Analysis Database Sync " JobProcessingStatus =" Idle ">

< LastRun QueueTimeUtc =" 2009-10-28T09:13:36.857Z " ExecutionStartTimeUtc =" 2009-10-28T09:13:38.69Z " EndTimeUtc =" 2009-10-28T09:13:38.833Z " Result =" Blocked ">

< ResultMessage > [Incremental Analysis Database Sync]: ---> TF221106: Reporting for Team System cannot execute job Incremental Analysis Database Sync for Team Foundation server TEAM FOUNDATION because the Analysis Services processing is offline. Use the Team Foundation Administration Console to start reporting. </ ResultMessage >

</ LastRun >

< NextRun QueueTimeUtc =" 2009-10-28T09:43:36.857Z " JobState =" QueuedScheduled " />

</ Job >

  </ Jobs >

  </ Instance >

I really appreciate any help how can I solve it and have the TFS running properly.
Thanks a lot in advance for your tips and any help.
Lukasz Chomin

Answer 1

Hello Lukasz,

1.       First let’s try to unblock the OLAP processing

a.        call BringAnalysisProcessingOnline web method

b.      call the ProcessAnalysisDatabase(Full)

c.       Wait for the status go Idle (0 running and queued jobs) and see the LastRun status for the Full Analysis processing job.

2.       I see you are using the non default name for your analysis database (TFsAnalysis), I have some questions about it

a.       Did you use the TFS Management Console to change the reporting settings? 

b.      Did you also changed the warehouse name?

c.       Do you use named (non-default) instance for either Relational warehouse or Analysis Services Database?

3.       Now Please send me the account names that used for db access:

a.       From the TFS Management Console navigate to the Reporting node, and click Edit

                                                               i.      On Analysis Services Tab, what is the Account for accessing data sources?

                                                             ii.       On Reports Tab, what is the Account for accessing data sources?

b.      From Microsoft SQL Server Management Studio (MS SSMS)

                                                               i.      SQL Server/Databases/{Warehouse}/Security /Roles/Database Roles/TfsWarehouseDataReader/ [Right Click] Properties/ Role Members

                                                             ii.      Analysis Server/Databases /{AnalysisDatabase}/Roles/TfsWarehouseDataReader/ [Right Click] Properties /Membership

Thanks,
Elena Kharitidi

Answer 2

Dear Eleana,
Thanks a lot for your tips. Sorry for my late reply but I didn't get any notification from this thread.

I configured TFS once again from scratch. I use default name for analysis database (TfsAnalysis) and Tfs_Warehouse as warehouse name. My reporting settings are set up properly as I have only green statuses. I also use a default database instance for warehouse and Analysis Services.
On Analysis Services Tab and Reports Tab I don't have any account. The username fields are empty.
In Role Members for warehouse and TfsAnalysis I have SharepointAppPool (my Sharepoint data access user), TS01$ (my TFS server) and SP01$ (my Sharepoint server).

In the meantime after some tweaks I was able to process the TFsAnalysis database successfully. In the TfsAnalysisDataSource properties I changed in the Security Setting section the Impersonation Info from Use a specific Windows user name and password to Use the service account. Than I had to give to NT AUTHORITY\NETWORK SERVICE (both SQL Server and Analysis Services work under this account) in SQL Server the db_datareader permission for Tfs_Warehouse database. That fixed the issue and allows me to process the TfsAnalysis database.
Still I have some doubts if this is proper configuration as my Reports work correctly but I still cannot view these reports on the Sharepoint dashboard as I get the error:
Unable to retrieve external data for the following connections:
TfsOlapReport

Do you think it could be related to my empty username fields in Reports configuration on the TFS server?

Thanks,
Lukasz.