This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
  << Previous Topic | Next Topic >>Return to Index  

ASO Employee Hierarchy

June 19 2017 at 3:02 PM
No score for this post
AU 
from IP address 68.114.34.11

Hi,
I’m building the employee hours ASO cube which contains employee hierarchy.
The cube will be build based on SQL server table extract.
Employee hierarchy table as follows:
CIO | SVP | VP | Sr. Director | Director | Sr. Manager | Manager | Developer1 | Developer2|
I used generation dim build method. My issue is that the data is consolidated from Level 0 to the top based on rollups.

CIO
SVP
VP
Sr. Director
Director
Sr. Manager
Manager
Developer1
Developer2

I need to have data for each company employee from CIO to Developers, not to be overwritten by Level 0 data..

I would appreciate your feedback/help.
Thanks!

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply
Cord123

38.110.207.226

Why generational then?

No score for this post
June 19 2017, 3:07 PM 

If you don't want them to consolidate to each other, then why build them as generational references? Drilling down in SmartView won't provide useful detail anyway since you said that you don't want them to sum up to the parents. Why not make them all level 0?

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AU

68.114.34.11

Re: Why generational then?

No score for this post
June 19 2017, 3:11 PM 

The initial business requirement is to build employee dimension rollup based on employee hierarchy relationship, Director->Manager->Developer. I cannot make all level 0 employees.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Tim Faitsch

166.76.0.1

Re: Why generational then?

No score for this post
June 19 2017, 3:56 PM 

Requirements were made to be re-written. Not having a full understanding of out of the box functionality was probably part of the problem. One work-around would be to create "load members".

CIO
---CIO_load
---SVP
------SVP_load
------VP
---------VP_load
---------Sr. Director
------------Sr. Director_load
etc...

That way you can load the hours to the correct level in the cube _and_ see them roll up. If this cube was BSO, you'd have more options (not that I would ever advocate loading to uppper level members).
Tim

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AU

68.114.34.11

Re: Why generational then?

No score for this post
June 19 2017, 5:03 PM 

Thanks Tim for your reply.

What dim build method should I use to build same level members CIO_load and SVP? The generation method, I don't think will work. Please let me know.

Thanks!


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Pete

210.11.186.210

Re: Why generational then?

No score for this post
June 19 2017, 9:02 PM 

Mate - your issue isn't the load rule format, but the structure of the dimension.

you need to be clear on what the output will look like and whether that fits the requirement you're trying to meet (and to echo Tim's point, it's the requirement that needs to change here - what you're asking for doesn't fit how essbase works).

None the less, I normally use parent child.

All Employees|CIO
CIO|CIO_Load
CIO|SVP
SVP|SVP_Load
SVP|VP
VP|VP_Load

etc etc

This 'creates' level zero members at upper levels of the hierarchy where you can load data.


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Tim Faitsch

67.162.119.107

Re: Why generational then?

No score for this post
June 20 2017, 9:21 AM 

Depending upon the system this is coming out of, you could leave the generation build alone and then have a second build that just uses the unique list of employees to do a parent child build using that list. Parent is the original list and then the child is a clone of the first field with the "_load" suffix added. Then you don't have to get too fancy with coding and such.
Tim

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AU

68.114.34.11

Re: Why generational then?

No score for this post
June 20 2017, 11:39 AM 

Thank you Tom. I will do that.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
 
  << Previous Topic | Next Topic >>Return to Index  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.