Question on many-to-many relationship in snowflake model

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Question on many-to-many relationship in snowflake model

suheng.cloud
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,
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?

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Yang
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 <[hidden email]>
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.
>
Loading...