Aggregate distinct OBIEE 11g

Luiz Araujo

HI guys.

I have these modeling scenario:

Table FB_Posts	Table FB_Comments
ID_POST	ID_POST
DS_POST	ID_COMMENT
QT_LIKES_POST	DS_COMMENT
ID_BRAND	QT_LIKES_COMMENT
	ID_BRAND

Where POST to COMMENTS has a 1:N relationship. So let's imagine the scenario below:

My FB_POSTS has the measure QT_LIKES already aggregated at the source. I have N comments for each post at the FB_COMMENTS. In a determinated moment I have to aggregate all measures to know how much comments, how much likes all comments had and how much likes all posts had given a specific brand.

At the detail level I have these results:

ID_POST	ID_COMMENT	ID_BRAND	QT_LIKES	QT_LIKES_COMMENT
1	1	1	15	40
1	2	1	15	40
1	3	1	15	20
2	1	1	230	450

If I wish to know how much POSTS, QT_LIKES and QT_LIKES_COMMENT a BRAND has, how can I aggregate the at the RPD?

I can use a COUNT DISTINCT at the ID_POST to know how much posts a Brand has, ok. I can use SUM(QT_LIKES_COMMENT) and it should return 550.

But the join between comments and posts generate a cartesian of the measures of the posts. The AGGREGATION of QT_LIKES should return 245 at the total level and I didn't know how to do this.

Can you help me?

Regards.

Luiz Araujo

nm.Mani

Hi Luiz,

you can use aggregateSUM (expr1 by expr2 ) function in Answers, in your case it looks like below

SUM(QT_LIKES BY  ID_POSTS)

Thanks

MM