Skip to Content
0
Former Member
Nov 27, 2017 at 10:57 AM

Universe Design - Best way to handle aggregated dimension

95 Views Last edit Nov 27, 2017 at 01:10 PM 3 rev

Hello,

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

1|A|a1

2|B|b1

2|B|b2

Table B :

ID|Product|Price

2|x|1

2|y|2

Query : Sales by Email lvl 1

Result :

Email lvl 1|Price

B|3

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 :

|ID|Email lvl1

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 ?

Thank you.

Attachments

capture.png (4.2 kB)