1 Reply Latest reply: Apr 22, 2015 6:15 AM by Gysbert Wassenaar RSS

    Data Modelling Advice



      Could do with some data modelling advice for a QlikView data model with training data. I've not got access to the app right now so I can't copy the script or print screen the data model...so I'll have to explain.


      I have 2 tables - dimTraining and dimEvents.


      dimTraining: the lowest level in this table is CourseRef, CourseDate and EmployeeRef. This is a table of all the courses created over the days they run on. There is a countEmployee field which is a count of the records.

      dimEvents: the lowest level in this table is CourseRef, CourseDate (just data on the events). This is a table of which employees are booked on each course by day. There is a countEvent field which is a count of the records.


      note: not all the CourseRef values in dimEvents exist in dimTraining, only once employees are booked on the course.


      I've created a Facts table using resident loads from these dimension tables


      The Facts table links to dimTraining on a compound key %_CourseDateEmp

      The Facts table links to dimEvents on a compound key %_CourseDate




           CourseRef&'-'&CourseDate&'-'&EmployeeRef as %_CourseRefDateEmp,

           CourseRef&'-'&CourseDate as %_CourseRefDate,

           'Employees' as MetricType

           countEmployee as DataValue





      Concatenate (tblFacts)


           Null() as %_CourseDateEmp,

           CourseRef&'-'&CourseDate as %_CourseRefDate,

           'Events' as MetricType,

           countEvent as DataValue





      So I have a 3 table data model. tblFacts associating with dimTraining on %_CourseRefDateEmp and tblFacts associating with dimEvents on %_CourseRefDate.


      The issue I have is that when I filter for an Employee (the field existing in the dimTraining table) it filters tblFacts for the associated %_CourseRefDateEmp values. My expression "=Sum({<MetricType={Events}>}DataValue)" will return nothing as that key is loaded as Null() for the Events data (as it is at a different granularity).


      My question is what's the best way to handle mixed granularity in a facts table in this instance? The main thing I need to get from this is being able to filter my events data in the facts table by employee selections.... i.e. if i select an employee ref I'd like to see how many events they have been booked on to.


      I'd prefer not to OuterJoin the 2 dimension tables if possible.


      Any help / advice appreciated.