Modelling Query

[dantheram]

Hi all

I have been wrestling with my data model and have run up against the following sticking point.

So, as you can see, i've stuck to the star schema and tried to get all the key dimensions in there and related in one to many relationships.

My issue is with the 'incident categories' dimension table and the different levels of granularity in the 2 fact tables. So, the 'Fact' table on the left is joined to the 'Incident Categories' dimension via 'Incident Reason', of which there is a unique list in the 'Incident Categories' dimension table. However, the 2nd fact table, 'Daily Targets Test', does not have the field 'incident reason', instead it has the next level up which is 'incident category' - this rolls up the reasons into parent categories. This leaves me with no way of creating a one to many relationship between the 'Daily Target Test' and 'Incident Categories' tables, thus meaning i cannot use the 'Incident Categories' table in my reports to show actuals V target.

I hope this makes sense and assume there is a way round this? 

[TheoC]

Hi @dantheram 

Can you use LOOKUPVALUE to return the values you require to the Daily Target Test table or from Daily Target Test to the Incident Category table?

Thanks heaps,

Theo