Data Modelling Advice

Tue, 21 Apr 2015 16:51:09 GMT

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

Re: Data Modelling Advice

Gysbert_Wassenaar — Wed, 22 Apr 2015 10:15:29 GMT

Perhaps this blog post helps: Fact Table with Mixed Granularity

topic Data Modelling Advice in QlikView

Data Modelling Advice

Re: Data Modelling Advice