Skip to Content
avatar image
Former Member

Universe Design - Best way to handle aggregated dimension

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.

capture.png (4.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 27, 2017 at 01:13 PM

    Do you have any table in which ID is unique? If not, you can simulate it with a derived table:

    SELECT DISTINCT id FROM A

    Either way, that becomes a pseudo-fact table. Join it to A and B on ID, then create a context for each. That is, one context for Fact-A, and one for Fact-B. BO will then create two queries and two report blocks for the Email Lvl 1 / Price query.

    Add comment
    10|10000 characters needed characters exceeded

    • It's difficult to make recommendations without seeing the entire model. But if Email Lvl1 is guaranteed to be unique within a customer ID, then my previous recommendation stands: create a derived table that presents the unique Email Lvl1 by ID.