CODEWITHSUNDEEP
ssascubeolap-cubecube-dimension
JotaBe
JotaBe

Reputation: 39014

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

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?

Upvotes: 1

Views: 388

Answers (2)

St C
St C

Reputation: 1

Thank you for answering this question.

I was looking for a solution myself, for Visual Studio Data Tools crash (when editing time dimension). It turned out that the application works correctly after changing the regional settings to "dd.MM.yyyy", alke does not work on "yyyy-MM-dd".

Finally, after several days of searching, I came across the indicated solution.

Upvotes: 0

JotaBe
JotaBe

Reputation: 39014

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.

Upvotes: 1

Related Questions