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

    Data Modelling Advice

      Hi,

       

      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

       

      tblFacts:

      Load

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

           CourseRef&'-'&CourseDate as %_CourseRefDate,

           'Employees' as MetricType

           countEmployee as DataValue

      Resident

           dimTraining

      ;

       

      Concatenate (tblFacts)

      Load

           Null() as %_CourseDateEmp,

           CourseRef&'-'&CourseDate as %_CourseRefDate,

           'Events' as MetricType,

           countEvent as DataValue

      Resident

           dimEvents

      ;

       

      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.

       

      Thanks

      Adam