Fact / Dimensional Table Approach

[vkedd]

Hey Folks,

Trying to create a quick Power BI dashboard to show some folks - having a little trouble on determing the right approach for the Dimensional / Fact tables. I started with a spotify data table where it listed song metadata and various ratings. I decided that the best approach was to split this into 3 tables - 1 fact table and 2 dimensional tables (potentially one additional bridge table)

Would someone provide some needed input on how they would handle this / if I am taking the right approach for the data I have.

Approach 1:

Slicers on the various visuals appear to work fine - however including the fields from BOTH dimension tables will break the visual unless one of the dimension fields is using summarization. Not a big issue as these Dim tables would generally be used to filter the fact table and not included in tables / visuals.

Note: I can change the relationship to many-to-many and it will provide same results as approach 2

Approach 2:

This appears to work better than Approach 1, I can use fields from every dimension table on a visual without a relationship error. This uses a bridge table to connect to the fact table. Once again, in this scenario, the artist and genre fields would only be used on the slicers since the original fields were delimited.

Snippet of source data:

Happy to elaborate or further explain setup / goal.

[v-lgarikapat]

Hi @vkedd ,

Thanks for reaching out to the Microsoft fabric community forum.

@KNP , @d_m_LNK , 

Thanks for your prompt response

I wanted to follow up and confirm whether you’ve had the opportunity to review the information  provided. Should you have any questions or require further clarification, please don’t hesitate to reach out.

If you are still encountering any challenges, we would be more than happy to assist you further.

Thank you for being an active member of our community.

Best regards,
Lakshmi Narayana

[v-lgarikapat]

Hi @vkedd ,

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @vkedd ,

As we haven’t heard back from you, we are closing this thread for now. If you are still experiencing the issue, please feel free to create a new thread we’ll be happy to assist you further.

Thank you for your patience and continued support.

If you found our response helpful, kindly mark it as Accepted as Solution so that others with similar queries can benefit from it.

Best regards,
Lakshmi Narayana

[d_m_LNK]

To me the song table is it's own dimension and the fact table would be more along what songs were played over a period of time.  The songs themselves are really dimensions and the plays of the songs would be the facts for this data in my opinion.  

[KNP]

Your Artist and Genre dimensions should join fact tables 1 --> many. (Always 1 to many from dimension to fact)

I'm not sure I would have a Track dimension but I've not explored Spotify data, so I don't currently know what options are available for joins, i.e. is there an artist_id in the data.

[vkedd]

How would that one-to-many dimension table apply here though? A genre dimension table could never be a one-to-many as songs can have multiple genres.... same thought process for artists.

Maybe this cannot be set up in traditional star-schema format as the fact table does not have duplicate IDs - just one entry per song.

[KNP · Accepted solution]

I see what you mean.

I would look into separate bridge tables for bridge_track_artist (join on artist_id or artist) and bridge_track_genre (join on genre_id or genre). May require additional modeling work.

This would get rid of many to many but honestly, I'm not sure how much benefit you'll get from this, outside of knowing you've built a "best practice" model.