Union all of two logical table source

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.

User_R93U9

This is not helping. When D1 is selected it goes to LTS2 and for D1 automatically it selects blank.

I have tried following priority group setting for LTS2, LTS1, LTS3 respectively.

1 , 2 , 0

1 , 1 , 0

0, 1 , 0

am i doing anything incorrect.

Gianni Ceresa

I don't see why OBIEE would use a LTS knowing it doesn't have a column. Did you check all the content levels are set correctly?

[Deleted User]

Content levels and any unnecessary joins (retrieving attributes from a dimension into the fact for calculations for example) which could force the inclusion.