Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Johnsnowlife
Helper III
Helper III

Best Practice for equating multiple client names to a single client name

My data comes from different sources and in each source the names for my clients are different. So in my fact table name column I could have "Mr John Doe", "Mr J Doe" and "John Doe" but they all represent the same person.

 

Currently, I have a ClientNameMap table where ClientNameAlias is the primary key which looks as follows:

KeyClientNameClientNameAlias
1John DoeMr John Doe
2John DoeMr J Doe
3John DoeJohn Doe

 

Then I have an additional lookup table for ClientInfo about the client. 

KeyClientNameCityCompany
1John DoeLondonBig Company
2Jane SmithMiamiSmall Company

 

Should I relate ClientInfo[ClientName] to ClientNameMap[ClientName] ? 

Client Name Relationships 1.JPG

 

Or should I relate ClientInfo[ClientName] to ClientNameMap[ClientNameAlias]?  

Client Name Relationships 2.JPG

 

In both cases I then relate ClientNameMap[ClientNameAlias] to Sales[ClientNameAlias] 

Client Name Relationships 1-2.JPG

 

Then I merge Sales with ClientNameMap on the ClientNameAlias column and expand ClientName so that if I want to display a table I can display the official ClientName and not all the different aliases. 

 

Does this make sense? How should I be dealing with the different names for a single client? 

1 ACCEPTED SOLUTION
BeemsC
Resolver III
Resolver III

I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.

 

With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.

View solution in original post

1 REPLY 1
BeemsC
Resolver III
Resolver III

I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.

 

With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.