Get the RPD changed ... current setup isn't using the basic functionality of the BI server for which you've paid a lot of money for.

You aren't using the same DB driver the documents reference ... this might be why you are having the issue. Did you try the referenced driver?

post the logical and physical queries, we'll take a look.

The query in the xml output might be your issue ... try taking out the special characters to start and the where clause doesn't have the proper syntax for multivalue as your original post states.

what does the XML file say when you view the Engine Log?

Anonymous login to SMTP? or not authenticated to SMTP server first? Also, check with your network guys to see if any changes to firewall rules... Might be why you are now getting denied. It's an SMTP error, not an OBIEE error.

If the physical query works, then looks like a logical issue in the RPD and how you have your facts and dims joined and content levels/hierarchies setup.

In Presentation layer .... Date Dim Day Month Year Data_Quality_Fact #CreatedEvents #ModifiedOpen Events #ModifiedClosed Events #CorrectedOpen Events #CorrectedClosed Events Now I pick Date = 9/1/2016 and I can see: #CreatedEvents = all new events on that date #ModifiedOpen Events = all open events that were modified on…

Yes, however you plan on using the information will drive your design ... you can combine the measures into a single fact presentation table for simplicity.

Build the counts in each of the logical fact tables (each with its own source) then co-locate them in the same fact folder in your presentation subject area... Or have one logical fact with 3 logical table sources

Canonical means one MAIN one ... src:Oracle BI Applications: Canonical Time Dimension in OBIEE so you would alias your fact table and join the create date key in one to time. the modified date key to time, the corrected date key in time ... now you can relate facts through the time dimension (should you need to) --- pick a…

Does your lookup table have keys defined and are you looking up based on all the keys?

"What I need is the position of the Nth occurrence of that underscore(which basically INSTR() function in SQL does). Is there any alternate way of deriving it?" -- use the INSTR() in the PHYSICAL mapping on the logical column in the RPD... this way the function is shipped back to the database which can handle it better…

"Works in pivot too ... the sample I have doesn't have the month number by itself, so the pivot isn't the way to show it," ...

12/31/2016 as end of month row already -- how do you know it is end of month and not end of year or just the day's values from that table ... your date dimension cannot help you with that determination if your facts are additive. 

You need to mark the rows in your fact to the grain they are - the dimensional attribute won't help as you could have 12/31/2016 as both a day row and a month end row and a year end row ... you need to logically fragment the fact table on the row-type not a dimensional attribute of the date.

Physically co-locate your data - but get yourself a row-type flag (D-daily, M-monthly), then logically split it - you are mixing granularity and will have nothing but issues. Use of information drives model design.

Works in pivot too ... the sample I have doesn't have the month number by itself, so the pivot isn't the way to show it, but I'm assuming your year is YYYY and your month is MM only.

If your subject area has multiple fact tables from your logical model, the implicit fact drives the relating of dimensional attributes. General DW principles are that EVERY fact has a dimensional context and dimensional attributes are related through the facts. Basically the 'idea' behind the star schema and the avoidance…

What is your warehouse DB? SQL Server? if so, https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/