Data Model with Target Table having 2 Dims from Fact Table and 2 Dimensions from hierarchy table

[rammarthi]

Hi, 

Please help me create this data model.

I have one fact table with the following fields:

1. Emp_ID

2. Order_Date

3. Department -- Utilities, Retail, Aviation etc.,

4. Item_Category 

5.Sales Qty

6. Sales Amount

7. State/ Region/ Province

Hierarchy Table:
1. Emp_ID

2. Job_titl -- Sales Director, Regional Manager, Area Manager, Sales engineer

3. Department -- Utilities, Retail, Aviation etc.,

4. Emp_Engagement_Start_Date -- Orders between these two dates 4,5 determine the sales by an employee

5. Emp_Engagement_End_Date -- Orders between these two dates 4,5 determine the sales by an employee

6. Name of the Employee

Target Table:

Target for employees may vary across departments, individually, item category wise and state/ province wise 

1. Target Value -- Numeric

2. Measure of target -- per Week, per Month, per Quarter, per Annum

3. Job Title -- Same as hierarchy table

4. Department -- Same as hierarchy table

5. Item_Category -- Same as in Fact table

6. State/ Province as in Fact table

7. Date -- Date on which the latest record is updated 

I also have a Time lookup table from which I can take Dates for the last 2 years, Months and years on a rolling dates basis of order date from Fact Table.

The problem is we have two target table columns from Hierarchy table and two columns from Fact table.

Thanks in Advance

Ram Marthi

[stevedark]

I think what you need to do is create two ApplyMap tables with Emp_ID as key and one with Job_titl and one with Department.

Once those two tables are mapped you can do a preceding load to build a Emp_ID, Job_titl and Dept key.

Then, when you load the targets drop the three separate fields and just build the same key.

Info of ApplyMap and Preceding load here:
https://www.quickintelligence.co.uk/applymap-is-it-so-wrong/
https://www.quickintelligence.co.uk/preceding-load-qlikview/

That should hopefully move you in the right direction?