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 <[email protected]> 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. >
