Hi Shao, I am using following concept for OLAP so I can get all the child (descendants) and their levels by querying parent employee. So that I can have sum(sales revenue) for each employee and I can aggregate them upto their manager. e.g. manager can have 5 employees, so he can see total sum for entire group and also drill down to see sum for each employee.
My problem is one child_employee_key can be belongs to one or many parent_employee_key, so I would have duplicate child_employee_key records. Also for maintaining level details it would need duplicate entries, but in kylin any dimension it requires unique entry which I am facing challenge and trying to figure out how can I create work around for this. Appreciate your help. <http://2.bp.blogspot.com/-NQKnhb2Luxk/VNs-gPZIpqI/AAAAAAAAE_I/j10g7TsJ0Jw/s1600/4.png> The following are the paths: 1. Employee 1 to Employee 1 (Zero-length pathway to itself) 2. Employee 1 to Employee 2 3. Employee 1 to Employee 3 4. Employee 1 to Employee 4 5. Employee 1 to Employee 5 6. Employee 1 to Employee 6 7. Employee 1 to Employee 7 8. Employee 2 to Employee 2 (Zero-length pathway to itself) 9. Employee 2 to Employee 4 10. Employee 2 to Employee 5 11. Employee 2 to Employee 7 12. Employee 3 to Employee 3 (Zero-length pathway to itself) 13. Employee 3 to Employee 6 14. Employee 4 to Employee 4 (Zero-length pathway to itself) 15. Employee 5 to Employee 5 (Zero-length pathway to itself) 16. Employee 5 to Employee 7 17. Employee 6 to Employee 6 (Zero-length pathway to itself) 18. Employee 7to Employee 7 (Zero-length pathway to itself) <http://1.bp.blogspot.com/-FXIp9ezkC0k/VNs-i3I3J8I/AAAAAAAAE_w/ItAR6HPbC9o/s1600/x.png> <http://1.bp.blogspot.com/-yyFVpzOcNZY/VNs-jrLkt7I/AAAAAAAAE_8/jr3lEo38G0I/s1600/y.png> *I was planning to generate following SQL to generate the report for descending the hierarchy* *Select E.Full_Name, SUM(F.Sales_Revenue)* *From* *Employee E, Bridge_Traverse_Hierarchy B, Sales_Fact F, Date D* *where* *E.EmployeeKey= B.Parent_Employee_Key* *and* *B.Child_Employee_Key= F.EmployeeKey* *and* *F.DateID= D.DateID* *and* *E.[FULL NAME]= ‘Employee 2’* *// and D.Date= January 2005 Data (Date SQL Code logic depends on Database)* *GROUP BY E.Full_Name* On Fri, Feb 2, 2018 at 9:26 AM, ShaoFeng Shi <[email protected]> wrote: > 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 <[email protected]>: > >> 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 <[email protected]> >> 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 <[email protected]>: >>> >>>> You need to flatten it out – bring it to Column level – All hierarchy >>>> needs to be do that. >>>> >>>> >>>> >>>> Regards, >>>> >>>> Manoj >>>> >>>> >>>> >>>> *From:* deva namaste [[email protected]] >>>> *Sent:* Thursday, February 01, 2018 9:07 PM >>>> *To:* [email protected] >>>> *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 <[email protected]> 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 史少锋 > >
