Universe Design - Best way to handle aggregated dimension

[Former Member]

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.

[amitrathi239]

can you attach the sample data set in each table.it will be helpful to understand the issue.

[Joe_Peters]

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.

[Former Member]

Hello,

Thank you for your answer.

This works but only if the query contains the ID.

As we have million records, i can't use the ID in the query.

[Joe_Peters]

No, that's not true. The ID table I proposed is only there to provide a common dimension. You don't actually need to include the ID object in queries.

[Former Member]

Maybe i've missed something but if i don't include the ID, BO create two queries :

1- Email Lvl1

2-Price

But Price is the total Price for all Email and not Price BY Email.

[Joe_Peters]

Ok, so the ID represents Email Lvl 1? In that case, create the derived table as I suggested, but as "select distinct id,email lvl1 from a". You can then join this to B on ID as a 1-n join.

If you don't need the lvl2 detail, then you won't need to reference table A at all in the universe.

If you do need lvl2 detail, then join the derived table to A on ID, create one context for the derived table to A, and another for the derived table to B.

[Former Member]

No the ID represent the customer.

[Joe_Peters]

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.