OBI: 12.2.1.4.0 - Global Currencies

Question

Hi All, I'm hoping you can help.

I am part of the team that looks after report development front-end for a company who has recently procured OBI, I have good experience with OBI but am in no way a developer.

Recently, suddenly AP reports became very poor performance-wise, a group of schedules overlapped and brought the system to a crash. These schedules had worked for a couple of years prior.

Coincidentally Global Currency 1 had been enabled to allow reporting in EUR at around the same time by a consultant no longer with us.

The app developers (a fairly new offshore third party team hired after implementation) are assuring me that the conversion to report local currency in EUR is done at RPD level and thus hasn't caused any changes to warehouse tables which would cause poor performance, but I'm not 100% convinced.

A quick look at docs.oracle shows fact tables hold columns for Global currencies and their corresponding exchange rates, but the developers are persistent, this is their latest:

We have checked in 2 Dashboards (that contain the Currency filter) and both contain formula in RPD for getting Amount in EUR Global currency (Amount * Exchange Rate). We are not storing the EUR Amount in the database table.

Can anyone shed some light on this for me please?

Regards,

Gianni Ceresa · Answer

Ok, so that's definitely BI Apps.

The concept of "global currency" as far as I know it's also a BI Apps thing as I don't remember having seen something for it in OBIEE (other than setting display options and be able to use a variable to retrieve the correct currency or something like that).

Still, the best way to prove that something changed and that it is now worse (from a performance point of view) compared to before is the query.

Usage tracking if it's fully setup you could have the physical queries stored. Take for the same analysis the physical query before the change and after, and you will easily be able to show the impact of the change.

Also by analyzing the query you could probably see the new one as an higher total cost when you look at the explain plan. You could also maybe easily identify a fix at the database level: maybe adding an index or something else.

Lee Gledhill · Answer

Hi Gianni, thanks for taking the time to respond,

I'm talking about OBIEE Analyses that use Answers Subject areas:

Financials - AP Transactions

Procurement & Spend - Invoice Lines

These are the main culprits of poor performance.

All I'm looking for really is for detail of what exactly happens when an extra global currency is enabled, so I can ascertain whether this is the root cause of the performance problems.

Gianni Ceresa · Answer

Hi,

So you are on OBIA and not a simple OBIEE?

And I'm not fully sure what you are looking for... You say that some reports become slower: are they really BI Publisher reports or is that OBIEE analyses? Could be analyses but always better to be precise with wording...

So, are you trying to confirm that what the developers changed introduced extra load and made your system slower?

Did you look at queries? Because in the end OBIEE doesn't do any magic: it does generate queries and move data around between a source and the user screen.

If you have usage tracking enabled, you can even compare queries before/after the change of the developers, and by analysis queries you can easily prove they are the source of the issues.