Question on many-to-many relationship in snowflake model

["suheng.cloud" <pe...@hotmail.com>]

Hi,all
I think kylin fit well for the relationship of “many-to-one” scene. But for
many-to-many situation, I didn't find a lot information or technic
directions.
I have read  KYLIN-1875 <https://issues.apache.org/jira/browse/KYLIN-1875> 
,
in "Enforced joint" part liyang said "This limitation is a problem for
many-to-many relationships. User can work around by creating multiple
models, for example let each fact table has its own model."
But I coundn't understand how it works...can you tell some detail?

For example, we have following schema
Dimensions : Province->City  is a hierarchy group
Facts: userAction

Province             Province_City                      Fact_table
     P1                   P1    C1                     100(userId) 
C1,C2,C3(cities)
                           P1    C2
     P2                   P2    C3

For each userAction will impact multiple cities(belong to same province or
not)
Sql will query the "count(action)" of each userId by the hierarchy
dimension. 
I have tried multiply the userActions by cities,but in query if city not
choosed,the result will be dup.

If we keep original fact size, Kylin doesn't allow bridge table for join key
must be unique.
Can we use one cube or model to solve this? If not,how can two model work in
coordination?



--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Question-on-many-to-many-relationship-in-snowflake-model-tp8204.html
Sent from the Apache Kylin mailing list archive at Nabble.com.

Re: Question on many-to-many relationship in snowflake model

[Li Yang <li...@apache.org>]

You could have two models like below:
- Model 1: just the fact table
- Model 2: the fact table JOIN the city table

Kylin will choose the right model based for every query. If the query goes
like "select .... from fact_table group by ....", then Model 1 will be
chosen. If the query goes like "select ... from fact_table join city on ...
group by ...", then Model 2 will be chosen.

Cheers
Yang

On Sat, Jun 10, 2017 at 12:19 PM, suheng.cloud <pe...@hotmail.com>
wrote:

> Hi,all
> I think kylin fit well for the relationship of “many-to-one” scene. But for
> many-to-many situation, I didn't find a lot information or technic
> directions.
> I have read  KYLIN-1875 <https://issues.apache.org/jira/browse/KYLIN-1875>
> ,
> in "Enforced joint" part liyang said "This limitation is a problem for
> many-to-many relationships. User can work around by creating multiple
> models, for example let each fact table has its own model."
> But I coundn't understand how it works...can you tell some detail?
>
> For example, we have following schema
> Dimensions : Province->City  is a hierarchy group
> Facts: userAction
>
> Province             Province_City                      Fact_table
>      P1                   P1    C1                     100(userId)
> C1,C2,C3(cities)
>                            P1    C2
>      P2                   P2    C3
>
> For each userAction will impact multiple cities(belong to same province or
> not)
> Sql will query the "count(action)" of each userId by the hierarchy
> dimension.
> I have tried multiply the userActions by cities,but in query if city not
> choosed,the result will be dup.
>
> If we keep original fact size, Kylin doesn't allow bridge table for join
> key
> must be unique.
> Can we use one cube or model to solve this? If not,how can two model work
> in
> coordination?
>
>
>
> --
> View this message in context: http://apache-kylin.74782.x6.
> nabble.com/Question-on-many-to-many-relationship-in-
> snowflake-model-tp8204.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>