Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Count leaf node in level-based in hierarchy
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
-
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.
0 -
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)
0 -
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.
0 -
so i divide Dimension on 2 logical tables on BussinessLayer and calc count on fact table. That's work
0 -
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.
0 -
@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 ;-))
0 -
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!!
0 -
This just happened. basically I was unsubscribed from everything.
0 -
It has just taken me 15 minutes to log in, and no, I don't type that slowly...
In the immortal words of 'Bones'...
0