When is Left Outer Join Used for Metrics in 7.2.2

[sunnyphoenixier]

You may find this question strange but it really puzzles me now. Any help will be appreciated.

Here is the question. In 7.2.2, with Teradata as backend, when we set all metrics to be inner join, sometimes the report uses "left outer joins" in the last SQL pass between intermedia tables, but sometimes it uses just "join". Does anyone know how MSI decides on which one to use?

Thanks,

Phoenixier

 

[nlim]

I think left outer joins are used when you have set the "preserve all lookup elements" vldb setting. This shows attributes with no metric values.

Metrics inner join ensures that only the attributes with all metrics values are shown. But all metrics could be null, in which case the attribute left outer join gives you those attribute values.

 

[sunnyphoenixier]

Thanks! nlim,

We have always selected the option "Perserve common element of lookup and final pass result tables." But the join among temporary tables are sometimes left outer join and sometimes inner join, which exclude some attribute values.

For example, Metric A and B both set to have inner join. A has value C1, C2, C3, C4, C5 for attribute C in its result, and B has value C3, C4, C5, C6 for attribute C in ints result. When left out join is used, the report will have all C1,... C6 in its result. Meanwhile, if inner join is used, the report will have only C3, C4, C5 in its results. I could not find a pattern when MSI decides to use which join type. I thought it might related to report level filter and metric level filter, but I can't verify that.

Any thoughts?

Thanks,

Phoenixier

 

[nlim]

hmm, it's been a while since I had to play with report metric filter interactions...

the last thing I might mention is that the vldb setting can be set at different levels, hopefully you have it at the report level.

The temp table joins I believe are dependent on the metric join type. If all inner, than you'll have only the 3 values you mentioned.

The last pass join type to the attribute lookup is what the "preserve" controls.

 

[sunnyphoenixier]

Thanks, nlim,

As I am still not very clear on why MSI did that, I did confirm one thing from my testing, which is that MSI will join a metric differently than others if this metric has its own qualifications (where clause or sub query) other than report filters. I think that could be the cause of this issue. Anyway, thanks for the help!

Phoenixier