Forum Discussion
Need Help with Table Relationships
Thanks for the quick response!
#2 - The current relationship is on week. Both week and date exist within the fact_sales database; I opted to load sales data by week instead of date under the impression that it's a smaller dataset to import (and visually I'd only be showing sales by week). Does this have any implications in connecting fact_sales with fact_webMetrics through visual filters applied on dim_date, seeing that the fact_webMetrics dataset only has date data and not week?
#3 - Store promotion data is currently imported from a separate excel file - would I just be importing this, and merging data to both fact_sales/webMetrics tables through merge query based on matching ArticleKey and Week? In this case the store promotion dates are only on week basis so a bit confused as to how I'd link this to the fact tables as well (i.e. fact_sales has week but webMetric doesn't)
#4 - dim_region table looks like below, where SalesDBCountryCode is the relationship to fact_sales and CountryISO is the relationship to fact_webMetrics. Both are unique to their respective fact tables and not natively available in the other. SalesDBCountryCode is straightforward as the values are unique and identifies country-channel. This however is not available in fact_webMetrics, with the only identifier being CountryISO and the table only contains Online channel information (i.e. no data should show up if I filtered for sales data for Store). I wanted to set up visual filters by CountryName and Channel, hence building the dim_region table as such. Is there a better way around it?
| SalesDBCountryCode | CountryISO | Channel | CountryName |
| US-S-001 | US | Store | United States |
| US-O-105 | US | Online | United States |
| AU-O-502 | AU | Online | Australia |
| AU-S-572 | AU | Store | Australia |
#2 - If you never care to do any analysis on date, aggregating at the weekly level is fine. Aggregating at different levels between the two tables will have no impact because your dimension table has both Date and Week. However, that begs the question: Why not aggregate your web metrics by week too?
#3 - Can you not add week to the web metrics table, even if you're not aggregating at that level?
#4 - Ah I see. I think it's not ideal but not as problematic as normal because your many-to-many relationship is single direction and between a dimension and a fact table. One possible way to change it to a one-to-many relationship is to bring in SalesDBCountryCode to the web metrics table (presumably it doesn't matter which one) to have the relationship on the unique SalesDBCountryCode column to both fact tables.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)