topic How to resolve this circular reference / synthetic table situation in QlikView

How to resolve this circular reference / synthetic table situation

Mon, 03 Jan 2011 15:11:01 GMT

Hi All,

I've got the following problem: I want to do some KPI reporting (within a single qvw) on two separate measures:

The number of illness days (absenteeism) per period (month+year) and per department (drill down to employee level). The data are available per employee, and each employee works in a department.
The total revenue per project (each project has been done in one department, and in one period) and per period.

You can see what is happening here: I've got a classic example of a circular reference.

Now, what I tried to do here is to remove the circular reference by concatenating the two fact tables (Absenteeism and Revenue). Now, this works, and will get me the correct figures, but leaves me with a synthetic table for the combination of EmployeeId and DepartmentId. This is the source table view, so you can see this can't be solved by using a composite key:

I'm a bit puzzled here, as to what would be the best way to solve this.

Any ideas would be welcome.

Thanks,

Fréderic

How to resolve this circular reference / synthetic table situation

hector — Mon, 03 Jan 2011 15:24:06 GMT

Hi, is it possible to duplicate the "Department" table but with another alias for the fields?

I think it will be


Employee_Department:
Load
 DepartmentID as Employee_DepartmentID,
 Department as Employee_Department
resident Department;

and in the Employee table change

DepartmentID as Employee_DepartmentID

This will avoid the circular reference

Rgds

How to resolve this circular reference / synthetic table situation

Mon, 03 Jan 2011 15:28:44 GMT

Well, sure, but I'm not a big fan of redundancy. Because this raises another question: if I select a department filter on the revenue data, that filter will not be imposed on the absenteeism data. This would imply that the user would have to select that same department a second time. And I can tell you, users don't like these kind of things, it just doesn't seem logical.

How to resolve this circular reference / synthetic table situation

Mon, 03 Jan 2011 16:47:17 GMT

One idea, maybe not the most performant one but very useful would be to rename the PeriodId into two fields PeriodID_Absenteism and PeriodID_Revenue

Then the Period table would be detach from other tables ( that means you create a kind of Calendar table).

Then you can use set analysis very easily to filter data based on the calendar selection.

Or you create two Periods tables, this can be a possibility too.

Or, at the end create fact tables, this will be more powerful in terms or selection but could be more complicated in the script...

Personnaly, when we talk about date issues I like to work with one alone Calendar table (island table) and different set analysis expressions.

Regards,

Sébastien

How to resolve this circular reference / synthetic table situation

Oleg_Troyansky — Mon, 03 Jan 2011 17:11:38 GMT

Well, this is a rather simple data modeling exercise...

You started on the right path when you decided to concatenate the two fact tables into one
The only missing step is to ensure that all the relevant Dimension Keys are represented in the combined Fact Table.
In your case, this means moving "Department ID" from the Employees table into Absenteeism table, prior to concatenating.

This is not causing any duplication of the data (in QlikView, we only store distinct values), but rather helps transforming your data model in the classical "Star Schema", that always works best in QlikView.

How to resolve this circular reference / synthetic table situation

Mon, 03 Jan 2011 17:30:22 GMT

Thank you all for your contributions.

I guess I still have to get used to the non-relational nature of QlikView

How to resolve this circular reference / synthetic table situation

Thu, 09 Jun 2011 11:09:29 GMT

I am not sure - loading the same dimension table more than once is not recomended. even if you join its date to different facts - the solution is somewhere there but someone from QLIKTECH must response to that.