OBIEE 11.1.1.9 Adding or removing a measure column in a view changes displayed values

Christophe Servolin

Hello all ,

I am facing a strange behaviour of the presentation server 11.1.1.1.9 running on Windows 2012 R2

I use a very simple query that shows sales figures for march 2017 and previous year ( using Ago function on year level defined in the rpd)

SELECT

   0 s_0,

   "Activité "."Articles"."Famille Reporting" s_1,

   "Activité "."Articles"."Gamme - Libellé" s_2,

   "Activité "."Articles"."Univers - Libellé" s_3,

   SORTKEY("Activité SVR"."Articles"."Famille Reporting") s_4,

   SORTKEY("Activité SVR"."Articles"."Univers - Libellé") s_5,

   "Activité "."Facturation"."Montant Facturé N-1" s_6,

   "Activité ."Facturation"."Montant Facturé" s_7

FROM "Activité"

WHERE

(("Clients"."Marché" = 'FRANCE') AND ("Temps"."Année Mois" = '201703'))

ORDER BY 1, 6 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 500001 ROWS ONLY

To run this report , Obiee runs a first physical query for march 2017 , then one for march 2016 and the join the two queries to display data in the report.

I went to session manager , copied both physical queries and ran them directly against the oracle database.

Both queries are correct.

Problem : it seems that in some circonstances ( adding or removing some columns of the "Activité "."Articles" dimension ) the presentation server does not join the to physical queries correctly

Some (!!!) not all data disappear and  corresponding sub totals and totals show the wrong figures.

I copied physical ,business and presentation layers on a 11.1.1.6 rpd and ran it on 11.1.1.6 environnement , data is displayed correctly

Any ideas where to look ?

Shall I open an SR ?

Thanks

Gianni Ceresa

Hi,

Christophe Servolin wrote:Problem : it seems that in some circonstances ( adding or removing some columns of the "Activité "."Articles" dimension ) the presentation server does not join the to physical queries correctly

Are you sure it's the presentation server doing the final join?

In a normal AGO analysis the join between the current and old figures is pushed down to the DB as one of the last steps of the query.

So if you look at the physical query OBIEE generate you must clearly be able to identify the join and why (logical reasons) of some things to disappear...

Christophe Servolin

Hi Gianni ,

I 've always assumed that it is obiee ( presentation or bi server ) that does the final join ( the ability to join different datasources made me think that ) , I may be wrong though.

In session manager I see both pysical ( ie oracle sql) queries for current and old figures but the final join is not written in sql and can't be run against the database as is ( if you have a trick to do so , i'd love to know how)

I came to the conclusion that presentation ( or bi ) server have a problem  because there isn't such behaviour in a 11.1.1.6 environnement against the same database

and also , hiding a column in the report ( ie not changing the criterias and thus the query) may change the values displayed

Thanks

Gianni Ceresa

