Data Model Problem with Mixed Fact/Dimension table

[shanemichelon]

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!

[Anonymous]

Hi,

Please attached file.

Thanks,

JaswantC

[shanemichelon]

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:

[Anonymous]

Hi,

Please find updated one.

[shanemichelon]

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.

[Anonymous]

Hi,

Can you check again my previous post?

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

[shanemichelon]

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.

[shanemichelon · Accepted solution]

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.