Group by on union report

User_R93U9

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.

Martin van Donselaar

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

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

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

+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

"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]

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.