im not sure if i connect my data in the right way. i have data with different granularity for example 3 fact tables and some dimensions (20). i tried different solutions like partial link tables (attached), full link tables and the qlik default syn key without changing anything (probably cartesian problem) but my numbers are still wrong in set analysis. My last approach was the partial link table. So far it doesnt work properly. i cant Sum values correctly.
Some info on the tables: fact_actualpoints: key1 (composite of department,period,person,unit),actualpoints, some more columns fact_employee: key2 (composite of department,period,person), some more columns fact_targetpoints: key3 (composite of department,unit),targetpoints, some more columns linktable: key1,key2,key3,department,period,person,unit dimensions are connected with facts and other dimensions (star schema) department has multiple units. units have different targetpoint values
my set analysis Sum(aggr(count(distinct person)*max(targetpoints),unit))
i have an example in a qvf-file with reduced data. Its maybe a fundamental thing or my model is totally wrong. i would appreciate your help and thoughts about the data model.
Ideally you won't need a link-table and using a real star-scheme data-model which means to have a single fact-table with n surrounding dimensions-tables. Such data-structure could be reached by merging the facts mainly per concatenating by harmonizing the field-names and data-structures as much as possible.
This means to have no troubles with the associations between the tables as well as avoiding large key-fields and creating huge link-tables.
From what you are describing it should be applicable for your scenario - very similar fields within the facts with equally data because actuals and targets are in general the same - having just a different direction.
in general i would highly agree with you but i think my fact tables are too different. i would say that the fact_employee is like a student-life-cycle (changing departments or quitting/beginning each year) whereas fact_actualpoints is like the examinationsystem (written exams each year per person). fact_targetpoints is the base structure of grading rules (sums up all the necessary exams to graduate).
I could imagine that the hinted star-scheme is as main-idea applicable because some degree of asynchronous within the facts is normal and not critical - neither logically + technically nor from a performance point of view.
It sounds a bit that the target table is rather a dimension-table containing all possible combinations of courses/modules ... and not the only the mandatory ones for a certain student/employee. Therefore it could be useful to split the information into a fact-part which is concatenated to the actuals and a real dimension-table.
