Oracle Analytics and AI

User_R93U9

Hi,

I have three logical table source with following columns.

LTS1: A1, B1, C1, D1, M1 (this has history data)

LTS2: A1, B1, C1, M1 (this has history data)

LTS3: A1, B1, M1 (this has history + incremental data)

I want my query to pull complete data from LTS1 (or LTS2, depending upon the column selection in query) + only incremental data from LTS3. So, it should be like below.

Query 1:

Select A1, B1, C1, D1, sum(M1) from LTS1

group by A1, B1, C1, D1

UNION ALL

Select A1, B1, NULL, NULL, sum(M1) from LTS3

group by A1, B1;

Query 2:

Select A1, B1, C1, sum(M1) from LTS2

group by A1, B1, C1

UNION ALL

Select A1, B1, NULL, sum(M1) from LTS3

group by A1, B1

I am able to generate union all for LTS1 & LTS3 using fragmentation but OBI server is not able to to choose between LTS1 & LTS2 as per the column selection. It is always selecting first LTS which is LTS1 in my case. So, only fist query is generated even though i select A1, B1 & M1 column only. Is there anyway to achieve the desired result. 

Gianni Ceresa

You can set the priority group for LTS1 and LTS2 to make LTS2 a higher priority than LTS1.

Priority group numbers rank your logical table sources in numeric order, with 0 being the highest-priority source.

In your case you seem to want to use LTS2 when only A1, B1, C1, M1 are selected, and use LTS1 only when D1 is part of the query.

So change the priority group number and you can also rename them to have the order LTS2, LTS1, LTS3.

OBIEE will know that if you request D1 it can't use LTS2, so it will jump directly to LTS1.