"string was not recognized as a valid datetime" when building schema with wizard

Question

I've used the Cube Design wizard to design an SSAS cube, using top down approach, i.e. creating the cube before the relational database exists.

After finishing the cube design, which includes a time dimension, I've tried to run the "Database" > "Generate Relational Schema..." menu option. I've chosen to populate the Date dimension, and I get this error:

string was not recognized as a valid datetime

Which can be the reason and how can I solve it?

Answer 1

I don't know if it's because I'm using a Windows which is localized to a different language, but the problem was in the XML definition of the dimension.

Once you know it, it's easy to solve the problem: go to the solution explorer, right click the time dimension, and choose "View Code". This opens the XML definition of the dimension. Look for the <CalendarStartDate> and <CalendarEndDate> nodes in the XML file. In my case I found these dates:

<CalendarStartDate>1/1/2015 12:00:00 AM</CalendarStartDate>
<CalendarEndDate>12/31/2008 12:00:00 AM</CalendarEndDate>

For some reason the schema generation tool isn't able to parse the dates in that format. So you have to change them to something which works. If you see the other dates in the dimension file, you'll see that they're in ISO format. So change these ones to that format, like this:

<CalendarStartDate>2005-01-01T00:00:00Z</CalendarStartDate>
<CalendarEndDate>2008-12-31T00:00:00Z</CalendarEndDate>

Run the schema generation tool again, et voilà! it works fine, the schema is correctly generated and the time dimension is populated with the expected dates.