Hi there,
I have a metric that counts orders in the customer fact table. I'd like to modify the count to exclude order id's that are in the suspended user fact table. I haven't been successful playing with count parameters, and I'm not sure about importing a list of 400K orders to be used as a filter. The SQL I'd like to have is basically:
select count distinct(C.order_id)
from customer_fact C
where... (a bunch of filters on attributes for table C and some other tables)...
and not exists (select 1 from customer_suspend S
where C.order_id=S.order_id
and S.startdate<20030901
and S.enddate>20030901)
When I copy my original metric and change the parameters to count on this second table, I get the error that the facts are not at a level that can be supported, which probably is because the second fact table doesn't have the same attributes as the first one. The first fact table has millions of records, so it wouldn't be easy to create a new fact table. We're on 7.2.1./Oracle. What do you suggest as the best approach?
Much appreciated,
26point2
I have a metric that counts orders in the customer fact table. I'd like to modify the count to exclude order id's that are in the suspended user fact table. I haven't been successful playing with count parameters, and I'm not sure about importing a list of 400K orders to be used as a filter. The SQL I'd like to have is basically:
select count distinct(C.order_id)
from customer_fact C
where... (a bunch of filters on attributes for table C and some other tables)...
and not exists (select 1 from customer_suspend S
where C.order_id=S.order_id
and S.startdate<20030901
and S.enddate>20030901)
When I copy my original metric and change the parameters to count on this second table, I get the error that the facts are not at a level that can be supported, which probably is because the second fact table doesn't have the same attributes as the first one. The first fact table has millions of records, so it wouldn't be easy to create a new fact table. We're on 7.2.1./Oracle. What do you suggest as the best approach?
Much appreciated,
26point2