COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

Hello,

We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions.

while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine.

Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 (http://support.microsoft.com/kb/918251). We have installed this hotfix on top of SP1 but without results.

Is somebody familiar with this problem or knows how to solve this !!!!

Thanks in advance.

Dirk

When you say "totals are incorrect", can you please provide the expressions of the calculated members, the expected values and the actual values. I.e. if you expected to see NULLs, but get values - it could be because of IgnoreUnrelatedDimensions setting etc.|||

Hi Mosha,

I expect values but the results are not correct ( so as an example i expect 82.0415,00 and i get 80.0345,53)

regards,

Dirk

|||

> please provide the expressions of the calculated members

The investigation will likely to go into the values on which these calculations are based. Another reason for disrepancies could be RI problems in the data.

|||

Hi Mosha,

I will provide details for you asap. At the moment i can't log in into the remote customer sql server (machine is down). I will provide you with full details tomorrow (same time).

Regards,

Dirk

|||

Hi Mosha,

Here are the details you asked for:

Virtual Cube: Cube_Omspanning

Calculated Measures:

[KostenquotientOmzet]

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

Cube_Omzet_Kostenquotient

Calculated measures:

[Gemiddelde Kostenquotient] (Not Used in virtual cube calculated fields)

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

Measures:

Kostenquotient_OMZ

Teller_OMZ

Cube_Omzet

Calculated measures:

None

Measures:

EenhedenOmzet

BedragOmzet

BedragBasis

A couple of general questions:

1.) Are Grand Totals calculated during load of the cube ?

2.) Could this be a Rounding (to integer) problem?

Regards,

Dirk

|||Based on these formulas - I can tell, that the differences must exist for the real measures. Please start comparing the real measures in AS2000 vs. AS2005, and start slicing by different dimensions. As soon as you identify dimension which causes the difference, I recommend checking whether the attribute relationships are defined correctly in that dimension - this seems to be the most probable cause of the difference. The difference is way too big to be explained by rounding here.