Hi Deva, Does the fact table has "project" information? If yes, can you use employee + project to join the dimension table (assuming employee + project is unique in the dimension table) ?
If you can share the table structure, that would be easier to understand the problem. 2018-02-02 20:33 GMT+08:00 deva namaste <ohd...@gmail.com>: > 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 史少锋 >> >> > -- Best regards, Shaofeng Shi 史少锋
