kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Roberto Tardío <roberto.tar...@stratebi.com>
Subject Re: Issues with order for joins on Kylin 2.1
Date Mon, 23 Oct 2017 11:09:21 GMT
Many thanks ShaoFeng Shi,

I understand this could be necesary to support snowflake schema. 
However, some BI tools could generate queries putting first a dimension 
table and after the fact table, with correct ANSI-92 SQL sintax but 
incorrect for Kylin 2.1. Maybe could be useful and option to select 
between Star Schema and Snowflake schema when you define data model on 
Kylin. What do you think about?

Best Regards,


El 23/10/2017 a las 10:10, ShaoFeng Shi escribió:
> Should be related to the snowflake support; Now all joined query 
> should start from the fact table. Add the second join doesn't work I 
> believe.
>
> 2017-10-22 0:36 GMT+08:00 Roberto Tardío <roberto.tardio@stratebi.com 
> <mailto:roberto.tardio@stratebi.com>>:
>
>     Hi,
>
>     I have replaced (not updated) Kylin 1.6 for Kylin 2.1. I created a
>     cube (and also underlying model) with the same sources and
>     metadata that I have used for the same I previously implementend
>     on Kylin 1.6. The cube construcction was Ok. However, some strange
>     occurs with join queries. The following query goes
>
>     /F_RENDIMIENTO is the fact table and //D_CURSO_ACADEMICO_VK is a
>     dimension table:
>     /
>
>         /select D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO, sum(CREDITOS)/
>         /from F_RENDIMIENTO JOIN D_CURSO_ACADEMICO_VK ON
>         F_RENDIMIENTO.ID_CURSO_ACADEMICO =
>         D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO/
>         /group by D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO/
>
>     But susprisingly if I change the INNER JOIN order the following
>     query does not go
>
>         /select D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO, sum(CREDITOS)//
>         //from D_CURSO_ACADEMICO_VK JOIN F_RENDIMIENTO ON
>         F_RENDIMIENTO.ID_CURSO_ACADEMICO =
>         D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO//
>         //group by D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO/
>
>
>     /Error while executing SQL "select
>     D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO, sum(CREDITOS) from
>     D_CURSO_ACADEMICO_VK JOIN F_RENDIMIENTO ON
>     F_RENDIMIENTO.ID_CURSO_ACADEMICO =
>     D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO group by
>     D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO LIMIT 50000": No
>     realization found for
>     rel#7393:OLAPTableScan.OLAP.[](table=[DM_ACAD_KYLIN_ORC,
>     D_CURSO_ACADEMICO_VK],fields=[0, 1]), JoinDesc [type=INNER,
>     primary_key=[ID_CURSO_ACADEMICO], foreign_key=[ID_CURSO_ACADEMICO]]/
>
>     This does not happend with the same cube implemented using Kylin 1.6.
>
>     Why does this happen?
>
>     Maybe is related to the new snowflake schema support. I used I a
>     star schema and I defined the INNER JOIN as I show in the next picture
>
>     Maybe I have to add a second explicit JOIN between
>     D_CURSO_ACADEMICO --> F_RENDIMIENTO, i.e, the inverted join.
>
>     Regards,
>
>     Roberto
>
>     -- 
>
>     *Roberto Tardío Olmos*
>
>     /Senior Big Data & Business Intelligence Consultant/
>     Avenida de Brasil, 17
>     <https://maps.google.com/?q=Avenida+de+Brasil,+17&entry=gmail&source=g>,
>     Planta 16.28020 Madrid
>     Fijo: 91.788.34.10
>
>
>
>
> -- 
> Best regards,
>
> Shaofeng Shi 史少锋
>

-- 

*Roberto Tardío Olmos*

/Senior Big Data & Business Intelligence Consultant/
Avenida de Brasil, 17, Planta 16.28020 Madrid
Fijo: 91.788.34.10

Mime
View raw message