I have 2 tables relating to insurance ClAIMS and PREMIUMS. Unfortunately we do NOT have data at POLICY level which would have been ideal as we will then have a nice 1-to-many relationship to do all our reporting, agreegations etc.
Instead we have just 2 tables that store data at different levels of granularity - I have colour coded all the equivalent fields from each table.
Common sense told me to create a composite key comprising Agent+Year+Use+Region+Category which DOES create an association between the tables but leads to not only erroatic results but also erratic behaviour.
One such example of erratic behaviour is a pie chart for example where:
The pie chart may look accurate and functional but it does not allow me, for example, to click on any of the pie segments to select a category. I am assuming that this is because it is getting in some kind of cyclical or circular reference. Most bizarre.
How would you deal with this granularity issue where my PREMIUM data is made available in SUMMARY form and the ClAIMS data in DETAIL form and are being deprived of the single field that would form the connector in all this, the PolicyNo?