Forum Discussion
Data model for Search terms in web
Hello everyone,
First of all thanks a lot for your support.
At this moment I have a challenge with a dashboard to analyze Search Terms in the web, I have this situation:
Table 1: Search Terms, wich cotains all search terms.
Table 2: Category, wich contains a categorization for every search term.
Table 3: Sub Category, wich contains a subcategorization for every search term.
Table 4: Keyword, wich contains a especial type de clasification for every term.
The thing is, every Search term can belongs to multiple Keywords, multiple Categories and multiples Subcategories. The relationship between them is many to many, for example, the next relationship SearchTerm * : * Keywords is many to many.
My question, is there a solution without add to the model bridge tables for every relationship? My concern is the performance.
Regards!
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.
6 Replies
- Greg_Deckler
Community Champion
jesusbritog Power BI supports many-to-many relationships. Also, why not have all of that information in a single table perhaps? Sorry, not entirely sure what you are doing. Why would you list search terms multiple times? Seems like they should be unique in the Search Terms table. You other tables should then be related on these unique search terms.
Search Terms:
One
Two
Three
Keywords
One, Keyword1
One, Keyword2
One, Keyword 3
Two, Keyword 1
Two, Keyword4
...
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.
Excelent! I did and it's working!
Thanks!
