After a TFS rebuild I am no longer able to process the OLAP cube.  Initially TFS (and SQL) was built on a server with Australian regional settings.  However the rebuild was done on a server that had default US regional settings.  I have since changed the default collation for the database server back to the Australian collation (to fix errors in comparing and merging files).  The warehouse is still not working though.  It seems like its something to do with date formats, although I am not 100% sure.  Aus date format is the same as British (ie DD/MM/YYYY), eg today is 06/04/2006.  The value of TfsWarehouse.Today.Date was "4/6/2006", we tried changing it to "6/4/2006" but it still returned the same errors.  I also tried rebuilding the olap cube using the procedure described in the earlier warehouse post, still exactly the same error.  Any help is much appreciated.  Here is some of the output, the error information, from doing a manual process of the cube:

Errors and Warnings from Response
 The member '[4/6/2006]' was not found in the cube when the string, [Date].[Date].[Date].[4/6/2006], was parsed.
 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.
 The member '[Week of 4/2/2006]' was not found in the cube when the string, [Date].[Week].[Week].[Week of 4/2/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (70, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[April, 2006]' was not found in the cube when the string, [Date].[Month].[Month].[April, 2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (74, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[4/6/2006]' was not found in the cube when the string, [Date].[Date].[Date].[4/6/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (6, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[Week of 4/2/2006]' was not found in the cube when the string, [Date].[Week].[Week].[Week of 4/2/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (10, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[April, 2006]' was not found in the cube when the string, [Date].[Month].[Month].[April, 2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (14, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[4/6/2006]' was not found in the cube when the string, [Date].[Date].[Date].[4/6/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (5, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[Week of 4/2/2006]' was not found in the cube when the string, [Date].[Week].[Week].[Week of 4/2/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (8, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[April, 2006]' was not found in the cube when the string, [Date].[Month].[Month].[April, 2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (11, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[4/6/2006]' was not found in the cube when the string, [Date].[Date].[Date].[4/6/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (5, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[Week of 4/2/2006]' was not found in the cube when the string, [Date].[Week].[Week].[Week of 4/2/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (8, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[April, 2006]' was not found in the cube when the string, [Date].[Month].[Month].[April, 2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (11, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[4/6/2006]' was not found in the cube when the string, [Date].[Date].[Date].[4/6/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (5, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[Week of 4/2/2006]' was not found in the cube when the string, [Date].[Week].[Week].[Week of 4/2/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (8, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[April, 2006]' was not found in the cube when the string, [Date].[Month].[Month].[April, 2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (11, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[4/6/2006]' was not found in the cube when the string, [Date].[Date].[Date].[4/6/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (6, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[Week of 4/2/2006]' was not found in the cube when the string, [Date].[Week].[Week].[Week of 4/2/2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (10, 1) The END SCOPE statement does not match the opening SCOPE statement.
 The member '[April, 2006]' was not found in the cube when the string, [Date].[Month].[Month].[April, 2006], was parsed.
 The END SCOPE statement does not match the opening SCOPE statement.
 MdxScript(Team System) (14, 1) The END SCOPE statement does not match the opening SCOPE statement.

Which regional setting does your AT have? The warehouse adapters are run on the AT, so the CSS adapter will use the date format from the AT to populate the date dimension.

If you have mixed date formats in your Date dimension and Today dimension, you can run into this error. Here's how you can force the warehouse to repopulate those date dimensions:

1. Set [CSS Date Dimension Start Dense Date] and [CSS Date Dimension End Dense Date] in [TfsWarehouse].[dbo].[_PropertyBag]. Make sure your date range include all the dates in your Date dimension.

2. Remove the row from the [TfsWarehouse].[dbo].[Today]

3. Go to the warehouse webservice and run the adapters

Hi Jimmy,

Thanks for the reply.  I have been on annual leave hence the delay.

Regarding our AT regional settings - we have a single server deployment so AT and DT regional settings are the same, ie en-AU.

I have attempted the 3 steps above but with no luck (although the date its looking for has changed to todays date).  While doing it though I noticed that the values for [CSS Date Dimension End Dense Date] and [CSS Date Dimension Start Dense Date] where empty.  What values / format should go in here?

hi scotty,

Just in case, reset IIS to be absolutely sure that IIS knows you're using en-AU now. It should have already recycled itself in this time, though.

the format to use for the css date dimension fields you mention above is in this format: yyyy-mm-ddThh:mm:ss, for example:

please let us know how that works for you.

mauli

Hi Scotty,

Were you able to resolve these issues? Please let me know if we can help further.

Thanks!

Mauli

Hi Mauli,

No, unfortunately that does not solve the problem.

Scotty.

Hi Scotty,

Let me ask you a couple of questions to see if I can help you find and solve the problem:

1) Are you using RTM version?

2) When you say that TFS was rebuilt, what do you mean exactly? I want to understand what steps did you follow?

3) In the Application Tier machine, is the OS settings still US regional settings? You say that you changed the default collation of the database back to be Australian but I want o know if the OS settings are still US regional. More specific, I want to know if the regional settings for the user running the TFS AppPool in IIS are US or Australian.

4) Also, when you say you tried to rebuild the cube, did you run setupwarehouse with the "-o" option or did you run it with the "-rebuild" (first option will rebuild the cube only and second option will rebuild the warehouse relational and the cube).

Thanks.

Federico

Hi Frederico,

Thanks for getting back to me. This time the delay is due to wisdom teeth then a week later a broken jaw! bad timing...

1) Are you using RTM version?

No. we are still on RC since I wasn't sure if I should resolve the issue before upgrading.

2) When you say that TFS was rebuilt, what do you mean exactly? I want to understand what steps did you follow?

The server from OS up was rebuilt. Ie reformat, install windows 2003, sql 2005, tfs, according to the RC install guide. I then did a restore on the relevant databases, keys (except the reporting one which i could not remember) etc. I had to do this as for some reason one monday I came to work and the only response we could get the sql server was something like - "no process at the end of the pipe". we tried for 2 days to fix it but ended up reinstalling sql, this didn't fix it either. i had the whole team working without source control so decided to just do the full reinstall so at least they could get back to checking in code productively.

3) In the Application Tier machine, is the OS settings still US regional settings? You say that you changed the default collation of the database back to be Australian but I want o know if the OS settings are still US regional. More specific, I want to know if the regional settings for the user running the TFS AppPool in IIS are US or Australian.

The regional setting for the user running the appPool for TFS AppPool is US.

S-1-5-20 - the SID for the Network System, which is running the appPool for Reporting Services is in fact US regional settings as well.

I have determined both of these regional settings by doing some digging around to find an SID converter tool, reading then reading the registry on the AT, HKU\SID\Control Panel\International - in there the date format is mm/dd/yy and also the sCountry is "United States". Is there any easy way of changing this to Aussie? Do I have to copy the HKU\SID\Control Panel\International hive from a user known to be Aussie?

4) Also, when you say you tried to rebuild the cube, did you run setupwarehouse with the "-o" option or did you run it with the "-rebuild" (first option will rebuild the cube only and second option will rebuild the warehouse relational and the cube).

I ended up trying both, but am quite happy to try again if you like.

Thanks heaps,

Scotty

Hi Scotty,

I'm going to try reproducing your scenario on RTM bits here, and I will let you know what I find.

Thanks!
Mauli

Hi Scotty,

I've been able to repro the problem here as well. I will have a developer investigate, and will keep you posted.

Mauli

Hi Scotty,

I've figured out a workaround for this problem.

Make sure your service account on the AT has its regional settings to en-AU. You can check this:

1. Log onto the AT with service account credentials
2. Control Panel->Regional and Language Options
3. Regional Options tab.
4. Choose English(Australian) from dropdown.
5. Click OK.

Reset IIS to make sure that IIS has picked up this change.

Run the following sql script on TFSWarehouse.  This will clear out the date strings.

DELETE FROM [TfsWarehouse].[dbo].[Today]

UPDATE [TfsWarehouse].[dbo].[Date]

SET [Year] = NULL

,[YearString] = NULL

,[Month] = NULL

,[MonthString] = NULL

,[Month of Year] = NULL

,[Week] = NULL

,[WeekString] = NULL

,[Week of Year] = NULL

,[Date] = NULL

,[Day of Year] = NULL

,[Day of Month] = NULL

,[Day of Week] = NULL

Then run the adapters. (On AT, http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=Run and click Invoke.)

All the dates should be fixed to en-AU, and the cube should process.

Let me know how that works.

Mauli

Yay, the cube processed successfully!!

Thanks for all the help, I really appreciate it.

Mauli & Scotty

Many thanks for this thread, I have been experiencing the same issues and the thread has solved my problem.

Thanks again