Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Group by on union report

Received Response
21
Views
6
Comments
User_R93U9
User_R93U9 Rank 3 - Community Apprentice
edited Aug 13, 2024 5:52PM in Oracle Analytics Cloud and Server

How can we run group by in union all report in OBIEE.

My Report generate following query

select A1, B1, SUM(M1)

from Table1

group by A1, B1

UNION ALL

Select A2, B2, SUM(M2)

from Table2

group by A2, B2

i want result set to be

Select A, B, SUM(M)

FROM (

select A1, B1, SUM(M1)

from Table1

group by A1, B1

UNION ALL

Select A2, B2, SUM(M2)

from Table2

group by A2, B2)

Group by A, B

Thanks.

Answers

  • Martin van Donselaar
    Martin van Donselaar Rank 6 - Analytics Lead

    You probably get the results you want using a pivot table presentation.

    BUT: Why is this data divided over more then one table? As Dimensions and Measure seem to be the same (from functional POV)

    If you model this correctly you wouldn't have to do a union (which limits flexibly of your analysis) in the first place.

    Please provide some more details so we can discuss model improvement.

  • User_R93U9
    User_R93U9 Rank 3 - Community Apprentice

    Pivot is not option for me as the data is huge. and export in excel doesn't work. I want it in table view only.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    You could resort to using the physical SQL as the basis to create the basis of an opaque view in the physical layer, this obviously takes more modelling in the rpd and is not as performant, but it will get you an answer.

    It might also be possible to model it in the Business Model layer using fragmentation content, depending on your exact circumstances.

    There may be other options, but as @Martin van Donselaar alludes to we really need more detail to provide a more definitive answer.

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    +1 to @Robert Angel a opaque view would be great and performance issue could be manage by a materialized view or view but it looks like don’t know the detalil about your requirement

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    "huge data" - OBIEE is not a data pump ... what's the end game?  what are the individual pieces that drive an action?  Any data that doesn't drive the decision on how to act is noise ...

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    This is so typically something that has NOTHING to do with OBI and doing it in the tool is just forcing "data dump requirments" down the throat of an analytical tool.