August 11, 2009 at 10:21 am
I'm having a problem where measures are getting very strange and large (either positive or negative) values associated to them when they are aggregated in my time dimension. It appears that the values at the lowest level of the hierarchy are fine (in my case five minute buckets), however, higher levels in the hierarchy are showing the strange results.
For example, I have a simple measure that is a count of the records, and for one fifteen minute period I have the values 10, 12, and 14 in each of the three buckets in the period. Yet the rollup value for this fifteen minute period is -1,073,741,792.
Also, the level of rollup with the problem is not consistent. The example above had the strange values at the fifteen minute level, but some fifteen minute values are fine and the hour rollup values are strange.
The problem seems to arise when I am working on new measures and calculations, and once the problem is seen I can't resolve it via processing of any kind or rolling back changes. If I restore an earlier version of the solution and make all of the changes again they tend to work, however it is only a matter of time before the problem arises again.
I have verified that there is no issue with the source data, and have performed dbcc checkdb and dbcc checktable to ensure there is no corruption there.
I have other cubes in the solution that use the same time dimension (and other dimensions) that work without issue, however, this cube is giving me a very big head ache. Please let me know if you need any more information on the system. I'm hoping it is a simple issue but I haven't found anything to help me yet.
August 13, 2009 at 1:42 am
Hi,
I have come across similar before and the problem I had was a known bug at one point. which version of SQL Server and service pack are you using?
If it is not the same issue then my guess (and it is a guess) is that it is the way it is doing the arithmetic. Looking at the figures involved your result is not that far away from 2 to the power of 30. Do you have any more figures to see if the behaviour (arithmetic) is the same?
-1,073,741,792
36
1,073,741,824 = 2 to the power of 30
What are the Data types for the measures involved?
Hope this helps you on the right tracks.
Ells.
:w00t:
August 13, 2009 at 1:45 am
August 13, 2009 at 5:48 am
Thanks very much for your help and suggestions.
Apologies for being light on the detail of the system specs. They are as follows:
SQL Server 2005 x64 (SP3)
Running on Windows Server 2003 R2 Standard x64 Edition (SP2)
The problem has gone away for the time being although through luck more than judgement I think. I had some distinct count measures that were in the same measure group as other measures and I moved these into their own measure groups, full processed the OLAP DB and the figures went back to what they should have been. Like I say, I don't believe this is the solution, and I'd be rather nervous stating that the problem is fixed.
I've noticed that some of the measures are set with a data type of integer where the source data type is bigint, so even though my figures are well under the capacity of integer could this be the source of my problem?
Thanks again for your help.
August 13, 2009 at 6:03 am
I am not sure what is causing the problem but it does appear to be some sort of data overflow. If or when it comes back have a look at all the values and their data types and I think a bit of maths and a bit of googling may help.
Or if you are really lucky someone will appear by magiuc with the answer.
Ells
August 13, 2009 at 8:24 am
I've updated my data types to match the OLAP data type with the source data type and if it does happen again I'll do some more digging and update this post with what I find.
Thanks again (again) 🙂
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply