Excellent idea, gentlemen, but unfortunately that is not the problem here. As I mentioned, I am using a very tiny, simple model to test this and the physical query is correct and returns data for all columns. Additionally, both this sample model and all of our other affected models work correctly in 11g; this issue is…

I think this is just a restatement of what Christian is suggesting, but I'm going to spell it out a bit for future readers who may not have the skill of either you or Christian. Mark Christian's as the correct answer, if this works. 1) Create a new logical dimension and have as its source your Header + Detail combination…

Hello Hibai, Any filter applied to a measure will be applied after aggregation to the level-of-detail (LOD) of the analysis. So for example, if I have this analysis: Customer Name, Year, Revenue, Volume And I put a filter of "Volume >= 1000" on the request, OBIEE will first calculate Volume by Customer and Year, and then…

Let's agree to disagree on the benefits of command-line-based configuration and maintenance of complex systems! As for developers, I've been a developer for 30 years and spent the first half in mainframe, Unix, and Solaris command-line environments. Give me an IDE over vi any day. And if they have a feature already…

This is a long-time irritation with 11g (haven't checked 12c yet to see if it persists). I haven't yet found any workaround to the "Save System-Wide Defaults" privilege being required. Which means none of my users have the option to treat text as HTML in non-narrative views. No way am I giving them the system-wide defaults…

There is a way to get a "top 10" look, though it won't actually be returning just the top 10. Users won't know the rest of the data is there behind the scenes. 1. Sort your measure descending (in the "Result Columns" level of your union) 2. Create a pivot table of the results; you don't need to pivot any of your…

Bumping this once, just in case. We have a sev 2 service request for this problem and it's been 2 weeks with no response. Seems Oracle doesn't know the answer either. I do not get why they removed this functionality from EM and made it a command-line operation. 1997 called, it wants its user interface back.

Challenge accepted! If there is more than one measure, it's a bit trickier but still doable. You have to pivot your Criteria tab, sort of, by using a union query. So instead of having Region, Quarter, Label, M1, M2 in the criteria, you have two unioned sub-queries: Region, Quarter, Label, M1 --union-- Region, Quarter,…

I may be misunderstanding this requirement, but is this the sort of thing you want (I have added "Product" because it wasn't clear what was on the Y axis of your pivot): Q1Q2Q3Q4ProductM1BBBP1nnnnnnnnnnnnP2nnnnnnnnnnnnP3nnnnnnnnnnnnP4nnnnnnnnnnnn Where all of the 'nnn' are actually measure "M1" from your model? If that's…

The very few times when we've needed to promote column formats we have just recreated them by hand in the production environment. Very rarely will we work with the defaults files directly (and usually it is to remove system-wide defaults that were created by accident). That said, at some threshhold (maybe ~50 columns that…

Thomas has the best design idea, but to address the question you are asking (and maybe help with future issues troubleshooting SQL), I would point out that SELECT DISTINCT requires processing of all rows, to ensure a distinct result. Without DISTINCT, Oracle can just start feeding rows (the first 50 in your example) as it…

Also, for what it's worth, it would be surprising to me if your dimension hierarchy is supporting any useful behavior in the front end. As you have seen, unless Dept ID is actully on the report, the Dept Name gives almost no useful information to the end user. You see several "Finance" but which is which? You'd have to add…

You should be able to get the results you want by using the AGGREGATE() function. Try using this formula for your measure: AGGREGATE(EmployeeCount BY Company, Country, Dept Name) I think that will force it to use Dept Name as the 'group by' column, instead of the defined dimension key. If that doesn't work, you might have…

Thomas is correct, the design is sub-optimal, but you have a problem, let's try to solve it. There are a couple of ways to do it. Brute Force: If you require twelve measures, "Jan Volume", "Feb Volume", etc., you can create them in your logical fact table by using a CASE statement on the last two digits of your month_wid.…

kzane, Srini is correct - examining your fact-dimension joins and the levels (on the "Content" tab) of your fact source(s) is a good start. Whenever you have aggregation weirdness, those are places to look. In addition, I would look at the dimension hierarchy for your Department dimension and specifically I would look at…