data model question

[amien]

whats a good practice for a model in qlikview?

1. fact data on employee level (key) .. more tables

2. fact data on employee and function level (key) .. more tables

3. master calender

4. all fact tables contain a date field. some are per month, some are per day

5. several tables with dimension employee data (like name, function name, date of birth etc).

how shoud i load the data into qlikview?

my approach:

1. concatenate all dimension employee data into one table (contains both employee level key and employee function level key)

2. concatenate all fact tables based on employee level key (seperate table for fact table based on month and day)

3. concatenate all fact tables based on employee function level key (seperate table for fact tabled based on month and day).

4. link mastercalender to all facttables (key is day or month)

any suggestions? other ways to link the master calender?

[tanelry]

I would first try to concatenate all fact tables containing time dimension (date or month) into one table.
Facts that are per month only - convert the month to date (for example 2010_10 = Oct 1st 2010). Master calendar will be connected to single date field which is same for all facts.
Then single dimension table for employee.
I'm not sure how function relates to employee but I assume it is not always 1:1, in this case I'd make separate dim table for function.

This would be my scratch:

Fact: // all concatenated
EmployeeID,
FuctionID,
Date,
Fact1,
Fact2

Employee:
EmployeeID,
EmployeeName,
EmployeeBirthday,
EmployeeNativeFunction

Function:
FuctionID,
FuctionName

Calendar:
Date,
Month,
Year

[#3]

Please, see the file QlikView Data Model Best Pratices.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0363.The_5F00_QlikView_5F00_Data_5F00_Model_5F00_Best_5F00_Practices.pdf:550:0]

[amien]

Thanks for your reply ... the date thing is a good solution.

EmployeeID and FunctionID is only a bit different.

Overtime an employee can have more than 1 function. therefor the fact table is something like this:

employeeID, activityID, sales

10000, 1, 42343

i create a key based on employeeID and activityID.

in your description, when i select a fact which only contains a employee_function_id .. than i will never get a name.