If your query come from the same source (so using the same connection pool) it's generally pushed down to the DB because it's faster than having the BI server (so not presentation but BI server in case) doing the work (as BI server isn't a DB).

So I find it a bit weird that you see both queries but not the final join in the physical query. Does both queries go on the same connection pool?

Christophe Servolin

Yes both queries are on the same connecion pool

Here is the end of the session log page , I can see both physical queries and what I assume to be the join :

First physical query for Previous Year

WITH

OBICOMMON0 AS (select T44176.DATE_COMPLETE as c3,

  T44176.ANNEE_MOIS as c4,

  T44176.ANNEE as c5,

  ROW_NUMBER() OVER (PARTITION BY T44176.ANNEE ORDER BY T44176.ANNEE DESC) as c6,

  ROW_NUMBER() OVER (PARTITION BY T44176.ANNEE, T44176.ANNEE_MOIS ORDER BY T44176.ANNEE DESC, T44176.ANNEE_MOIS DESC) as c7

from

  BI_ALL_TEMPS T44176 /* 1_DIM_TEMPS */ ),

SAWITH0 AS (select Case when case D1.c6 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( ORDER BY case D1.c6 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c1,

  Case when case D1.c7 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( PARTITION BY D1.c5 ORDER BY case D1.c7 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c2,

  D1.c3 as c3,

  D1.c4 as c4,

  D1.c5 as c5

from

  OBICOMMON0 D1),

SAWITH1 AS (select min(D1.c1) over (partition by D1.c5) as c1,

  min(D1.c2) over (partition by D1.c5, D1.c4) as c2,

  D1.c3 as c3,

  D1.c4 as c4

from

  SAWITH0 D1),

SAWITH2 AS (select D1.c1 + 1 as c1,

  D1.c2 as c2,

  D1.c3 as c3,

  D1.c4 as c4

from

  SAWITH1 D1),

SAWITH3 AS (select Case when case D1.c6 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( ORDER BY case D1.c6 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c1,

  Case when case D1.c7 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( PARTITION BY D1.c5 ORDER BY case D1.c7 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c2,

  D1.c4 as c3,

  D1.c5 as c4

from

  OBICOMMON0 D1),

SAWITH4 AS (select distinct min(D1.c1) over (partition by D1.c4) as c1,

  min(D1.c2) over (partition by D1.c4, D1.c3) as c2,

  D1.c3 as c3

from

  SAWITH3 D1)

select sum(T44235.MONTANT_NET) as c1,

  T44163.FAMILLE_REPORTING as c2,

  T44163.GAMME_ARTICLE_LIB as c3,

  D3.c3 as c4,

  T44163.FAMILLE_REPORTING_TRI as c5

from

  BI_ACTSVR_ARTICLE T44163 /* 1_DIM_ARTICLE */ ,

  BI_ACTSVR_FACTURE_F T44235 /* 2_FAIT_FACTURE */ ,

  SAWITH2 D5,

  SAWITH4 D3

where ( T44163.CODE_ARTICLE = T44235.CODE_ARTICLE and T44235.DATE_FACTURE = D5.c3 and D3.c1 = D5.c1 and D3.c2 = D5.c2 and D3.c3 = '201703' )

group by T44163.GAMME_ARTICLE_LIB, T44163.FAMILLE_REPORTING, T44163.FAMILLE_REPORTING_TRI, D3.c3, D5.c4

order by c2, c3, c5, c4

]]

[2017-04-21T13:11:31.201+02:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ecf18e16d2e44622:127cc17b:15b1abd0c9f:-8000-00000000001933e6,0:1:29:5] [tid: f18] [messageid: USER-18] [requestid: af70023] [sessionid: af70000] [username: cservolin] -------------------- Sending query to database named DWH (id: <<841675>>), connection pool named srvbidata3, logical request hash 12fc482b, physical request hash 68f12215: [[

Second Physical query for Actual Year

select sum(T44235.MONTANT_NET) as c1,

  T44163.FAMILLE_REPORTING as c2,

  T44163.GAMME_ARTICLE_LIB as c3,

  T44176.ANNEE_MOIS as c4,

  T44163.FAMILLE_REPORTING_TRI as c5

from

  BI_ALL_TEMPS T44176 /* 1_DIM_TEMPS */ ,

  BI_ACTSVR_ARTICLE T44163 /* 1_DIM_ARTICLE */ ,

  BI_ACTSVR_FACTURE_F T44235 /* 2_FAIT_FACTURE */

where ( T44163.CODE_ARTICLE = T44235.CODE_ARTICLE and T44176.DATE_COMPLETE = T44235.DATE_FACTURE and T44176.ANNEE_MOIS = '201703' )

group by T44163.GAMME_ARTICLE_LIB, T44163.FAMILLE_REPORTING, T44163.FAMILLE_REPORTING_TRI, T44176.ANNEE_MOIS

order by c2, c3, c5, c4

]]

[2017-04-21T13:11:31.717+02:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ecf18e16d2e44622:127cc17b:15b1abd0c9f:-8000-00000000001933e6,0:1:29:5:2] [tid: 1334] [messageid: USER-20] [requestid: af70023] [sessionid: af70000] [username: cservolin] -------------------- Execution Node for logical request hash 12fc482b : <<841675>>, Close Row Count = 119, Row Width = 208 bytes

[2017-04-21T13:11:31.764+02:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ecf18e16d2e44622:127cc17b:15b1abd0c9f:-8000-00000000001933e6,0:1:29:5:2] [tid: 1334] [messageid: USER-42] [requestid: af70023] [sessionid: af70000] [username: cservolin] -------------------- Query Result Cache: [59124] La requête de l'utilisateur 'cservolin' a été insérée dans le cache de résultats des requêtes. Nom du fichier : 'c:\OBIEE119\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache\NQS__736442_47491_78.TBL'.

[2017-04-21T13:11:31.795+02:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ecf18e16d2e44622:127cc17b:15b1abd0c9f:-8000-00000000001933e6,0:1:29:5:1] [tid: f88] [messageid: USER-20] [requestid: af70023] [sessionid: af70000] [username: cservolin] -------------------- Execution Node for logical request hash 12fc482b : <<841396>>, Close Row Count = 119, Row Width = 208 bytes

[2017-04-21T13:11:31.826+02:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ecf18e16d2e44622:127cc17b:15b1abd0c9f:-8000-00000000001933e6,0:1:29:5:1] [tid: f88] [messageid: USER-42] [requestid: af70023] [sessionid: af70000] [username: cservolin] -------------------- Query Result Cache: [59124] La requête de l'utilisateur 'cservolin' a été insérée dans le cache de résultats des requêtes. Nom du fichier : 'c:\OBIEE119\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache\NQS__736442_47491_77.TBL'.

[2017-04-21T13:11:31.873+02:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ecf18e16d2e44622:127cc17b:15b1abd0c9f:-8000-00000000001933e6,0:1:30] [tid: f18] [messageid: USER-42] [requestid: af70023] [sessionid: af70000] [username: cservolin] -------------------- Query Result Cache: [59124] La requête de l'utilisateur 'cservolin' a été insérée dans le cache de résultats des requêtes. Nom du fichier : 'c:\OBIEE119\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache\NQS__736442_47491_79.TBL'.

[2017-04-21T13:11:31.889+02:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ecf18e16d2e44622:127cc17b:15b1abd0c9f:-8000-00000000001933e6,0:1:30] [tid: f18] [messageid: USER-50] [requestid: af70023] [sessionid: af70000] [username: cservolin] -------------------- The logical query seeds the plan cache [[

plan

what I assume to be the join query

RqList <<844187>> [for database 0:0,9] /* FETCH FIRST 500001 ROWS ONLY */

  1 as c1 [for database 0:0,9],

  2 as c2 [for database 0:0,9],

  3 as c3 [for database 0:0,9],

  4 as c4 [for database 0:0,9],

  5 as c5 [for database 0:0,9],

  6 as c6 [for database 0:0,9],

  7 as c7 [for database 0:0,9]

Child Nodes (RqJoinSpec): <<844206>> [for database 0:0,1]

  RqJoinNode <<844205>> []

  CacheTable T1

]]

I checked the log for the same report in 11.1.1.6 and in this case there is a physical join query that is run by the database !

select D1.c1 as c1,

  D1.c2 as c2,

  D1.c3 as c3,

  D1.c4 as c4,

  D1.c5 as c5,

  D1.c6 as c6,

  D1.c7 as c7

from

  (select 0 as c1,

  case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end as c2,

  case when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end as c3,

  case when D1.c4 is not null then D1.c4 when D2.c4 is not null then D2.c4 end as c4,

  case when D1.c5 is not null then D1.c5 when D2.c5 is not null then D2.c5 end as c5,

  nvl(D1.c1 , 0) as c6,

  D2.c1 as c7,

  ROW_NUMBER() OVER (PARTITION BY case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end , case when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end , case when D1.c4 is not null then D1.c4 when D2.c4 is not null then D2.c4 end , case when D1.c5 is not null then D1.c5 when D2.c5 is not null then D2.c5 end ORDER BY case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end ASC, case when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end ASC, case when D1.c4 is not null then D1.c4 when D2.c4 is not null then D2.c4 end ASC, case when D1.c5 is not null then D1.c5 when D2.c5 is not null then D2.c5 end ASC) as c8

  from

  SAWITH5 D1 full outer join SAWITH6 D2 On SYS_OP_MAP_NONNULL(D1.c4) = SYS_OP_MAP_NONNULL(D2.c4) and D1.c3 = D2.c3 and D1.c2 = D2.c2 and D1.c5 = D2.c5

  ) D1

where ( D1.c8 = 1 )

So my 11.1.1.9 is not acting like it should ?

EDIT : checked on google , found that this behaviour is call a stitch join that is performed by the BI server instead of the backend DB

checked my database functionnalities and the PERF_PREFER_INTERNAL_STITCH_JOIN is not checked

is there a param in the nqsconfig or elsewhere ?

Thanks gianni for your replies , it helps a lot !

Gianni Ceresa

What is the DB? Oracle?

Is the system a fresh upgrade or something you are upgrading or it's there and running for ages?

Are all the settings of the DB in the physical layer the best matching options with your real DB?

Between different versions it's totally possible to have different behaviour in the way the queries are generated, it's part of the changes you will have to do to get again the right results.

I just find strange that OBIEE do not want to push the join down to the DB ...

By the way: disable cache while you test these things, at least you avoid hitting the cache and having weird results because of that.

Christophe Servolin

Yes the DB is Oracle 11g

The 11.1.1.9 system is a new install performed 3 months ago. It's in production mode since March and we didn't notice anything strange since then

The database has been running for ages

I left the default settings in the DB options in physical layer

The cache setting is different between the 2 env ( old one cache is disabled)

I'll try this right now

Thanks again

Christophe Servolin

Disabling the cache solved the problem. !

So my understanding is that BI server uses the cache entries of the 2 physical queries and joins them

After disbaling the cache , the join query is a physical query ran against the DB

It's the first time I wanted to use the BI cache for improved perf.....

Anyway , gianni thanks a lot for your advices and expertise

Regards

Gianni Ceresa

It's always better to work without cache as cache often bring more hidden issues than the advantages you can easily see.

And never ever say out loud "I wanted to use the BI cache for improved perf"

That's one of the best "worst practice" in OBIEE: people enabling cache hoping to solve poor performance without taking into consideration the issues (and without a proper cache management strategy).

There are other ways to improve perf first, and cache is a like "la cerise sur le gâteau" when things are already fine tuned and working well.

Christophe Servolin

Don't worry I WILL remember that !!

Worst thing is I don't have any performances issues.

I've always worked with cache disabled as a consultant ( especially for the cache management strategy )

just thought it would be a "cool" thing to use now that I am in an IT dept.

That's part of the job to learn every day ( even the hard way)

Thanks again for your advices

Regards

[Deleted User]

Cool thread. I should have sent my "turn off caching" joke before heading out.

But this way @Gianni Ceresa vould have some fun ;-)  Edit: Even if he is quoting me without paying royalties...tsktsktsk