OBIEE disable internal stitch

Antonija Mandić

Hi all,

We have OBIEE 12.2.1.3.0 and Oracle 12c as database and we would like to avoid internal stitch if possible.

In Administrator tool on database features tab we have configured following:

FULL_OUTER_JOIN_SUPPORTED - true

WITH_CLAUSE_SUPPORTED - true

PERF_PREFER_MINIMAL_WITH_USAGE - false

PERF_PREFER_INTERNAL_STITCH_JOIN - false

Also, cache has been disabled.

Unfortunatly when we run reports with multiple fact tables and conformed dimensions (on single db) in session browser we see multiple queries being sent to DB.

Is there something we are missing?

Any information is appreciated.

Best regards,

Antonija

[Deleted User]

Antonija,

First things first: Who told you to set these parameters this way? Why these and why these values? Oracle? A blog? Just guessing?

Antonija Mandić

Hi Christian,

I've read it on gerardnico's site, here is the link:

https://gerardnico.com/wiki/dat/obiee/obis/vertical_fragmentation_sql

and those are acctually default valuse if I choose Oracle 12c as database.

Thank you for answering,

Antonija

[Deleted User]

Ok very good source then :-)

The problem is that due to the nature of the product and how it creates its internal data sets for analytical purposes you will probably not have a single switch which turns this "OFF". That holds true especially when doing multi-fact queries.

Many of the database features and more "obscure" settings like FILTER_METRIC_SPLITTING_LEVEL (ref Doc ID 2243480.1) are quite specific and not all are documented. There are some which you add to the NQSConfig for example but aren't there in the vanilla file.

Antonija Mandić

I understand that it may not be possible to turn it off completly but I'm really puzzled by this PERF_PREFER_INTERNAL_STITCH_JOIN parameter.

One would expect that by turning this paramater off at least some queries would be pushed to the database as single query using full outer join.

We have usage tracking enabled and in S_NQ_DB_ACCT table which tracks issued SQL queries, when i search for full outer join there seams to be non.

It's really odd that OBIEE always does internal stitching, although I told him that it is not preferred.

Could you please try to explain this?

Best regards,

Antonija

[Deleted User]

The detail of why which query is structured how depends on the models so I can't answer your question with any certainty without knowing your business model, how the LTS are built, the dimensionalities, etc etc

Antonija Mandić

It would be too much to discuss here, thank you for your effort.

Best regards,

Antonija

[Deleted User]

If it's too much for here then maybe you want to try and get an answer from Oracle Support.

Robert Angel

Or were you serious

[Deleted User]

tl;dr version - not everything has a one-shot, "turn knob A to position 42". And if it apparently is not worth spending time and a minimum amount of brain energy and typing out the issue, then who am I to insist on an intelligent problem-solving approach?

¯\_(ツ)_/¯

Edit: You know what? I'll spell out the rest.

Obviously it will go as all the time:

"Oh it's an OBI problem. It's a crap product. We can do nothing about it." And management will accept that statement. Then probably the product will be changed, people will get promoted and be in positions where they do not actually have to think. People moving up from below or into the team from outside will work the same way with the new product and the cycle will repeat itself.

Robert Angel

Hi Christian,

no offence intended Christian and mockery not aimed at you, but entirely at Oracle support - I agree entirely with everything you say, it is just I despaired many years ago of getting useful, timely advice via the medium of the Oracle SR, particularly when it comes to development activities. I usually find it is a long hard fight with SRs just to get to the person who actually adds value and does not consult the same knowledge base that we all have access to. I have had good experiences on occasion, but those occasions have been so rare as to be highly notable. It should not be this way.

Sincerest apologies if any offence was caused.