topic Data Model Problem with Mixed Fact/Dimension table in QlikView

Data Model Problem with Mixed Fact/Dimension table

shanemichelon — Mon, 17 Mar 2014 22:08:16 GMT

Hi everyone. I have a problem trying to setup a data model that works for a case with a fact table that is also a dimension table. I have created a simplified version to illustrate (attached).

Basically, I have a concatenated fact table with payroll data and hazard data (and other stuff) in it. This table has keys of date/employee/location. Location is only used for hazard data. It is null for the other records.

I also have a staff table, which also has a location field (staff working location). I also need the location table because it has many other fields that I need (postcode/building size/etc).

This is the data model that is created by QV with synthetic keys:

As you can see in the simple pivot table, If I try to get pay by firstname and amount, it doesn't work.

I have reviewed many documents and sample regarding link tables, but the difference is that my second fact table is also a dimension table.

Please help, I can feel my brain melting!

Re: Data Model Problem with Mixed Fact/Dimension table

Anonymous — Mon, 17 Mar 2014 23:14:11 GMT

Hi,

Please attached file.

Thanks,

JaswantC

Re: Data Model Problem with Mixed Fact/Dimension table

shanemichelon — Mon, 17 Mar 2014 23:24:55 GMT

Thank you for your incredibly fast reply! However there is a problem with the solution. In the data there is a hazard for location 100 (Head Office). If you Create a list box for locations and select Head Office, it excludes the Hazard. Ie Hazards are not tied to locations:

Ideally, I would like to create a dashboard table that shows hazards by location and amount paid to staff by location. Eg:

Location No Hazard Payroll Total

Head Office 15 $4,000

Branch Office 4 $1,000

At the moment, it looks like this:

Re: Re: Data Model Problem with Mixed Fact/Dimension table

Anonymous — Mon, 17 Mar 2014 23:46:19 GMT

Hi,

Please find updated one.

Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

shanemichelon — Mon, 17 Mar 2014 23:52:43 GMT

Thanks again for your assistance. However, the table still does not link hazards to locations:

I have attached your solution with a pivot table showing the summary info.

Re: Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

Anonymous — Tue, 18 Mar 2014 16:57:06 GMT

Hi,

Can you check again my previous post?

Here again i am attaching same file with additional metric what you are looking.

Re: Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

shanemichelon — Tue, 18 Mar 2014 22:42:15 GMT

Thanks again for your continued assistance.

However, I think I made the example a little too simple. In my actual case, I have other metrics about the Hazards. Such as Hazard type code, description etc. In you sample, you have eliminated the hazard table and not added it to the fact table. How can we accommodate extra hazard info such as a hazard type, hazard description etc.

Re: Re: Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

shanemichelon — Sun, 23 Mar 2014 06:31:10 GMT

I think I have solved this myself. Effectively we need to treat employee plus location as a single combined key. The fact tale will then have a dimension of %EmployeeCodeLocation. When the fact table is populated, build the %EmployeeLocation key as you go. If one of the two bits is not relevant just leave it blank. Then load the link table from this. Create one record for every record in the fact table. Simply use SubField to break the %EmployeeLocation filed into the two parts to link to staff and locations dimension tables.

Here is the new data model:

Here is the UI view:

I have attached the solute for reference.

Thanks and I hope this is useful to someone.