cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BOBALENIS
Regular Visitor

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 2
amitchandak
Super User IV
Super User IV

@BOBALENIS , Based on what I got. I think multiple join with authority table to fact. with one relation inactive and activate that using userelationship

 

refer :https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Morning and wow- thank you so much for replying so swiftly. 

 

I am not sure I fully understand your idea but do you mean something like this? Thanks again

 

BOBALENIS_0-1618996909426.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors