Logical Table Source(LTS) Override – A multiple LTS logical fact with Count and Count Distinct Measures


One of my colleague has encountered a strange problem which I even have not noticed ever because the problem is bit ridiculous for me . The problem is about applying count distinct and count aggregation rule while building a measure . The measure will be mapped to multiple logical fact table source .

The problem in nutshell is, while applying the Count on column NUM_OF_CONTACT it is doing SUM(NUM_OF_CONTACT) instead of COUNT (NUM_OF_CONTACT) whereas using Count Distinct on the same column will be driving the Distinct count on Lowest level of LTS all time(i.e query hitting single/one LTS)  irrespective of the dimension and dimensional content specification .

Initially it is bit strange for me . After investigating across metalink I got couple of such issue encountered by users . Some of them are : Doc ID : 510223.1 SR 38-1054325181, Doc ID 526854.1 SR 38-1505875337 . But it does not give me the proper info about the workaround or solution.

Re-engineering on it reveals that there are some interesting and unknown feature left behind by Oracle/Siebel to overcome such problems where measures need to be totally driven by the content specification of fact table LTS. Its nothing but “Logical Table Source Override” . I proof this I replicate the problem in my env and done a POC and outcome/workaround of it as below :

1 . Create the below tables and insert the handcrafted data .

LTS Override1

 

 

 

 

 

 

LTS Override2

 

 

 

 

 

2. Configure the repository as below :

  LTS Override3

 

 

 

3. The Logical Fact table source content has been mapped as applicable . The 2 LTS content mapping as below . Similarly other LTS has been mapped .

LTS Override4

 

 

 

 

 

4. Now see how the Count distinct has been defined .

  LTS Override5 

 

 

 

 

However the definition prove to be wrong and erroneous while you drag the measure with the dimensional column .Here I have drag LVL2 and as per the content mapping the query should be routed via “DIM_POSITION_FLM” . But strange enough whatever column you choose from dimension table the query always routing via “DIM_POSITION_KAM” .Have a look on below :

LTS Override7

 

 

 

 

 

 

5 . Definitely the above result is wrong . A correct approach is to redefine the measure by LTS overriding as below :

LTS Override8

 

 

 

 

 

 

6. And finally see the correct result and the query log .

LTS Override9

 

 

 

 

 

7. Whereas a totally different kind of behavior has been observed while you are dragging “NUM_OF_CONTACT_Count” with measure . A simple count aggregation has been defined against it . The result would be below :

LTS Override10

 

 

 

 

 

 

So this create another major problem as any dimension you choose always SUM aggregation will be applied whereas as per Repository definition it should be Count .

8. A correct approach/workaround of the problem is to redefine the measure . However for Count aggregation you haven’t any choice of LTS overriding . Here you have to go by Based On Dimensions . Lets see this and the issue has been resolved !

LTS Override11

 

 

 

 

9. So apparently it seems everything is working fine with some tweaking as LTS override and based on Dimensions funda . But wait there are some more strange thing awaiting  and I am really bit astonished how this would happened ! If any body could explain me the reason I will be grateful .

I have deleted the measure “NUM_OF_CONTACT” and “NUM_OF_CONTACT_Sum” from logical fact table as it is of no use for me for this POC . However after removing this I observed only the measure “NUM_OF_CONTACT_Count_Distinct_Override” is functional and rest of them is not behaving what it should be , as explained above .

Dragging “LVL2” and any of the measure (apart from “NUM_OF_CONTACT_Count_Distinct_Override”) leads to inconsistent query firing in DB :

select distinct T41.LVL2 as c1,
     T38.NUM_OF_CONTACT as c2
from
     DIM_POSITION_H T41,
     DIM_POSITION_FLM T38
where  ( T38.POSITION_WID = T41.POSITION_WID )
order by c1

And more astonishing fact is that for all 3 measures the query is same !!! How come this possible ? I dont know if I am missing something .But this is quite strange how removing an independent measure impact the rest of the measures query ! Still can’t find any justification ……

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s