Sort order of datasets on Saiku GUI

[Alexandre Savaris]

Hello all.

I am using Saiku Community Edition 3.90, connected to a PostgreSQL 12 database through a Mondrian 4 datasource. The content of the database is in Brazilian Portuguese, which implies the extensive use of diacritical marks (e.g. Á, é, ç, ...).

To correct the sort order of textual fields, I am using the collation "pt-BR-x-icu" on the database (as can be seen on the snippet tha follows):

CREATE TABLE public.table_1

(

    field_1 integer NOT NULL,

    field_2 character varying COLLATE pg_catalog."pt-BR-x-icu" NOT NULL,

    ...

);

When accessing the database using pgAdmin4, the ORDER BY clause works as expected: words initiated with 'A', 'Á', 'a' and 'á' appear togheter in the resultset. A simple Java program using JDBC obtains the same result.

However, the results obtained in the Saiku GUI are quite different (see Figure 1). Words initiated with diacritical marks are placed at the end of the resultset. The database logs show that the queries are correct, using the ORDER BY or GROUP BY clauses and generating resultsets sorted according to the definitions of the "pt-BR-x-icu" collation. It seems like the Saiku GUI is reordering the resultset according to a default collation before exhibition.

Is there a way to assume the database order on displaying the results, or to configure the Saiku GUI to sort resultsets according to specific collations?

Thanks in advance!

Regards,

Alexandre Savaris

[Isaias Sanchez]

There is a file when you can configure these locale for saiku:
saiku-server/tomcat/webapps/saiku/WEB-INF/classes/saiku.properties

By default it takes JVM or UTF-8.

Check java compatibility as well.

You must restart Saiku after that, also is convenient to dowload latest PostgreSQL JDBC driver and replace the one in saiku, it offers better compatibility with pg12:

https://jdbc.postgresql.org/download.html

Cheers,

Isaias S.

Sorry, I reply only to the user, now to the list.

--
To unsubscribe from this group and stop receiving emails from it, send an email to dev+uns...@saiku.meteorite.bi.

[Alexandre Savaris]

Dear Isaias,

thank you for the response. After your indications I have reconfigured my environment as follows:

SO (Ubuntu Linux - Docker)

LANG=pt_BR.utf8

LANGUAGE=pt_BR

LC_CTYPE="pt_BR.utf8"

LC_NUMERIC="pt_BR.utf8"

LC_TIME="pt_BR.utf8"

LC_COLLATE="pt_BR.utf8"

LC_MONETARY="pt_BR.utf8"

LC_MESSAGES="pt_BR.utf8"

LC_PAPER="pt_BR.utf8"

LC_NAME="pt_BR.utf8"

LC_ADDRESS="pt_BR.utf8"

LC_TELEPHONE="pt_BR.utf8"

LC_MEASUREMENT="pt_BR.utf8"

LC_IDENTIFICATION="pt_BR.utf8"

LC_ALL=pt_BR.utf8

Java (OpenJDK 8)

Default locale: pt_BR

Apache Tomcat

-Duser.timezone=America/Sao_Paulo -Duser.language=pt -Duser.region=BR

Saiku Server

saiku.format.default.locale=pt_BR

I am using the latest version of the PostgreSQL JDBC driver compatible with version 12;

However, the sort order remains the same. Is there another configuration available for locale/collation?

Regards,

Alexandre Savaris

[Alexandre Savaris]

UPDATE: After observing the Saiku logs, it seems that the sorting problem occurs on Mondrian. Follows a snippet of such logs where the incorrect order can be seen (there are words initiated with the diacritical mark 'Á' that should be listed togheter with words initiated with 'A'; however, these words as listed after 'Z').

2020-08-07 15:03:37,236 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Exames].[Tipos de Exames].[Tipos de Exames]]: executing sql [select "grp_exame"."tipo_exame" as "c0" from "grp_exame" as "grp_exame" group by "grp_exame"."tipo_exame" order by CASE WHEN "grp_exame"."tipo_exame" IS NULL THEN 1 ELSE 0 END, "grp_exame"."tipo_exame" ASC], exec 200 ms

2020-08-07 15:03:37,238 DEBUG [mondrian.sql] 98: , exec+fetch 202 ms, 229 rows

2020-08-07 15:03:37,238 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Exames].[Tipos de Exames].[Tipos de Exames]]: done executing sql [select "grp_exame"."tipo_exame" as "c0" from "grp_exame" as "grp_exame" group by "grp_exame"."tipo_exame" order by CASE WHEN "grp_exame"."tipo_exame" IS NULL THEN 1 ELSE 0 END, "grp_exame"."tipo_exame" ASC], exec+fetch 202 ms, 229 rows, ex=99, close=99, open=[]

