0

I created a cube in Apache Kylin (1.5.3) with 2 tables who look like this:

Facttable   || Dimensiontable
id_a | id_b ||  id  | pos
------------------------------
01   | 011  || 011  | 1
01   | 011  || 012  | 1
01   | 011  || 013  | 0
01   | 012  || 021  | 1
01   | 013  || 022  | 0
02   | 021  || 023  | 0
02   | 022  || 031  | 1
02   | 023  || 032  | 0
03   | 031  || 033  | 0
03   | 032  || 034  | 1
03   | 033  || 035  | 1
03   | 034  ||
03   | 034  ||
03   | 034  ||
03   | 035  ||
03   | 035  ||

The tables are joint on facttable.id_b = dimensiontable.id. In 'cube designer - advanced settings' I created one aggregation group with id_a, id_b and pos in 'includes'.

Now I want to know every id_a where 'pos = 1' is more than once related to.

So in the case with the tables above:

id_a | count
------------ 
01   | 2
03   | 3

In the 'insight' tab I tried the query

select ft.id_a, count(ft.id_a)
from(
    select id_a, id_b
    from facttable
    group by id_b, id_a
) as ft inner join (
    select id
    from dimensiontable
    where pos = 1
) as dt on (ft.id_a = dt.id)
group by ft.id_a
having (count(ft.id_a) > 1);

But it returns

Error while executing SQL "[query]": null

Anyone knows what the problem is? Do I need to change some settings in 'model creation' or 'cube creation'?

Any help is appreciated!

Søren
  • 31
  • 3

1 Answers1

1

Your requirement sounds like below to me.

select
    id_a, count(distinct id_b)
from
    facttable
    inner join dimensiontable
    on facttable.id_b = dimensiontable.id
where
    pos = 1
group by
    id_a
having
    count(distinct id_b) > 1
Li Yang
  • 284
  • 1
  • 6