UNION OBIEE 11G- 2 rows

Joe Choueiri-Oracle

Hi

The image below shows how the union is done- the final result is creating 2 rows for that specific order I am filtering on my reports and I need the order channel to be on the same row as the rest- I tried creating additional column by adding result column and use the MAX(saw_2 by saw_y) etc but did not work- any idea what I am doing wrong?

Thnks

Joe

[Deleted User]

As in the other thread you seem to have a data mismatch:

Also:

And "Order Number" is an attribute and must NOT reside in a fact table. That's really bad dimensional modelling and will definitely cause issues!

Joe Choueiri-Oracle

Thank you for pointing it out.

I dont manage the RPD, I only create reports in the user interface. But I told them after finding out that the order number in one of the SA is a text. When you go to the order number column prosperity then data format...it is text but until they change it I went and converted that number order with .00 at the end (you highlighted in red) in that SA and now it is the same as the one above but still showing 2 rows... is it because it has to be changed in RPD?

Joe Choueiri-Oracle

I even changed by criteria, I removed the order number just in case that is causing the issue.

So now I only have 3 columns, Division or region, order channel, SUM(0) on one SA and on the other I have the Division, Null, Amount...so I am trying to union the order channel if this is the right term.

I was hoping to see LAD for the division, then order channel (Inbound, Migrated, etc) and beside each order channel an amount.

Pls see the image- hope you can help me.

Thanks

Joe

[Deleted User]

I've quickly created an example of such a UNION in one of my VMs with some dummy values - disregarding the ones that match (FY, Division and the emails) and the assumption that "Order Channel" is NULL and that the Amount is 0 (zero) for the second row.

Two lines. "Outbound" has 0 dollars with makes it valid and NULL has 5000 dollars which also makes it valid. Here's the result with NULL as an Amount for the second row:

The problem here is that you are trying to do something that goes against the whole concept of an analytical system and points to

a) data quality issues

b) badly designed subject areas

c) both

Basically what you are trying to do is a cross-SA analysis but using UNIONS instead of (proper) cross-SA analyses via conformed dimensions!

[Deleted User]

I was building while you were updating, so again: what you are trying to do is a cross-SA analysis but using UNIONS instead of (proper) cross-SA analyses via conformed dimensions!

You can't ever split a value by something it has no relation to. And a UNION will in no use case whatsoeve do that. That's why OBI uses the BI server and its *models* to do this.

Again: data quality and RPD design need to be reviewed as they are clearly suboptimal

Joe Choueiri-Oracle

Thank you Christian for the helpful information, much appreciated.

Joe