Count leaf node in level-based in hierarchy — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Count leaf node in level-based in hierarchy

Received Response
32
Views
9
Comments
Alex Sharkov
Alex Sharkov Rank 5 - Community Champion

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

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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.

    pastedImage_0.png

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    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
    Robert Angel Rank 8 - Analytics Strategist

    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
    Alex Sharkov Rank 5 - Community Champion

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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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]
    [Deleted User] Rank 2 - Community Beginner

    @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
    Robert Angel Rank 8 - Analytics Strategist

    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]
    [Deleted User] Rank 2 - Community Beginner

    pastedImage_0.png

    This just happened. basically I was unsubscribed from everything.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

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

    In the immortal words of 'Bones'...

    Image result for its worse than that he's dead jim