Forum Discussion

BOBALENIS's avatar
BOBALENIS
Frequent Visitor
5 years ago

data modelling

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

 

2 Replies