using distinct count on mondrian, but still duplicate occur

Question

i have a question regarding mondrian

My facts table

customer | regionid | customertype | productsold
------------------------------------------------
A        | 1        | T1           | P1
A        | 1        | T1           | P2
A        | 1        | T1           | P3
B        | 2        | T1           | P1
B        | 2        | T1           | P2
C        | 1        | T2           | P1
C        | 1        | T2           | P2
C        | 1        | T2           | P3
C        | 1        | T2           | P4
... so on

Region dimension

regionid | state | district | subdistrict
-----------------------------------------

CustomerType dimension

customertype | name  | more column
----------------------------------

ProductSold dimension

productsold | name | more column
--------------------------------

Each customer have 1 to 1 relation with region and customertype, and 1 to many with productsold

my MDX query like

select 
{[Measures].[CustomerCount]} on columns,
{([Region].[AllRegion], [CustomerTypes].[AllCustomerTypes], 
[ProductSold].[AllProductSold])} on rows
from [Customers]

I configured distinct-count for CustomerCount, but still the result is duplicate.

<Measure name="CustomerCount" column="Customer" aggregator="distinct-count" visible="true">

Any advice appreciate. Thanks.

Answer 1

I would have expected your MDX statement to return just a single cell (containing the distinct count of 3) since your rows axis includes the All member from each dimension.

I would suggest you enable SQL logging in Mondrian and check the Segment.load query since it should be a pretty simple SQL statement to return the distinct count of Customers.

Mondrian 9.3 and onwards uses log4j2 so see this example for configuration: https://github.com/pentaho/mondrian/blob/master/mondrian/src/it/resources/log4j2.xml

Prior versions of Mondrian still use log4j1.2 so see this example for configuration: https://github.com/pentaho/mondrian/blob/9.1.0.0/mondrian/src/it/resources/log4j.xml