Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
We have different products sold through different channels with one product per channel. Each channel has their own unique identifier for the end client but they are different across channels.
In my database I need to map the different channels' unique identifiers to our unique identifier for the end client so we can see who is purchasing multiple products through different channels.
So I have one table, ClientInfo, with a list of the end clients and our unique identifier, OurUniqueID.
OurUniqueID | ClientName |
1 | Sherlock Holmes |
2 | John Watson |
Now, I need a table to map the different channels' unique identifiers to our unique identifier. Should I do that in a tall, narrow table with a row for each channels' unique ID (see below) or a short, broad table with a column for each channel which will necessarily have many null entries for the channels where the client doesn't have an ID.
Tall, Narrow table:
ClientMapKey | ClientName | OurUniqueID | ChannelID |
1 | Sherlock Holmes | 1 | 123 |
2 | Sherlock Holmes | 1 | 800175 |
3 | John Watson | 2 | 987 |
4 | John Watson | 2 | ABC456 |
Short, broad table:
ClientMapKey | ClientName | OurUniqueID | Channel1ID | Channel2ID | Channel3ID |
1 | Sherlock Holmes | 1 | 123 | 800175 | null |
2 | John Watson | 2 | 987 | null | ABC456 |
Solved! Go to Solution.
It's better to create a table like your first one.
ClientMapKey | ClientName | OurUniqueID | ChannelID |
1 | Sherlock Holmes | 1 | 123 |
2 | Sherlock Holmes | 1 | 800175 |
3 | John Watson | 2 | 987 |
4 | John Watson | 2 | ABC456 |
Since each client may have dynaic number of Channels, it's not a good practice to make one column for each Channel. You can hardly analyze fact data on Channel level. Please refer to: Third normal form
Regards,
It's better to create a table like your first one.
ClientMapKey | ClientName | OurUniqueID | ChannelID |
1 | Sherlock Holmes | 1 | 123 |
2 | Sherlock Holmes | 1 | 800175 |
3 | John Watson | 2 | 987 |
4 | John Watson | 2 | ABC456 |
Since each client may have dynaic number of Channels, it's not a good practice to make one column for each Channel. You can hardly analyze fact data on Channel level. Please refer to: Third normal form
Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |