"Depending on volume of your data a SQL based table / Opaque view is going to under perform" ... no predicate filtering. Using an opaque view brings ALL data back and let's BI server filter ...

https://bicoach.wordpress.com/2014/09/05/working-through-rpd-patch-files/ CAUTION: doing the above results in having your connection info (user/pwd) in plain text in your patch file ... some places this is a no-no.

Depending on volume of your data a SQL based table / Opaque view is going to under perform ... it's been mentioned 'do it on the source' - I second and third that advice.

The following is from BICS, but a quick look leads me to believe something similar can be done in Answers ... http://www.ateam-oracle.com/executing-a-stored-procedure-from-oracle-business-intelligence-cloud-service-bics/

Performance will play a factor ... if you have large row counts this case is going to be evaluated constantly and may prove problematic. So, yes, you can do it, & yes it works ... doesn't always work well.

Try a tnsping from the server to the DB ... if you get timeout there - it's usually (not always) because a firewall rule is in place. Have your network team open up the DB server and port to your BI server...

Check firewall rules between BI Server and DB ...

In addition to the above, if you don't have public synonyms in your database for these tables, make sure you are using fully qualified name setting in connection pool - then these tables get referenced as SCHEMA.TABLE ...

Painful experience conveyed in the master's words ... +1 @Gianni Ceresa

What dos your date dimension table look like? are you using an accumulating fact or a snapshot? the key to success is there ...

Then use a proper scorecard and strategy tool where you can create KPIs out of metrics and metrics out of measures ...

SQL generated is not wrong ... you are going to have a difficult time getting OBIEE to write the equivalent of on its own ... REGEXP_LIKE (column, 'ABC.*|XYZ.*|PQR.*') Given what you've posted you'd be better off setting a logical grouping (hierarchy) for your status codes to achieve the same result. I don't see a…

"thanks for your response ,its a measure column , just to show the value without any aggregation , still we need to move it to dimension (degenerated)?" If it's not aggregated @Gianni Ceresa is correct ... BUT you can aggregate it if you just need a discreet value with MAX, MIN, FIRST, or LAST Pick the one that best fits…

Option 4 is federate/replicate the data to a central database and use the concept of a DATA_SOURCE_ID to filter the rows by their original source DB ... via a 'select sinlge value only'-type prompt

anything here of assistance? Oracle BI 12.2.1.2.0 Upload RDP Failed

Then build an appropriate physical data structure that allows analysis of the data ... or perhaps OBIEE is the wrong tool for the job on this specific requirement.

You are asking the tool to do something it cannot do by design ... what I have done in the past is: Create a single hybrid logical column to hold past actuals and future forecasts, then by putting the one column in pivot (or chart) you can show a continuous line, then it is also easy to change the line style/color by time…

Your physical model is deficient ... listing your dimensional keys in a single column/row for any given measure violates star schema principles: DIMENSION FACT --------------- ---------------------- 101 101,104(2),102(1) 102 103 104 105 ^ nearly impossible to work with for analysis ... * what measure possibly has a…

And if he can - get himself physical mini-dimensions which only have the distinct list of values ... if need be!

If I wasn't off being "employably" busy ... what you need to do is model a lookup table to return the exchange/conversion rate ... +1 to my friends for sticking around!