I'm strugglin to choose the best way to handle aggregated data in our universes.
This is what we want to do :
Table A : People who receveid an email
Table B : Sales
Table A :
ID|Email lvl 1|Email lvl2
Table B :
Query : Sales by Email lvl 1
Email lvl 1|Price
My question is : How to build the universe ?
If we just join the tables on the ID and create the dimension, there will be a problem if a user make the following query : |Email lvl 1|Price|
The price would be multiplied by the number of Email lvl2, so price = 6
1. A simple way to get the correct result would be to make a sub query :
[Price] subquery [Email lvl1] = B
But this is not easy for an end user to understand this subquery, and this forces to make as many query as Email lvl1 (in our case dozens).
2.In order to retrieve the correct result, we have created an aggregated table :
and add contexts, aggregation awarness and delagated projection function
But this solution is complex and need as many aggregated tables as level of aggregation.
How do you deals with those cases ?