error with shared time dimension

  • Hi. I have this error in shared time dimension with Analysis Services 2000.

    I created a first time dimension from a view like this:

    select

     convert(datetime,(a.Anno + m.Mese + g.Giorno)) as Data

    from TimeAnno   as a

     cross join

     TimeMese   as m

     cross join

     TimeGiorno as g

    where isdate(a.Anno + m.Mese + g.Giorno) = 1

    where TimeAnno contains the years (2004, 2005, ...), TimeMese the months and TimeGiorno the days.

    Then I created a second time dimension from a table which contains the same data returned by the view.

    If I use the first diemnsion in a cube, I have an error without description; if I use the second one in the same cube I have no error.

    Do someone know why ?

    Thanks. stefano

     

     

  • This was removed by the editor as SPAM

  • Possibly it could be the date format which is causing the issue. When the data is in table it's taking the date format correctly.

  • thanks you for your raplay,

    but I think that date format is correct, because I use the convertion function CONVERT.

    stefano

     

     

  • Two comments, neither of which will necessairly give you an answer as to why this is happening. -> Why use a view (that crossjoins and then uses filters to remove all unnecessary returned records) whena typical 'time' table is likely to be the smallest (data footprint) table your datawarehouse contains?  (e.g 20 yrs @ 365 days/yr = ~7300 rows).  If you're using a view because you don't want to store the data, I was under the impression that AS2K5 let you build a time dimension without a datasource, so you could investigate this?

    If you really are interested in finding out why, set up a trace on both your SQL and AS instances to see what's actualy being called from AS to SQL when trying to process the dimension.  I would hazard a guess that it's related to a 'sql prepare' or something similar.

    Cheers,

     

    Steve.

  • correct me if I am wrong. The error which you are getting is

    'Data source provider error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.;22007'

    It seems there is a problem for the cube process & while processing it's not able to accept as datetime value.

  • 1. AS shows a general error, without any description.

    2. I tried to use a view for a dynamic management of the time dimention (table TimeAnno is automatically feeded by a process). That was a facility for the project, but I can use a 'time' table as well, and now I am using one.

    This starnge behavior lets me find out the why. I will try to trace it.

    Thank you and regards.

    stefano

     

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply