Count leaf node in level-based in hierarchy

Alex Sharkov

Hi all I have level-based dimension Employee Division->Department->Employee I have to count how many employee in each department an each Division is. How to do it ?

So i try formula:

SUM(CASE WHEN ISLEAF("DIMENSION"."HIERARCHY") THEN 1 ELSE 0 END)

but this is incorrect ,  because on parent level= (Division) i have 0.

Is it way to do it without calculation on DB

[Deleted User]

Level-based measures. Create an "Employee Count by XYZ" measure for each level you want and then tie its level of aggregation to exactly that dimensional hierarchy level.

Robert Angel

Hi Christian,

my thinking was that OBIEE by default gives you a group by on any dimension attribute, so provided you could create an equivalent of count(distinct employee_id) then pulling Division, Department and the afore mentioned measure in to an analysis would provide the correct answer, or am I missing something? (- with all due respect, no sarcasm intended or implied, I am purely wanting to satisfy my curiosity)

Robert Angel

Okay, after coffee my brain is saying, "but you don't know what other measures he wants in the same analysis and that is why Christian is suggesting to fix it using the 'level based measure' functionality".

Okay I get it.

My brain also added you could use Count (distinct employee_id by division||department) which would also work.

Alex Sharkov

so i divide Dimension on 2 logical tables on BussinessLayer and calc count on fact table. That's work

[Deleted User]

You can do either or both or also AGGREGATE AT...as usual you have 50 ways of achieving this and each one has its advantages and disadvantages. I just proposed the RPD way as it's the most encapsulated and re-usable.

[Deleted User]

@3150818 No insult intended but with that you have pretty much chosen the least optimal and most convoluted / weird approach that neither Robert nor me would have ever suggested. (Maybe @Gianni Ceresa would have come up with that last Friday night at 2am ;-))

Robert Angel

Understood, build it once in the rpd and everyone can benefit from it without working out how to 'get there' again!

And sincere thanks for answering, we all seem to be in the dark at the minute on the forums as to updates!!

[Deleted User]

This just happened. basically I was unsubscribed from everything.

Robert Angel

It has just taken me 15 minutes to log in, and no, I don't type that slowly...

In the immortal words of 'Bones'...