Forum Discussion
Data model for Search terms in web
jesusbritog That's a little more clear. One approach would be to remove your current bridge tables and include Product, Keyword and Subcategory columns in your Search Terms table. That would then mean that you would have the same search terms in your Search Terms table multiple times. You could even have Search term and "Thing" where you list each keyword, category and subcategory associated with the search term as separate rows. Your other three tables woudl simply relate on that "Thing" column. Then you only have a single many-to-many relationship between your search terms table and your fact table. That's not the end of the world, Power BI can handle many-to-many but if you want to eliminate it then you would simply put in a Search terms bridge table that consists only of distinct search terms. That would cut your total tables down from 8 to 6.
Hi Greg_Deckler ,
Thanks for your answer, I have this:
From endpoint the data coming is like the last table, and I thought to separate the column "groups" in four tables in a model in power bi, but I don't know if it's the best approach to this. For each Search Term could have multiple Keywords or Category Or Subcategories, but, is this the better solution considering the fact table with 20.000.000 aprox.
Again, I appreciate your support.
jesusbritog That's a little more clear. One approach would be to remove your current bridge tables and include Product, Keyword and Subcategory columns in your Search Terms table. That would then mean that you would have the same search terms in your Search Terms table multiple times. You could even have Search term and "Thing" where you list each keyword, category and subcategory associated with the search term as separate rows. Your other three tables woudl simply relate on that "Thing" column. Then you only have a single many-to-many relationship between your search terms table and your fact table. That's not the end of the world, Power BI can handle many-to-many but if you want to eliminate it then you would simply put in a Search terms bridge table that consists only of distinct search terms. That would cut your total tables down from 8 to 6.