November 16, 2006 at 7:40 am
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
November 20, 2006 at 8:00 am
This was removed by the editor as SPAM
November 28, 2006 at 7:52 am
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.
November 28, 2006 at 7:59 am
thanks you for your raplay,
but I think that date format is correct, because I use the convertion function CONVERT.
stefano
November 28, 2006 at 12:29 pm
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.
November 29, 2006 at 12:07 pm
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.
November 30, 2006 at 1:39 am
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