I have a fact table where each row contains different data at a local authority level. There are therefore many rows regarding the same local authority. I then have a dim/lookup table where I list a unique list of local authorities, so I can use this to slice my data. All great so far. Now I have a table that lists the local authority in one colum and in the next it lists all the other local authorities that are considered statistcally similar.( stat neighbours, see below).
e.g
LA Name Stat Name
Waltham. Eastbourn
Waltham. Hasting
Waltham. Borough
Wirral Eastbourn
Wirral Leeds
etc
OUTCOME: I would like to select a LA Name and the fact table is filtered according to the stat Names so I can show this data as a comparison for the LA Name - you might like to consider them as benchmarks, which are there to help the user undertsand if the selected LA Name is doing well or not
PROBLEMS: For each Local Authority you have many other local authorties that are stats neighbouts (up to 10 ish) Also, each local authority could be in one or more different stat neighbour groups.
So using the example above the local authority 'Eastbourne' is a stat meighbour of both 'Waltham'. and 'Wirall' above.
Solution 1. is to create a many to many relationship between the stat neighbour LU/Dim table and the fact table. NOT IDEAL as you would prefer a 1 to many relationship here
Solution 2. You can use the LA dim/LU to create a bridge table between the stat neighbout and the fact table- this has limited success and you need to add bi directional flow- NOT IDEAL as you would ideally have the flow in one direction only
Is there a nicer solution that I am missing?
THANKS SO MUCH
