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

Question

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

Boček Petr · Answer

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.

Boček Petr · Answer

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...

[Deleted User] · Answer

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.