Different results from static and dynamic sets

Question

Using icCube 5.1.2, dynamic sets and members are defined the same way static sets are in the advanced/scripts section. The results computed in the MDX IDE are different. How is it possible?

-- Advanced / Scripts
-- CREATE STATIC SET [SET MTD]   as Mtd( ClosingPeriod([temps].[temps].[jour]) )
-- CREATE STATIC SET [SET MTDLY] as Mtd( ParallelPeriod( [temps].[temps].[annee], 1, ClosingPeriod([temps].[temps].[jour]) ) )
-- CREATE CATEGORY MEMBER [Util].[Categories].[default].[MTD]   as [SET MTD]
-- CREATE CATEGORY MEMBER [Util].[Categories].[default].[MTDLY] as [SET MTDLY]

with
-- from static sets
member [Measures].[CM (STATIC SET)]    as Sum( [Util].[Categories].[default].[MTD], [Measures].[ventes] ) * 0.001, format_string="#.#"
member [Measures].[CM LY (STATIC SET)] as Sum( [Util].[Categories].[default].[MTDLY], [Measures].[ventes] ) * 0.001, format_string="#.#"

-- dynamic members
member [Measures].[CM (DYNAMIC MEMBER)]    as Sum( Mtd( ClosingPeriod([temps].[temps].[jour]) ), [Measures].[ventes] ) * 0.001, format_string="#.#"
member [Measures].[CM LY (DYNAMIC MEMBER)] as Sum( Mtd( ParallelPeriod( [temps].[temps].[annee], 1, ClosingPeriod([temps].[temps].[jour]) ) ), [Measures].[ventes] ) * 0.001, format_string="#.#"

-- dynamic sets
set [SET MTD 2]   as Mtd( ClosingPeriod([temps].[temps].[jour]) )
set [SET MTDLY 2] as Mtd( ParallelPeriod( [temps].[temps].[annee], 1, ClosingPeriod([temps].[temps].[jour]) ) )
member [Measures].[CM (DYNAMIC SET)]    as Sum( [SET MTD 2], [Measures].[ventes] ) * 0.001, format_string="#.#"
member [Measures].[CM LY (DYNAMIC SET)] as Sum( [SET MTDLY 2], [Measures].[ventes] ) * 0.001, format_string="#.#"
select
{
[Measures].[CM (DYNAMIC MEMBER)],
[Measures].[CM (DYNAMIC SET)],
[Measures].[CM (STATIC SET)],
[Measures].[CM LY (DYNAMIC MEMBER)],
[Measures].[CM LY (DYNAMIC SET)],
[Measures].[CM LY (STATIC SET)]
}
on columns
from [Ventes]

Results:
-- current month this year
CM (DYNAMIC MEMBER): 42698.7
CM (DYNAMIC SET):    42698.7
CM (STATIC SET):     42697.1

-- current month last year
CM LY (DYNAMIC MEMBER): 44243.4
CM LY (DYNAMIC SET):    44243.4
CM LY (STATIC SET):     44242.1

Answer 1

Strange as the difference between static and dynamic set is the where clause and the slicer that in this case we don't have.

We should be handling this through the support ( feel free to send an email ). On the meantime, let's see 3 possible source of the problem :

1) The issue could be on the set definition, can you check there are the same (should really be the case) ?

member [C1] as count( [SET MTD] - [SET MTD 2] )
member [C2] as count( [SET MTDLY] - [SET MTDLY 2] )

If not zero use the SetToStr function to check what is different ?

2) Another possibility is that [temps] dimension defines a many-to-many relation. When calculating [CM (STATIC SET)] we're using the category that does not count twice the same row.

This

member [Measures].[CM (STATIC SET)]    as Sum( [Util].[Categories].[default].[MTD], [Measures].[ventes] ) * 0.001, format_string="#.#"

is the same as

member [Measures].[CM (STATIC SET)]    as ( [Util].[Categories].[default].[MTD], [Measures].[ventes] ) * 0.001, format_string="#.#"

as [MTD] is a category that can be seen as a member. We could check this by using the underlying set :

member [Measures].[CM (STATIC SET) II]    as Sum( [SET MTD] , [Measures].[ventes] ) * 0.001, format_string="#.#"

and comparing with the version using the category.

If the set and the category give different values it could be due to a many-to-many relation or for a large amount of rows a numerical issues:

3) The difference could be due to the floating point inaccuracy (see more and more). If we're adding millions of doubles it could be the case.

Why it's different ?

When we're using the sum we're adding for each member of the set the evaluated value for this member. SumForEachMember( SumRowMember() )

When using a category we're converting the set into a new pseudo-member. We're going to sum for each matching row the value. SumRowPseudoMember().

Both do not return the same value if we're using floating point aritmetic. The first one should be more accurate.