Hi,
I have the following data model made of 2 tables:
1st table:
EVENTS_INTERNAL
event_internal_id PK
date -- dimension
region -- dimension
symbol -- dimension
amount -- measure
2nd table:
EVENTS_EXTERNAL
event_external_id PK
event_internal_id FK -> EVENTS.event_internal_id
router_statistics -- dimension
symbol_statistics -- dimension
amount_1 -- measure
amount_2 -- measure
amount_3 -- measure
Also unique on (event_internal_id, router_statistics, symbol_statistics)
I have a very simple pivot table showing only the data from the 1st table, i.e. SUM(amount) per date, region, symbol
What business needs is to be able to discover under which conditions SUM(amount) from the 1st table is better / worse when filtering amount_i from the 2nd table
Example of filters:
amount_1 > 50 AND amount_2 BETWEEN 10 and 20 for router_statistics R1 and symbol S1
AND
amount_1 < 30 AND amount_3 >= 10 for symbol S2, regardless of the router_statitistics
Does any of you experts know how to achieve this in an optimal way?
Thanks!
