1 Reply Latest reply on Jan 13, 2017 3:10 PM by nm.Mani

    Aggregate distinct OBIEE 11g

    Luiz Araujo

      HI guys.

       

      I have these modeling scenario:

       

      Table FB_PostsTable FB_Comments

      ID_POST

      ID_POST

      DS_POST

      ID_COMMENT

      QT_LIKES_POST

      DS_COMMENT
      ID_BRANDQT_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_POSTID_COMMENT
      ID_BRANDQT_LIKESQT_LIKES_COMMENT
      1111540
      1211540
      1311520
      211230450

       

      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