1

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?

JotaBe
  • 38,030
  • 8
  • 98
  • 117

1 Answers1

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.

JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • You mean manually editing ALL dates, just to accommodate SSAS's ISO-date intolerance? Is there another cleaner, more elegant way to fix this? – alejandrob Jun 01 '16 at 19:17
  • SSAS tolerates ISO dates,which it doesn't tolarate is localized formats. I don't know which versions are affected by this problem, and if there is a cleaner solution. But this is the only working solution I've been able to find to sovle this problem, and I hope it can help other people. – JotaBe Jun 02 '16 at 08:42