Why aren't you using the W_DAY_D date column based on the lookup from the WID? Basically a simple alias of the W_DAY_D can get you the date column (it's a self-join) ; then you have no conversion to do ... conversion is clunky and slow. Let the DB handle it in a physical join: "Dim_W_DAY_D_Common"."CAL_WEEK_START_DT_WID"…

"This fact table is not joined to any time dimensions" --- that's why any approach will probably be inefficient ... you should always have if atr the least a rudimentary time dimension from which to translate keys into textual items for use. USE OF INFORMATION DRIVES DESIGN

Modeling it makes it reusable and works in both 10g/11g ...

Better off creating an entry system so someone can manage the grouping/binning in a database table, you model it through the BI RPD, then it's as good and current as the steward maintains it. OBIEE is not designed as a transactional or a data entry system.

Don't 'sell on features' ... @Andrewif and @Christian Berg are pointing you in the right direction. It's about business value and architecting a system that can serve answers to questions about the business when the business needs them. It's about a system that can facilitate the decision-making process to give people more…

Another approach .... SELECT TO_CHAR(TRUNC((((86400*x)/60)/60)/24)) || 'days ' || TO_CHAR(TRUNC(((86400*x)/60)/60)-24*(TRUNC((((86400*x)/60)/60)/24)), 'FM00') || 'hrs ' || TO_CHAR(TRUNC((86400*x)/60)-60*(TRUNC(((86400*x)/60)/60)), 'FM00') || 'mins ' || TO_CHAR(TRUNC(86400*x)-60*(TRUNC((86400*x)/60)), 'FM00') || 'secs'…

"But every time I include a column BI server generates the physical query and hits the database, which is giving me a huge performance issue." - that's a RED flag! Take a serious look at: BI Forum 2014 preview - No Silver Bullets : OBIEE Performance in the Real World - Rittman Mead Consulting thanks to @rmoff for his great…

What table is the column headers coming from?

To do that you are probably going to go on a long hunt deep into the application ...

Sort in OBIEE Answers - Desc Nulls Last

Pass the fiscal_period to a report that you don't put on the dashboard, accept the prompted value and put Year as the only criteria column. Then in your report filter Year by the results of another analysis ... skip the messy functions all together.

how does your logical star look like? I assume Revenue is indeed a measure column from a fact table ...

Like this? if so then in Compound Layout: Your options are limited ... above I set the border and background.

Field2 Value1 & Field1 Value4 are adding what value to the information displayed? It is unclear given how you've represented your pivot table.

Design? You bought the design -- you implement that design ... unless you mean something different that 'design' -- do you mean customize? Outline what you are trying to achieve.

Firstly: Rank(any fact) is not a dimension ... Notice I did the rank of revenue by the sales rep type ... so the total revenue across all rep types produces the ranking for the product types. Then I just moved that fact column tot eh row beside the product type dimensional attribute in the pivot table.

Agreed, yes you can ... the RANK(REVENUE) was built in the criteria tab

RANK(SUM(fact by dimension) will force the granularity of the calculation ... this example ignores the year ... so all values by sales rep are summed, then the rank is applied:

Does the user in the connpool have access to write data to those tables?

The way you've drawn your pivot you've got Rank as an attribute ... seems you aren't giving us the other dimension (time and ???) I did no special moves here (Year, Sales Rep Name, Revenue and RANK(Revenue)) -- worked by clicking the up/down sorting arrows (the default sort was the Sales Rep Name vertically)