Thanks Manoj and ShaoFeng, I already tried creating view and using within cube, but my challenges were - 1. One employee can have more than one manager for different projects (So duplicate key problem comes) 2. I need recursive total per all descendant hierarchy employees (So I need from that level to down list of all employees in order to query them from Fact)
The fact table is very big 300 Million records, so I cannot use single employee related records duplicated per its manager. The first item is the main issue with duplicates. Thanks - Deva On Fri, Feb 2, 2018 at 4:47 AM, ShaoFeng Shi <shaofeng...@apache.org> wrote: > I agree Manoj about "need to flatten it out"; You can easily achieve this > by creating a Hive view, in which join the fact table with the dimension > table. Then use the view as the only table for the model and Cube. In the > cube, you still have the chance to specify "manager" and "employee" as a > hierarchy in the aggregation group settings. > > 2018-02-02 11:44 GMT+08:00 Kumar, Manoj H <manoj.h.ku...@jpmorgan.com>: > >> You need to flatten it out – bring it to Column level – All hierarchy >> needs to be do that. >> >> >> >> Regards, >> >> Manoj >> >> >> >> *From:* deva namaste [mailtoohd...@gmail.com] >> *Sent:* Thursday, February 01, 2018 9:07 PM >> *To:* user@kylin.apache.org >> *Subject:* Re: Manager employee hierarchy in Kylin >> >> >> >> Hello, >> >> >> >> Any Idea or updates on how to achieve building hierarchy I explained in >> my question 2 days ago? Any workaround I can implement in order to achive >> this requirement? >> >> >> >> Any advice is appreciated. Thanks >> >> >> >> - Deva >> >> >> >> On Tue, Jan 30, 2018 at 5:51 PM, deva namaste <ohd...@gmail.com> wrote: >> >> Hello >> >> >> >> I am plannning to implement Manager - Employee Hierarchy in Dimension >> table and join with the fact table similarly its been explain in following >> link >> >> >> >> http://brazenly.blogspot.com/2015/02/datawarehouse-dimension >> al-modeling.html >> >> >> >> So far, I am unable to achieve. >> >> >> >> How do I achieve? If no straight forward solution, any workaround which I >> can implement? >> >> >> >> (One employee can be under one manager for one task and also can be under >> another manager for different task, so above link hierarchy will work. but >> unfortunately I am getting duplicate records in dimension table when I try >> to achieve because Kylin looking for single record primary key key in >> dimension. How can I achieve?) >> >> >> >> Please advice. >> >> Thanks >> >> >> >> This message is confidential and subject to terms at: http:// >> www.jpmorgan.com/emaildisclaimer including on confidentiality, legal >> privilege, viruses and monitoring of electronic messages. If you are not >> the intended recipient, please delete this message and notify the sender >> immediately. Any unauthorized use is strictly prohibited. >> > > > > -- > Best regards, > > Shaofeng Shi 史少锋 > >