OBIEE 12c When use IFNULL in RPD then I get wrong data in analysis

Boček Petr

Hi all,

   I have tricky one. We have formula in RPD like IFNULL(column1) + IFNULL(column2) + IFNULL(column3). If I use it in report I'm getting quite different data in compare to 11g version. If I squize the data to some certain point I get correct data. Finally if I use sorting in the pivot table the row sum is constantly changing :-D so it looks like it take some top based on order... I solved that that I do not use the function but I'm wondering what caused that behaviour change between 11 and 12. Any hint?

Many thanks

Petr

[Deleted User]

Boček Petr wrote:If I use it in report I'm getting quite different data in compare to 11g version.

As in it aggregates differently? What does the log say about how the query parses the RPD and chooses its path (log level 7)?

Boček Petr wrote:If I squize the data to some certain point I get correct data

What "certain point"?

I have a hunch that you're facing an issue with implicit aggregation and also pre- / post-aggregate calculation and things start "being correct" when you force the query grain down to atomic level but can't be sure from your limited input.

Boček Petr

Thanks Christian, everything is the same in 11 and 12 version. And unfortunately I'm not familiar with log level 7 (how to read). Only I can say that SQL statements are the same.  One report I was able to fix the problem by excluding the month detail which is not used in output so decreased  the dataset 12times. But I´m not successfull with the others...

Boček Petr

It looks like this is not a problem of IFNULL... This most likely happens when BI has to build the result from more then one query to db...

[Deleted User]

Ok so again as I said above without knowing what you are doing and how your models are built it's nigh impossible to tell you "do X" or "do Y". I'm sure some people will jump into this thread and yell "USE THIS FORMULA" but that's just not professional or empiric.

You have to start with simple queries and check you logical model, your physical model and your data. Work your way up. Check where things start to go wrong. If your model only has 1 fact and 3 dims and each of them has 1 LTS with precisely 1 table mapped....all the better. The Problem arises - and the reason I can't immediately answer is when you talk about 5 facts, 20 dimensions and each respective LTS can have 15 nested mapped tables.

Boček Petr

I understand you point and I did it already. Thing is we recently upgraded from 11 to 12 and even the architecture is the same and also all queries are the same in 11 and 12 version. During my investigation I came across the fact when I'm sorting the columns the sum of the rows is changing. Also I if a have two table in one analysis and will sort them differently then I get different results... I'm despaired...

Boček Petr

So after 2 moths communication and proving the issue to Oracle support the problem has been found. The problem is czech type of sorting. If you use czech NLS you have to set up.

1) In the NQSConfig.INI. set the following:

LOCALE = "Czech";

SORT_ORDER_LOCALE = "Czech";

SORT_TYPE = "default";

2) In the rpd,

a) set the SORT_ORDER_LOCALE for data warehouse is Czech.

b) In the Connection pool, go to the "Connection Scripts", edit "PreConnect script" and enter "alter session set NLS_SORT='XCZECH' ".

3) Restart server after above changes.

4. Run the problematci reports and verify the results.