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 <shaofeng...@apache.org> 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 <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 史少锋
>
>

Reply via email to