Dimension does not aggregate

Question

Hello everyone,

when I add a certain dimension to my report, it is not aggregated. This means that the records with same dimension value are repeated. Example (without the column in question):

CompanyCountryEmployee CountCompany1England3Company1Norway2Company1Sweden5Company1France7

Now when I add this specific column, for example in my case 'Department', this happens (taking only England as an example):

CompanyCountryDepartmentEmployee CountCompany1EnglandFinance1Company1EnglandFinance1Company1EnglandSales1

Why does the Finance department not aggregate Employee Count to 2, like this:

CompanyCountryDepartmentEmployee CountCompany1EnglandFinance2Company1EnglandSales1

I'm a bit lost as to where I should begin looking at what might be causing this problem. Any help or tip is appreciated.

Thank you

JustTheFacts · Answer

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 Dept ID to the report to know, and when you do that you're showing the One True Key at the department level anyway.

Depending on who you talk to, the way your Department keys are set up now range from "probably not a good idea" to "WRONG! WRONG! WRONG!" :-) I lean towards the first interpretation. I mean, who knows? Maybe someone in your user community really needs to see reports broken out with three identical "Finance" rows with no way to tell them apart? I might question that person's sanity, but if it's a user requirement and you can't convince them otherwise, you have to support it. I have users like that. :-)

I'd be interested to know how many reports have Dept Name on them without also having Dept ID, and how the users interpret those reports?

One final note about "Use For Display" keys. These are mostly used to support natural-drill interactions. So you build a report with Company on it, and OBIEE automatically makes each Company a link which drills down to Department. OBIEE will use the "Use For Display" key, if it exists, when it drills to the Department level (but behind the scenes, the actual primary key will be in the GROUP BY of the query). When you are hand-building reports you can obviously choose whichever columns you want to repressent Department, and it looks to me like Dept ID & Dept Name are both required for the results to be meaningful.

JustTheFacts · Answer

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 to get more devious with your report design.

kzane · Answer

Thanks justthefacts,

this was exactly the reason:

justthefacts wrote:

I would look at the dimension hierarchy for your Department dimension and specifically I would look at dimension keys. If something like "Department ID" is specified as the dimension level key, but "Department Name" (what you are showing in the report) is specified as column at that same level of the hierarchy (possibly also marked as a "use for display" key at that level), then you could see this behavior if there were multiple (sub?) departments with different IDs but which all carried the name "Finance".

Dept Name to Dept ID was definitely not 1:1. In addition to that, the primary key for the dimension level was the Dept ID while Dept Name was set to 'Use for Display'. So it makes sense why there were multiple entries for a single Dept Name. If I set the Primary Key for the Dimension Level to Dept Name, then it works as expected. Thanks for that!

My following question would be, what is the best way I can make this work without changing the existing dimension? Our system has a lot of reports and there might be a specific reason why someone set that Dimension Level like it was (Dept ID as primary key). So I would prefer to keep this dimension as it is. Do I just make a whole new dimension and connect it to a copy of the KPI used for that report?