2020-08-07 15:03:37,238 DEBUG [mondrian.server.monitor] SqlStatementInfo{sqlStatementId=98, sql=select "grp_exame"."tipo_exame" as "c0" from "grp_exame" as "grp_exame" group by "grp_exame"."tipo_exame" order by CASE WHEN "grp_exame"."tipo_exame" IS NULL THEN 1 ELSE 0 END, "grp_exame"."tipo_exame" ASC}

2020-08-07 15:03:37,239 DEBUG [mondrian.server.monitor] SqlStatementEndEvent(98)

2020-08-07 15:03:37,240 DEBUG [mondrian.olap.ResultBase] mondrian.rolap.RolapNamedSetEvaluator@696387b3: Named set ~ROWS evaluated to:

[Exames].[Tipos de Exames].[1,4 BUTANODIOL]

[Exames].[Tipos de Exames].[A1C (Hemoglobina Glicosilada)]

[Exames].[Tipos de Exames].[ALA-U (Ácido Delta Aminolevulínico)]

[Exames].[Tipos de Exames].[ALA-U (Ácido Delta Aminolevulínico) urinário]

[Exames].[Tipos de Exames].[Albumina]

[Exames].[Tipos de Exames].[Alfafetoproteína]

[Exames].[Tipos de Exames].[Alprazolam]

[Exames].[Tipos de Exames].[Amilase]

[Exames].[Tipos de Exames].[Amitriptilina]

[Exames].[Tipos de Exames].[Anfetaminas]

[Exames].[Tipos de Exames].[Antidepressivos Tricíclicos]

[Exames].[Tipos de Exames].[Arsênico]

[Exames].[Tipos de Exames].[Arsênico urinário]

[Exames].[Tipos de Exames].[ATA (Anti peroxidase)]

[Exames].[Tipos de Exames].[ATG (Anti tireoglobulina)]

[Exames].[Tipos de Exames].[Atividade de Colinesterase]

[Exames].[Tipos de Exames].[Atividade de Protrombina (AP)]

[Exames].[Tipos de Exames].[Atividade de Protrombina (AP) %]

[Exames].[Tipos de Exames].[Barbitúricos]

[Exames].[Tipos de Exames].[Bastões]

[Exames].[Tipos de Exames].[Basófilos]

[Exames].[Tipos de Exames].[BE - Excesso de Base]

[Exames].[Tipos de Exames].[Benzodiazepínicos]

[Exames].[Tipos de Exames].[BETA-HCG (Gonadotrofina coriônica humana)]

[Exames].[Tipos de Exames].[Bicarbonato]

[Exames].[Tipos de Exames].[Bilirrubina Direta (BD)]

[Exames].[Tipos de Exames].[Bilirrubina Indireta (BI)]

[Exames].[Tipos de Exames].[Bilirrubina Total (BT)]

[Exames].[Tipos de Exames].[Bomazepam]

[Exames].[Tipos de Exames].[Brodifacum]

[Exames].[Tipos de Exames].[Bromadiolona]

[Exames].[Tipos de Exames].[Cafeína]

[Exames].[Tipos de Exames].[Canabinóides]

[Exames].[Tipos de Exames].[Carbamato]

[Exames].[Tipos de Exames].[Carbamazepina]

[Exames].[Tipos de Exames].[Carbendazim]

[Exames].[Tipos de Exames].[Carboxihemoglobina]

[Exames].[Tipos de Exames].[Catinona]

[Exames].[Tipos de Exames].[CHCM]

[Exames].[Tipos de Exames].[Chumbo]

[Exames].[Tipos de Exames].[Chumbo sanguíneo]

[Exames].[Tipos de Exames].[Chumbo urinário]

...

[Exames].[Tipos de Exames].[Zinco]

[Exames].[Tipos de Exames].[Zinco sérico]

[Exames].[Tipos de Exames].[Zinco urinário]

[Exames].[Tipos de Exames].[Zolpidem]

[Exames].[Tipos de Exames].[Ácido Acetil Salicílico]

[Exames].[Tipos de Exames].[Ácido Lisérgico (LSD)]

[Exames].[Tipos de Exames].[Ácido Láctico]

[Exames].[Tipos de Exames].[Ácido Valpróico]

[Exames].[Tipos de Exames].[Ácido Úrico]

Is it possible to configure Mondrian to use a different locale, perhaps through the datasource configuration?

Regards,

Alexandre Savaris

[carlos santos]

Boa tarde Alexandre 

Tenho um problema similar ao apresentado em que não consigo obter a ordenação por hierarquia de acordo com a ordenação dada pelo postgres.

No meu caso todas Designações da Categoria em que a primeira letra seja um carater especial aparece no fim e a ordenação é sempre feita primeiro pelas maiúsculas e depois pelas minúsculas.

Conseguiste resolver este problema?

As minhas configurações estão todas com o valor "pt_PT-UTF-8".

Abraço 

Carlos Santos