hi,
we are just migrating our exisitng 11G OBI instance to 12C (important note: both are still connected to exactly the same 11G database, so literally using same data ) and, when validating report values, we are finding some issues. When fighting them, I've found some significant differences between how the data is aggregated in 12C vs. how it was done in 11G.
* in 11G usually most aggregations were usually done at DB level
*in 12C, when checking reports' logs I see that more of it is moved to BI server. But, what is more confusing (and actually causing issues) is the fact that now, depending on number of tables (or columns? not sure on this ) used in an analysis , some aggregations may be pushed to physical SQL query, or just done at BI server . To give you an example:
I had one metric, let's call it metric1 that in BMM had case statement in its formula, with data types loosely set (i.e. one of the conditions was WHEN TabName. SomeColumn > 0 THEN ..... where SomeColumn was of Varchar type).
Now, if my report was very simple, had just 2 metrics, metric1 & some other metric2, report was executing correctly (cause this case formula was pushed to physical SQL query, and database doesn't mind such loose type definition). But, whenever I've added any dimension column to the report (and, as you can imagine, the actual report containing metric1 was using some dim columns + filters), the execution of metric1 formula was getting pushed to BI server / logical query, which fails at condition TabName. SomeColumn > 0 saying something about incorrect data types used. Well, that issue wasn't a big deal, since just adding CAST in RPD formula solved it. But now I've found some more reports which are giving me headache, because values of some metric(s) differ between 11G & 12C, and if I edit the report and remove some of the metrics, limiting the report just to some dim columns and only 1 metric (the one that differes on dashboard), the values are the same between both envs.... but adding any extra metric to 12G report automatically affects (changes) value of this problematic metric (and it shouldn't, I only used dims conformed for both facts), so I guess that this time using different aggregations leads to different figures being returned. Unfortunately, because of complexity of the report (many filters used, some dimensions & some of them hidden at resulting table, so many levels of aggregations), the SQL query I get is difficult to analyze & to make any conclusions ("so where actually is the issue?").
Any idea what can be done to avoid this / force 12C to use same aggregations as in 11G to get the values match?
Also, our admin says that there's possibility of using '11G mode' in 12C, but even if we use it for some testing, we wouldn't use it long term, so it doesn't seem like a solution.
11G is:
11.1.1.7.150120 (Build 150113.1200 64-bit)
12C is:
12.2.1.4.0 (Build BIPS-20190627163935 64-bit)
Oracle DB to which both OBIs above are connected is:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Thanks in advance, cheers
p.s. my first post here, hello everybody!
p.p.s if anything in description I've provided is unclear, let me know
