Data at different levels of Granularity

[alexis]

Hi all,

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:

Dimension: Premium.PremiumCategory

Expression: Sum(Premiums.NetPremium)

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?

Thanx

Alexis

[Gysbert_Wassenaar]

Perhaps this blog post can help you find a way to deal with your problem.

[alexis]

Thanks Gysbert.

I saw this many months ago but now it's more relevant then ever!

regards

Alexis

[alexis · Accepted solution]

In the end with the help of the very helpful Nicolas Martin we resolved this using concatenation of the tables.