MDX Query returning different results

Question

SO community,

I'm having a problem with my BI application:

I have a schema built on the Schema Workbench, and it works perfectly and accurately when I use the built-in query tester. However, the same schema returns different cell values for the exact same MDX query when called from my java application (I've already tried both the PreparedOlapStatement.execute() and OlapStatement.executeOlapQuery(String) methods).

The values are wrong as soon as the statement is executed, so I believe it's not a problem with my application. I've tried hardcoding the query to ensure it is the same as the one I used in the workbench, and it still returns the wrong values.

The query is the following, with some names altered because of (a rather stupid) company policy:

WITH
SET [SET1] as Filter(Filter1)
SET [T] as FILTER(FilterTime)
MEMBER Measures.[V] as SUM([T].item(0):Time.currentmember, Measures.[Measure1])
SET [Medida] as ({Measures.[V]})
SET [CJ] as ([ClassFin]*[Medida])

SELECT
[CJ]  on 1,
[T] on 0
FROM [Cube]

Assuming the filters work as intended (because the results I get from the Schema Workbench are correct, double checked with the underlying database), what could be going wrong?

Edit:

Both SQL queries being generated are the same:

SELECT "td"."ano" AS "c0",
           "td"."mes" AS "c1",
           "fechamento_classificacao_financeira"."id_pai" AS "c2",
           sum((CASE
                    WHEN sinal = 0 THEN vr
                    ELSE 0
                END)) AS "m0"
    FROM "util"."tempo_datas" AS "td",

      (SELECT fc.classificacaofinanceira AS classfin,
              fc.bempatrimonial AS bempat,
              tt.id_pessoa AS participante,
              tt.sinal AS sinal,
              fc.centrocusto AS cc,
              fc.data AS DATA,
              fc.projeto AS pj,
              fc.valorrealizado_recebimento vr,
              fc.valorrealizado_pagamento vp
       FROM financas.dadosanaliticosfluxocaixa fc
       LEFT JOIN financas.titulos tt ON tt.id = fc.titulo) AS "fc",
         "util"."fechamento_classificacao_financeira" AS "fechamento_classificacao_financeira"
    WHERE "fc"."data" = "td"."data_completa"
      AND "td"."ano" = '2017'
      AND "fc"."classfin" = "fechamento_classificacao_financeira"."id_filho"
      AND "fechamento_classificacao_financeira"."id_pai" IN ('8875114b-5dd3-4e5c-915f-55f91a825a74',
                                                             'cbae1877-9913-44b2-8533-f33a9a28ea31',
                                                             'e22fb59b-a6be-4d6b-a2de-8bb66b2b1b2a')
    GROUP BY "td"."ano",
             "td"."mes",
             "fechamento_classificacao_financeira"."id_pai"

SO won't let me upload a screenshot of the queries side by side, but I used a diff checker to confirm they're the same.

If you go to tomcat/webapps/pentaho/WEB-INF/classes, you can edit the log4j.xml to display the SQL send by Mondrian to the underlining database (uncomment the block under `Special Log File specifically for Mondrian SQL Statements`, and add the resulting SQL with your question. Do the same for the Schema Workbench. — AlainD, Oct 09 '17 at 16:12
@AlainD thanks for the comment. I did just that and used a diff checker on the output from the workbench and my application. Both SQL queries are identical. I'll update the question with the information and the queries. — J. Sallé, Oct 09 '17 at 17:47

score 1 · Answer 1 · answered Oct 09 '17 at 18:33

I have found out what went wrong. Turns out the underlying DBMS had two databases with the same name, one capitalized and one not. The schema workbench was using the capitalized one, and my application the other one. Now that it's fixed it's giving me the correct values.

MDX Query returning different results

Edit:

1 Answers1