We have a time dimension which is used by around 90 columns out of 20 tables. We have a fixed time table which started in 1800-01-01 and ends in 2199-12-31. This gives around 150000 members in that dimension. That does hurt the performances and the users
complain like why does the time start in 1800 ?. So we created a view a which says give the last 5 years and coming 2 years. Users are happy and the performance is fine.
Until one day we added another system. That system has some "strange" dates in it. They are dates like 1900-01-01 ,1901-01-01,1966-02-23, 1995-04-31 but also 2017-01-01, 2019-01-01 or 2022-05-01 or even 2098-03-04.
The guy who build the export says : I am not validating but only exporting what the user has entered. The guy who build the import says : I am only importing data and I validate only the type (dates like 2020-03-35 are rejected)
So what should I do, make time big again and have angry users and bad performance ?
I can make filters so that they won't show up but I don't like losing data and the check reports will fail.
I can replace them with another date but I don't like that since I am manipulating data.
I can convert them to unknown but I don't like that too.
Any other suggestions ? And is a time dimension with 400 x 365 members big ?
View Complete Post